|
|
Keyword search using AJAX in MySQL databaseWe 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
The sample code is here but before that you can check our DEMO which is developed using the student table.
This script uses two files, one is the htm file which uses Ajax to send the data to PHP script. Here is the code.
<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.
<?
//***************************************
// 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);
////// Your Database Details here /////////
$dbservertype='mysql';
$servername='127.0.0.1';
$dbusername='test';
$dbpassword='test';
$dbname='sql_tutorial';
////////////////////////////////////////
////// DONOT EDIT BELOW /////////
///////////////////////////////////////
connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword)
{
global $link;
$link=mysql_connect ("$servername","$dbuser","$dbpassword");
if(!$link){die("Could not connect to MySQL");}
mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
}
///////////////////// Main Code sarts ////////
$in=$_GET['txt'];
$msg="";
if(strlen($in)>0 and strlen($in) <20 ){
$t=mysql_query("select name, id from student where name like '$in%'");
while($nt=mysql_fetch_array($t)){
$msg.=$nt[name]."->$nt[id]<br>";
}
}
echo $msg;
?>
Download the SQL dump of student table here
|
| |
|
|
|
|
|
