PHP - Using Pdo With in Clause Array

PHP - Using PDO with IN clause array

PDO is not good with such things. You need to create a string with placeholders dynamically and insert it into the query, while binding array values the usual way. With positional placeholders it would be like this:

$in  = str_repeat('?,', count($in_array) - 1) . '?';
$sql = "SELECT * FROM my_table WHERE my_value IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($in_array);
$data = $stm->fetchAll();

In case there are other placeholders in the query, you could use the following approach (the code is taken from my PDO tutorial):

You could use array_merge() function to join all the variables into a single array, adding your other variables in the form of arrays, in the order they appear in your query:

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE foo=? AND column IN ($in) AND bar=? AND baz=?";
$stm = $db->prepare($sql);
$params = array_merge([$foo], $arr, [$bar, $baz]);
$stm->execute($params);
$data = $stm->fetchAll();

In case you are using named placeholders, the code would be a little more complex, as you have to create a sequence of the named placeholders, e.g. :id0,:id1,:id2. So the code would be:

// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];

$ids = [1,2,3];
$in = "";
$i = 0; // we are using an external counter 
        // because the actual array keys could be dangerous
foreach ($ids as $item)
{
    $key = ":id".$i++;
    $in .= ($in ? "," : "") . $key; // :id0,:id1,:id2
    $in_params[$key] = $item; // collecting values into a key-value array
}

$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stm = $db->prepare($sql);
$stm->execute(array_merge($params,$in_params)); // just merge two arrays
$data = $stm->fetchAll();

Luckily, for the named placeholders we don't have to follow the strict order, so we can merge our arrays in any order.

PDO with WHERE... IN queries

Since you can't mix Values (the Numbers) with control flow logic (the commas) with prepared statements you need one placeholder per Value.

$idlist = array('260','201','221','216','217','169','210','212','213');

$questionmarks = str_repeat("?,", count($idlist)-1) . "?";

$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` IN ($questionmarks)");

and loop to bind the parameters.

PDO Query with in clause that contain an Array won't work

You don't want to bind the imploded list as one element but rather each of the values individually using ? so the end of the statement would be WHERE EMAIL IN (?,?):

$values  = ["Email1","Email2"];
# This should give you ?,?
$bindstr = implode(",",array_fill(0,count($values),'?'));
$query = $connection->prepare("SELECT ID FROM USERS WHERE EMAIL IN({$bindstr})");
# Use the raw values individually in the execute
$query->execute($values);

Hopefully that should get results back you are looking for.

PDO with WHERE... IN queries

Since you can't mix Values (the Numbers) with control flow logic (the commas) with prepared statements you need one placeholder per Value.

$idlist = array('260','201','221','216','217','169','210','212','213');

$questionmarks = str_repeat("?,", count($idlist)-1) . "?";

$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` IN ($questionmarks)");

and loop to bind the parameters.

PHP PDO with IN clause and LIMIT

This seems a simpler (and infinitely faster) way of doing much the same thing:

SELECT t.*
  FROM following f
  JOIN user_tweets t
    ON t.user_id = f.following_user_id
 WHERE f.user_id=?
 ORDER 
    BY t.tweet_date DESC

MYSQL - PDO with multiple IN clause

Your current query comes out as

SELECT * FROM my_table WHERE (my_value1 IN (?,?,?)) AND (my_value2 IN (?,?,?))

So your execute usage is incorrect, http://php.net/manual/en/pdostatement.execute.php. It should only be passing one array with values inside it.

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

I think using array_merge, http://php.net/manual/en/function.array-merge.php, will allow you to accomplish what you are trying

$stm->execute(array_merge($in_array1,$in_array2));

This way the execute is the equivalent of

$stm->execute(array(1,2,3,4,5,1));

This may seem incorrect because the array pairings are now gone but placeholder 1 (the first question mark) will map to 1, placeholder 4 to 4, and so on.

Use PDO to bind array

It's not possible to bind an array to a list used in an IN clause. You can however bind multiple scalar values like so:

where location.region in (:region1, :region2, :region3, [...])

$params = array(
    ':region1' => $regions[0],
    ':region2' => $regions[1],
    ':region3' => $regions[2]
    [...]
);

$stmt->execute($params);

You'd need to programatically build up the IN ($placeholders) part of the query to handle a dynamic number of parameters.