Autocomplete using database table as source in JQuery UI

We have one auto complete filed with id =t1, here source of the auto complete field is name column of our student table available inside MySQL database. We will use this data ( name of student table ) as source for filling the autocomplete.
Autocomplete Tutorial DEMO of Autocomplete with Database as source
Search for Pin code and other details of an Indian location by using autocomplete with database as source
Autocomplete Search

Watch the Part 1 and Part 2 of the Video tutorial on Autocomplete
  • Autocomplete using Database as source Part III

Script Output

Part One : User can enter chars in the auto complete field, based on the input the matching record will be returned by backed PHP script as options of the auto complete

Part Two : Once user selects the name from the available options ( menu ) of the autocomplete list , the full details of the selected record will be displayed.
Demo of Auto Complete with data from MyQL database

How the script works

We kept the auto complete box inside index.php file.
<input type=text  id='t1' >
Once user enters characters in side this box it tries to collect the options from autocomplete-source.php file by using JQuery.
$( "#t1" ).autocomplete({
source: function (request, response) {
         $.ajax({
      url: "backend-source.php",
         type: "POST",
         data: request,
         dataType: 'json',
         success: function (data) {
           response($.map(data, function (el) {
             return {
                         label: el.label,
                         value: el.value
                     };
            }));
          }
        });
    },
});
The file backend-source.php first receives the user entered text .
$t1=$_POST['term'];
Here we have used type:POST to send data by post method. A request object with a single term property carries the data to backend script. If we are not specifying type then by default GET type is used. In such a case a GET request will be made like this
backend-source.php?term=foo
Here term equals to the data the user enters inside the box.

Return data and showing values

On return of data from backend script or database we will map it to two different set of values.
 response($.map(data, function (el) {
                   return {
                     label: el.label,
                     value: el.value
               };
            }));

On select Displaying label or value from the menu list

By default the return data menu will show label and on select the value will be displayed in the autocomplete text box. If you want while selecting the label to go as data then this changes are required.
select:function (e, ui) {
e.preventDefault();
$("#t1").val(ui.item.label);
}

SQL like command

Multiple word search

User may enter single search word or more than one word. We have to return records matching to all the words entered by user. So before developing the qury based on the input keywords we will first break it and create an array of words. For example if user enters Alex Ro as search term then our query should be
SELECT name AS label,id AS value FROM student WHERE NAME LIKE '%Alex%' or NAME LIKE '%Ro%'
We are using PHP string functions to break the string and creat Query with multiple OR combination.
$kt=explode(" ",$t1);//Breaking the string to array of words
// Now let us generate the sql
while(list($key,$val)=each($kt)){
if($val<>" " and strlen($val) > 0){$q .= " NAME LIKE '%$val%' OR ";}
}// end of while
$q=substr($q,0,(strlen($q)-3)); // remove the last OR from the output
Use of multiple keyword for matching record using LIKE query is explained here
After the query generation we will use it to collect records from database ( student ) table. Read more about collecting records in our PDO section.
$row=$dbo->prepare($q1);
$row->execute();
$result=$row->fetchAll(PDO::FETCH_ASSOC);
Note that we have kept our database login details inside config.php file and same file is included at the starting of the page.

In above code $result is an array, from this we will generate our JSON string using json_encode.
echo json_encode($result); // Output JSON formatted data

Using MySQLi database connection


$result_set = $connection->query($q1);
$data=array();
while($row = mysqli_fetch_array($result_set,MYSQLI_ASSOC)){
$data[]=$row;
}
echo json_encode($data);

Read more on MySQLi connection and database management

Once the options are displayed , user can select one of the available options from the list. On selection of option user can trigger the load method by JQuery

Part Two : Displaying details of selected option

select:function (e, ui) {
$("#d1").load("autocomplete-record.php?id="+ui.item.value);
}
Here our PHP script inside autocomplete-record.php will receive the ID value and return the matching record.
<?Php
error_reporting(0);// With this no error reporting will be there
require "config.php";
$id=$_GET['id'];
$q="select *  from student where id=:id";
//echo $q;
$count=$dbo->prepare($q);
$count->bindParam(":id",$id,PDO::PARAM_INT,5);

if($count->execute()){
$row = $count->fetch(PDO::FETCH_OBJ);
echo "Name: $row->name , Class: $row->class, Mark: $row->mark";
}
?>
The data as returned by autocomplete-record.php file is displayed inside div layer id=d1
Demo of displaying details of selected option from MyQL database

Pages in the script

You can download the script at the end of the page and here are the list of pages used.
config.php:
Login details for MySQL database .
index2.php:
Main file having JQuery & HTML code .
autocomplete-source2.php:
PHP Script to collect query and return matching records in JSON format
autocomplete-record.php:
PHP script to receive ID and return all columns of the matching record.
How to Install and test
  • Download the zip file at the end of this page.
  • Inside you will find sql_student_dump.text file to create tables inside your database.
  • Open config.php and enter your MySQL database login details.
  • Go to index2.php file and check the autocomplete.
You can contact us to integrate this script in your website.

Autocomplete Tutorial DEMO of Autocomplete with Database as source



Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com









    Most Popular JQuery Scripts

    1

    Two dependant list boxes

    2

    Calendar with Date Selection

    3

    Data change by Slider

    4

    Show & Hide element


    JQuery Video Tutorials




    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer