Perl DBI Query -> JSON -> JQuery AutoComplete

I've been trying to read up on how to implement a JSON solution in order to use JQueryUI's autocomplete functionality. I am trying to use autocomplete to search a database on for a name and after selection populate the ID to a hidden object. I've seen alot of examples around开发者_开发知识库 the web, but haven't found the best way to implement this. The database doesn't change that often, so I'm not sure how to best approach this performance wise.

Backend:

#!/usr/bin/perl 
use CGI;
use DBI;
use strict;
use warnings;

my $cgi = CGI->new;
my $dbh = DBI->connect('dbi:mysql:hostname=localhost;database=test',"test","test") or die $DBI::errstr;

my $sth = $dbh->prepare(qq{select id, name from test;}) or die
$dbh->errstr;
$sth->execute() or die $sth->errstr;
my $json = undef;
while(my @user = $sth->fetchrow_array()) {
   $json .= qq{{"$user[0]" : "$user[1]"}};
}

print $cgi->header(-type => "application/json", -charset => "utf-8");
print $json;

---

The jQuery autocomplete needs a "value" or "label" field returned with the json result. If you do not include it, the jquery autocomplete will not work:

The basic functionality of the autocomplete works with the results of the query assigned to the ‘label’ and ‘value’ fields. Explanation on the ‘label’ and ‘value’ fields from the jQuery UI site:

“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.”

Link to full example: http://www.jensbits.com/2011/05/09/jquery-ui-autocomplete-widget-with-perl-and-mysql/

---

You need to grap the JSON package from CPAN instead of doing this:

my $json = undef;
while(my @user = $sth->fetchrow_array()) {
   $json .= qq{{"$user[0]" : "$user[1]"}};
}

For example, with JSON it'd look like this:

use JSON;
my $json = {};
while(my @user = $sth->fetchrow_array()) {
   $json->{$user[0]} = $user[1];
}
print JSON::to_json($json);

The JSON package will automatically construct a valid JSON string from any Perl data structure you provide it. We use it all over the place on Melody and it's proved to be a real life saver for sanely converting a structure into valid JSON.

---

Here I'm talking about performance.

There is some trigger you can set to improve performance, client side you can set the minimum number of characters required before the request is sent. You can also set the "timeout" between two characters typing before the request is sent.

If your database table is really huge, I suggest you put a LIMIT on results you retrieve. First to avoid long request processing, but also because some clients like IE6 arent't really fast handling more than a hundred results (Not to say, it's also not really user friendly).

On a project using IE6, we limited the elements returned to 100. If the user can't reduce the search to 100 elements, we presume he/she doesn't know what he/she is looking for.

Hope it helps a bit.