CODEWITHSUNDEEP
phpmysql
Patrick Beardmore
Patrick Beardmore

Reputation: 1032

How to fetch only certain rows from an MySql database table

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

Upvotes: 1

Views: 1145

Answers (9)

Seva Alekseyev
Seva Alekseyev

Reputation: 61361

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))."')"

Upvotes: 2

Jordan Ryan Moore
Jordan Ryan Moore

Reputation: 6887

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

Upvotes: 1

jonescb
jonescb

Reputation: 22761

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.

Upvotes: 1

user151841
user151841

Reputation: 18046

Use php's implode function:

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

Upvotes: 0

mmundiff
mmundiff

Reputation: 3951

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)

Upvotes: 4

Nick Craig-Wood
Nick Craig-Wood

Reputation: 54079

Just use this form of select

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

Upvotes: 0

Derek Slager
Derek Slager

Reputation: 13841

Two options:

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

Upvotes: 1

rosscj2533
rosscj2533

Reputation: 9323

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

Upvotes: 2

VolkerK
VolkerK

Reputation: 96159

You can use MySQL's IN() operator.

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

Upvotes: 9

Related Questions