How do i select multiple rows which match the value of an item in an array?

Question

I have written a php script that returns an arbitrary number of specific ids (which are in the format of numbers) in an array. I would like to make a new query that selects each row from a table that belongs to each id. I know i can do 1 query to get one row with the matching id. But i would like to do this all in one query. Here is my code:

$id = array(1,4,7,3,11,120); // The array containing the ids

$query = mysql_query("SELECT * 
                        FROM posts 
                       WHERE id = '$id[0]'"); 
// I would like to Do this for each id in the array and return it as one group of rows.`

score 3 · Accepted Answer · 2010-12-14 01:58:19Z

3

I think you want the IN clause:

$idList = implode(",", $id);

SELECT *
FROM posts
WHERE id IN ( $idList );

The implode() function will turn your array of numbers into a comma-separated string of those same values. When you use it as part of an IN clause, it tells the database to use those values as a lookup table to match id against.

Standard Disclaimer/Warning:
As with any SQL query, you really shouldn't be directly concatenating variables into the query string. You're just opening yourself up to SQL injection. Use prepared/parameterized statements instead.

edited Dec 14, 2010 at 1:58

answered Dec 14, 2010 at 1:51

anon

Sign up to request clarification or add additional context in comments.

Comments

OMG Ponies · Accepted Answer · 2010-12-14 01:52:30Z

2

Use PHP's implode function to convert the array into a comma separated value string.

Then, you can use the SQL IN clause to run a single SQL statement containing the values associated with the ids you captured from PHP:

$id = array(1,4,7,3,11,120);
$csv = implode(',', $id); 

$query = sprintf("SELECT * 
                    FROM posts 
                   WHERE id IN (%s)",
                  mysql_real_escape_string($csv)); 

$result = mysql_query($query)

I omitted the single quotes because they aren't necessary when dealing with numeric values in SQL. If the id values were strings, each would have to be encapsulated inside of single quotes.

answered Dec 14, 2010 at 1:52

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

2 Comments

Ben Over a year ago

does the mysql_real_escape_string() escape each of the values in the array? or does it escape the array in general? i have never used it in this way.

OMG Ponies Over a year ago

@Ben: It doesn't escape each value. It's a poor example because of the numeric data, but if your array contained characters that were illegal to SQL/MySQL -- the illegal characters would be properly escaped.

Dan Dorman · Accepted Answer · 2010-12-14 01:57:38Z

What you want is SQL's IN clause.

SELECT * FROM posts WHERE id IN (1, 4, 7, 11, 120)

In PHP, you'll probably want something like:

$query = mysql_query(sprintf("SELECT * FROM posts WHERE id IN (%s)", implode(',', $id)));

Obviously, that's assuming you know you have integer values for $id, and that the values for $id didn't come from the user (that is, they should be sanitized). To be safe, you really ought to do something like:

$ids = implode(',', array_map('mysql_real_escape_string', $id));
$query = mysql_query("SELECT * FROM posts WHERE id IN ($ids)");

And if $id is dynamically generated, don't forget to put something in that IN clause, because SELECT * FROM foo WHERE bar IN () will give you an error. I generally make sure to set my IN-clause variables to 0, since IN (0) is good, and primary keys are pretty much never 0.

Collectives™ on Stack Overflow

How do i select multiple rows which match the value of an item in an array?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related