jQuery Autocompletion PHP MySQL JSON

By | October 3, 2014

Autocomplete is one of the most important web feature , which provides you suggestion while you type into input field , jquery provides an efficient widget called jQuery Autocompletion which is very easy simple to do it .

In this tutorial we will see an autocompletion of data from MySQl database using php .

Download            Live Demo

Installing Auto Completion 

Add the following JavaScript and CSS inside the <head> tag , which you can find inside the download .

<script src=”js/jquery-1.7.1.min.js”></script>
<script src=”js/jquery.ui.core.min.js”></script>
<script src=”js/jquery.ui.widget.min.js”></script>
<script src=”js/jquery.ui.position.min.js”></script>
<script src=”js/jquery.ui.autocomplete.min.js”></script>
<link rel=”stylesheet” href=”css/jquery-ui-1.8.16.custom.css”/>

Encode MySQL Data To JSON Format

Create a database countries with table country .

CREATE TABLE IF NOT EXISTS ‘country’ (
  ‘idCountry’ int(5)  AUTO_INCREMENT,
  ‘countryCode’ char(2) ”,
  ‘countryName’ varchar(45)  ”,
  ‘currencyCode’ char(3) ,
  ‘capital’ varchar(30) ,
  ‘continentName’ varchar(15) ,
  ‘areaInSqKm’ varchar(20) ,
  PRIMARY KEY (‘idCountry’)
)

Now fetch the data from MySQL database table and encode into JSON Format .

file : source.php

<?php
include(“config.php”);
$term=$_REQUEST[‘term’];
$result = mysql_query(“SELECT * FROM country Where countryName LIKE ‘%$term%’ “);
if (mysql_num_rows($result) > 0) {
    $response[“orders”] = array();
    while ($row = mysql_fetch_array($result)) {
        $results[] = array(‘label’ => $row[‘countryName’] ,
                           ‘code’=>$row[‘countryCode’] ,
                           ‘currency’=>$row[‘currencyCode’],
                           ‘capital’=>$row[‘capital’],
                           ‘continent’=>$row[‘continentName’],
                           ‘area’=>$row[‘areaInSqKm’]);
    }
    echo json_encode($results);
}
?>

JavaScript

Add the following javascript inside the head tag , the autocompletion function takes the following parameters which represents .

Source : data source for autocompletion
minLength : It takes an integer value , which specifies autocompletion should triggers after minlength chararcter . 

<script>
$(document).ready(function(){
    $(‘input.search’).autocomplete({
            source:’source.php’,    
            minLength:3,
            select:function(evt, ui)
            {
                 // Get the data
                var countryName=ui.item.label;
                var countryCode=ui.item.code;
                var currency=ui.item.currency;
                var capital=ui.item.capital;
                var continent=ui.item.continent;
                var area=ui.item.area;
              
               // displaying the data
                $(“#mytable”).show();   
                $(“.countryName”).html(countryName);
                $(“.countryCode”).html(countryCode);
                $(“.currency”).html(currency);
                $(“.capital”).html(capital);
                $(“.continent”).html(continent);   
                $(“.area”).html(area);                   
            }   
        });
});   
</script>

HTML 

Create a html markup to display data

file : index.php

<input type=”text”   class=”search” placeholder=”Country…. “/>
        <table 
style=’display:none;’ id=’mytable’  cellpadding=”5″ cellspacing=’1′>
            <tr>
            <td>
Country</td>
            <td
class=”countryName”></td>
            </tr> 
 
          
            <tr>
            <td>
Code</td>
            <td
class=”countryCode”></td>
            </tr>

            <tr>
            <td>
Capital</td>
            <td
class=”capital”></td>
            </tr>
           
            <tr>
            <td
>Currency</td>
            <td
class=”currency”></td>
            </tr>

            <tr>
            <td>Continent</td>
            <td
class=”continent”></td>
            </tr>   
           
            <tr>
            <td>
Area(SqKm)</td>
            <td
class=”area”></td>
            </tr>   
        </table>