AutoComplete Search with Href Link [PHP MySQL - Remote DataSource Example]

Hi friends, hope you all are doing well, Today in this tutorial we will see an example of Autocomplete Search Box with PHP MySQL, Not only autocomplete we will make it more dynamic with remote datasource, in short this example provides suggestion while you type in search box and the suggestion will be coming from server-side script which returns JSON Data. this widget makes searching much easier you can use this widget for any type of search that you want in your website cause getting suggestion while typing seems cool and more user friendly, any search like post, products, category, catalogs or anything else that you want for your web project. you must have seen autocomplete search example on facebook and twitter so it makes searching someone easier on those social sites, so let's have a look at this simple quite useful widget tutorial and before proceeding you can check the demo.

Live Demo

Download Script

Database / Table

following is the sample database and table named 'tbl_posts' with dumping data contains post title, and post url, so just copy paste this sql code into your phpMyAdmin.

--
-- Database: `codingcage`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_posts`
--

CREATE TABLE IF NOT EXISTS `tbl_posts` (
  `postID` int(11) NOT NULL AUTO_INCREMENT,
  `postTitle` varchar(255) NOT NULL,
  `postUrl` varchar(255) NOT NULL,
  PRIMARY KEY (`postID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `tbl_posts`
--

INSERT INTO `tbl_posts` (`postID`, `postTitle`, `postUrl`) VALUES
(1, 'Simple jQuery Add, Update, Delete with PHP and MySQL', 'http://goo.gl/IL6NTr'),
(2, '15 Free Bootstrap Admin Themes Demo and Download', 'http://goo.gl/1dBwEy'),
(3, 'Easy Ajax Image Upload with jQuery, PHP', 'http://goo.gl/jXZ6LY'),
(4, 'How to Send HTML Format eMails in PHP using PHPMailer', 'http://goo.gl/kQrzJP'),
(5, 'Ajax Bootstrap Signup Form with jQuery PHP and MySQL', 'http://goo.gl/yxKrha'),
(6, 'Submit PHP Form without Page Refresh using jQuery, Ajax', 'http://goo.gl/14vlBe'),
(7, 'How to Convert MySQL Rows into JSON Format in PHP', 'http://goo.gl/qgOiwB'),
(8, 'Designing Bootstrap Signup Form with jQuery Validation', 'http://goo.gl/nECERc'),
(9, 'Upload, Insert, Update, Delete an Image using PHP MySQL', 'http://goo.gl/HRJrDD'),
(10, 'Login Registration with Email Verification, Forgot Password using PHP', 'http://goo.gl/O9FKN1');

Database Connection

well, i don't need to explain this code, this is 'dbconfig.php' file.

<?php
 
 $DBhost = "localhost";
 $DBuser = "root";
 $DBpass = "";
 $DBname = "codingcage";
 
 try {
  $DBcon = new PDO("mysql:host=$DBhost;dbname=$DBname",$DBuser,$DBpass);
 } catch(PDOException $ex){
  die($ex->getMessage());
 }

Server-Side Script / post_search.php

this file is for getting suggestion from 'tbl_posts' and works silently behind the scene, whenever user types in search box this file will get triggered and will return JSON format. and that JSON data will be shown as a auto suggestion.

<?php

 require_once 'dbconfig.php';

 $keyword = trim($_REQUEST['term']); // this is user input

 $sugg_json = array();    // this is for displaying json data as a autosearch suggestion
 $json_row = array();     // this is for stroring mysql results in json string
 

 $keyword = preg_replace('/\s+/', ' ', $keyword); // it will replace multiple spaces from the input.

 $query = 'SELECT postID, postTitle, postUrl FROM tbl_posts WHERE postTitle LIKE :term'; // select query
 
 $stmt = $DBcon->prepare( $query );
 $stmt->execute(array(':term'=>"%$keyword%"));
 
 if ( $stmt->rowCount()>0 ) {
  
  while($recResult = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $json_row["id"] = $recResult['postUrl'];
      $json_row["value"] = $recResult['postTitle'];
      $json_row["label"] = $recResult['postTitle'];
      array_push($sugg_json, $json_row);
  }
  
 } else {
     $json_row["id"] = "#";
     $json_row["value"] = "";
     $json_row["label"] = "Nothing Found!";
     array_push($sugg_json, $json_row);
 }
 
 $jsonOutput = json_encode($sugg_json, JSON_UNESCAPED_SLASHES); 
 print $jsonOutput;

Of course, you will need jQuery UI libraries here is that.

<link rel="stylesheet" href="http://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>

Search Box

Simple search form created with bootstrap just to give pretty look,

<div class="col-lg-offset-2">
<form>
<div class="form-group">
<div class="input-group">
<input id="txtSearch" class="form-control input-lg" type="text" placeholder="Search for PHP, MySQL, Ajax and jQuery" />
<div class="input-group-addon"><i class="glyphicon glyphicon-search"></i></div>
</div>
</div>
</form>  
</div>

JavaScript / AutoComplete jQuery Code

this is the main JavaScript code which makes a search box more dynamic, with the help of .autocomplete({}) function we can achieve autocomplete functionality with the just few lines of code. #txtSearch is the id of search input field.

$(document).ready(function(){
 
 $('#txtSearch').autocomplete({
     source: "post_search.php",
     minLength: 2,
     select: function(event, ui) {
         var url = ui.item.id;
         if (url != '#') {
             location.href = url
         }
     },
     open: function(event, ui) {
         $(".ui-autocomplete").css("z-index", 1000)
     }
 })
 
});

Custom CSS

I have customized the default look of autocomplete widget, you can check that in live demo. this is style.css file so create this one by copy pasting this following css code.

form .form-group{
   width: 700px;
   max-width: 100%;
   border-radius: 0 !important;
}
* {
  -webkit-border-radius: 0 !important;
     -moz-border-radius: 0 !important;
          border-radius: 0 !important;
}
.ui-autocomplete {
    max-height: 300px;
    overflow-y: auto;
    /* prevent horizontal scrollbar */
    overflow-x: hidden;
}
* html .ui-autocomplete {
    height: 300px;
}

.ui-autocomplete .ui-menu-item-wrapper {
    font-size: 15px;
    border-radius: 0;
    border-bottom: solid #eceff1 2px;
    padding: 10px;
    color: #000;
    background: #f9f9f9;
}
.ui-autocomplete .ui-menu-item {
    color: #000;
    background: #f9f9f9;
}

.ui-autocomplete .ui-menu-item:hover, .ui-menu-item-wrapper:hover {
    color: #fff;
    background: #a8a9d0;
}

.ui-menu .ui-state-focus,
.ui-menu .ui-state-active {
 color: #fff;
    background: #967cfc;
    background: #a8a9d0;
    border: 0;
}

Complete Index file / index.php

This is complete index file in which all required HTML and JavaScript code combined so that you won't get confused.

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="initial-scale=1.0, maximum-scale=2.0">
<title>AutoComplete Example in PHP MySQL</title>
<link rel="stylesheet" href="bootstrap/css/bootstrap.min.css">
<link rel="stylesheet" href="http://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
</head>

<body>

 <div class="container">
    
     <div class="page-header">
        <h3 style="color:#00a2d1; font-size:30px; font-family: Impact, Charcoal, sans-serif; text-align: center;">AutoComplete Search with Href Link in PHP MySQL</h3>
        </div>
         
        <div class="row">
        
         <div class="col-lg-12 text-center">
          
         <div class="col-lg-offset-2">
             <form>
             <div class="form-group">
             <div class="input-group">
             <input id="txtSearch" class="form-control input-lg" type="text" placeholder="Search for PHP, MySQL, Ajax and jQuery" />
             <div class="input-group-addon"><i class="glyphicon glyphicon-search"></i></div>
             </div>
             </div>
             </form>  
             </div> 
                
            </div>
        
        </div>
        
    </div>

<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="bootstrap/js/bootstrap.min.js"></script>

<script>
$(document).ready(function(){
 
 $('#txtSearch').autocomplete({
     source: "post_search.php",
     minLength: 2,
     select: function(event, ui) {
         var url = ui.item.id;
         if (url != '#') {
             location.href = url
         }
     },
     open: function(event, ui) {
         $(".ui-autocomplete").css("z-index", 1000)
     }
 })
 
}); 
</script>

</body>
</html>

---

That's it for the day guys, hope you will like this tutorial and please don't forget to share this with your friends, and if you are getting any problem with any of tutorial you can let me know by comment or by email. thanks :)