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() {
url="http://url.to.my.server/ajaxserver.php"
$("#sampletype").autocomplete({
source: function(request, response) {
$.ajax({
url: url,
data: {
fetchnames: "sampletype",
pattern: request.term
},
dataType: 'json',
success: function(data) {
response(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";
$sqlh=$dbh->prepare($sql);
if($pattern){
$pattern="%$pattern%";
// searchpattern may be different
$sqlh->bindParam(':pattern',$pattern);
}
$sqlh->execute();
$data=$sqlh->fetchAll(PDO::FETCH_NUM);
/*
$names is a simplified version of $data - as needed in the javascript
*/
foreach($data as $i){
$names[]=$i[0];
}
header('Content-type: application/json');
print(json_encode($names);