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:
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’); ?>
$q = strtolower($_GET);
$query_RS_names = “SELECT id, CONCAT( value,’, ‘,label ) AS value FROM contacts_json […]