homepage Welcome to WebmasterWorld Guest from 54.205.207.53
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Use an array as db select criteria
DrBurkstrom

5+ Year Member



 
Msg#: 3160830 posted 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?

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3160830 posted 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.

JAG

DrBurkstrom

5+ Year Member



 
Msg#: 3160830 posted 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

10+ Year Member



 
Msg#: 3160830 posted 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.

Mav

coopster

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



 
Msg#: 3160830 posted 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved