How to Easily Prepare Database Import Using Perl

I’m revisiting this topic because in my last post about translating Excel data into a text file for database import I used a php script and yet still had some manual steps (the one that bugged me most was adding custom line termination characters for multiline data). I mentioned the idea that if I wanted to put more time in I could use a Perl script to do the whole process. Well my next set of data I needed to import was almost 700 records spanning almost ten times as many lines in the tab delimited text file I exported from Excel (due to the description fields having newline characters). So here’s my latest solution:

  1. First I added a numeric column in the left-most position (within excel) and filled it with incremental integers.
  2. Then I exported the spreadsheet as tab delimited text
  3. Then I wrote a perl script to loop through the file and add line termination characters before each new record, this time I had to use double equal signs (==) because the data contained single equal signs
  4. I also found a perl module similar to the php htmlentities() function which actually worked for me, as opposed to the php function which I abandoned in favor of hard-coded string replacement commands
  5. Then I ran the perl script on my tab delimited text file to create a tab delimited file with double equal signs as the line terminator and all the funny characters encoded so they would show up correctly in a browser
  6. Finally I imported my file using the load data infile option in the phpmyadmin interface, specifying the delimiter and line termination as \t and == respectively.
  7. Then I looked at a webpage which pulled that data and it looked like it did in the excel file!

Here is the code for the perl script I created:

#!/usr/bin/perl
# Filename: prepareTextImport.pl - this script will take a list of data 
#	including multiline data where the first field is a line count.
#	It will add == before each line count to enable mysql importing and 
#	will encode html entities and convert newline characters to <br>
use HTML::Entities;

my $input = $ARGV[0]; #this is the file to parse
my $output = $ARGV[1]; #this is the file to create
my $lineTerminator = "==";
my $count=1;
my $line = '';
open (IN,$input) or die "can't open file $input\n";  
open (OUT, ">$output") or die "Can't open outfile : $output\n";
while (<IN>) {	# each line of the current input file
	chomp;
	$line .= "<br>"; # include this here so the <br> will be encoded too
	$line = encode_entities($_); #This is the function which encodes html symbols
	# Check if this is the beginning of a new record.  If so then add the line terminator
	if ($line =~ /^$count/) {
		$count++;
		$line = $lineTerminator.$line;
	}
	print OUT $line;
}
close IN;
close OUT;
print "there were $count records processed.\n";
exit;

The syntax for running the script (assuming perl is installed on your system – I use activeperl on my PC which is free):

perl prepareTextImport.pl inputFileName.txt outputFileName.txt

How to Export One Table Structure in Mysql and Infobright

I have a database with tables in it which have over 300 fields each. I wanted to create a new table like the others but I didn’t have the table structure saved in a file. Due to the number of fields it was impractical to copy the screen after executing a “show create table tablename” command in that database, so I had to find another way.

I found that I could use the mysqldump command to export just the table structure, and this command would probably have worked if my database engine was regular mysql:

# /usr/local/infobright/bin/mysqldump -d -u adminUserName -p'adminPassword' dbName tableName > file.sql
mysqldump: Got error: 1031: Table storage engine for 'BRIGHTHOUSE' doesn't have this option when doing LOCK TABLES

So I looked up how to avoid the lock tables error and found the single transaction flag which made it work in Infobright (which is based on mysql but has some differences):

# /usr/local/infobright/bin/mysqldump -d -u adminUserName -p'adminPassword' --single-transaction dbName tableName > file.sql

How to Convert Excel Spreadsheets to SQL Statements

Last week I told a client he could send me a file in excel and I could import that into the mysql database directly. When I got the file I learned that it wasn’t quite that easy. I had planned to just save it as tab delimited text or CSV, but there was a “description” field which was copied from various webpages (by copying the rendered text in the browser window).

Within the description field there were trademark symbols, copyright symbols, slanted quotes, triple dots, non-standard dash marks, bullets, and even a cross symbol (or dagger) used for footnotes. In addition, there were several paragraphs (including multiple line breaks) in each description field.

The first challenge to overcome was getting the database to handle line breaks properly so I could import the data. I figured if I added a special character before each new record then I could cause the database to ignore the newline characters, but when I tried pipe (|) and three pipes (|||) neither of those worked (I only got one record imported each time). It finally worked for me when I added an equal sign (=) before each new record (after checking to make sure there were no equal signs in the text – if there had been it should have worked by escaping them with a backslash and telling mysql that is the escape character).

In order to insert the equal signs I first saved the excel sheet as tab delimited text, then opened it in notepad++ and used their regular expression search and replace tool to do the insertions quickly. Since I had numbers in the first field of my data, I searched for ^(\d) and replaced it with =\1. If I wanted to spend more time on it I could have made a perl script to go straight from the .txt file to sql statements, but I already had a php file to create sql statements so I wanted to use that.

Once I had the equal signs in place, I used a load data infile command (actually created by phpmyadmin, I just had to make the selections – the local keyword was necessary for permission requirements) like this:

LOAD DATA LOCAL INFILE 'path-to-text-file-here' INTO TABLE 'myTableName' FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '=';

I was happy to get the lines imported, but the line breaks were lost and special characters showed up as question marks on the webpage where I displayed the data. So then I went back to my php script used for going from access to mysql from a few years ago and made a few modifications. I first tried using the php htmlentities() command to convert the symbols back to html code but for some reason that didn’t work for me. So through an iterative process I added replace commands for each symbol I found. I wasn’t trying to create all the symbols but to preserve the essence of the text. If you want to convert all the symbols you find you can look at a w2schools reference symbol page for a list – there is also an entity list at w3schools, if you don’t find your symbol in the first list. I also wanted to preserve the newlines so I used the php command nl2br and that worked great. Here is the final code I added for processing each field:

$data_value = trim($data_value);
$data_value = trim($data_value,'"');
if (empty($data_value)) continue;
$data_value = str_replace('™','&amp;amp;trade;',$data_value);
$data_value = str_replace('®','&amp;amp;reg;',$data_value);
$data_value = str_replace('’','\'',$data_value);
$data_value = str_replace('…','...',$data_value);
$data_value = str_replace('•','-',$data_value);
$data_value = str_replace('–','-',$data_value);
$data_value = str_replace('†','*',$data_value);
$data_value = str_replace('“','"',$data_value);
$data_value = str_replace('”','"',$data_value);
$data_value = str_replace('—','-',$data_value);
$data_value = nl2br($data_value);

I also couldn’t just copy the sql queries from the webpage because all the html code was converted back to symbols, so I added this line to write the queries directly to a local file:

file_put_contents("queries.txt",$myQueries);

Then I could open the queries.txt file and have a bunch of sql queries which I could run to populate my database table in mysql. My updated text to sql php script can be downloaded and used freely.

Update: I put the time into creating a perl script to make this easier, so take a look at that if the above process is too much work for you. 🙂

How to Add the Sidebar to WordPress Twenty Eleven Theme Single Posts

This is my first post since splitting off my technical posts into a new blog focused in that area. Part of the process was setting up a new wordpress blog, and of course I wanted to try a new theme, the Twenty Eleven theme. After I began importing a few posts I realized that there was no simple option to add the sidebar to the single post view. I tried messing with the CSS for a few minutes and then realized that there must be plenty of answers from people about this issue already.

I found one that looked promising at http://tricksmommy.com/2011/07/08/add-sidebar-in-single-post-pages-of-twenty-eleven-wordpress-theme/. Unfortunately after following the detailed instructions there the single post page had the sidebar added but the main index page was shifted off to the right. I couldn’t figure out the exact reason why, but the instructions mentioned copying css from another view so I figured it must be a different setup or something. I chose the left sidebar on my site whereas they used the default with the sidebar on the right, so that might be why it didn’t work for me. After removing those changes I came up with a simpler solution (step 1 is the same as the site mentioned above, step 2 is where we differ):

  1. Go the the Theme Editor and select the Single Posts(single.php). Search for the following line:
    <?php get_footer(); ?>
    

    Just above it place the following code:

    <?php get_sidebar(); ?>
    
  2. In the same file, look for the following code:
    <?php get_template_part( 'content', 'single' ); ?>
    					
    <?php comments_template( '', true ); ?>
    

    You’ll want to add another div around the above two lines and give it a new class name so you can set the positioning, like this:

    <div class="singlePostWrapper">
    <?php get_template_part( 'content', 'single' ); ?>
    					
    <?php comments_template( '', true ); ?>
    </div>
    
  3. Next you’ll open the style.css and add a simple definition for the new css class you created above as a wrapper around the post and comments on the single post pages. You can add this at the end of the file:
    /*Allow room for sidebar in single posts*/
    .singlePostWrapper {
      position:relative;
      left:25%;
      width:75%;
    }
    

    Note that the above class definition works well for the sidebar on the left, but if you chose to put the sidebar on the right in the theme settings you can change the word ‘left’ above to ‘right’ and it should work fine.

How to add special shipping by product in Zen Cart

originally posted at http://www.goodfeelingplace.com/how-to-add-special-shipping-by-product-in-zen-cart
I am in the process of migrating a static site (peacemonger.org) to a database driven site “powered by” zen cart. One of the features requested was the ability to add special shipping charges for odd shaped items, like posters.

After doing some research I discovered a page with instructions at http://tutorials.zen-cart.com/index.php?article=190, but it took me awhile to figure out how to implement them for the specific situation I was targeting. After reading the instructions a few times I realized that I had to really create an extra read-only text attribute to make my “special shipping” attribute be selected (as a single radio button) by default. Here’s what it looks like in the admin panel:

zencart attribute controller legend
zencart attribute controller, showing the options to add a required attribute to a product

And here’s what it looks like on the product page (the radio button is selected by default now):

zencart shipping example

Though I just realized that the order is the opposite of what I want, even though I specifically set the order as noted above. Does anyone know why I can’t set the order in this way?

Update: I found a thread which helped me get the attributes sorted at http://www.zen-cart.com/forum/showthread.php?t=87470. The specific change I made which worked was to set the sort order for the attribute names, under Catalog -> Option Name Manager (they were all set to sort order 0 on that page before).

The Essence of AJAX – How I learned it in an hour

originally posted at http://www.goodfeelingplace.com/the-essence-of-ajax-how-i-learned-it-in-an-hour
I was a bit resistant to learning ajax for a couple years because I didn’t see a need for it in the programs I was developing.  However, one day I learned it without knowing it.  I was working with code written by someone else which called a javascript function that invoked a url, then called another function to handle the results of the url.  An example that looks very similar to the code I was working with is at http://www.captain.at/howto-ajax-form-post-request.php (unfortunately that page no longer exists, but I’ve made my own example that you can see at http://www.wilycode.com/simple-ajax-auto-complete-example/ – the details below refer to the not found page but the concepts are the same).

If you look at that code, there are 3 javascript functions used – get (this is called from clicking on a button in the form on the page), makePostRequest (this is the function which calls the url and sends parameters to it via the post method), and alertContents (this function is called from the makePostRequest function when the response is received from the url).

The get function just creates a string with parameters to pass, defines an url, and then passes them to the makePostRequest function.  Here are the main lines of code in the makePostRequest function and what they mean:

  • http_request.onreadystatechange = alertContents; – this says when the http_request.readyState changes, then call the alertContents function.
  • http_request.open(‘POST’, url, true); – this defines what url is going to be called and what method is going to be used
  • http_request.send(parameters); – this sends the parameters specified in the post array to the url requested

There are a few other lines specifying a the header of the request, and a bunch of lines in the beginning that setup the http_request object to work for different browsers.

Then in the alertContents function (which is actually called several times, whenever the readyState changes), once the readyState of 4 is reached (you can read more about readyStates at http://www.yaldex.com/wAjax/DiggingdeeperintoHTTPreadystates.html) the http status is checked.  If the status is 200 (and not 404, 501, 301, etc. – see http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html for a full list with explanations) then the result of that http request (like what you would get if you put that url into a browser address bar and sent the appropriate post parameters in the header – done through a script, not a browser – then checked the page source) is stored into a javascript variable and you can do whatever you want with it.

In that example they just stick it into a span element on the existing page, but you can do many other things.  If you invoke an url which takes as parameters the values of certain form elements on the page, you could return a list of values to populate another dropdown with, etc.  So you can take the example and modify the parameters passed, what the invoked url does, and what is done with the results – then you will have your own ajax function!

How to import and export large datasets in mysql

originally posted at http://www.goodfeelingplace.com/how-to-import-and-export-large-datasets-in-mysql
I’ve seen many questions around the importing and exporting of large tables and/or databases in mysql, so I’d like to share how I’ve learned to manage those processes.  Using phpmyadmin for these tasks is not suitable because web servers are not designed for running large scripts or uploading/downloading large files.  You can changes the limits in phpmyadmin but there are several different limitations in that environment which you may run into.

For exporting large databases you can use the mysqldump command from a prompt (unix shell or dos prompt).  For exporting large tables, I login to mysql and use a query with the syntax “SELECT column1name, column2name, column3name, … FROM myDatabaseName.myTableName INTO OUTFILE ‘/path/to/outfilename.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\” ESCAPED BY ‘\\’;” – this gives you a csv file in the location you specify with the fields surrounded by single quotes, separated by commas, and escaped by a backslash.  The only thing that needs to be escaped in this context is a single quote.

For importing large databases created from mysqldump or phpmyadmin, I save the sql dump file into a local directory, cd to that directory (in unix shell or windows dos), login to mysql from the command line, then type the following command: source dbdump.sql (or whatever the dump file name is with your db data).

For importing large tables that were exported using the export syntax I mentioned above, I login to mysql and then type something like “LOAD DATA INFILE ‘/path/to/outfile.csv’ INTO TABLE myDatabaseName.myTableName FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\” ESCAPED BY ‘\\’;”

Free MDB file to MySQL Conversion

originally posted at http://www.goodfeelingplace.com/free-mdb-file-to-mysql-conversion
This week I created a pretty useful tool I’d like to share. I was given an MS Access file (.mdb) and I needed a way to transfer the structure and the data into a MySQL database. I don’t have MS Access and I have no plans to purchase it, so I did a lot of searching to find free tools for getting at the data.

I found a few ways that worked ok, two ways that did what I needed and one that I liked the best. The one I liked the best was MDB Viewer Plus, an exe file that doesn’t need to be installed – you can get the latest version at http://www.alexnolan.net/software/mdb_viewer_plus.htm. I got the 1.5.1 version from another site found through google, but later discovered that I couldn’t get into an mdb file that required a password using that version. Once I went to the source of the program I got the 1.5.3 version and the password authentication worked great. The other program that worked fairly well was called Open Office Base, but exporting the data proved a bit tricky, as I had to first create reports that queried the tables before I could save them as text. I liked MDB Viewer Plus better because exporting a table is very easy, you can do it by clicking a tab with the table name you want to view it, then click the “Export Table” button to export it in one of nine different formats.

So once I got the tables exported from the mdb file into text files, then I looked around for an easy way to get it into mysql. Unfortunately phpmyadmin requires more than just field delimiters to import csv files, but also some character has to “enclose” every field. I did some more searching and found a simple php script posted on the codewalkers site that converted a type of csv file into a bunch of sql insert queries. This looked promising, so I modified it to ignore empty fields, convert dates into valid mysql date format, take tab delimited text as the input instead of semicolon delimited fields (I don’t know where you would get that kind of csv file anyway), and I added a table creation query with a user definable field type. I had a table with over 100 fields in it that I didn’t want to type out, so this was the script I used to generate the table creation query as well as the insert statements.

Below you will see a screenshot of the script with a small example tab delimited text file that I pasted into the main input box:
Example usage of the conversion script
Download the zipped php file here

So the summarize, here are the steps to follow for this MDB file to MySQL transfer or migration:

  1. Use one of the free programs mentioned above like the MDB Viewer Plus to open the Access file and see what’s in it
  2. Save each table as a TXT file (which happens to be tab delimited when you use the MDB Viewer Plus)
  3. Download the php file I put together and shared above, and put it in the webroot of either a web hosting account you have on a remote server (like Blue Host, which I use for this site) or on your local webserver root. If you are interested in installing a free webserver easily you can try XAMPP, which I use on my PC.
  4. For each table you want to add to MySQL, type in the name and default field type (the type that most of your fields will be, so you will have to manually set as few of them as possible afterwards), and paste the tab delimited text of your table (with column names on the first line) into the big input box.
  5. Push the button at the bottom, and then use the first generated sql statement to create your table
  6. Change the field types that don’t match the default (like an autoincrement integer field for the index, or a few date fields)
  7. Then run the rest of the sql statements to populate your table and you’re done:)

Update: I just figured out how to do something that makes the whole process much easier for windows users. Using php you can connect to just about any database that exists, and query it directly. And for Access, an .mdb file counts as a database. So here’s the code to connect to a local mdb file using php on a windows machine:

$db_connection = new COM(&amp;quot;ADODB.Connection&amp;quot;, NULL, 1251);
$db_connstr = &amp;quot;DRIVER={Microsoft Access Driver (*.mdb)};&amp;quot; .
&amp;quot; DBQ=C:\Path to mdb file\MyAccessFileName.mdb; Password=MyPasswordHere; DefaultDir=C:\DataDir&amp;quot;;
$db_connection-&amp;gt;open($db_connstr);
$rs = $db_connection-&amp;gt;execute(&amp;quot;SELECT * FROM TableName&amp;quot;);
$rs_fld0 = $rs-&amp;gt;Fields(0);
$rs_fld1 = $rs-&amp;gt;Fields(1);
while (!$rs-&amp;gt;EOF) { // loop through the result set from the mdb file
$field1Value    = $rs_fld0-&amp;gt;value;
$field2Value    = $rs_fld1-&amp;gt;value;
$rs-&amp;gt;MoveNext();
// Do something with the data here, like put it into mysql, put it into a file, or print it to the screen
echo &amp;quot;the first field value is $field1Value, the second field value is $field2Value&amp;lt;br&amp;gt;&amp;quot;;
}
$rs-&amp;gt;Close();
$db_connection-&amp;gt;Close();

You can read more about it in the PHP manual at http://us3.php.net/manual/en/function.odbc-connect.php

How to enable SOAP on a LAMP server

originally posted at http://www.goodfeelingplace.com/how-to-enable-soap-on-a-lamp-server
I wanted to use the MSN search API on a VPS (Virtual Private Server) I have setup for a client, but the existing CentOS 5 operating system only had PHP5.1.6 and I needed PHP5.2.1 (at least) with SOAP enabled for the search API to work. So I downloaded all my code files, created a dump of the MySQL database (I had to use mysqldump from the command line for this, as the DB was 147Mb, way over the limit for phpmyadmin to export) and downloaded the db dump. Then I re-imaged the server using the OpenSUSE 10.3 (with plesk) operating system, which I was told would have at least PHP5.2.4 on it by the 1and1 server support person I spoke with.

After the re-imaging process was done (it takes about an hour), I used plesk to easily recreate my accounts, domain, database, and scheduled job. Then I used winscp to upload my gzipped database dump and the code files, logged into the newly created db from the command line through ssh and used the command “source dbdump.sql” from a mysql prompt (and from the same directory as where I gunzipped the dbdump.sql file) to populate the large database in under a minute. I also had to change the ownership of my webroot and everything in it to “default”, since that’s the name of the user I have running my scheduled job which reads and writes to the server. One requirement of running php in safe mode is that scripts can only read and write to files/directories owned by the same user who is running the script. There’s probably a way for me to turn off the PHP safe mode but the workaround was easy so I haven’t messed with it.

Once I got everything put back together I checked the phpinfo page and I was pleased to see that I now had php5.2.6 running. But when I tried using MSN search it still didn’t work, because SOAP wasn’t enabled. Then I did a bunch of searching and found several people in similar situations but I didn’t find any good answers. I tried to update the php configuration using yum and apt-get, but those commands were not on my system. Then I found an RPM file (from http://rpm.pbone.net) that contained a soap.so file in it, but when I tried to install it I got the message “rpmlib(PayloadIsLzma) <= 4.4.2-1”, which I learned was some kind of dependency that wasn’t met by my system. I was starting to get frustrated and the kids were playing kind of loud behind me, so I decided it would be best to take a break and try to feel better about it.

So I turned around and saw all 5 of my kids jumping on the couch. It was our basement couch so I didn’t mind, but I began to feel better and grabbed my camera. I started taking pictures of them and showing them the pictures, which they enjoyed, so we all had a good time. Here’s one with all of them jumping on the couch:

It reminded me of the children’s book, Eight Little Monkeys Jumping on the Bed. The last page (after they all fall off and bump their head and the doctor says “No more monkeys jumping on the bed!”) has them all on the couch and says “But no one said anything about jumping on the couch!”. So later after putting the children to bed I was feeling better and got an idea.

I remembered reading a post which suggested going to the specific operating system home page to get the appropriate version of the rpm file needed, so I searched for the opensuse home page and found http://software.opensuse.org/search, where I just chose my operating system from the dropdown and searched for soap. Actually they have many operating systems there, including RHEL, CentOS, Debian, Ubuntu, openSUSE, Fedora, Mandriva, and SLES. From the results page I just chose the php5-soap rpm for my version of php (5.2.6) and my system (I’m using a 64 bit system so I chose the x86_64 link, I assumed the other link was for 32 bit systems), downloaded it to my PC, then uploaded it to my server, used gunzip to decompress it, and ran the “rpm -i php5-soap-5.2.6-35.6.x86_64.rpm” command to install soap. This file passed all the dependency checks, then put the soap.so file in the right place on my system, and modified my php.ini file. Then all I had to do was restart the apache service and my LiveSearch sample page (using SOAP to interact with the MSN search engine) worked! By the way, for dedicated servers and virtual private servers I’ve had good experiences with 1and1, their server department is usually very helpful and quick to respond to questions or concerns. For shared hosting, I prefer Blue Host (which I use for this site).

How to allow up and down arrows to navigate your form fields

originally posted at http://www.goodfeelingplace.com/how-to-allow-up-and-down-arrows-to-navigate-your-form-fields
I learned something pretty cool last week. I fielded a request from a client who wanted to be able to use the up and down arrows on his keyboard to navigate a rather large form on the site I created for him. I believed it was possible but I had never done it. So after doing some research I figured out how to do it. Below is a simplistic version (put your cursor in one of the text boxes below and push the up or down arrow keys to move between them):

1-1 1-2 1-3
2-1 2-2 2-3
3-1 3-2 3-3

The HTML for the simple table is as follows:

<table border="1">
<tr>
<td>1-1</td>
<td><input id="ItemCost11" onkeydown="return checkKey(event,3,3,'ItemCost',1,1)" maxlength="30" name="ItemCost11" size="3" /></td>
<td>1-2</td>
<td><input id="ItemCost12" onkeydown="return checkKey(event,3,3,'ItemCost',1,2)" maxlength="30" name="ItemCost12" size="3" /></td>
<td>1-3</td>
<td><input id="ItemCost13" onkeydown="return checkKey(event,3,3,'ItemCost',1,3)" maxlength="30" name="ItemCost13" size="3" /></td>
</tr>
<tr>
<td>2-1</td>
<td><input id="ItemCost21" onkeydown="return checkKey(event,3,3,'ItemCost',2,1)" maxlength="30" name="ItemCost21" size="3" /></td>
<td>2-2</td>
<td><input id="ItemCost22" onkeydown="return checkKey(event,3,3,'ItemCost',2,2)" maxlength="30" name="ItemCost22" size="3" /></td>
<td>2-3</td>
<td><input id="ItemCost23" onkeydown="return checkKey(event,3,3,'ItemCost',2,3)" maxlength="30" name="ItemCost23" size="3" /></td>
</tr>
<tr>
<td>3-1</td>
<td><input id="ItemCost31" onkeydown="return checkKey(event,3,3,'ItemCost',3,1)" maxlength="30" name="ItemCost31" size="3" /></td>
<td>3-2</td>
<td><input id="ItemCost32" onkeydown="return checkKey(event,3,3,'ItemCost',3,2)" maxlength="30" name="ItemCost32" size="3" /></td>
<td>3-3</td>
<td><input id="ItemCost33" onkeydown="return checkKey(event,3,3,'ItemCost',3,3)" maxlength="30" name="ItemCost33" size="3" /></td>
</tr>
</table>

The javascript function used is as follows:

<script type="text/javascript"><!--
function checkKey(event, numRecs, numCols, myName, myRow, myCol) {
  var key = event.keyCode;
  var bSetFocus = false;
  var myId = '';
// if the left arrow has been pressed and we're not in the first column
  if( key == 37) {
  if (myCol-1 > 0) {
  myId = myName + myRow + (myCol-1);
  bSetFocus = true;
  }
  }
  // if the up arrow has been pressed and we're not in the first row
  if( key == 38) {
  if (myRow-1 > 0) {
  myId = myName + (myRow-1) + myCol;
  bSetFocus = true;
  }
  }
  // if the right arrow has been pressed and we're not in the last column
  if( key == 39) {
  if (myCol+1 <= numRecs) {
  myId = myName + myRow + (myCol+1);
  bSetFocus = true;
  }
  }
  // if the down arrow has been pressed and we're not in the last row
  else if( key == 40 ) {
  if (myRow+1 <= numCols) {
  myId = myName + (myRow+1) + myCol;
  bSetFocus = true;
  }
  }
  if (bSetFocus) document.getElementById(myId).focus();
} // end checkKey function
// --></script>

In my code I generate the HTML using PHP so when I pass the number of records to the checkKey function (3 in this example) it is the number of rows returned from a database query, and the current row for any given row of the table is the counter in a loop I use to print the table.

By the way, if you want to allow for the left and right arrows to move left and right in a form, you can do that by expanding the checkKey function to look for key code 37 (left arrow) and key code 39 (right arrow), and you’ll need to implement a column ordering scheme (like the counter I used for the rows) so you’ll know where to take the cursor.

Four Direction Code

(submitted by Mr Arrows below, but the comment fields do not accept code so I put it in the main post. I actually had trouble putting two working code samples in the same post so I just updated the main example above to include the left and right arrows)

What if there are values in each field?

These two javascript functions need to be defined, which will tell you the index of the start of the current selection and the end of the current selection (which will be the same if nothing is selected and you just have a blinking cursor in the input box). They both take as input the same object (called o below) that is passed into checkKey as the last param (called myObj there):

function getSelectionStart(o) {
  if (o.createTextRange) {
  var r = document.selection.createRange().duplicate();
  r.moveEnd('character', o.value.length);
  if (r.text == '') return o.value.length;
  return o.value.lastIndexOf(r.text);
  } else return o.selectionStart;
}
function getSelectionEnd(o) {
  if (o.createTextRange) {
  var r = document.selection.createRange().duplicate();
  r.moveStart('character', -o.value.length);
  return r.text.length;
  } else return o.selectionEnd;
}

Then you need to define some variables inside the checkKey function, to get the current input value and the index of the cursor position within that value:

  var inputVal = myObj.value,
  startIndex = getSelectionStart(myObj);
  endIndex = getSelectionEnd(myObj);

I only use startIndex because it will be the same as endIndex when nothing is highlighted or selected in the input box.

Next you can use a simple regular expression (or any other string function you want) to see if there is any text before (for the left arrow condition) or after (for the right arrow condition) the cursor. For example, this is the code I use when when the left arrow has been pressed:

  var testBC = inputVal.substring(0, startIndex);
  var blankRE=/^\s*$/;
  //alert("beforeCursor is '"+testBC+"');
  // if the regular expression test passes, then there is nothing to the left and we can move cells
  if (blankRE.test(testBC)) {
  var nextField = document.getElementById(columns[colIndex-1]+String(myRow));
  nextField.focus();
  //If we are moving into a select dropdown, restore the previously selected value half a second after it switches to the first or last option (I don't know how to prevent that, so this is my workaround):
  if (nextField.tagName=="SELECT") {
  var cur_ind = nextField.options.selectedIndex;
  var cur_val = nextField.options[nextField.options.selectedIndex].value;
  var cur_text = nextField.options[nextField.options.selectedIndex].text;
  setTimeout(function(){nextField.options.selectedIndex=cur_ind}, 5);
  }
  return false;
  }