Using MySQL, Perl and jQuery to Auto-Populate a Form Field on a Web Page

If you have ever built a form on a web page, you might have used a drop-down menu to display the choices available for a particular field. With a drop-down menu, you restrict the choices a user may select so that the user doesn’t enter invalid data (among other reasons). If a user misspells an entry, then a subsequent search for that value would not produce a found result.

A friend of mine who runs an online forum sent me an email about a problem he was having. He was trying to modify an existing registration web page using jQuery to auto-populate the state names, and then pass the state abbreviation back to his MySQL database. Believe it or not, he was actually having problems with people knowing their own state abbreviation. He had searched and found an example of what he wanted to do, but he couldn’t get it to work. So, I took the example that he found and figured out what he was doing wrong.

I had first suggested that he just hard-code the list of states and their abbreviations in the HTML code. But, he said that if he figured out how to use jQuery with his state abbreviation problem, he would also use it on other parts of his web page where he had dynamic data. With drop-down menus, it is fairly easy to create a list of static data to use in the HTML form. But, in his case, what happens if the data is dynamic, or if there are too many items to list in a drop-down menu? If you have ever had to include your country in a form (and you live in the US), it is always a pain to have to scroll down to the bottom of the long list to find “United States”.

An easy way to create a dynamic list (or even a long static list) is to use jQuery to access your data from a MySQL database, and to build an auto-populated field in your form. When the user starts typing their entry, jQuery will search the MySQL database for a list of “like” matches, and then return a list of all of the matching results for that field.

For this example, we will be using jQuery, Perl and a MySQL database to auto-populate a “state” field in a form. Once a user has typed three characters in the state field box, a list of matching states will appear in a drop-down menu. It will also return the state abbreviation and state id (code), which can then be saved in the MySQL database after the form has been submitted. In this picture from our example, the user has typed “new”, and returned all states that match that string:

First, let’s start by creating the MySQL database that we will use. Here is the SQL code to create the database, which is named “states”. The table will contain the state name, the state abbreviation, and an ID number.

CREATE TABLE `states` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`state` varchar(50) NOT NULL,
`abbrev` char(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Next, you will want to import values into the database. You could do this by importing a .csv file containing this information into MySQL, but to make it quicker for this example, here is the SQL:

insert into states (state, abbrev) values ('Alabama', 'AL');
insert into states (state, abbrev) values ('Alaska', 'AK');
insert into states (state, abbrev) values ('Arizona', 'AZ');
insert into states (state, abbrev) values ('Arkansas', 'AR');
insert into states (state, abbrev) values ('California', 'CA');
insert into states (state, abbrev) values ('Colorado', 'CO');
insert into states (state, abbrev) values ('Connecticut', 'CT');
insert into states (state, abbrev) values ('Delaware', 'DE');
insert into states (state, abbrev) values ('District of Columbia', 'DC');
insert into states (state, abbrev) values ('Florida', 'FL');
insert into states (state, abbrev) values ('Georgia', 'GA');
insert into states (state, abbrev) values ('Hawaii', 'HI');
insert into states (state, abbrev) values ('Idaho', 'ID');
insert into states (state, abbrev) values ('Illinois', 'IL');
insert into states (state, abbrev) values ('Indiana', 'IN');
insert into states (state, abbrev) values ('Iowa', 'IA');
insert into states (state, abbrev) values ('Kansas', 'KS');
insert into states (state, abbrev) values ('Kentucky', 'KY');
insert into states (state, abbrev) values ('Louisiana', 'LA');
insert into states (state, abbrev) values ('Maine', 'ME');
insert into states (state, abbrev) values ('Maryland', 'MD');
insert into states (state, abbrev) values ('Massachusetts', 'MA');
insert into states (state, abbrev) values ('Michigan', 'MI');
insert into states (state, abbrev) values ('Minnesota', 'MN');
insert into states (state, abbrev) values ('Mississippi', 'MS');
insert into states (state, abbrev) values ('Missouri', 'MO');
insert into states (state, abbrev) values ('Montana', 'MT');
insert into states (state, abbrev) values ('Nebraska', 'NE');
insert into states (state, abbrev) values ('Nevada', 'NV');
insert into states (state, abbrev) values ('New Hampshire', 'NH');
insert into states (state, abbrev) values ('New Jersey', 'NJ');
insert into states (state, abbrev) values ('New Mexico', 'NM');
insert into states (state, abbrev) values ('New York', 'NY');
insert into states (state, abbrev) values ('North Carolina', 'NC');
insert into states (state, abbrev) values ('North Dakota', 'ND');
insert into states (state, abbrev) values ('Ohio', 'OH');
insert into states (state, abbrev) values ('Oklahoma', 'OK');
insert into states (state, abbrev) values ('Oregon', 'OR');
insert into states (state, abbrev) values ('Pennsylvania', 'PA');
insert into states (state, abbrev) values ('Rhode Island', 'RI');
insert into states (state, abbrev) values ('South Carolina', 'SC');
insert into states (state, abbrev) values ('South Dakota', 'SD');
insert into states (state, abbrev) values ('Tennessee', 'TN');
insert into states (state, abbrev) values ('Texas', 'TX');
insert into states (state, abbrev) values ('Utah', 'UT');
insert into states (state, abbrev) values ('Vermont', 'VT');
insert into states (state, abbrev) values ('Virginia', 'VA');
insert into states (state, abbrev) values ('Washington', 'WA');
insert into states (state, abbrev) values ('West Virginia', 'WV');
insert into states (state, abbrev) values ('Wisconsin', 'WI');
insert into states (state, abbrev) values ('Wyoming', 'WY');
insert into states (state, abbrev) values ('American Samoa');
insert into states (state, abbrev) values ('Guam', 'GU');
insert into states (state, abbrev) values ('Northern Mariana Islands', 'MP');
insert into states (state, abbrev) values ('Puerto Rico', 'PR');

Now that we have the database, we will need to create the Perl script. Here is the Perl script, and you will need to change the database variables to match your system:

#!/usr/bin/perl -w
use CGI;
use DBI;
use DBD::mysql;
use JSON;

# print the http header specifically for json
print "Content-type: application/json; charset=iso-8859-1\n\n";

# your database variables
my $database = "scripting_mysql";
my $host = "192.168.1.2";
my $port = "3306";
my $tablename = "states";
my $user = "root";
my $pass = "root_password";
my $cgi = CGI->new();
my $term = $cgi->param('term');

# mysql connection information
$dsn = "dbi:mysql:$database:$host:$port";

# open the database connection
$connect = DBI->connect($dsn, $user, $pass) || die print "Can't connect - error...";

# prepare the query
$query = $connect->prepare(qq{select id, state AS value, abbrev FROM states where state like ?;});

# execute the query
$query->execute('%'.$term.'%');

# obtain the results
while ( my $row = $query->fetchrow_hashref ){
push @query_output, $row;
}

# close the database connection
$connect->disconnect();

# print the json output to be returned to the HTML page
print JSON::to_json(\@query_output);

For this example, the Perl script should be named states.pl. When executed from the jQuery script in the web page, it will perform a search on the database where the search criteria is in a “like” statement, with a percent sign on both sides of the search term, like this:

# execute the query
$query->execute('%'.$term.'%');

You might want to change this query if you have a long data set where entering a few letters would bring up a result that isn’t what the user would probably want. Here is an example of the same form where we are asking the user to enter their state name, and they enter “min” for Minnesota, but it also brings up Wyoming:

If you want to only search for the beginning of the word, remove the first percent sign, like this:

# execute the query
$query->execute($term.'%');

This would remove Wyoming from being populated as a choice:

Lastly, here is a simple web page that uses jQuery. We are using the jQuery hosted by Google, and we are using one of their css templates. You may delete their css template and use your own. Also, you will need to change the path of your Perl script on this line:

source: "http://192.168.1.2/cgi-bin/mysql/jquery/state.pl"

And here is the HTML:

<html>

<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jQueryui/1.8.16/themes/base/jQuery-ui.css">

<!-- source = http://code.google.com/apis/libraries/devguide.html#jQuery -->
<script src="https://ajax.googleapis.com/ajax/libs/jQuery/1.6.4/jQuery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jQueryui/1.8.16/jQuery-ui.min.js"></script>

<!-- Here is the jQuery code -->
<script>
   $(function(get_states) {
    $('#abbrev').val("");
    $("#state").autocomplete({
    source: "http://192.168.1.2/cgi-bin/mysql/jQuery/state.pl",
    minLength: 3,
    select: function(event, ui) {
     $('#state_id').val(ui.item.id);
     $('#abbrev').val(ui.item.abbrev);
    }
    });
   });
</script>

<!-- The HTML is simplistic for the example: -->
<form method="post">
<table>
<tr><td><p class="ui-widget" ><label for="state"/>State: </label></td>
<td><input type="text" id="state" name="state" size=30/> </td>
<td><input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></td></tr>
</table>
<input type="hidden" id="id" name="id" />
<p><input type="submit" name="submit" value="Submit" /></p>
</form>

<!-- This section will populate the form field with a list of matching states -->
<script>
  $("#auto_populate_field").submit(function(get_states){
   $("#submitted").html("State: " + $("#state").val() + "<br />State Abbreviation: " + $("#abbrev").val() + "<br />State ID: " + $("#state_id").val());
   return false;
  });
</script>

In this example, once the user starts typing past three characters, a drop-down menu will appear with choices that match the letters being typed. You can decrease/increase the total number of characters that the user will need to type before it queries the database. This is done via this line in the jQuery code:

minLength: 3,

Every time that the person types a character past the initial three characters, the jQuery script will query the MySQL database. This needs to be taken into consideration if you will have a lot of traffic on the pages where you are using jQuery.

The Perl script returns three values in json output – the id, state and abbreviation fields. The values of these fields are place in the text boxes by the autocomplete jQuery function.

In this example, the abbreviation for the state field is returned and placed in the “abbrev” field in the form. The value for the state id is also returned, but since the id field is a hidden field in the form, you won’t see the output/value – but it will be stored in the form.

If you want to see the value of the state id, simply change your web form to look like this:

<form method="post">
<table>
<tr><td><p class="ui-widget" style="textalign:right;"><label for="state"/>State: </label></td>
<td><input type="text" id="state" name="state" size=30/> </td>
<td><input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></td>
<td><input type="text" size=4 id="state_id" name="state_id" /></td>
</tr>
</table>
<p><input type="submit" name="submit" value="Submit" /></p>
</form>

You can now see the third field which will contain the state id:

When you enter “min” and then click on Minnesota, you will see the state ID (24) in the third field:

You could also use this to auto-populate city and state fields after someone enters their zip code. Or you could even use this to auto-populate the shipping costs for an online order (I might tackle this one in a future post). Hopefully you will be able to copy and paste this example and have a working jQuery form web page example in just a few minutes.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
About these ads

4 Responses to Using MySQL, Perl and jQuery to Auto-Populate a Form Field on a Web Page

  1. Ted says:

    On the face of it, this looks like a neat capability.

    However, when I tried it, it worked only the first time the page loaded in a browser on the same machine. It never worked at all when trying to access the page from a different machine. Equally bad, when I cleared the entry field, the autocompletion did not work on the second and subsequent values I tried. Obviously, this is not acceptable in any form intended to support data entry.

    I observe your HTML is sloppy, missing a variety of tags such as …

    The SQL Perl is OK, though.

    The only part of your presentation that is new to me is jQuery and the Javascript, but with the current state of your HTML, I don’t really want to spend time debugging your Javascript and HTML to figure out why it is good for only a singe use.

    • Tony Darnell says:

      I am not sure why you had problems. I gave the code to a friend of mine and he is using it on his web site without any problems. Without looking at your code, I can’t offer any advice on how to fix it or what may be happening. And yes, the HTML is missing some tags. I try to keep the example code as small as possible – the end user is free to add tags and enhancements. I also try to keep the code as minimal as possible so that newbies can figure out what is happening with the code. Thanks.

  2. sasa says:

    Hi Tony,

    I managed to reproduce this with little tweaking

    var \$j = jQuery.noConflict();

    \$j(function(get_states) {
    \$j(‘#abbrev’).val(“”);
    \$j(‘#state’).autocomplete({
    source: “country_code.pl”,
    minLength: 1,
    select: function(event, ui) {
    \$j(‘#state_id’).val(ui.item.id);
    \$j(‘#abbrev’).val(ui.item.abbrev);
    }
    });
    });

    However, auto populate did not work, i noticed that we are calling auto_populate_field which was not defined anywherelse.

    How does this work? I suspect it should be called at the “select: function(event, ui)” but i cant seem to get it to autopopulate.

    when setting the term to “new” the result returned from the perl script is
    [“NH”,”NJ”,”NM”,”NY”]

    Is it supposed to look like this below??
    [30,”New Hampshire”,”NH”,31,”New Jersey”,”NJ”,32,”New Mexico”,”NM”,33,”New York”,”NY”]

    Regards,
    sasa

  3. sasa says:

    Hi Tony,

    I seem to have found my own bug in the perl script where i replaced the fetchrow_hashref with fetchrow_array.

    Now the response look like this
    [{“abbrev”:”NH”,”value”:”New Hampshire”,”id”:30},{“abbrev”:”NJ”,”value”:”New Jersey”,”id”:31},{“abbrev”:”NM”,”value”:”New Mexico”,”id”:32},{“abbrev”:”NY”,”value”:”New York”,”id”:33}]

    However, I’m not getting the dropdown list of available abbrev anymore… it seem now the html side is not able to read the response, or read something as blank(a tiny dropdown did appear, upon inspecting the code, a bunch of empty were generated).

    Regards,
    sasa

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: