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

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

By
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.
Autocomplete Remote DataSource Example with PHP MySQL Live Auto Suggestion




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 :)



17 comments:

  1. thank you very much brother you are very good teacher thank you so much

    ReplyDelete
  2. can you help me making the search bar for my video website like youtube site or viemo

    ReplyDelete
    Replies
    1. hey M.A sorry for the late reply, and i'll try to post.

      Delete
  3. i tried using this code in my homepage site that im experimenting and its not working

    ReplyDelete
    Replies
    1. hi jeff, please make sure that php file is ok.

      Delete
    2. its okay now. thank you so much for this :) lot of your tutorials helped me learn a lot in php :)

      Delete
  4. Database Dont's Support Other Language
    Ex.thailand

    ReplyDelete
  5. how can i add a path to the url ?

    ReplyDelete
  6. Hi PRADEEP KHODKE,

    I am trying to do this tutorial but with a slightly modification.

    I would to write 3 characters and show the result inside a table; it's a mix of this tutorial and another one which it owns you too.

    By another hand, I would like to ask you a question:
    - inside the 'post_search.php' file, you use 'trim($_REQUEST['term']);'; so, where was the 'term' variable created? I'm not be able to see it mentioned in the index.html file (¿?).

    Great work! Have a nice Monday! Best regards.

    ReplyDelete
  7. hey pradeep. give me your social links or something i will subscribe to you and follow you. man you helped me a lot. not just me. "US" thankyou man

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Great tutorial!!
    Can you please tell us how to integrate in mobile devices.
    Thank you.

    ReplyDelete
  10. how can i open url in new window like target="_blank"

    ReplyDelete
  11. Hello Pradeep Khodke,
    Bro, your tutorials are very very interesting, easy to understand, clean and updated code. Your tutorials will help who wants to learn web development. Hope you will continue.. . Best wishes from my heart.
    Also I want to thank you, sir.

    ReplyDelete