Welcome to WebmasterWorld Guest from 54.163.40.152

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Use an array as db select criteria

     

DrBurkstrom

1:14 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



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

3:29 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

JAG

DrBurkstrom

5:51 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



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

6:23 pm on Nov 18, 2006 (gmt 0)

10+ Year Member



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

5:52 pm on Nov 20, 2006 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month