Welcome to WebmasterWorld Guest from 54.196.233.208

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Use an array as db select criteria

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

New User

5+ Year Member

joined:Nov 18, 2006
posts:2
votes: 0


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....

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 21, 2003
posts:1069
votes: 0


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

JAG

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

New User

5+ Year Member

joined:Nov 18, 2006
posts:2
votes: 0


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.
6:23 pm on Nov 18, 2006 (gmt 0)

Junior Member

10+ Year Member

joined:June 3, 2002
posts:169
votes: 0


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

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

Administrator

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

joined:July 31, 2003
posts:12533
votes: 0


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.