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.
CODE
**********************************************************
$db_server = "localhost";
$db_name = "database_name";
$db_username = "database_username";
$db_password = "database_password";
**********************************************************
$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.
CODE
**********************************************************
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_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.
CODE
***********************************************************
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;
}
***********************************************************
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.
CODE
***********************************************************
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;
***********************************************************
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.
CODE
**********************************************************
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;
**********************************************************
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:
CODE
********************************************
<form type=”GET” action=”filename.php”>
<input type=”text” name=”key”>
<input type=”submit” value=”Search”>
</form>
********************************************
<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
--- Site Administrator of WebMasters-Forums.Com
