PHP: Multi-word MySQL Search

Here we have a simple PHP search algorithm that queries a MySQL database and outputs the result. It is a simple alternative to using the FULLTEXT search feature of MySQL.

The script autmatically takes into consideration how many search words are in the search string which is submitted from the HTML search form. It then creates a MySQL query and executes it, displaying the results. You can easily edit the fields that the search script searches by changing the values in the$arrayFields array.

cdl_capture_2011-04-21-43_ 000

 

<?php

// checks if a search has been 

submitted

if(!empty($_REQUEST['search']))

{

 //the table to 

search

 $table = "yourTable";

 // explode search words into an 

array

 $arraySearch = explode(" ", $search);

 // table fields 

to search

 $arrayFields = array(0 => "title", 1 => 

"content");

 $countSearch = count($arraySearch);

 $a = 

0;

 $b = 0;

 $query = "SELECT * FROM ".$table." WHERE 

(";

 $countFields = count($arrayFields);

 while ($a < 

$countFields)

 {

 while ($b < 

$countSearch)

 {

 $query = 

$query."$arrayFields[$a] LIKE 

'%$arraySearch[$b]%'";

 

$b++;

 if ($b < 

$countSearch)

 

{

 $query = $query." AND 

";

  }

 }

 

$b = 0;

 $a++;

 if ($a < 

$countFields)

 {

  $query = 

$query.") OR (";

 }

 }

 $query = 

$query.")";

 $query_result = mysql_query($query);

 // print 

title

 echo '<h1>Your Search 

Results</h1>'."\n\n";

 if(mysql_num_rows($query_result) < 

1)

 {

echo '<p>No matches found for 

"'.$search.'"</p>';

 }

 else

 

{

 echo '<p>Search Results for 

"'.$search.'":</p>'."\n\n";

// output list of 

articles

 while($row = 

mysql_fetch_assoc($query_result))

 

{

 // output whatever you want here for each 

search result

 echo '<a 

href="index.php?id='.$row['id'].'">'.$row['title'].'</a><br 

/>';

 }

}

}

else

{

 // 

display a welcome page

}

?>

<p><form method="get">

  <input type="text" name="search" value="<?php echo $_REQUEST['search'] ?>" />

  <input type="submit" value="Search" />

</form></p>

This PHP script produces and executes the following MySQL query when given a search input of "really cool php scripts". "title" and "content" are the two fields that are being searched upon.

SELECT * FROM articles WHERE (title LIKE '%really%' AND title LIKE '%cool%' AND title LIKE '%php%' AND title LIKE '%scripts%') OR (content LIKE '%really%' AND content LIKE '%cool%' AND content LIKE '%php%' AND content LIKE '%scripts%')

2 thoughts to “PHP: Multi-word MySQL Search”

Leave a Reply