PHP search script for multiple words

[vlad230]

Hey guys!

I've made a PHP script that searches things in my mysql database but it doesn't work how I would like it to work...

For example I have a mysql database that has fields like: id, title, link etc..
I'm searching in the title field.

I'm guessing that the problem is coming from my mysql_query so here's the code for it:

$result = mysql_query("SELECT * FROM `$table` WHERE title LIKE '%$keyword%' ");

For example if I have a title like this : Ferrari 599 GTB Fiorano by Novitec and I search for ferrari it works fine, if I search for ferrari 599 it works fine BUT if I search for ferrari GTB it doesn't return any results... So it seems like it only finds words that are near each other and in the same order as they are in the title.... How could I make it return the correct result?

Thanks,
Vlad

 

[user-7256]

That's because your current query looks literally for the term "ferrari GTB" in the title, but will not account for anything in between. (No wildcards in the middle of your search term.)

This is one of the main challenges for searching algorithms and why searches often go slow. Conventional methods require that you include a lot of "OR" statements in your query or do multiple queries (probably a bad idea) or use regular expressions, but that may not account for keywords separated by other keywords (I haven't tried).

Heuristics are convenient in this situation but I'm guessing you don't want anything that complicated. (Unless you're writing the Google of car websites.)

Off the top of my head, one possible approach is to first trim the keywords that the user is searching for (removing leading and trailing spaces with the trim() function) and then split the search string into an array of keywords with explode(). You then loop through each element of that string array and add to your query an "OR" statement:

$query .= " OR title LIKE '%$thisKeyword%'";

(or something like that anyway -- there's probably a few caveats with this method)

where $thisKeyword is the temporary variable created inside your loop that holds the next keyword to add to the search query.

 

[vlad230]

I thought I could avoid this with some kind of "magic" mysql keyword instead of LIKE....

Thanks a lot

>Back to the drawing board<

 

[xrvel]

I thought I could avoid this with some kind of "magic" mysql keyword instead of LIKE....

Thanks a lot

>Back to the drawing board<

Or you can replace space with "%".
So your search query "ferrari gtb" will be replaced into "ferrari%gtb" and the final query is

SELECT * FROM `$table` WHERE title LIKE '%ferrari%gtb%'

$keyword = str_replace(' ', '%', trim($keyword));
$result = mysql_query("SELECT * FROM `$table` WHERE title LIKE '%$keyword%' ");

 

[qbert220]

Or you can replace space with "%".

This won't work if you swap the search words round. Your final query would be:

SELECT * FROM `$table` WHERE title LIKE '%gtb%ferrari%'

which would not match.

As an alternative to mholt's answer, you could use str_replace to replace spaces with additional parts of the query something like this:

$keyword = ereg_replace('[[:space:]]+', ' ', trim($keyword));
$result = mysql_query("SELECT * FROM `$table` WHERE title LIKE '%" .
                  str_replace(' ', "%' OR title LIKE '%", $keyword)."%' ");

 

[Kate]

Maybe you should consider using FULLTEXT indexes. They will perform better if your dataset is large but there are limitations.

 

[vlad230]

Actually, this is what I came up with, thanks to mholt's idea:

$query = "SELECT * FROM `$table` WHERE";
$array = explode(" ", $trimmed);
foreach ($array as $key => $keyword){
	$query .= " title LIKE '%$keyword%'";
	if ($key != (sizeof($array) - 1))   //skip adding the last 'AND' to 
            $query .= " AND ";                // prevent a bad query

}

I've used 'AND' because if you use 'OR' you'll get results for the first "subkeyword" that has results.
ex. search for 'ferrari gtb' will return:

Ferrari 599 GTB Fiorano by Novitec
Ferrari Modena
Ferrari f430

etc..
Just like a search for 'ferrari'

I like xrvel's idea because it's shorter and will definitely work (the position of the search keywords doesn't matter).

Many thanks to you all guys! REP added of course