PHP search script help

searching multiple keywords

[Maynard]

Hi everyone, I hope you can help me. I am a PHP newbie but I love learning. I am scripting a simple search engine using PHP and a MySql database. The user enters a search string eg

$searchstring = red blue black

The PHP processes it like this (searching multiple fields of one table):

$sql = "SELECT * FROM table WHERE field1 LIKE '%$searchstring%' OR field2 LIKE '%$searchstring%' OR field3 LIKE '%$searchstring%'";

Even though all these words exist in the database - albeit each one in a separate field - no results are returned.

I think the problem lies in the fact that the script is looking for the "red blue black" together in one place. I think I need to split the string, store in an array and have the script search each word separately.

But I have no idea how to do this. Can anyone help?

Many thanks,
Maynard.

[jpjones]

Yep, you're right. The script is currently looking in each field for "red back blue".
Try this (and modify as appropriate):

$searcharray = Array();
$searcharray = explode(" ",$searchstring);
$sql = "SELECT * FROM table WHERE 1=1 ";
reset($searcharray);
while (list($key,$val) = each($searcharray)
{
 $sql .= " AND (field1 LIKE '%$val%' OR field2 LIKE '%$val%' OR field3 LIKE '%$val%')";
}

The above should break apart your string string based on spaces, and then search for a a match in the database where each word is contained in a field.

Is this what you're after?
HTH,
JP

[Maynard]

Would something like this work?

$arrWords = explode(" ", $searchstring);

// splitting on the space

for($i = 0; $i < sizeof($arrWords); $i++)

{

$sqlquery = "SELECT * FROM table WHERE field1 LIKE '%{$arrWords[$i]}%' OR field2 LIKE '%{$arrWords[$i]}%' OR field3 LIKE '%{$arrWords[$i]}%'";

[jpjones]

With that code, if you did a query on every iteration of the loop, you'd get:

Iteration 1) Results for any field matching "red"
Iteration 2) Results for any field matching "blue"
Iteration 3) Results for any field matching "black"

If thats' what you're after, then sure it would work.

[Maynard]

Ah, I see so any duplicates could be displayed also.

Your code does not display duplicates?

JP, thanks for being so helpful. As I love learning PHP, could you explain - line by line - what your code is doing (if you have time)?

Eg, what does "WHERE 1=1" mean, what is "reset($searcharray)" doing; what does this mean "while (list($key,$val) = each($searcharray)"; and this ".="?

$searcharray = Array();
$searcharray = explode(" ",$searchstring);
$sql = "SELECT * FROM table WHERE 1=1 ";

reset($searcharray);

while (list($key,$val) = each($searcharray)
{
$sql .= " AND (field1 LIKE '%$val%' OR field2 LIKE '%$val%' OR field3 LIKE '%$val%')";
}

[jpjones]

Sure, I don't mind helping out :)

[b]// define variable as array[/b]
$searcharray = Array(); 
[b]// split up input string based on space character and store in array[/b]
$searcharray = explode(" ",$searchstring); 
[b]// start sql statement. 1=1 is there so that we can use AND at the start of every iteration of the while loop. Not very clean, but quicker to code :)[/b]
$sql = "SELECT * FROM table WHERE 1=1 "; 
[b]// ensure the pointer is back at the start of the array, or else each will not work properly.[/b]
reset($searcharray); 
[b]// step through each element of searcharray, using $key to hold the key reference of the array, and $val to hold the actual value of the current key.[/b]
while (list($key,$val) = each($searcharray) 
{ 
[b]// .= appends the string in " " to the contents of variable $sql. Just having = would overwrite $sql.[/b]
$sql .= " AND (field1 LIKE '%$val%' OR field2 LIKE '%$val%' OR field3 LIKE '%$val%')"; 
} 

HTH,
JP

[dcrombie]

In PostgreSQL you can do something like:

SELECT * FROM table WHERE field1~*'(key1Škey2Škey3)' OR field2~*'(key1Škey2Škey3)';

Not sure if there's an equivalent for MySQL but someone here will know...

;)

[Maynard]

Thanks JP!

I tried to run the script but came up with a parsing error at this line:

while (list($key,$val) = each($searcharray)

Is there a missing ; or something?

[dcrombie]

Have you counted the brackets?

[jpjones]

Thanks dcrombie!

Forgot the closing ) at the end of the while list - each line, Maynard!

[Maynard]

JP you are a star! it worked! if I search for "red blue" it only returns the records with both "red" and "blue" in them which is just what I was hoping for. This is the equivalent of a "search all words" search, isn't it?

How would I include an option to "search any words", based on the code you provided?

[jpjones]

In the $sql .= line, change the AND to an OR.
That should do the trick...

Bear in mind that the query is going to be get rather slow as your search table holds more information. In an ideal world you don't want to use like %WORD% as this means MySQL has to search through every single word of each field you're looking at, and this takes *lots* of time!

If its' for a small-ish web site, this shouldn't matter too much. Just don't expect Google-like responsiveness using this sort of system when using masses of data! :)

JP

[Maynard]

thanks JP. I had no luck, though. I did this:

$sql .= " OR (field1 LIKE '%$val%' .....

and all the records in the database were returned.

any thoughts?

[HelenDev]

Wow! Great thread guys! I had been needing to do the exact same thing with my search script but I had been putting it off because I thought it might be really hard. I now have it sorted thanks to your neat little script :)