Searching for an array?

apzer0

Is it possible to do something like this:
$x=array();
SELECT blah FROM blah WHERE blah='$x'

Or do I need to use a foreach loop or maybe there is some kind of mysql function that can handle this?
The number of values is not known.

devinemke

$array = array(1,2,3,4,5,6,7,8,9);
$sql = 'SELECT * FROM table WHERE field IN(' . implode(',', $array) . ')';

apzer0

I forgot to mention the i'm using the array in a LIKE so I don't think that'll work =/

devinemke

$array = array(1,2,3,4,5,6,7,8,9);
foreach ($array as $key => $value)
{
	$array[$key] = "field LIKE '%" . $value . "%'";
}
$sql = 'SELECT * FROM table WHERE ' . implode(' OR ', $array);

apzer0

Smart method, didn't think about it 😉

raccoonandzebra

Is there any way to modify this solution to work for a situation in which the original array has duplicates but you still want to get a reply from the database? In other words, if the array looks like this:

$array = array(1,2,1,2,1,2);

and you follow it with this:

$sql = 'SELECT * FROM table WHERE field IN(' . implode(',', $array) . ')';

MySQL only gives one match for 1 and one match for 2. Is there a way to modify the php to make it match every time? Is there a way to make null values in the array return as null from the database?

devinemke

Originally posted by raccoonandzebra
...MySQL only gives one match for 1 and one match for 2. Is there a way to modify the php to make it match every time?

mySQL will return every record matching all of the arguments passed to IN(). what do you mean by "make it match every time"? there is not necessarily a 1 to 1 correlation between all of the arguments passed to IN() and the returned record set. let's say we had 100 records where field = 1 and 200 records where field = 2 and we did:

$array = array(1,2,1,2,1,2); 
$sql = 'SELECT * FROM table WHERE field IN(' . implode(',', $array) . ')';

we would get 300 records returned.