MySQL Text Searching with PHP

Posted On September 20, 2007 by Rose Mary filed under Internet

When we start design a website with PHP and MySQL database and we really desire to have search functionality. To create -text search with PHP you need PHP, MySQL and a Web Server capable of parsing PHP pages, like Apache.

This search script does not spider all your pages by crawling the links, so the content you want to be searchable must be within the MySQL database. Knowledge of PHP and MySQL is also necessary because this script is just the bones of a working solution.

Now we will write the script how to build the search.

Open the Text editor or the PHP compatible editor, add the following code in the <body> tag of the HTML code. This is the form which is contain the textfield to enter the string.

.................................................................................................................................................................
<form name="form" action="searchscript.php" method="get">
<input type="text" name="query" />
<input type="submit" name="Submit" value="Search" />
</form>

Now we will add the actual PHP script for searching the test.

add the following code in the Seatchscript.php page.

.................................................................................................................................................................

<html>
<head>
<title>DeveloperIQ search script</title>
</head>
<body>
<h3> <font color="#800000"> Search for PHP and MYSQL WebSite</font>
</h3>
<form name="form" action="searchscript.php" method="get">
<input type="text" name="query" />
<input type="submit" name="Submit" value="Search" />
</form>

<?php
// Get the search variable from URL
$var = @$_GET['query'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return

$limit=10;

// check for an empty string and display a message.

if ($trimmed == "")
{
echo "<p>Please enter a word ot test to search</p>";
exit;
}

// check for a search parameter

if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}

// So now we're ready to build some SQL. I'm assuming a single content table with a
// single field to match.

//$db = mysql_connect("hostname","username","password");
//mysql_select_db("databasename",$db);
//connect to your database

mysql_connect("hostname","username","password"); //(host, username, password)

//specify database

mysql_select_db("databasename") or die("Unable to select database"); //select which database

we're using

// Build SQL Query
$query = "select * from tablename where columnname like \"%$trimmed%\"
order by description";

// EDIT HERE and specify your table and field names for the SQL query

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";

// google

echo "<p><a href=\"http://www.google.com/search?query="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google to get the information</p>";
}

// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}

// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
// display what the user searched for
echo "<p>You searched for: "" . $var . ""</p>";

// begin to show results set
echo "Results";
$count = 1 + $s ;

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["title"];
echo "$count.) $title;";
echo "<br />";
$count++ ;
}

$currPage = (($s/$limit) + 1);

//break before paging
echo "<br />";
// next we need to do the links to other results

if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
Prev 10</a>&nbsp ";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

// check to see if last page

if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

// not last page so give NEXT link

$news=$s+$limit;

echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";

}

$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>

</body>
</html>

The above code first accepts the string from the User and check for the string. If the string find in the column then it will display total number of similar values found in the column. Suppose if the value not fond in the particular column then it will display “Sorry, your search: "asp.net" returned zero results” Click here to try the search on Google. Then it will take you to the Google search.

Later you can figure out how to expand it to something more complicated yourself according to your requirements.

Added on September 20, 2007 Comment

MySQL Text Searching with PHP

Comments

Post a comment