Atul Vaibhav Atul Vaibhav - 1 year ago 60
SQL Question

PHP - How to substitute array as host parameter in prepared statement

I am able to bind values of type

but I am unable to bind array type.

I have tried both functions, i.e.
but neither of them worked.
How can I accomplish this ?

// a helper function to map Sqlite data type
function getArgType($arg) {
switch (gettype($arg)) {
case 'double': return SQLITE3_FLOAT;
case 'integer': return SQLITE3_INTEGER;
case 'boolean': return SQLITE3_INTEGER;
case 'NULL': return SQLITE3_NULL;
case 'string': return SQLITE3_TEXT;
throw new \InvalidArgumentException('Argument is of invalid type '.gettype($arg));

$sql = "SELECT * FROM table_name WHERE id IN (?)";
$params = [[10, 9, 6]]; // array of array
$dbpath = '/path/to/sqlite.sqlite';
$db = new SQLite3($dbPath, SQLITE3_OPEN_READONLY);
$stmt = $db->prepare($sql);

try {
foreach ($params as $index => $val) {
if (is_array($val)) {
/************* I am stuck here *************/
$ok = $stmt->bindParam($index + 1, $val);
// Using bindValue also didn't worked!
} else {
$ok = $stmt->bindValue($index + 1, $val, getArgType($val));

if (!$ok) {
throw new Exception("Unable to bind param: $val");
} catch (Exception $ex) {
// NO exception is thrown from bindValue() or bindParam()
$reason = "Error in binding statement. " . $ex->getMessage();

$result = $stmt->execute();
$data = [];
while ($row = $result->fetchArray($mode)) {
$data[] = $row;

Edit: I already tried replacing single
with required number of question marks in param array, but then it is working only if my array has less than 1000 values! I think it's a limitation of how statements are prepared in SQLite3 in PHP.


Unfortunately this is not possible! You cannot bind an array.

The easiest solution for you problem would be the following:

  1. Create the SQL-Query with one placeholder (?) per value in the array
  2. Bind each value by iterating over the array.

But there are also another options (e.g. a sub-SELECT)

More information here (even if it's a Java question, it is nearly the same topic/problem because the database type doesn't matter in this case)

EDIT: Normally, the SQL Limit for bound parameters is set so 999, but you can change it if you need to.