Ajax based dynamic keyword search on a database table

We can use Ajax to send keywords as we type to the database to get the matching records. The advantage of this is we are not waiting for the full keyword to be typed and then submit to get the matching records. Here as we type the text in a text box the data is posted to our PHP script using Ajax and the result is displayed below the text box.

By watching the result returned by the database we can modify our search keyword.

This is a simple one field matching search only. We have not used other fields or dynamic keyword combinations to get the result from the table.

Our search query uses SQL Like statement to get the matching records from the table.

You can learn the basics of using GET method in Ajax to post form data here This script uses two files, one is the htm file which uses Ajax to send the data to PHP script. Here is the code.

Sorce Code : ajax-search-demo.php

<html>
<body>
<style>
#displayDiv{
background-color: #ffeeaa;
width: 200;
}
</style>
<script type="text/javascript">
function ajaxFunction(str)
{
var httpxml;
try
  {
  // Firefox, Opera 8.0+, Safari
  httpxml=new XMLHttpRequest();
  }
catch (e)
  {
  // Internet Explorer
  try
    {
    httpxml=new ActiveXObject("Msxml2.XMLHTTP");
    }
  catch (e)
    {
    try
      {
      httpxml=new ActiveXObject("Microsoft.XMLHTTP");
      }
    catch (e)
      {
      alert("Your browser does not support AJAX!");
      return false;
      }
    }
  }
function stateChanged() 
    {
    if(httpxml.readyState==4)
      {
document.getElementById("displayDiv").innerHTML=httpxml.responseText;

      }
    }
	var url="ajax-search-demock.php";
url=url+"?txt="+str;
url=url+"&sid="+Math.random();
httpxml.onreadystatechange=stateChanged;
httpxml.open("GET",url,true);
httpxml.send(null);
  }
</script>
</head>
<body>

<form name="myForm">
<input type="text"
onkeyup="ajaxFunction(this.value);" name="username" />
<div id="displayDiv"></div>

</form>

</body>
</html>

Our PHP script connects to database and uses Like query to get the data, the same data is returned to the htm script by using Ajax.

Sorce Code : ajax-search-demock.php

<?Php
//***************************************
// This is downloaded from www.plus2net.com //
/// You can distribute this code with the link to www.plus2net.com ///
//  Please don't  remove the link to www.plus2net.com ///
// This is for your learning only not for commercial use. ///////
//The author is not responsible for any type of loss or problem or damage on using this script.//
/// You can use it at your own risk. /////
//*****************************************
//error_reporting(E_ERROR | E_PARSE | E_CORE_ERROR);
require "config.php"; // database connection
/////
/// Main Code sarts ////////////
/////

$in=$_GET['txt'];
if(!ctype_alnum($in)){
echo "Data Error";
exit;
}

$msg="";
$msg="<select id=s1 size='15'>";
if(strlen($in)>0 and strlen($in) <20 ){
$sql="select name, id from student where name like '%$in%'";
foreach ($dbo->query($sql) as $nt) {
//$msg.=$nt[name]."->$nt[id]<br>";
$msg .="<option value=$nt[id]>$nt[name] => $nt[id]</option>";
//$msg .="<option value='$nt[name]'>";

}
}
$msg .='</select>';
echo $msg;
?>

Download Basic search script using Ajax PHP MySQL

Advance keyword search using Ajax PHP and MySQL

To understand this script you must know how to use Ajax, Json, Paging of records and SQL. In addition to this you must read how we break a string and prepare keyword linked query.

Using the above concept we can develop a script to search database. To prepare the table we have used the title tags of php section of this site. We kept all the text inside title tag of PHP Section tutorials and apply search to this.

Suggesting matching records by using a datalist

While we type the words in the textbox, we can post them to backend search script and get the matching records which can be used as options of the datalist. You can read more on basic of datalist and how to add options to datalist by using records from MySQL here.

Searching with multiple keywords.

It is likely that users will search for multiple keywords where we have to return any matching word. For example if we search for the sentence plus2net demo then our script should find out records having word plus2net or word demo in the title field. So the query string should be like this.

select title from TABLE where title like '%plus2net%' or title like '%demo%'

So we need to extend our above query depending on the number of words used for searching.

Multiple keywords

Users are likely to search multiple keywords. Say for example we search for the words PHP MySQL Ajax. Now the best result is where all three words present in the page. So we will search for pages where all keywords present and place them ahead of other results. After placing these results then we will search for records where any one of the keyword is present. Now we will join these two results and return to visitors.

Here we have not kept any algorithm to rank search results. We will match only keywords in title or description column. That can be developed … That is the technique used by search engines and success depends on indexing and developing complex search algorithm to give better user experience.

Paging of Records

We should not show all the records (results ) in a single page. It is better we show limited records pages ( say 10 records ) and allow visitors to navigate forward or backward to other pages by using buttons.

Paging of records using Ajax

Json

Along with records we will also return several other data to the users so we will use Json for returning data to users.

Database table

For this tutorial we indexed our PHP section and kept it in a table. How to read file and index it ( spider ) is not a part of this tutorial. In your downloaded file some sample data is kept for you to test the script. Try searching for Ajax PHP MySQL script etc.

Keyword search using AJAX in MySQL database