Searching with PHP and MySQL beyond LIKE

Ever wanted to add a search feature to your php & MySQL driven web site? Then your in luck this tutorial shows you how!

This tutorial assumes you already have a MySQL database and a table with some data already in your table. This tutorial also assumes your familiar with basic php syntax.

You can search your MySQL database in many ways the most common way is using the command LIKE eg. LIKE '$search' which would search for a word like the one you entered into the search field the problem with this is if your search term is not exactly the same as the word your searching for you won't get a match.

For example if you searched for 'schoo' and in your table there was a word called 'school' they do not match so you would not get a result.

To get round this you can use a wild card search using the Astrix '*' eg LIKE '%$search%' this would search for any word with all the letters that your search for and then 'schoo' would match 'school'. So that's exactly what you want to be able to search for part and full words but LIKE has a major drawback you can only search for one word at a time not multiple words!

Thankfully there's another way of doing your search using a full text search. With a full text search you can search multiple part or full words.

To start open a text editor and define your php page. Then we need to get the search term that was searched for in the event of a search being performed, to do this we use they super global $_GET and assign it to a variable

 $search = $_GET['search'];

We then use a php function strip_tags to remove any html, php ,css or any other code from the search term.

$search = strip_tags($search);

We also want to escape the search term before it interacts with the database to prevent a mysql injection attack.

$search = mysql_real_escape_string($search);

Now we have retrieved the search term from the form, we want to make sure its not empty to do this we use an if statement. what this does is if the search term is equal to nothing meaning its empty then echo a message to the user else carry on with the script.

if ($search == "")
{
   echo "<p>Opps! You forgot to enter a search term.</p>";
} else {

Now all the checks have been done its time to check the search term is in the database using a mysql query. This is like any other mysql query with a select (all) from the table name where, from this point on its where its different then normal. for the search to work MATCH(articlesTitle) must match the title of the article of what ever field you want to search against the search term AGAINST('$search' IN BOOLEAN MODE) in boolean mode means true or false mode. In this mode do don't need an index in your database.

(NOTE * With this kind of search you cannot search for 3 characters or less.)

Then a result variable is used to perform the query or show a message if the query fails.

$sql = "SELECT * FROM articles WHERE MATCH(articlesTitle) AGAINST('$search*' IN BOOLEAN MODE)";
$result = mysql_query($sql) or die("Problem, with Query:".mysql_error());

Then were counting how many results were count using the mysql_num_rows function then add the result to a variable.

// Get number of articles, assign value to a variable $count = mysql_num_rows($result);

<p>Now we show the actual results from the search, first were echoing a title and a short message letting the user know what you search for and how many results they were.</p>


```php
echo "<h3>Search Results</h3>";
echo "<p>you searched for <b>$search</b> there are $count matches.</p>";

Again were counting the results and creating a new variable with a value of zero.

$TableRows = mysql_num_rows($result);
$i = 0;

Then a while loop is performed to show all the results I am not explaining the while loop as this have been explained in Viewing data from a database.

while ($i <$TableRows)
{
//Add resulting tablerow to relvant variable
$articleID = mysql_result($result, $i, "articleID");
$articleTitle = mysql_result($result, $i, "articleTitle");
$articleDesc = mysql_result($result, $i, "articleDesc");

echo "<h1><a href='http://www.mydomain.com/$articleTitle'>$articleTitle</a></h1>";
echo "<p>$articleDesc</p>";
echo "<p><a href='http://www.mydomain.com/$articleTitle'>Read More</a></p>";

// loop through all results and indent number on each loop
$i ++;

} // close while loop

A closing bracket is not used to close the else statement to complete the search script.

Now you can search your database show the results and search for part or full words with multiple words.

Happy searching.

} // close else

Here's the full script:

<?php
//Post search words
$search = $_GET['search'];

// remove any code from the search term
$search = strip_tags($search);

//escape all data to prevent mysql injection attacks
$search = mysql_real_escape_string($search);

//No keywords entered.
if ($search == "")
{
   echo "<p>Opps! You forgot to enter a search term.</p>";
} else {

// perform the search
$sql = "SELECT * FROM articles WHERE MATCH(articlesTitle) AGAINST('$search*' IN BOOLEAN MODE)";
$result = mysql_query($sql) or die("Problem, with Query:".mysql_error());

// Get number of articles, assign value to a variable
$count = mysql_num_rows($result);

echo "<h3>Search Results</h3>";

echo "<p>you searched for <b>$search</b> there are $count matches.</p>";

$TableRows = mysql_num_rows($result);
$i = 0;
while ($i <$TableRows)
{
//Add resulting tablerow to relvant variable
$articleID = mysql_result($result, $i, "articleID");
$articleTitle = mysql_result($result, $i, "articleTitle");
$articleDesc = mysql_result($result, $i, "articleDesc");

echo "<h1><a href='www.mydomain.com/$articleTitle'>$articleTitle</a></h1>";
echo "<p>$articleDesc</p>";
echo "<p><a href='www.mydomain.com$articleTitle'>Read More</a></p>";

// loop through all results and indent number on each loop
$i ++;
} // close while loop
} // close else
?>

<form action="<?php $_SERVER['PHP_SELF'];?>" method="get">
<input name="search" type="text" size="20" />
<input type="submit" name="submit" value="Search" />
</form>