PHP - How to replace an array as a node parameter in a prepared message

Question

I can bind values ​​of type int , str , bool and null , but I cannot bind an array type.
I tried both functions i.e. bindValue and bindParam , but none of them worked. How can i do 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: Have I tried replacing a single ? for the required number of question marks in the param array, but then it only works if my array has less than 1000 values! I think this is a limitation on how statements are prepared in SQLite3 in PHP.

Answer 1

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

The easiest solution to your problem:

• Create SQL-Query with one placeholder ( ? ) Per value in an array
• Bind each value, iterating over the array.

But there are other parameters (e.g. sub-SELECT)

More information here (even if it is a Java question, it is almost the same topic / problem, because the database type does not matter in this case)

EDIT: Typically, the SQL constraint for related parameters is set to 999 , but you can change it if you need to .

Answer 2

You cannot bind arrays as a list for an IN (?) . Each value in the IN list must have its own separate place holder.

To make this dynamic, first define an array of values, and then dynamically create the SQL.

This will 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 

Answer 3

(Caveat: this answer was written before the mysql tag was removed, I don't know if addslashes works for sqlite3.)

In PHP, $ list is given as an array of values ​​for the 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) ...";; 

gives

 ... IN ('1', '2', 'abcd', 'double quote: \"', 'apostrophe: don\'t') ... 

(Yes, this can be done using the usual for loop without using array_map and the "anonymous function".)

Do not worry; quotes around numbers ( '123' ) are OK for numeric columns.

Answer 4

I would suggest a job in what you are trying 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])); 

Answer 5

You can add? placeholder as much as the number of elements in your array

 Select * from table_a where field in (?,?,?,?,?,?,?,.....) 

If they are more than 1000, divide them into two or more queries.