How to Bind an Array to an In() Condition in a Pdo Query

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.

PDO: Bind an array to an IN() condition with multiple WHERE clauses?

execute accepts a single array of parameters. You need to do:

$sth->execute(array_merge($ids, [$location_id]));

php Bind array to IN()

You're not binding an array. You're converting your array into a string, and then binding that string. Bound parameters can represent only SINGLE values, not lists-of-values, so your implode/bind version is functionally identical to

SELECT ... foo IN ('1,2,3')

which executes exactly the same as

SELECT ... foo = '1,2,3'                   

instead of

SELECT ... IN (1,2,3)
               ^-^-^--- three separate values

What you want is simply not possible with a single placeholder. You need to build your query dynamically (one placeholder per value), and then you pass your array in to the query call, e.g.

$temp = array_fill(1, count($customer_ids), '?');
$in_arg = implode(',', $temp);
$sql = "SELECT ... IN ($in_arg)";
$stmt = $db->prepare(sql);
$res = $stmt->execute($customer_ids);

This would produce a query string like

SELECT ... IN (?,?,?,....)

with a placeholder for every value you want to bind.

PDO bind array to Where IN

You could use some string manipulation.

You can count the number of ? you'd need by using str_repeat("?", count(explode(",", $refIdsPartial))). This will create your placeholders.

$totalCount = 
"SELECT referral, COUNT(username) AS cnt FROM accounts
WHERE referral IN (". str_repeat("?,", count(explode(",", $refIdsPartial))-1) . "?) GROUP BY referral";

Now that the placeholders are in place, you can explode the , from the string and execute

$ps_totalCounts->execute( explode(",", $refIdsPartial) );

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.

php pdo bind array parameters in bindParam without foreach loop

Many PDO users think they have to use bindParam(). You don't.

You can pass an array directly to execute() with all your parameter values. It's this easy:

$stmt->execute($data);

If you used named parameters in your SQL, use a hash array. If you used positional parameters, use a plain array.

For more complete code examples, read them here: http://php.net/manual/en/pdo.prepare.php

Conditional query with PDO prepare and bind statement

you can use handy PDO's feature that lets you to send array with parameters straight into execute()

$where  = '';
$params = array();
if (isset($parameters['searchTerm'])) {
    $where =" And title LIKE :searchTerm";
    $params['searchTerm'] = "%$parameters[searchTerm]%";
}
$sql = "Select * from table data Where tableId = 5 $where";
$pdo->prepare($sql)->execute($params);

Note that PHP syntax in your code is also wrong.

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.

Binding parameters for WHERE IN clause with PDO

You can't bind a parameter for the IN clause like that. The $myArray string will only count as one value, like if you did this:

SELECT foo FROM bar WHERE ids IN ('1,2,3')

Even though there are three comma delimited values, the database reads them as only one string value.

You need to manually insert the IN list into the query, the old-school way.

'SELECT foo FROM bar WHERE ids IN (' . $myArray .')'

There is unfortunately no other way. At least for now.

---

Related Topics