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.

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.

ints:

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

strings:

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

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.

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.

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;
       }

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)";

For mysqli with escape function:

$ids = array_map(function($a) use($mysqli) { 
    return is_string($a) ? "'".$mysqli->real_escape_string($a)."'" : $a;
  }, $ids);
$ids = join(',', $ids);  
$result = $mysqli->query("SELECT * FROM galleries WHERE id IN ($ids)");

for PDO with prepared statesment:

$qmarks = array_implode(',', array_fill(0, count($ids), '?'));
$sth = $dbh->prepare("SELECT * FROM galleries WHERE id IN ($qmarks)");
$sth->execute($ids);

$ids = join(',',$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)";

You can use "implode" or "join" for array to string

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

mysql_query($sql);

We can use this "WHERE id IN" clause if we filter the input array properly. Something like this:

$galleries = array();

foreach ($_REQUEST['gallery_id'] as $key => $val) {
    $galleries[$key] = filter_var($val, FILTER_SANITIZE_NUMBER_INT);
}

Like the example below:

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

I.e. now you should safely use $query = "SELECT * FROM galleries WHERE id IN ({$galleryIds})";

use mysql IN function

$galleries = array(1,2,5);
$galleriesClause = implode(",",$galleries);

SELECT *
FROM galleries
WHERE id IN ($galleriesClause)

We should take care of SQL injection vulnerabilities and empty condition. I am going to handle both as below.

For pure numeric array use appropriate type conversion viz intval or floatval or doubleval over each element. For string types mysqli_real_escape_string() which may also be applied to numeric values if you wish. MySQL allows numbers as well as date variants as string.

To appropriately escape the values before passing to the query create a function similar to:

function escape($string)
{
    // Assuming $db is a link identifier returned by mysqli_connect() or mysqli_init()
    return mysqli_real_escape_string($db, $string);
}

Such function would most likely be already available to you in your application, or may be you've already created one.

Sanitize string array like:

$values = array_map('escape', $gallaries);

Numeric array can be sanitized using intval or floatval or doubleval instead as suitable:

$values = array_map('intval', $gallaries);

Then finally build the query condition

$where  = count($values) ? "`id` = '" . implode("' OR `id` = '", $values) . "'" : 0;

or

$where  = count($values) ? "`id` IN ('" . implode("', '", $values) . "')" : 0;

Since the array can also be empty soemtimes, like $galleries = array(); therefore we should note that IN () does not allow for an empty list. One can also use OR instead but problem remains. So the above check count($values) is to ensure the same.

And add it to the final query:

$query  = 'SELECT * FROM `galleries` WHERE ' . $where;

TIP: If you want to show all records (no filtering) in case of empty array instead of hiding all rows, simply replace 0 with 1 in the ternary's false part.