Simple auto suggest example using PHP, jquery and MySQL

0 138

Today i am going to show you the simplest method to add auto-suggest feature in you website using php, jquery and mysql.

Here my example is showing auto-suggest for country list.

Step-1: Create countries table in your database.

countries.sql

CREATE TABLE IF NOT EXISTS `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sortname` varchar(3) NOT NULL,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=247 ;

Insert some data in your table.

Step-2: Create index.html file and paste below code
Here i am using jquery auto-complete library, You can study more about it from https://jqueryui.com/autocomplete/

index.html

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Simple auto-suggest using php, jquery and mysql</title>
  <link rel="stylesheet" href="http://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
 <style>
  .ui-autocomplete-loading {
    background: white url("img/ui-anim_basic_16x16.gif") right center no-repeat;
  }
  </style>
</head>
<body>
    <div class="ui-widget">
  <input type="text" id="country" name="country" placeholder="Type country name.">
</div>
  <script src="https://code.jquery.com/jquery-1.10.2.js"></script>
  <script src="https://code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
  <script>
  $(function() {
   $( "#country" ).autocomplete({
      source: function( request, response ) {
        $.ajax({
          url: "request.php",
          dataType: "json",
          data: {
            q: request.term
          },
          success: function( data ) {
            response( data );
          }
        });
      },
      minLength: 1,
      select: function( event, ui ) {
            // Do something on select event
        console.log(ui.item); // ui.item is  responded json from server
      },
      open: function() {
                 // Do something on open event.
      },
      close: function() {
               // Do something on close event
      }
    });
  });
  </script>
</body>
</html>

Step-3: Now time to work on server side create a file name request.php, it is just a basic example you can modify it as per your need.

request.php

<?php
// Remove below comments from header If  you are making calls from another server
/*
header("Access-Control-Allow-Origin: *");
header('Content-Type: application/json');
*/
$hostname = "localhost";
$username = "root";
$password = "root";
$dbname = "location";
$q = $_GET['q'];
if(isset($q) && !empty($q)) {
    $con = mysqli_connect($hostname, $username, $password, $dbname);
    $query = "SELECT id, name FROM countries WHERE name LIKE '$q%'";
    $result = mysqli_query($con, $query);
    $res = array();
    while($resultSet = mysqli_fetch_assoc($result)) {
     $res[$resultSet['id']] = $resultSet['name'];
    }
    if(!$res) {
        $res[0] = 'Not found!';
    }
    echo json_encode($res);
}
?>

Now time to run our example on browser

Hope this example will help you to implement auto-suggest in your website..!! :)

If you like this post please don’t forget to subscribe My Public Notebook for more useful stuff.

Leave A Reply

Your email address will not be published.