APR
27

Multi word search strings

Handling multi-field searches in php/mysql

To start, this is in no way the definitive way to handle multi word/field searches.

I was recently updating an old search system on a php/mysql website, the search does the usual LIKE %searchterm% for 3 separate fields for each word, then strips out the duplicates and presents a field. However, this was producing results which were not very helpful, ie. searching "red tail" would produce everything with red or tail in it, which is alot of things.

I searched google lazily for a while but couldn't see an obvious solution, there's some nifty mysql functions but they were poorly documented so I ignored them and charged on. After a few permutations of horrible mysql and with a little help I came across a possible solution.

Splitting the search term into single words, then for each word running a separate sub-query using the previous result set as the table to search from. This may seem obvious but I couldn't find it very easily, so maybe it will help someone.

For completeness here is the horrible php I used for the end result:

<?php
// Check for spaces in the string.
if(strpos($search, " ")>0){
	$sql = "";
	// Explode them into an array.
	$searchq = explode(" ", $search);
	// Count the words
	$numq = count($searchq);
	// Processing more than 4 words would be overkill .
	if($numq > 4){
		$runQuery = false;
	}
	if($runQuery == true){
		// For each word start a new sub-query.
		for($i = 0; $i < $numq; $i++){
			// Check that the word is over 1 character long, to make sure it searches only words (could probably be more).
			if(strlen($searchq[$i]) > 1){
				// Concatinate into the $sql string.
				$sql .= " (SELECT * FROM ";
			}
		}
		// Once you've opened up the sub-queries (something like "(SELECT * FROM (SELECT * FROM (SELECT * FROM) you then word backwards closing them.
		for($i = $numq-1; $i>=0; $i--){
			// Called the current array item $sq becuase it's easier than typing the array name/position each time.
			$sq = $searchq[$i];
			// Check if it's the final word in the string, if so you search from your original database instead of another sub query.
			// Just add the name of the table(s) you want to search at the start
			if($i == $numq-1){
				// Generic table name fields for easy reading, named as Set$i as sub-queries require unique names for their sets.
				$sql .= "Table WHERE Field1 LIKE '%$sq%' OR Field2 LIKE '%$sq%' OR Field3 LIKE '%$sq%') AS Set$i ";	
			}
			// The SQL to add if it's isn't the final word (searching from the previous result set).
			else{
				$sql .= " WHERE Field1 LIKE '%$sq%' OR Field2 LIKE '%$sq%' OR Field3 LIKE '%$sq%') AS Set$i"; 	
			}
		}
	// The final SQL string	
	$sql = "SELECT * FROM $sql ORDER BY Field1, Field3";
	}
}
?>

The final result of "teh monkey cheese" being something along the lines of:

SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM Table WHERE Field1 LIKE '%cheese%' OR Field2 LIKE '%cheese%' OR Field3 LIKE '%cheese%') AS Set2 WHERE Field1 LIKE '%monkey%' OR Field2 LIKE '%monkey%' OR Field3 LIKE '%monkey%') AS Set1 WHERE Field1 LIKE '%teh%' OR Field2 LIKE '%teh%' OR Field3 LIKE '%teh%') AS Set0 ORDER BY Field1, Field3

Of course I'm not sure this is the best way to do things, if you know something better leave a comment or send me an abusive email.

Cool Comments!

1Your Name left this wonderful comment on the 12th of May, 2010 at 22:32 Your comment

NEW Your comment