jQuery Autocomplete Search using PHP, MySQL and Ajax

Posted 2:57 am by muni & filed under Ajax, HTML5, JAVASCRIPT, JQUERY, MYSQL, PHP.

In this tutorial we are going to implement jQuery autocomplete search using PHP, MySQL and Ajax. This jQuery autocomplete gives suggestions when users starts to type on search box like in google search box. I had implemented two types of jQuery autocomplete

1. Simple jQuery autocomplete

2. Populate multiple textbox with single jQuery autocomplete request.

please refer my latest tutorial on jQuery automplete

jQuery Autocomplete Mutiple Fields Using jQuery, Ajax, PHP and MySQL

Invoice System Using jQuery AutoComplete

For every letter user types something on textbox, ajax request will sent to the Apache server. There Apache server will commuincate with MySQL database and fetch some data’s based on the text users typed in the textbox. Finally Apache server sends the response to the ajax request sent by client, the response data will showed as suggestions to the users.

jQuery Autocomplete using PHP, MySQL and Ajax

Learn jQuery Autocomplete in 3 Minutes

jQuery Autocomplete With Dynamic JSON Data from PHP, Ajax and MySQL Database

jQuery Autocomplete – Show No Record Found

Step 1:

Create following sample database and tables to implement this jQuery autocomplete functionality using following sql queries.

CREATE DATABASE IF NOT EXISTS `autocomplete` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `autocomplete`;

CREATE TABLE IF NOT EXISTS `country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `iso` char(2) NOT NULL,
  `name` varchar(80) NOT NULL,
  `nicename` varchar(80) NOT NULL,
  `iso3` char(3) DEFAULT NULL,
  `numcode` smallint(6) DEFAULT NULL,
  `phonecode` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=240 ;

CREATE TABLE IF NOT EXISTS `names` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fruit` varchar(100) NOT NULL,
  `human` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=166 ;

Please download sql dump and source of this tutorial using blow Download link .

Step 2:

Create config.php file to have database connections in a separate file. Also kept two database utility functions to prepare prepared statements and get results from executed query.

<?php
/**
 * File config.php
 * @author muni
 * @link https://smarttutorials.net/
 */

define('BASE_PATH', 'http://localhost/demo_tut/jquery-autocomplete/');
define('DB_HOST', 'localhost');
define('DB_NAME', 'smarttut_demo');
define('DB_USER','xyz');
define('DB_PASSWORD','mysql');

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
    exit();
}

// $sql = "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))";
// $mysqli->query($sql);

/**
 * Preparing Statement
 */
function prepareStatement($sql, $params = null)
{
    global $mysqli;
    $stmt = $mysqli->prepare($sql);
    if (!($stmt)) {
        throw new Exception("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
    }

    if (!empty($params) && is_array($params)) {
        $count = count($params);            
        $bindStr = str_repeat('s', $count);
        $stmt->bind_param($bindStr, ...$params);
    }

    if (!$stmt->execute()) {
        throw new Exception("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    return $stmt;
}

/**
 * GetResult
 */
function getResult($stmt)
{
    if (!($res = $stmt->get_result())) {
        throw new Exception("Getting result set failed: (" . $stmt->errno . ") " . $stmt->error);
    }
    return $res;
}

Step 3:

Now create index.php file and add the following textbox.

<input id="country_name" class="form-control txt-auto"/>

Step 4:

If you want to implement this autocomplete functionality minimum you need following jQuery and css files.
(jquery.min.js, jquery.ui.min.js and jquery.ui.min.css). add following files in your index.php file.

<link rel="stylesheet" href="css/jquery-ui-1.10.3.custom.min.css" />
<script src="js/jquery-1.10.2.min.js"></script>	
<script src="js/jquery-ui-1.10.3.custom.min.js"></script>

Step 5:

This following jQuery scripts will make ajax request to the server with user entered data in textbox. Finally get the response from the server and shows as suggestions.

$("#country_name").autocomplete({
    source: function(data, cb){
        $.ajax({
            url: 'ajax.php',
            method: 'GET',
            dataType: 'json',
            data: {
                name: data.term
            },
            success: function(res){
                var result;

                result = [
                    {
                        label: 'There is matching record found for '+data.term,
                        value: ''
                    }
                ];

                if (res.length) {
                    result = $.map(res, function(name){
                        return {
                            label: name,
                            value: name
                        };
                    });
                }

                cb(result);
            }
        });
    }
});

Step 6:

Create ajax.php and functions.php files. Where ajax.php will get client request and make appropriate function call in functions.php to get response.

<?php
/**
 * @filename ajax.pgp
 * @link https://smarttutorials.net/
 * @author muni
 */
require_once 'config.php';
require_once 'functions.php';
$data = [];
if ($_GET['type'] == 'country') {
	$data = getCountryList($_GET);
}

<?php
/**
 * @file functions.php
 * @author muni
 * @link https://smarttutorials.net/
 */

function getCountryList($data)
{
    $name = strtoupper($data['country_name']);
    $name = $name.'%';
    $sql = "SELECT name FROM country where UPPER(name) LIKE ? limit 25";
    $stmt = prepareStatement($sql, [$name]);
    $res = getResult($stmt);
    return $res->fetch_all(MYSQLI_ASSOC);
}

Please download source code(added all example code showed in the demo page)  of this tutorial using above download link.