Welcome to WebmasterWorld Guest from 184.108.40.206 , register , free tools , login , search , pro membership , help , library , announcements , recent posts , open posts Become a Pro Member
Use an array as db select criteria DrBurkstrom msg:3160832 1:14 pm on Nov 18, 2006 (gmt 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?
$array = "green";
$array = "red"; $array = "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 msg:3160883 3:29 pm on Nov 18, 2006 (gmt 0)
Why not use an OR? It works just fine for me when I do it on indexed fields.
DrBurkstrom msg:3161008 5:51 pm on Nov 18, 2006 (gmt 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. mavherick msg:3161024 6:23 pm on Nov 18, 2006 (gmt 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.
coopster msg:3162704 5:52 pm on Nov 20, 2006 (gmt 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.