28 November 2009

How to make suggetion box using ajax in php from database onchange event

Hell Friends
Here is the example to get data from database using ajax in php (autocomplete)

This is the front page viewable to the user. Here is the input box that get input by the user and showData function take the request and send it to the getdata.php file via ajax and send response to the user.

index.php


<html>
<head>
<title>autocomplete | Home</title>
<script type="text/javascript">
  var xmlhttp;

  function showData(str) {
    xmlhttp=GetXmlHttpObject();
    if (xmlhttp==null) {
      alert ("Browser does not support HTTP Request");
      return;
    }
    var url="getdata.php";
    url=url+"?q="+str;
    url=url+"&sid="+Math.random();
    xmlhttp.onreadystatechange=stateChanged;
    xmlhttp.open("GET",url,true);
    xmlhttp.send(nodata);
  }

  function stateChanged() {
    if (xmlhttp.readyState == 4) {
      document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    } else {
      document.getElementById("txtHint").innerHTML="No Data available";
    } 
  }

  function GetXmlHttpObject() {
    if (window.XMLHttpRequest) {
      // code for IE7+, Firefox, Chrome, Opera, Safari
      return new XMLHttpRequest();
    }
    if (window.ActiveXObject) {
      // code for IE6, IE5
      return new ActiveXObject("Microsoft.XMLHTTP");
    }
    return null;
  }
</script>
</head>

<body>
 <div align="center" style="padding-top:10%; padding-left:25%; width:400px;">
   <table>
     <tr>
       <form>
         <td><span style="font-size:14px; font-family:Verdana, Arial, Helvetica, sans-serif;">Type any letter here</span></td>
         <td><input type="text" name="txtblog" id="txtblog" value="" onchange="showData(this.value)"/></td>
       </form>
     </tr>
     <tr>
     <td> </td>
       <td>
         <div align="right" style="overflow:scroll; max-height:200px;" id="txtHint"> </div>
       </td>
     </tr>
   </table>
  </div>
</body>
</html>

getdata.php


<?php
mysql_connect('HOST_NAME','USERNAME','PASSWORD');
mysql_select_db('DB_NAME');

$q = $_GET["q"];
$sql = "SELECT * FROM TABLE_NAME WHERE SEARCHING_COULMN_NAME like '%".$q."%'";
$result = mysql_query($sql);
echo '<table>';

while($row = mysql_fetch_array($result)) {
  echo '<tr>';
  echo '<td>' . $row['SEARCHING_COULMN_NAME'] . '</a></td>';
  echo '</tr>';
}
echo '</table>';
?>