Php Search Engine Script For Mysql Database

[ivenms 0]

A search engine is provided to facilitate the user with undemanding and clear-cut search options. The search facility includes simple search, search by title, search by word/phrase, ect… Thus, the user is at a safe distance from the risk of selecting files/folders ambiguously. In addition, a history of recent searches can be preserved for future perusal.


Now day's visitors have a large option for his needs on internet and so visitors are not vesting their time by following the dead links on your site. So a search engine is essential for your site to attract your visitors and hand around on your site by directly deviating them to their interesting topics. This also make the visitors happy and the probability of next visit by them also been increased.


In here, I am trying to give some tips on how to create simple search engines on your database. Normally a large amount of webmasters now using open source and free database and programs like MySql with PHP scripting. So my search engine considered on the basis of MySql database and using PHP scripting.


For Database Tables a SQL query called LIKE is used for finding matching parts on your text fields on your table. This is normally used for search engines. An example for LIKE query used in search engine is as follows:

********************************************************************************
SELECT FIELDS FROM TABLE WHERE TEXTFIELD LIKE ' %keyforsearch%'
********************************************************************************

Here this query will select all rows from the table named TABLE on which its column TEXTFIELD containing search keyword. You can search on multiple column by using AND query. An example query for multiple search is as follows:



********************************************************************************
SELECT FIELDS FROM TABLE WHERE TITLE LIKE ' %keyforsearch%' AND
TEXTFIELD LIKE '%keyforsearch%'
********************************************************************************


The FIELDS denotes all fields you want to select as a results. Multiple fields can be selected by using comma operator: FIELD1, FIELD2.


Now I am going to tell about the PHP script which makes use of this query for its database search. I am giving the PHP codes I used on my sites for database search. This code is so simple and optimized. You just want to change some parts and put the entire code to your site for working.
The 1st part is information about your site database. Put this part to the top of your PHP page. Change this part with your information about your database for connection.

**********************************************************			$db_server = "localhost";	 $db_name = "database_name";	 $db_username = "database_username";	 $db_password = "database_password";**********************************************************

The next part is a function which used for establish connection of database. No need of changing this part. Just put this codes to the page.

**********************************************************function db_connect(){	global $db_server;	global $db_name;	global $db_username;	global $db_password;	$con =@ mysql_connect($db_server,$db_username,$db_password);	if ($con!=false){		if (!(mysql_select_db($db_name,$con))){			// cannot find database			die("Cannot find Database");		} else {			// fine		}	} else {		die ("Cannot connect to database");	}	return $con;}***********************************************************

The next part of search engine script is SQL generating and running function which retrieves the results of search. You have to change some portions on the function which denoted with <> change that part replacing with your information. Don't forget to replace < > symbols because that makes syntax error. For changing this part, refer the previously explained portions on SQL QUERY.

***********************************************************function db_search($key) {		$con = db_connect();	$key = mysql_escape_string($key);	$sql = "SELECT DISTINCT <FIELDS RETRIVED> FROM <TABLE NAME> WHERE <FIELDNAME FOR SEARCH> LIKE '%".$key."%'";		$result = mysql_query($sql,$con);		mysql_close($con);	return $result;}***********************************************************

The next part is for retrieving the posted search key posted on an html form. This part catches the search key and passes it to previous function db_search(). It also convert the result to an array for displaying it on your page as the result.

***********************************************************if(isset($_REQUEST['key'])){   $key = $_REQUEST['key'];   $result = db_search_bible($key);  if($result == FALSE)	$content = 'No Match Found';else	while ($row = mysql_fetch_row($result)) {			$content .=  $row[0].', '.$row[1].', '.$row[2].',  '.$row[3].'…<br>';}echo $content;***********************************************************

The $content is the variable which stores the result. The selected fields will come in the format of $row[0], $row[1], $row[2],…… according to your selection. If you selected two fields named title and body then you can display them as selecting $row[0] for title and $row[1] for body.

If you want to see the result on a formatted style then ad some more code and replace a last part of the code by this. This part is for displaying the two selected fields TITLE and BODY. Change this display property with your structure.

**********************************************************if(isset($_REQUEST['key'])){   $key = $_REQUEST['key'];   $result = db_search_bible($key);   $html = '<html><head><title>Search Results</title></head><body><table border=0 width="100%"><!-contentà'</table></body></html>';  if($result == FALSE)	$content = '<tr><td>No Match Found</td></tr>';else   {	$content = "";	while ($row = mysql_fetch_row($result)) {			$content .=  '<tr><td><h2>'.$row[0].'</h2> <br><br>'.$row[1].'</td></tr>';   }}$html = str_replace("<!--content-->",$content,$html);echo $html;**********************************************************

Your search engine script is ready. You now want an html form for submitting search words. Make sure that the form contains the text field naming "key" where to submit search words. An example for search form is given below:

********************************************<form type="GET" action="filename.php"><input type="text" name="key"><input type="submit" value="Search"></form>********************************************

That's all. You now were owning a search engine which is capable of searching your database. Enjoy yourself by using this searching script. For more technical support, visit php.net.

By Iven Mathew Simon
Edited June 6, 2012 by moderator (see edit history)

[ivenms 0]

Post your satisfactions here on my code for SQL DATABASE SEARCH SCRIPT. If there any error, Please mention that on here.

[electron 0]

Well if this search script is for your site than quite satisfactory.But it does lack quite a lot of security POST handling.Also you have named the search function "db_search()" but while using it you referred to it as "db_search_bible()"People will get confused.Another thing is that your result page wont work at all.As you have put in the '$html' variable a comment ' <!-content?? ' and while replacing it you are using '<!--content-->'.The replace wont take place dude(I am sorry if i am rude).One last thing this is a search script that would search within a small database.You cant call it as a SEARCH ENGINE.For the real tough part of a search engine is to get and INDEX the result of a link it has and then find new links again and then store them.That is a real SEARCH ENGINE dude.There you got to consider storing the whole of the WEB and you require PB's(Peda Bytes) of space.So alot has to be done on the script that you have written and then rightly call it a search engine.Sorry if i am being very rude.

[ivenms 0]

Thanks for your valuable informations given. The error happened because of careless typing. The code after replacing bugs is as follows:

******************************************************	 $db_server = "localhost";	 $db_name = "database_name";	 $db_username = "database_username";	 $db_password = "database_password";****************************************************************************************************************function db_connect(){	global $db_server;	global $db_name;	global $db_username;	global $db_password;	$con =@ mysql_connect($db_server,$db_username,$db_password);	if ($con!=false){		if (!(mysql_select_db($db_name,$con))){			// cannot find database			die("Cannot find Database");		} else {			// fine		}	} else {		die ("Cannot connect to database");	}	return $con;}**********************************************************************************************************************function db_search($key) {		$con = db_connect();	$key = mysql_escape_string($key);	$sql = "SELECT DISTINCT <FIELDS RETRIVED> FROM <TABLE NAME> WHERE <FIELDNAME FOR SEARCH> LIKE '%".$key."%'";		$result = mysql_query($sql,$con);		mysql_close($con);	return $result;}**********************************************************************************************************************if(isset($_REQUEST['key'])){   $key = $_REQUEST['key'];   $result = db_search($key);  if($result == FALSE)	$content = 'No Match Found';else	while ($row = mysql_fetch_row($result)) {			$content .=  $row[0].', '.$row[1].', '.$row[2].,  '.$row[3].'<br>';}echo $content;*********************************************************************************************************************if(isset($_REQUEST['key'])){   $key = $_REQUEST['key'];   $result = db_search_bible($key);   $html = <html><head><title>Search Results</title></head><body><table border=0 width=100%><!-content--></table></body></html>;  if($result == FALSE)	$content = '<tr><td>No Match Found</td></tr>';else   {	$content = ;	while ($row = mysql_fetch_row($result)) {			$content .=  <tr><td><h2>.$row[0].'</h2> <br><br>'.$row[1].'</td></tr>;   }}$html = str_replace("<!--content-->",$content,$html);echo $html;******************************************************************************************************<form type=GET action=filename.php><input type=text name=key><input type=submit value=Search></form>********************************************

The mentioned script is only a path which leads you to built your own database search. This is not a full script which can installed for your site. I am giving you an idea. I just explaining what I done for searching my database and calling it as my search engine. Any problem with that?

[electron 0]

No that is good now.But I think u could improve this greatly.For instance if the user typed inmany keywords you are not taking them seperately but as one whole string and trying to look for that as one string.You might consider by exploding the string and then search for the results.Best of luck

[ivenms 0]

Ya I know that works well by parsing the searching key and divide it into seperate keywords. If you have other ideas, make it into executable programming language and post it. So the other can see your ideas and use it. Make this script as base and develop more advanced scripts.Good Luck.

[electron 0]

Well i am working on some program now and it would be ready soon for beta testing to the public.There are others who provide the same software BUT i want to be better than them and I can do it better than them if not in the first BETA Release.If you know PHP , MySQL or JavaScript you could be one of its developers later after it goes public.

[SpinCode 0]

Speaking of parsing the input search keywords,
I recently came across a script at,

http://forums.xisto.com/no_longer_exists/

It automatically does all the parsing and generates the end search query for you.
I tested their online system, and it seems that its able to generate
AND, OR, NOT and Phrase searches, with search term highlighting too.

Check it out, it may be what you're looking for.

Spin

[farsiscript 0]

nice tutorial dear ivenmsYou can find many Search / add / edit / show / dump / read / write / update / delete / and .... query via phpmyadmin ,phpmyadmin is really nice php script for Beginner's for learn queryswhen you working via phpmyadmin , this script show your query at top of page and you can use it for any script thanks

[ivenms 0]

Thanks for your complement.Now Iam running a support forum for programmers. You can approach me for your every needs.

[iGuest 3]

It would be nice if instead of just one form field to do the searching, have more then one. Each for it's own search word. A real search rather then just keywords.-Saeru

[online 0]

Here is one i made 2 years ago...in your case it will be perfect

<?phpinclude("header.php");echo '<h1>S?kning</h1>				<div class="descr">S?k efter</div>';echo '<form action="search.php" method="get">';echo 'Sortera efter:<br><select name="orderby">';echo '<option value="username">User</option>';echo '<option value="Fname">F?rnamn</option>';echo '<option value="Lname">Efternamn</option>';echo '<option value="email">Email</option>';echo '</select>';echo '<br><br><br>';echo '<table>';echo '<tr><td><b>S?K:</b></td></tr>';echo '<tr><td><select name="select">';echo '<option value="username">Username: </option>';echo '<option value="Fname">F?rnamn: </option>';echo '<option value="Lname">Efternamn: </option>';echo '<option value="email">Email: </option>';echo '</select>';echo '<input type="text" name="username" /></td></tr>';echo '<tr><td><input type="submit" name="submit" value="Search" /></td></tr>';echo '</table>';echo '</form>';$result2=$_GET['username'];$orderby=$_GET['orderby'];$result3=$_GET['select'];$theres=trim($result2);if ($theres== "")  {  echo "<br><br><p>Fyll i f?re du s?ker !</p>";  }if($_GET['submit']=='Search'){echo'Results for <b>'.$theres.'</b>... ';$result80=mysql_query("SELECT COUNT(*) FROM all_users WHERE $result3 LIKE '%$theres%'");echo 'Found <b>'.mysql_result($result80, 0).'</b> match/s<br><br>';  $result = mysql_query("SELECT * FROM all_users WHERE $result3 LIKE '%$theres%' ORDER BY '$orderby'");	$count = 0;echo '<table align=center>';while($row=mysql_fetch_array($result)){if($count == 0 || $count == 4 || $count == 8 || $count == 12){ echo "<tr>";} $count++;echo '<td><a href="gallery.php?user='.$row['username'].'"><img src="allimages/'.$row['start_picture'].'" width="120px" height="95px" class="img" alt="'.$row['username'].','.$row['description'].'"></a><br><center>'.$row['username'].'</center></td>';}if($count == 28 || $count == 32 || $count == 36 || $count == 40 || $count == 44 || $count == 48){ echo "<tr>";}}echo "</table>";?><?phpinclude("footer.php");echo'</div>';?>

Notice from rvalkass:

Fixed code tags.

[annyphp 0]

May be you can check out at PHPKode.com for some free php search engine scripts

[iGuest 3]

Hello. I'd like to give this code a try. Can you show me the sql statement that would create a table that works with it just as it is, please? Thank you very much!

Here is one i made 2 years ago...in your case it will be perfect

<div class="descr">Sök efter</div>';

echo '<form action="search.php" method="get">';
echo 'Sortera efter linenums:0'><?phpinclude("header.php");echo '<h1>Sökning</h1> <div class="descr">Sök efter</div>';echo '<form action="search.php" method="get">';echo 'Sortera efter:<br><select name="orderby">';echo '<option value="username">User</option>';echo '<option value="Fname">Förnamn</option>';echo '<option value="Lname">Efternamn</option>';echo '<option value="email">Email</option>';echo '</select>';echo '<br><br><br>';echo '<table>';echo '<tr><td><b>SÖK:</b></td></tr>';echo '<tr><td><select name="select">';echo '<option value="username">Username: </option>';echo '<option value="Fname">Förnamn: </option>';echo '<option value="Lname">Efternamn: </option>';echo '<option value="email">Email: </option>';echo '</select>';echo '<input type="text" name="username" /></td></tr>';echo '<tr><td><input type="submit" name="submit" value="Search" /></td></tr>';echo '</table>';echo '</form>';$result2=$_GET['username'];$orderby=$_GET['orderby'];$result3=$_GET['select'];$theres=trim($result2);if ($theres== "") { echo "<br><br><p>Fyll i före du söker !</p>"; }if($_GET['submit']=='Search'){echo'Results for <b>'.$theres.'</b>... ';$result80=mysql_query("SELECT COUNT(*) FROM all_users WHERE $result3 LIKE '%$theres%'");echo 'Found <b>'.mysql_result($result80, 0).'</b> match/s<br><br>'; $result = mysql_query("SELECT * FROM all_users WHERE $result3 LIKE '%$theres%' ORDER BY '$orderby'"); $count = 0;echo '<table align=center>';while($row=mysql_fetch_array($result)){if($count == 0 || $count == 4 || $count == 8 || $count == 12){ echo "<tr>";} $count++;echo '<td><a href="gallery.php?user='.$row['username'].'"><img src="allimages/'.$row['start_picture'].'" width="120px" height="95px" class="img" alt="'.$row['username'].','.$row['description'].'"></a><br><center>'.$row['username'].'</center></td>';}if($count == 28 || $count == 32 || $count == 36 || $count == 40 || $count == 44 || $count == 48){ echo "<tr>";}}echo "</table>";?><?phpinclude("footer.php");echo'</div>';?>

Notice from rvalkass:

Fixed code tags.