SQL for keyword Search

search keyword by Query Search options are common in web pages and we can give option to visitors to search for keywords to locate the article or data within a site. Here we will apply the search to an existing table. The visitors will enter one or more than one keywords in the search box and we have to develop a query based on the selection of the visitor. Here we will discuss how to create a sql query to apply to the database with multiple keywords. Basically we will focus on construction of sql using keywords and will not go into details of getting the result or displaying in proper format with page breaks.
Search for Pin code and other details of an Indian location by entering keywords and selecting matching suggestion using JQuery autocomplete with database as source.
Autocomplete Search
Here we will give the option to the visitor to search for exact match or any where match on the table. For this we will use our student table and apply the search to the name field of the MySQL table. Based on the selected type of search we will generate the sql. Here are the steps required to develop a keyword search.

Demo of Keyword Search

Once the form is submitted check if the visitor has asked for exact match or any where match using if condition. Before that we will remove the blank space from left and right of the search sting by using ltrim and rtrim functions.
$search_text=ltrim($search_text);
$search_text=rtrim($search_text);
If the visitor has asked for exact match then create the query using simple where condition. Else .. If the visitor has asked for any where matching of keywords then read the search term and break it into array of keywords using explode command. Then loop through all the element of the array of words and create the sql command using like command for each word or the element of the array. Here is the code for this.
$kt=explode(" ",$search_text);//Breaking the string to array of words
// Now let us generate the sql 
			while(list($key,$val)=each($kt)){
if($val<>" " and strlen($val) > 0){$q .= " name like '%$val%' or ";}
			}// end of while
You can see we have broken the search text using explode command and then looped through the keywords. Here using one if condition we have taken care that blank space are removed in formatting the sql string. This way we will be adding one sql like command with OR combination for each word used. We will be adding each like command to the string with an SQL OR command. This way we will end with an extra OR command. This extra OR command we can remove from the end by using substr and strlen string functions.
$q=substr($q,0,(strlen($q)-3));
In the above line we have first calculated the length of the string by using strlen and then used that value inside the substr function after subtracting 3 from it. The 3 is subtracted as length of OR with one blank space is 3. This way we will get the string after removing 3 chars from the end. ( that is extra OR with a blank space )
$sql=preg_replace("/AND/","WHERE",$sql,1);
Above code will replace first occurance of AND with WHERE We can remove extra char from the end of the string by using PHP rtrim function

Once this sql is formatted then we can print it to the screen to check the syntax of the sql to match our requirement. Beyond this point you can use any server side script to collect the results from the table. You can read the tutorial on how to display data from mysql using php. You can down load the PHP version of this tutorial in a zip file at the end of this page. Here is the code till now.
<?Php
//ini_set('display_errors', true);
//Set this display to display  all erros while testing and developing the script
error_reporting(0);// With this no error reporting will be there
include "include/z_db.php";


$todo=$_POST['todo'];
$search_text=$_POST['search_text'];

if(strlen($serch_text) > 0){
if(!ctype_alnum($search_text)){
echo "Data Error";
exit;
}
}
////////// Displaying the search box /////
echo "<table>
<tr><td colspan=2 align='center'>";

echo "<form method=post action=''><input type=hidden name=todo value=search>
<input type=text name=search_text value='$search_text'><input type=submit value=Search><br>
<input type=radio name=type value=any checked>Match any where 
<input type=radio name=type value=exact>Exact Match

</form>
";

echo "</td></tr>";

/////////// if form is submitted the data processing is  done here///////////////
echo "<tr><td width='600' valign=top>";

if(isset($todo) and $todo=="search"){

$type=$_POST['type'];

$search_text=ltrim($search_text);
$search_text=rtrim($search_text);

	if($type<>"any"){
$query="select * from student where name='$search_text'";
		}else{
$kt=explode(" ",$search_text);//Breaking the string to array of words
// Now let us generate the sql 
			while(list($key,$val)=each($kt)){
if($val<>" " and strlen($val) > 0){$q .= " name like '%$val%' or ";}

			}// end of while
$q=substr($q,0,(strLen($q)-3));
// this will remove the last or from the string. 
$query="select * from student where $q ";
		} // end of if else based on type value
echo "<span style='background-color= #FFFF00'>$query</span><br>";
$count=$dbo->prepare($query);
$count->execute();
$no=$count->rowCount();
if($no > 0 ){echo " No of records = ".$no."<br><br>"; 
echo "<table><tr><th>ID</th><th>Name</th><th>Class</th><th>Mark</th><th>Sex</th></tr>";
foreach ($dbo->query($query) as $row){
echo "<tr><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td>
<td>$row[mark]</td><td>$row[sex]</td></tr>";
}
echo "</table>";
}else {
echo " No records found ";
}

}// End if form submitted
echo "</td><td width='400' valign=top>";
echo " Full records here ";
$query="select * from student";


echo "<table><tr><th>ID</th><th>Name</th><th>Class</th><th>Mark</th><th>Sex</th></tr>";
foreach ($dbo->query($query) as $row){
echo "<tr><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td>
<td>$row[mark]</td><td>$row[sex]</td></tr>";
}
echo "</table>";

echo "</td></tr></table>";
?>

Generate SQL based on user inputs for searching a table

Ajax based Keyword search on MySQL table

download Keyword search script

Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    P Biggs

    13-02-2009

    Thanks for your code on search-keyword.php. I've extended it to include an 'all' type for the search which is probably the most useful of the three, that returns results if all keywords are found in a record. If you want to use it or any part you're most welcome. It's on my site (see email address) in the miscellaneous section.
    Ragz

    10-03-2009

    Thanks for the code.. it saved my academic life
    A Marie

    14-04-2009

    Where do you put the "all" to search all tables?
    A Marie

    15-04-2009

    When I do a search the search string also shows up.. how do I hide this without damaging the code?
    smo

    15-04-2009

    There is a line saying echo $query; Remove this line or comment it like this //echo $query; This line is kept so before integrating the developer can know what is going to come
    ramesh dudala

    23-07-2009

    hai this is code is easly and good so thank u
    chris

    02-08-2009

    This is Great lesson. I was wondering also how I could add a message in case the return is false?
    Hugh

    21-08-2009

    Where do I put the connect??
    sangi

    08-12-2009

    hey this is really good thanks dude
    Pranjal

    14-01-2010

    how do i display the data which is not found?
    glaize

    17-01-2010

    tnx plus2net!i have learned a lot.
    Lashan

    07-02-2010

    Excellent tutorial. Explained very clearly. All the best and keep it up.
    Lashan Jayawardhana

    13-02-2010

    Excellent code plus2net. It is very useful. Fantastic work and great explanation. Keep up the good work. All the best !!!
    Thajul Hussain

    02-03-2010

    its very usefull, working fine. thanks a lot.........
    Mangal

    08-04-2010

    thanks for sharing this script.
    sunil

    11-04-2010

    how to match mysql database table vlaue whose given by user in php text..
    spencalot

    14-04-2010

    This is a great search engine. Thanks. my SQL table contains a field with key words such as: "apples fruits john doe car keys John Smith" How can I make the search engine search for "John Doe"? thanks!
    smo

    15-04-2010

    As your query has two words John
    Om Bahadur kc

    14-08-2012

    How to search from 2 or more table by one query without join Query,..
    anil

    14-09-2012

    hello dude its superv query...that u used...it realy works....realy thanks
    vaibhav

    25-06-2016

    sir, this was a great code.
    I changed dbo to mysqli as :
    <?php
    $dbo = mysqli_connect('localhost','root','','test');
    ?>

    now all the records are there for view only but when I search the keyword its not working.
    how do I change the mail file to show search results
    smo1234

    02-04-2017

    To use MySQLI respective functions are to be changed. By just changing connection it is not going to work.
    Angie

    10-10-2018

    Why do I receive Data Error?? Its not working for me..
    smo1234

    12-02-2019

    Your length of the variable $search_text is 0. You are not receiving the search text in your PHP script area.

    Post your comments , suggestion , error , requirements etc here .





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer