Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Using MySQL, Perl and jQuery to Auto-Populate a Form Field on a Web Page
+0 Vote Up -0 Vote Down

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.

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.