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
SQL Injection That Gets Around mysql_real_escape_string()
Why Shouldn't I Use MySQL_* Functions in PHP
Showing All Errors and Warnings
How to Create Cron Job Using PHP
PHP Code Is Not Being Executed, But the Code Shows in the Browser Source Code
Replace Preg_Replace() E Modifier With Preg_Replace_Callback
Cross-Origin Request Headers(Cors) With PHP Headers
How to Get Time Difference in Minutes in PHP
Show a Number to Two Decimal Places
PHP - Ssl Certificate Error: Unable to Get Local Issuer Certificate
Laravel, Sync() - How to Sync an Array and Also Pass Additional Pivot Fields
How to Parameterize the Table Name in a Prepared Statement
Call to a Member Function on a Non-Object
How to Remove the Extra Square Bracket from Json Having Multiple Arrays
How to Get Newest Data With 'Created_At' Column