I'm curious to know if it's possible to bind an array of values to a placeholder using PDO. The use case here is attempting to pass an array of values for use with an IN() condition. I'm not very good at explaining, so here's some psuedocode to demonstrate... I'd like to be able to do something like this:
<?php
$ids=array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id IN(:an_array)'
);
$stmt->bindParam('an_array',$ids);
$stmt->execute();
?>
And have PDO bind and quote all the values in the array.
At the moment I'm doing:
<?php
$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
foreach($ids as &$val)
$val=$db->quote($val); //iterate through array and quote
$in = implode(',',$ids); //create comma separated list
$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id IN('.$in.')'
);
$stmt->execute();
?>
Which certainly does the job, but just wondering if there's a built in solution I'm missing?
Cheers!
Source: Tips4all
i think soulmerge is right. you'll have to construct the query-string.
ReplyDelete<?php
$ids = array(1, 2, 3, 7, 8, 9);
$inQuery = implode(',', array_fill(0, count($ids), '?'));
$db = new PDO(...);
$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id IN(' . $inQuery . ')'
);
// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
$stmt->bindValue(($k+1), $id);
$stmt->execute();
?>
fix: dan, you were right. fixed the code (didn't test it though)
Looking at PDO :Predefined Constants there is no PDO::PARAM_ARRAY which you would need as is listed on PDOStatement->bindParam
ReplyDeletebool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type [, int $length [, mixed $driver_options ]]] )
So I don't think it is achievable.
Solution from EvilRygy didn't worked for me. In Postgres you can do another workaround:
ReplyDelete$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id = ANY (string_to_array(:an_array, ','))'
);
$stmt->bindParam(':an_array', implode(',', $ids));
$stmt->execute();
I extended PDO to do something similar to what stefs suggests, and it was easier for me in the long run:
ReplyDeleteclass Array_Capable_PDO extends PDO {
/**
* Both prepare a statement and bind array values to it
* @param string $statement mysql query with colon-prefixed tokens
* @param array $array_tokens list of tokens to replace with array data
* @param array $arrays list of arrays containing data to bind to the statement
* @param array $driver_options see php documention
* @return PDOStatement with given array values already bound
*/
public function prepare_with_arrays($statement, $array_tokens, array $arrays,
$driver_options = array()) {
// there should be an array of values for each token
assert(count($array_tokens) === count($arrays));
// a given token shouldn't appear more than once in the query
foreach($array_tokens as $token) {
assert(substr_count($statement, $token) === 1);
}
// replace array tokens with a list of value tokens
$replace_strings = array();
foreach($arrays as $x => $sub_array) {
assert(is_int($x));
$replace_string_pieces = array();
foreach($sub_array as $y => $value) {
$replace_string_pieces[] = ":{$x}_{$y}";
}
$replace_strings[] = '('.implode(', ', $replace_string_pieces).')';
}
$statement = str_replace($array_tokens, $replace_strings, $statement);
$prepared_statement = $this->prepare($statement, $driver_options);
// bind values to the value tokens
foreach($arrays as $x => $sub_array) {
foreach($sub_array as $y => $value) {
assert(is_int($y));
$prepared_statement->bindValue(":{$x}_{$y}", $value);
}
}
return $prepared_statement;
}
}
You can use it like this:
$db_link = new Array_Capable_PDO($dsn, $username, $password);
$query = '
SELECT *
FROM test
WHERE field1 IN :array1
OR field2 IN :array2
OR field3 = :value
';
$pdo_query = $db_link->prepare_with_arrays(
$query,
array(':array1', ':array2'),
array(
array(1,2,3),
array(7,8,9)
)
);
$pdo_query->bindValue(':value', 10');
$pdo_query->execute();
//$db = new PDO(...);
ReplyDelete//$ids = array(...);
$qMarks = str_repeat('?,', count($ids) - 1) . '?';
$sth = $db->prepare("DELETE FROM myTable WHERE id NOT IN ($qMarks)");
$sth->execute($ids);
My chiropractor recommended this.
What database are you using? In PostgreSQL I like using ANY(array). So to reuse your example:
ReplyDelete<?php
$ids=array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id = ANY (:an_array)'
);
$stmt->bindParam('an_array',$ids);
$stmt->execute();
?>
Unfortunately this is pretty non-portable.
On other databases you'll need to make up your own magic as others have been mentioning. You'll want to put that logic into a class/function to make it reusable throughout your program of course. Take a look at the comments on mysql_query page on PHP.NET for some more thoughts on the subject and examples of this scenario.
a little editing about the code of Schnalle
ReplyDelete<?php
$ids = array(1, 2, 3, 7, 8, 9);
$inQuery = implode(',', array_fill(0, count($ids)-1, '?'));
$db = new PDO(...);
$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id IN(' . $inQuery . ')'
);
foreach ($ids as $k => $id)
$stmt->bindValue(($k+1), $id);
$stmt->execute();
?>
//implode(',', array_fill(0, count($ids)-1), '?')); //'?' this should be inside the array_fill
//$stmt->bindValue(($k+1), $in); // instead of $in, it should be $id
//All are just typos, thanks Bro, i appreciate it God Bless