Ajax search suggestions using MySql and PHP

Posted on
  1. You are in : PHP  >>  Advance MySql  >>  

Following is the demonstration of how we can implement search suggestions from mysql database in PHP. Please note that this search is affected from Sql Injection and other database hacks. So fine tune is absolutely necessary.

First : index.php (which holds HTML form) 

<html>
<head>
<script>
function showResult(str)
{
if (str.length==0)
  {
  document.getElementById("livesearch").innerHTML="";
  document.getElementById("livesearch").style.border="0px";
  return;
  }
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("livesearch").innerHTML=xmlhttp.responseText;
    document.getElementById("livesearch").style.border="1px solid #A5ACB2";
    }
  }
xmlhttp.open("GET","livesearch.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>
<form>
  <input type="text" size="45" onKeyUp="showResult(this.value)">
  <div id="livesearch"></div>
</form>
</body>
</html>

Second : livesearch.php (which holds data processing logic) 

<?php

$q=$_GET["q"];

$con = mysql_connect('localhost', 'wordpress', '1234');
if (!$con)
{
	die('Could not connect: ' . mysql_error());
}

mysql_select_db("word", $con);

$sql="SELECT * FROM wp_posts WHERE post_title  LIKE '%".$q."%'";

$result = mysql_query($sql);

echo "<table border='1'>
		<tr>
		<th>Post Title</th>
		<th>Post Name</th>
		<th>ID</th>
		</tr>";

while($row = mysql_fetch_array($result))
{
	echo "<tr>";
	echo "<td>" . $row['post_title'] . "</td>";
	echo "<td>" . $row['post_name'] . "</td>";
	echo "<td>" . $row['guid'] . "</td>";
	echo "</tr>";
}
echo "</table>";
mysql_close($con);
?>