Passing an array to a query using a WHERE clause

Admin 1
Viewed 651108

Given an array of ids $galleries = array(1,2,5) I want to have a SQL query that uses the values of the array in its WHERE clause like:

SELECT *
FROM galleries
WHERE id = /* values of array $galleries... eg. (1 || 2 || 5) */

How can I generate this query string to use with MySQL?

mysql php
17 Answers

BEWARE! This answer contains a severe SQL injection vulnerability. Do NOT use the code samples as presented here, without making sure that any external input is sanitized.

$ids = join("','",$galleries);   
$sql = "SELECT * FROM galleries WHERE id IN ('$ids')";
user_1081680

Assuming you properly sanitize your inputs beforehand...

$matches = implode(',', $galleries);

Then just adjust your query:

SELECT *
FROM galleries
WHERE id IN ( $matches )

Quote values appropriately depending on your dataset.

user_586150

Use:

select id from galleries where id in (1, 2, 5);

A simple for each loop will work.

Flavius/AvatarKava's way is better, but make sure that none of the array values contain commas.

user_477730

Safe way without PDO:

$ids = array_filter(array_unique(array_map('intval', (array)$ids)));

if ($ids) {
    $query = 'SELECT * FROM `galleries` WHERE `id` IN ('.implode(',', $ids).');';
}
  • (array)$ids Cast $ids variable to array
  • array_map Transform all array values into integers
  • array_unique Remove repeated values
  • array_filter Remove zero values
  • implode Join all values to IN selection
user_491140

Because the original question relates to an array of numbers and I am using an array of strings I couldn't make the given examples work.

I found that each string needed to be encapsulated in single quotes to work with the IN() function.

Here is my solution

foreach($status as $status_a) {
        $status_sql[] = '\''.$status_a.'\'';
    }
    $status = implode(',',$status_sql);

$sql = mysql_query("SELECT * FROM table WHERE id IN ($status)");

As you can see the first function wraps each array variable in single quotes (\') and then implodes the array.

NOTE: $status does not have single quotes in the SQL statement.

There is probably a nicer way to add the quotes but this works.

user_5847280

Below is the method I have used, using PDO with named placeholders for other data. To overcome SQL injection I am filtering the array to accept only the values that are integers and rejecting all others.

$owner_id = 123;
$galleries = array(1,2,5,'abc');

$good_galleries = array_filter($chapter_arr, 'is_numeric');

$sql = "SELECT * FROM galleries WHERE owner=:OWNER_ID AND id IN ($good_galleries)";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
    "OWNER_ID" => $owner_id,
));

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
user_46982420
Related