How to make JQuery Autocomplete work with a MySQL Database using Json
If you would like us to help install JQuery Autocomplete on your site then please let us know and we ill see what we can do…
The problem with the excellent JQuery UI website’s examples for the Autocomplete plugin is that it doesn’t demonstrate how to create the Json file for the data to be loaded from the MySQL database. So here is a walk through and the pitfalls explained:
MySQL Table
This Autocomplete is going to be for a list of Contacts with first name and Last name. And we are going to Autocomplete on the Last name, but display the First name as well in case we have duplicate surnames.
It is very important that the table columns are named ‘label’, ‘value’ and ‘id’ as a minimum for the table. It can be done with only ‘value’ but this is a complete solution.
CREATE TABLE IF NOT EXISTS `contacts_json` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`label` varchar(255) DEFAULT NULL,
`value` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
)
Once you have created the MySQL table, next is the PHP to create the JSON data file that the Autocomplete is going to use. The really important note to observe is the Select command where we have added the ‘where value like %$q%’ because the process is run everytime a new letter is typed into the form and each time it is re-run it adds the $term variable to the URL with the contents of what you have written in the form.
<?php require_once(‘../Connections/myconnection.php’); ?>
<?php
$q = strtolower($_GET["term"]);
mysql_select_db($database_mydatabasename, $datahere);
$query_RS_names = “SELECT id, CONCAT( value,’, ‘,label ) AS value FROM contacts_json where value like ‘%$q%’”;
$RS_names = mysql_query($query_RS_names, $datahere) or die(mysql_error());
$rows = array();
while($r = mysql_fetch_assoc($RS_names)) {
$rows[] = $r;
}
print json_encode($rows);
?>
Save this PHP file as surname_json.php
Create the Page for the Form
Next we need to create the layout for the form, so here it is, similar to the JQuerey UI Autocomlete example so you can see the changes:
<head>
<meta charset=”utf-8″>
<title>jQuery UI Autocomplete – Remote datasource</title>
<link rel=”stylesheet” href=”../jqueryui/development-bundle/themes/base/jquery.ui.all.css”>
<script src=”jquery-1.7.1.js”></script>
<script src=”../jqueryui/development-bundle/ui/jquery.ui.core.js”></script>
<script src=”../jqueryui/development-bundle/ui/jquery.ui.widget.js”></script>
<script src=”../jqueryui/development-bundle/ui/jquery.ui.position.js”></script>
<script src=”../jqueryui/development-bundle/ui/jquery.ui.autocomplete.js”></script>
<link rel=”stylesheet” type=”text/css” href=”auto.css”>
<style>
.ui-autocomplete-loading { background: white url(‘images/ui-anim_basic_16x16.gif’) right center no-repeat; }
</style>
<script>
$(function() {
function log( message ) {
$( “<div/>” ).text( message ).prependTo( “#log” );
$( “#log” ).scrollTop( 0 );
}
$( “#birds” ).autocomplete({
source: “surname_json.php”,
minLength: 2,
select: function( event, ui ) {
log( ui.item ?
“Selected: ” + ui.item.value + ” aka ” + ui.item.id :
“Nothing selected, input was ” + this.value );
}
});
});
</script>
</head>
<body>
<div>
<div>
<label for=”birds”>Birds: </label>
<input id=”birds” />
</div>
<div style=”margin-top:2em; font-family:Arial”>
Result:
<div id=”log” style=”height: 200px; width: 300px; overflow: auto;”></div>
</div>
</div><!– End demo –>
<div>
<p>The Autocomplete widgets provides suggestions while you type into the field. Here the suggestions are bird names, displayed when at least two characters are entered into the field.</p>
<p>The datasource is a server-side script which returns JSON data, specified via a simple URL for the source-option. In addition, the minLength-option is set to 2 to avoid queries that would return too many results and the select-event is used to display some feedback.</p>
</div><!– End demo-description –>
</body>
Hope this helps.
If you would like help installing JQuery Autocomplete on your site then contact us and let us know.
