Create SQL query that takes an array

Question

I'm using php and need to do a bunch of select statements on the same table and column and get the rows that match those values.

I have all the values I'm searching on in an array and right now I'm just looping and doing a select statement on each one separately. How could I put this all into one select statement and ditch the loop?

Right now it's like this:

for (yaddah yaddah yahhah){
    SELECT * FROM scan WHERE order=var[$i];
}

Nearly the same as http://stackoverflow.com/questions/2382831/select-from-mysql-table-where-fieldarray posted just 30 minutes before. — Mark Byers, Mar 04 '10 at 22:02

score 3 · Accepted Answer · answered Mar 04 '10 at 22:01

3

You could use IN to specify a list of values :

select * 
from scan
whenre order IN (1, 5, 46, 78)

And you can get such a list using the implode function :

$array = array(1, 5, 46, 78);
$list_str = implode(', ', $array);
// $list_str is now "1, 5, 46, 78"

$query = "select *
from scan
where order IN ($list_str)
";

After, in your PHP script, it's only a matter of fetching several lines instead of only one.

answered Mar 04 '10 at 22:01

Pascal MARTIN

395,085
80
655
663

Thanks, this is much simpler than creating a temp table like I was told somewhere else. – William W Mar 04 '10 at 22:06

score 1 · Answer 2 · answered Mar 04 '10 at 22:01

1

Use IN and implode as follows:

$sql = "SELECT * FROM `scan` WHERE `order` IN (" . implode(',', $array) . ")";

answered Mar 04 '10 at 22:01

Mark Byers

811,555
193
1,581
1,452

score 0 · Answer 3 · answered Mar 04 '10 at 21:58

0

Check out SQL's In Operator

answered Mar 04 '10 at 21:58

BarrettJ

3,431
2
29
26

Create SQL query that takes an array

3 Answers3