php search script - multiple search terms?

[MDGM]

Hi all,

Iv looked at quite a lot of forums and cannot get the code to work properly for searching multiple search terms. I have tried exploding the search string and then doing a search similar to this:

WHERE fullname LIKE '%$1%' OR fullname LIKE '%$2%'

but it has not worked so I need a quick point in the right direction! Below is the code I am currently using which searches for a record that contains the whole phrase (e.g. john smith, NOT john or smith which is what I want.)

//connect to db
include('../../php/database/connect.php');

$q = $_GET['q'];
$q = strtolower($q);
$q = stripslashes($q);
$q = strip_tags($q);

$query  = "SELECT * FROM products WHERE fullname LIKE '%".$q."%' ";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $row['fullname'];
}

Thanks in advance!! :)

[Will Gresham]

Can you clarify this:

(e.g. john smith, NOT john or smith which is what I want.)

Do you want to look for john OR smith or are you looking for john AND smith?

Also, this may help: http://www.iamcal.com/publish/articles/php/search/

[MDGM]

I want to return all results with EITHER john OR smith in.

Also the query needs to be dynamic, i.e. accept more than 1 / 2 words.

Thanks :)

[MDGM]

Sorted. its probs not the most efficient code but it works. :)

$q = $_GET['q'];

//bit of sanitizing
$qtrim = strtolower($q);
$qtrim = stripslashes($q);
$qtrim = strip_tags($q);

//split values by whitespace into an array, then for each array member stick them together with "OR columnname LIKE '%$arrayvalue%' "
$qtrim = explode(' ',$q);
foreach($qtrim as $value)
	{
		$qtrim = $qtrim."OR fullname LIKE '%".$value."%' ";
	}
//we now have a string which has the following value:
//OR fullname LIKE '%".$value1."%' OR fullname LIKE '%".$value2."%' 

//we now need to remove the first OR, so the string looks like this:
//fullname LIKE '%".$value1."%' OR fullname LIKE '%".$value2."%' 
$qtrim = substr($qtrim,2);

//then we use the string in a mysql query
$query  = "SELECT * FROM products WHERE ".$qtrim;

//execute query
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
      echo $row['fullname'];
}