Simple ajax based autocomplete in jquery

Starting to abandon prototype.js to use what seems to be the new standard jquery – one of the first things I want to do, is to make some autocomplete fields fetching values from a data base.

I have made an input field with the id sampletype. All the examples I found was quite more complicated than what I needed, e.g at jqueryui. But when I managed to remove all functionallity I don’t need, then the javascript goes:

$(function() {
source: function(request, response) {
url: url,
data: {
fetchnames: "sampletype",
pattern: request.term
dataType: 'json',
success: function(data) {

The fetchnames and pattern parameters are defined by my backend application. The script just fetches all unique names from a table and returns them as a jsonencoded array. The css needs to be fixed a bit to make things look good, I am still not finished there.

The main part of the backend is as follows. This is written for postgres, but most of it should be similar for other databases, e.g. mysql.

// $dbh is set up connected to my server

must have done a check that $table really is a valid table, this is a classical possible SQL-injection trap...
(a prepared sample must know on which table(s) to operate, so this must be build up as a string

$table - from $_GET['fetchnames']
$pattern - frin $_GET['pattern']

$sql="select name from $table";
$sql.=$pattern?" where lower(name) like lower(:pattern)":'';
lower() to make a case insetensive search, may not be needed or be different on other databases
like - to search anywhere within a string, may be different for other databases
$sql.=" order by name";
// searchpattern may be different
$names is a simplified version of $data - as needed in the javascript
foreach($data as $i){
header('Content-type: application/json');

This entry was posted in Data, Diverse. Bookmark the permalink.