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.

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:

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:

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:

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:

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:

And here is the HTML:

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:

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:

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.

—————————————–