How to fetch only certain rows from an MySql database table

Question

I have a php array of "primary key" values, which correspond to just a few rows of a very long table of data, saved on a MySql database. How can I fetch with just one query only those rows. Is it possible or will I need to make one query per primary key? eg: SELECT * FROM table1 WHERE key = 8573

Thanks, Patrick

Answer 1

You can use MySQL's IN() operator.

... WHERE x in (1,4,9,16)

Answer 2

Select * from table1 WHERE key IN ([values separated by commas])

Answer 3

Two options:

select * from table1 where key in (8573, 5244, 39211);
select * from table1 where key = 8573 or key = 5244 or key = 39211;

Answer 4

Just use this form of select

SELECT * FROM table1 WHERE key IN (1234, 1235, 6789, 9876)

Answer 5

Select * from table WHERE primary_key IN (8573,8574,8578)

for your php array you could use implode

$key_array = implode(",", $array);

Select * from table WHERE primary_key IN ($key_array)

Answer 6

Use php's implode function:

$keys = array(1,2,3,4);
$sql = "SELECT * FROM table WHERE key IN ( " . implode($keys, ",") . ")";

Answer 7

On most databases, "key IN (set)" works faster than "key=a or key=b or...".

Specifically for PHP, you may use implode to generate your SQL:

$SQL = "select * from table where key in (".implode(',', $KeyArray)).")"

Assuming integer key. With a string key, you need to quote them:

$SQL = "select * from table where key in ('".implode("','", $KeyArray))."')"

Answer 8

Use the OR statement.

SELECT * FROM table1 WHERE key=8573 OR key=9999;

Of course this can get really long, so you'll probably want to use a loop and concatenate the keys to the query string.

Answer 9

$query = "SELECT * FROM tableName WHERE primaryKey IN ('" . implode("','", $keys) . "')";