jQuery UI Autocomplete Widget with Perl and MySQL

As a follow up to the jQuery UI Autocomplete Widget with PHP and MySQL post, I did one with Perl as the backend.

The jQuery UI folks have released an autocomplete widget that is pretty slick. This example uses Perl as the backend.
autocomplete
This example will use US states and territories to populate the autocomplete. It will also demonstrate how to fill other fields with data returned from the database. This data can be used to fill a visible text box or a hidden form field. It also demonstrates the basic autocomplete functionality which may be fine for some applications.

Of course, you will need the jQuery core file, the jQuery UI core file, and the jQuery UI style sheet of choice. The style sheet comes from the themes available in the jQuery UI website and can be downloaded with the core file or you can link to the latest versions of both the core files and the css:

<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1/themes/redmond/jquery-ui.css">
 
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1/jquery-ui.min.js"></script>

The HTML is straight forward and stripped down for the example:

<form  method="post">
<fieldset>
<legend>jQuery UI Autocomplete Example - PHP Backend</legend>
<p>Start typing the name of a state or territory of the United States</p>
<p class="ui-widget"><label for="state">State (abbreviation in separate field): </label>
	<input type="text" id="state"  name="state" /> <input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></p>
    <input type="hidden" id="state_id" name="state_id" />
<p><input type="submit" name="submit" value="Submit" /></p>
</fieldset>
</form>

As a bonus, we dump out the form values to see what we have right underneath the form itself:

$("#autocompleteForm").submit(function(){
    $("#submitted").html("State: " + $("#state").val() + "<br />State Abbreviation: " + $("#abbrev").val() + "<br />State ID: " + $("#state_id").val());
    return false;
});

And the jQuery on the page is equally brief:

$(function() {
        
            $('#abbrev').val("");
            
            $("#state").autocomplete({
                source: "states.pl",
                minLength: 2,
                select: function(event, ui) {
                    $('#state_id').val(ui.item.id);
                    $('#abbrev').val(ui.item.abbrev);
                }
            });

        });

The minLength for autocomplete to return results is set to 2 to prevent too many rows from being returned.

The jquery autocomplete will append the text typed into the autocomplete field as the URL parameter ‘term.’ This URL parameter is used to query the database.

From the jquery documentation:

The request parameter “term” gets added to that URL.

The Perl page return the data after a few steps:

  1. It queries the database
  2. Loops an array of the query results adding each row to a return string
  3. Outputs the string as JSON data

The states.pl file returns the id field, the state field as ‘value’, and the abbrev field. These values are placed in the appropriate text boxes by the autocomplete jQuery function. And, of course, you will have to make your own connection to your MySQL database before running the query.

#!/usr/local/bin/perl

# PERL MODULES WE WILL BE USING
use CGI;
use DBI;
use DBD::mysql;
use JSON;

# HTTP HEADER
print "Content-type: application/json; charset=iso-8859-1\n\n";

# CONFIG VARIABLES
my $platform = "mysql";
my $database = "YOUR_DB_NAME";
my $host = "localhost";
my $port = "3306";
my $tablename = "YOUR_TABLE_NAME";
my $user = "YOUR_USER_NAME";
my $pw = "YOUR_DB_PW";
my $cgi = CGI->new();
my $term = $cgi->param('term');

# DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306";
# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);

# PREPARE THE QUERY
$query_handle = $connect->prepare(qq{select id, trim(both char(13) from state) AS value, abbrev FROM states where state like ?;});

# EXECUTE THE QUERY
$query_handle->execute('%'.$term.'%');

# LOOP THROUGH RESULTS
while ( my $row = $query_handle->fetchrow_hashref ){
    push @query_output, $row;
} 
# CLOSE THE DATABASE CONNECTION
$connect->disconnect();

# JSON OUTPUT
print JSON::to_json(\@query_output);

Very important information below. Please read and understand before expecting the autocomplete to work properly.

The local data can be a simple Array of Strings, or it contains Objects for each item in the array, with either a label or value property or both. The label property is displayed in the suggestion menu. The value will be inserted into the input element after the user selected something from the menu. If just one property is specified, it will be used for both, eg. if you provide only value-properties, the value will also be used as the label.”

Demo

Posted in jquery. Tags: , . Permalink. Both comments and trackbacks are closed.

6 Comments

  1. August 2, 2011 at 9:43 am | Permalink

    @pankaj unhale

    Without seeing the code or a live sample page, I can’t even guess why it is not populating.

  2. August 2, 2011 at 9:11 am | Permalink

    I have return the JSON string contains id and value,but still the autocomplete dropdown is not populating..?? Do u have any idea

  3. July 11, 2011 at 9:19 am | Permalink

    @moto

    If you google “502 error perl” you will get some results that may help solve you problem.

  4. moto
    July 11, 2011 at 1:52 am | Permalink

    Hi. Thanks for the tip.

    Turns out there’s a 502 error (bad gateway). I know this is off-topic, but I’m not sure where the problem is. I am able to call perl scripts just fine via form posts, so does jquery/javascript use a different port/protocol? Or is this a firewall issue?

    I am a bit new to using javascript/jquery, so any help would be appreciated. Any ideas as to what should I be looking at?

    Thanks.

  5. July 10, 2011 at 1:05 pm | Permalink

    @moto

    The perl script in the same directory. You may have already done this but be sure to check the console in Chrome or install Firebug for Firefox to get some information on the response being sent from the server.

  6. moto
    July 10, 2011 at 3:14 am | Permalink

    Hi.

    I copied your scripts and loaded the HTML page, but for some reason it is not able to run the perl script. I’ve been able to trace it down to the HTML page not being able to find the .pl script. Both files, however, are in the same directory on my web server. The perl script runs in stand-alone and I have checked the permissions.

    So my question is, is there a particular directory (or path) the .pl script has to be at in order for the example to work?

    Thanks.

One Trackback

  1. [...] 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 [...]