Simple auto-suggest example using php, jquery and mysql

Do you want to add auto-suggest / auto-complete feature on text box, If yes then in this post I am going to share complete jQuery php and mysql tutorial for adding step by step auto-complete feature on input box. Auto suggest feature helpful as per user experience point of view like you have lot’s of data and you can’t load full data on page due to poor loading time. Then you must add auto-complete feature so that user can easily search and select desired input he/she looking for. In auto-complete case you have to type 2-3 character and script will suggest matched result in dropdown list and user can easily select relevant output.

In this example we i have taken world country list which i have inserted into mysql database. which i have to link input box like whenever someone type country initials script will suggest matched country so user do’t have to scroll select box they just have to type 2-3 character initials and result will be auto-suggest as per user input.

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


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="http://code.jquery.com/jquery-1.10.2.js"></script>
  <script src="http://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 ) {
            
        console.log(ui.item); 
      },
      open: function() {
                 
      },
      close: function() {
               
      }
    });
  });
  </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


$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.