array_intersect_key PHP array with MYSQL database

Question

I have a simple array in PHP, say

[1, 2, 4, 5, 6, 7]

and I wish to query a MYSQL database

[1, who]
[2, where]
[3, some]
[6, there]
[9, too]

I only wish to receive the rows of intersection between the PHP array and the database's indices column. So this would result in

[1, who]
[2, where]
[6, there]

Any ideas? Thanks!

adam · Accepted Answer · 2010-01-27 16:38:01Z

up vote 4 down vote accepted

You want the sql in keyword

SELECT id, title FROM tablename WHERE id IN (1, 2, 4, 5, 6, 7)

You can prepare the list of numbers using:

$nums = array(1, 2, 4, 5, 6, 7)
$sql = 'SELECT id, title FROM tablename WHERE id IN (' . implode(',', $nums) . ')';

Edit: you can make sure your input contains only numbers with array_filter:

$nums = array_filter($nums, 'is_numeric');

edited Jan 27 '10 at 16:38

answered Jan 27 '10 at 15:57

adam
11.4k11538

	Might want to add a giant disclaimer about the dangers of SQL injection somewhere... – Dominic Rodger Jan 27 '10 at 16:01
	Thank you very much, both of you. I knew about the IN operator, but as soon as you posted I knew what to do. I will also be sure to check the injection vulnerability. – Booker Jan 27 '10 at 16:10
	Thanks, added array_filter to ensure $nums only contains numbers – adam Jan 27 '10 at 16:38
	@ Dominic Rodger: i don't see any user input, so a giant disclaimer about the dangers of SQL injection seems to be a little inappropriate here. – ax. Jan 27 '10 at 17:02
	@Booker, if this answer worked, can you mark it as accepted? Thanks – adam Jan 28 '10 at 11:26

feedback

1 Answer