Live Search Using Ajax, PHP, and MySQL

To continue my last post which discussed about data input using xmlhttprequest / ajax in MySQL database, this time I'm going to explain you about another implementation of AJAX or xmlhttprequest in WEB programming.

I know that you were using Google Search for searching some pages based on your keywords. That's one of example from AJAX implementation in web application because it keeps you in the same page while you are typing some keywords and give you the result lively without reloading the current page.

So, let's begin.

First you have to create a keywords database for this simple implementation.

MySQL CREATE TABLE IF NOT EXISTS `keywords` (  
  `id_key` int(11) NOT NULL,  
  `keyword` varchar(360) NOT NULL  
 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;  
 --  
 -- Dumping data for table `keywords`  
 --  
 INSERT INTO `keywords` (`id_key`, `keyword`) VALUES  
 (1, ''),  
 (2, 'I am'),  
 (3, 'I am learning'),  
 (4, 'I am learning how to use AJAX'),  
 (5, 'I am searching'),  
 (6, 'I am searching AJAX example');  

Second, create a PHP file as your database connection named conn.php :

conn.php <?php  
 ini_set('display_errors',FALSE);  
 $host="localhost";  
 $user="root";  
 $pass="root";  
 $db="example";  
 $koneksi=mysql_connect($host,$user,$pass);  
 mysql_select_db($db,$koneksi);  
 if (!$koneksi){  
      ?><script language="javascript">alert("Failed to connected with Database Server !!")</script><?php   
 }  
 ?>  

Third, create a PHP file to execute sent keyword and find it on MySQL Database named searchexec.php :

searchexec.php <?php  
 require('conn.php');  
 if (isset($_GET['search']) && $_GET['search'] != '') {  
      $search = $_GET['search'];  
      $result = mysql_query("SELECT * FROM keywords where keyword like('%".$search."%')");  
      while($row = mysql_fetch_array($result))  
      {  
           echo '' . $row['keyword'] . "\n";       
      }  
 }  
 ?>  

Fourth, create a Javascript file for handle user's action when he is typing his keyword named search.js :

search.js function getXmlHttpRequestObject() {  
      if (window.XMLHttpRequest) {  
           return new XMLHttpRequest();  
      } else if(window.ActiveXObject) {  
           return new ActiveXObject("Microsoft.XMLHTTP");  
      } else {  
           alert("Your Browser Sucks!");  
      }  
 }  
 //Our XmlHttpRequest object to get the auto suggest  
 var searchReq = getXmlHttpRequestObject();  
 //Called from keyup on the search textbox.  
 //Starts the AJAX request.  
 function search() {  
      if (searchReq.readyState == 4 || searchReq.readyState == 0) {  
           var str = escape(document.getElementById('query').value);  
           searchReq.open("GET", 'searchexec.php?search=' + str, true);  
           searchReq.send(null);  
           searchReq.onreadystatechange = handleSearchResult;  
      }            
 }  
 function handleSearchResult () {  
      if (searchReq.readyState == 4) {  
                     var ss = document.getElementById('result');  
                     var str1 = document.getElementById('query');  
                     var curLeft=0;  
                     ss.style.visibility = "visible";  
                     if (str1.offsetParent){  
                          while (str1.offsetParent){  
                          curLeft += str1.offsetLeft;  
                          str1 = str1.offsetParent;  
                          }  
                     }  
                     var str =searchReq.responseText.split("\n");  
                     if(str.length==1) {  
                          ss.innerHTML = '';  
                          ss.style.visibility = "hidden";  
                     }  
                     else  
                     ss.innerHTML = '';  
                     for(i=0; i < str.length - 1; i++) {  
                          //Build our element string. This is cleaner using the DOM, but  
                          //IE doesn't support dynamically added attributes.  
                          var suggest = '<div style="cursor : pointer;" onmouseover="javascript:suggestOver(this);" ';  
                          suggest += 'onmouseout="javascript:suggestOut(this);" ';  
                          suggest += 'onclick="javascript:setSearch(this.innerHTML);" ';  
                          suggest += 'class="small">' + str[i] + '</div>';  
                          ss.innerHTML += suggest;  
                     }  
                }  
 }  

Fifth, create a HTML file for displaying a keyword field to user named index.html :

index.html <html>  
 <head>  
 <title>Live Search With AJAX</title>  
 <script language="javascript" src="search.js"></script>  
 <link href="search.css" rel="stylesheet">  
 </head>  
 <body>  
 <label>Keywords: </label><input autocomplete='off' onKeyUp='search()' id='query' name='query' type='text'/>  
 <div id='result'></div>  
 </body>  
 </html>  

Last, create a CSS file for displaying keyword results nicely and correctly named search.css :

search.css #main{  
 padding:15px;  
 margin:0 auto;  
 width: 265px;  
 border:2px double gray;  
 }  
 #result{  
      width:262px;  
      border:1px solid gray;  
      margin-top: -2px;  
      border-bottom-width: 0px;  
 }  
 #result a{  
      text-decoration:none;  
      text-transform:capitalize;  
      padding:5px;  
 }  
 .suggest_link{  
 background-color:#fff;  
 border-bottom:1px solid gray;  
 }  
 .small{  
 background-color:#fff;  
 border-bottom:1px solid gray;  
 }  
 .suggest_link_over{  
 background-color:#fff;  
 border-bottom:1px solid gray;  
 }  
 .suggest_link:hover{  
 background-color:#6d84b4;  
 border-bottom:1px solid gray;  
 }  
 .suggest_link_over:hover{  
 background-color:#6d84b4;  
 border-bottom:1px solid gray;  
 }  

When this simple live search run on browser, it looks like this :

Explanation

In index.html we are creating a simple form which is containing a keywords field and will execute a javascript function when user press any button on keyboard with onKeyUp='search()'. In search() javascript function, we have a function getXmlHttpRequestObject() to call AJAX class and create searchReq variable. 

When search() function executed by user's key press, it will checks the xmlhttprequest state first with if (searchReq.readyState == 4 || searchReq.readyState == 0) to make sure xmlhttprequest process on ready state or finish state and then send the keyword data to searchexec.php file with this command: searchReq.open("GET", 'searchexec.php?search=' + str, true);.

On searchexec.php file, sent keywords data will be searched on MySQL database with SQL query : SELECT * FROM keywords where keyword like('%".$search."%'). It means that keyword will be searched on every row which is containing the keyword and sent then displayed back to index.html with handleSearchResult () javascript function.

This example is very simple and needs more improvement to give the user more accurate result based on his keyword. But, I hope this example will helps you to understand about AJAX and also it would be good if you implement this kind live search on your web application. But, if you are really wish that it should be like Google Search, it needs more advanced improvement because Google has more complex code.

You could download my code directly with click below download link :

Password: 123revobrain321