PHP/MYSQL using an array in WHERE clause

In SQL the IN() operator checks if a value exists in a given set.

The PHP code below uses prepared statements for MySQLi or PDO to help mitigate SQL injection attacks. The variable $ids is an array that is assumed to contain at least one value. Error checking is omitted for brevity; you need to check for the usual errors for each database method (or set your DB driver to throw exceptions).

$in = join(',', array_fill(0, count($ids), '?'));

$select = <<<SQL
    SELECT *
    FROM galleries
    WHERE id IN ($in);
SQL;


// if using MySQLi (PHP 5.6):
$statement = $mysqli->prepare($select);
$statement->bind_param(str_repeat('i', count($ids)), ...$ids);
$statement->execute();
$result = $statement->get_result();

// if using PDO (or don't have PHP 5.6):
$statement = $pdo->prepare($select);
$statement->execute($ids);

Using the IN() operator with strings.

It is easy to change between strings and integers because of the bound parameters. For PDO there is no change required; for MySQLi change str_repeat('i', to str_repeat('s', if you need to check strings.

ints:

$query = "SELECT * FROM `$table` WHERE `$column` IN(".implode(',',$array).")";

strings:

$query = "SELECT * FROM `$table` WHERE `$column` IN('".implode("','",$array)."')";

How can I create a prepared statement for IN () operator?

Prepared statement can represent complete data literal only. Not a part of literal, nor a complex expression, nor identifier. But either string or number only. So, a very common pitfall is a query like this:

$ids = array(1,2,3);
$stm = $pdo->prepare("SELECT * FROM t WHERE id IN (?)");
$stm->execute(array($ids));

it will actually bind a word 'Array' and raise a warning.

One have to create a query with placeholders representing every array member, and then bind this array values for execution:

$ids = array(1,2,3);
$stm = $pdo->prepare("SELECT * FROM t WHERE id IN (?,?,?)");
$stm->execute($ids);

To make this query more flexible, it's better to create a string with ?s dynamically:

$ids = array(1,2,3);
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($ids);
$data = $stm->fetchAll();

Of course, if we have other variables to be bound, we need to add them to values array:

$ids = array(1,2,3);
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in) AND category=?";
$stm = $db->prepare($sql);
$ids[] = $category; //adding another member to array
$stm->execute($ids);
$data = $stm->fetchAll();

the code become quite bloated but that's all PDO can offer to handle such complex cases. As a further improvement one can invent their own placeholders to support such complex data types.

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.

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

Simple for each loop will work.

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

Besides using IN query you have two options to do so as In query there is a risk of SQL Injection vulnerability you can use looping to get the exact data you want or you can use the query with OR case

    1. SELECT *
          FROM galleries WHERE id=1 or id=2 or id=5;


    2. $ids = array(1, 2, 5);
       foreach ($ids as $id) {
          $data[] = SELECT *
                        FROM galleries WHERE id= $id;
       }

Because the original question relates to an array of numbers and I am using an array of strings I couldnt 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.

You may have table texts (T_ID (int), T_TEXT (text)) and table test (id (int), var (varchar(255)))

in insert into test values (1, '1,2,3') ;

the following will output rows from table texts where T_ID IN (1,2,3)

SELECT * FROM `texts` WHERE (SELECT FIND_IN_SET( T_ID, ( SELECT var FROM test WHERE id =1 ) ) AS tm) >0

this way you can manage a simple n2m db relation without extra table and using only SQL without need to us PHP or other programming lang.

As @Flavius Stef, you can use intval() to make sure all id is int values.

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

$ids = "(".implode(',',$galleries).")";  // you can remplace "implode" with "join"  
$sql = "SELECT * FROM galleries WHERE id IN $ids";

you can generate the queries by

$galleries = array(1,2,5);

$sql = "SELECT * FROM gallery WHERE id IN (". 
           implode(', ', 
                     array_map(function(&$item){ 
                                   return "'".$item."'";
                               }, $galleries)) 
        .");";

using array_map to add a single quote to each of elements in the $galleries. the generated $sql var will be :

SELECT * FROM gallery WHERE id IN ('1', '2', '5');

Safer.

$galleries = array(1,2,5);
array_walk($galleries , 'intval');
$ids = implode(',', $galleries);
$sql = "SELECT * FROM galleries WHERE id IN ($ids)";