Keyword search using AJAX in MySQL database
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
DEMO of Ajax Search
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.
Source 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;
?>
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%'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.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.SEARCH Box
Try searching for Ajax PHP MySQL script etc.
Ajax Json XML
Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
| John | 24-08-2013 |
| Awesome tutorial... Keep posting Ajax tutorials.. | |
| sopha | 23-10-2013 |
| I like your website | |
| Chinthaka | 01-06-2014 |
| Wow fantastic !!! 100% working | |
| Arshad | 28-09-2014 |
| Always Rocking Plus2net.com! My PHP site was created by the help of Plus2net! Thank you! so much.. | |
| John | 12-07-2015 |
| Fatal error: Class 'PDO' not found | |
| smo1234 | 17-07-2015 |
| Inside config.php file read the database connection part. | |
| Imran | 09-08-2018 |
| This works good but for very much low records in table. I have inserted 0.2 Million records in table and then i used this technique believe me my site got stuck after few requests. Do you have any solutions for big data searching with ajax. | |
