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

Question

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.

Not familiar with SQLLite, but I believe you'll need to put a placeholder in place of each value in your query (e.g. 3 `?`s in your current example). Then bind each value from the array; or pass the array in the `execute`. — chris85, Apr 13 '16 at 01:27
@chris85: it will only work if array has less than 1000 elements. I want a solution to work in all cases. thanks. — vaibhavatul47, Apr 17 '16 at 10:48
Is the limit you are hitting `PHP` or `sqllite`? Are you trying to take +1000 `POST` values? — chris85, Apr 17 '16 at 16:44
Its sqlite limit. https://www.sqlite.org/limits.html see 9th point — vaibhavatul47, Apr 17 '16 at 17:05
I'm curious as to find out why you need to filter by more than 1000 values... — Gustavo Rubio, Apr 22 '16 at 19:29
As I remember you need to bind a variable and it seems it will be accessed by reference, if you change it, next SQL-query will be changed too. Why you don't use `implode` instead of loop? Something like that: `$sql = "SELECT * FROM table_name WHERE id IN (".implode(',',$params).")";`. Is your `id`-field can be any type at the same time ? [Here](http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition) is similar question. — Wizard, Apr 23 '16 at 12:34

score 9 · Accepted Answer · edited May 23 '17 at 12:23

9

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

The easiest solution for you problem would be the following:

Create the SQL-Query with one placeholder (?) per value in the array
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.

edited May 23 '17 at 12:23

Community

1
1

answered Apr 17 '16 at 11:01

manniL

7,157
7
46
72

This solution has a limit of 999. I cannot bind more than 999 values. Any hint on how to change this default limit in php sqlite ? – vaibhavatul47 Apr 17 '16 at 12:00
@AtulVaibhav Sure you can change the SQLite limits. More information [on the official SQLite page](https://www.sqlite.org/limits.html). If this doesn't suit you, you can try other solutions from the link in my answer. – manniL Apr 17 '16 at 14:43

score 2 · Answer 2 · answered Apr 17 '16 at 11:18

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

Rick James · Answer 3 · 2016-04-23T15:38:54.083

(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.

score 2 · Answer 4 · answered Apr 19 '16 at 08:36

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]));

score 1 · Answer 5 · answered Apr 17 '16 at 11:17

1

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.

answered Apr 17 '16 at 11:17

Zamrony P. Juhara

5,222
2
24
40

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

5 Answers5