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.
Unfortunately this is not possible! You cannot bind an array.
The easiest solution for you problem would be the following:
?) per value in the arrayBut 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.
You can add ? placeholder as many as number of items in your array
Select * from table_a where field in (?,?,?,?,?,?,?,.....)
If they are bigger than 1000 then split them into two or more queries.
You cannot bind arrays as a list for an IN (?) clause. Each value in the IN list must get its distinct place holder.
To make this dynamic, first determine the array of values and then dynamically build the SQL.
This would be your code:
$arrayParam = [10, 9, 6];
$placeHolders = implode(',', array_fill(0, count($arrayParam), '?'));
$sql = "SELECT * FROM table_name WHERE id IN ($placeHolders) AND name = ?";
// Merge the "array" parameter values with any other parameter values
// into one non-nested array:
$params = array_merge($arrayParam, ['myname']);
// ...
foreach ($params as $index => $val) {
// No sub arrays allowed:
$ok = $stmt->bindValue($index + 1, $val, getArgType($val));
// ... etc
(Caveat: This answer was written before the 'mysql' tag has been removed; I don't know if it addslashes works for sqlite3.)
In PHP, given $list as an array of values destined for an IN list:
$list = array(1, 2, 'abcd', 'double quote: "', "apostrophe: don't");
$ins = implode(', ', array_map(
function($a) {
return "'" . addslashes($a) . "'";
}, $list));
echo $sql = "... IN ($ins) ...";;
yields
... IN ('1', '2', 'abcd', 'double quote: \"', 'apostrophe: don\'t') ...
(Yes, this could be done with a normal for loop, without using array_map and an "anonymous function".)
Don't worry; quotes around numbers ('123') is OK for numeric columns.
I would propose a work-around in what you try to do. Pass the query result to a temporary table.
// 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));
}
}
function getTempValues() {
$sql = "SELECT * FROM `myTemp`";
$params = [$in]; // array of array
$dbpath = '/path/to/sqlite.sqlite';
$db = new SQLite3($dbPath, SQLITE3_OPEN_READONLY);
$stmt = $db->prepare($sql);
$result = $stmt->execute();
$data = [];
while ($row = $result->fetchArray($mode)) {
$data[] = $row;
}
return $data;
}
function addToTemp($in) {
$sql = "SELECT * INTO `myTemp` FROM `table_name` WHERE `id` = ?";
$params = [$in]; // array of array
$dbpath = '/path/to/sqlite.sqlite';
$db = new SQLite3($dbPath, SQLITE3_OPEN_READONLY);
$stmt = $db->prepare($sql);
if(is_array($in)) {
foreach($in as $newValue) {
addToTemp($newValue);
}
} else {
try {
foreach ($params as $index => $val) {
$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);
}
$stmt->execute();
}
return getTempValues();
}
print_r(addToTemp([10,9,6]));