Atul Vaibhav Atul Vaibhav - 6 months ago 19
SQL Question

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

I am able to bind values of type

int
,
str
,
bool
and
null
but I am unable to bind array type.

I have tried both functions, i.e.
bindValue
and
bindParam
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;
default:
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();
die($reason);
}

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


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.

Answer

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.