Use an array as db select criteria

[DrBurkstrom]

Is there a neat and tidy way to select rows from a table in a mysql database where a certain field is contained in a pre-defined php array?

example:

$array[0] = "green";
$array[1] = "red";
$array[2] = "yellow";

then select all the rows where 'colour' is either red, green or yellow. but preferably without using OR in the query

i hope i've explained this properly....

[justageek]

Why not use an OR? It works just fine for me when I do it on indexed fields.

JAG

[DrBurkstrom]

I've used that in the past, mainly using a loop through the array to create a large string of OR statements. I was just wondering if there was a tidier (and perhaps more efficient) way of doing the same thing using a mysql command.

[mavherick]

You can take advantage of the sql construct:

SELECT * FROM you_table WHERE colour IN ('red','green', 'blue');

To build that query in PHP, you can use the implode function on your array like this:

$sql = "SELECT * FROM your_table WHERE colour IN ('".implode("','", $colour_array)."');";

Of course, make sure your $colour_array is not empty.

The quotes are a little tricky, but this should work.

Mav

[coopster]

The IN predicate mentioned here by mavherick sounds like a good fit. If the list gets too large you can also negate it with

NOT IN()

and use the opposite values in the list.

And welcome to WebmasterWorld, DrBurkstrom.