Search MySQL field for values in an array

[amavadia]

Hopefully this will be an easy one for someone...

 

I have...

 

array called $loc

a MySQL table called advert

a Field within the table called location

 

Im trying to...

Search for records which contain any of the values in the array in the location field.

 

So far ive got a query which will find a record if there is just one value in the field. But if the field contains more than 1 value, it dosen't find the record even if that field contains one of the values in the search array. Values in the Location field are storred separated by commas, so my guess is I need to add something to the search query to account for the spaces and and commas?

 

$query = "SELECT * 
FROM advert 
WHERE location IN('".join("','", $loc)."')";

 

any help appreciated!

[fizix]

I use a function like this:

 

function parsesearch($search) {
	$searches = split(" ", $search);
	$searchqty = count($searches);
	for($x=0;$x<$searchqty;$x++) {
		if(!isset($searchquery)) $searchquery = " WHERE (title_keys.title LIKE '%".$searches[$x]."%' OR ips.domain LIKE '%".$searches[$x]."%')";
		else $searchquery .= " AND (title_keys.title LIKE '%".$searches[$x]."%' OR ips.domain LIKE '%".$searches[$x]."%')";
	}
	return $searchquery;
}

 

You may need to tailor it to your needs but hopefully it gives you a starting point.

[amavadia]

I use a function like this:

 

function parsesearch($search) {
	$searches = split(" ", $search);
	$searchqty = count($searches);
	for($x=0;$x<$searchqty;$x++) {
		if(!isset($searchquery)) $searchquery = " WHERE (title_keys.title LIKE '%".$searches[$x]."%' OR ips.domain LIKE '%".$searches[$x]."%')";
		else $searchquery .= " AND (title_keys.title LIKE '%".$searches[$x]."%' OR ips.domain LIKE '%".$searches[$x]."%')";
	}
	return $searchquery;
}

 

You may need to tailor it to your needs but hopefully it gives you a starting point.

 

Thanks for that but I think ive found an easier way of making up the search query string:

 

$query = "SELECT * 
FROM advert 
WHERE location LIKE '%" .
join("%' OR location LIKE '%", $loc) . "%'";