4

According to https://www.php.net/manual/en/mysqli-stmt.bind-param.php, the different types are:

i   corresponding variable has type integer
d   corresponding variable has type double
s   corresponding variable has type string
b   corresponding variable is a blob and will be sent in packets

However, how can you handle this:

->prepare("SELECT blabla FROM foo WHERE id IN (?)")

Where ? would be a list of ids. There could be one or more items:

$ids = "3,4,78";
->bind_param('s',$ids);

Is that possible?

I'd like to use prepared statements because it will be executed in a loop.

Improve this question
1

4 Answers 4

Reset to default
3

The proper syntax would be

->prepare("SELECT blabla FROM foo WHERE id IN (?, ?, ?)")

for 3 items in that array, for example. You would then have to bind each of those items individually using bind_param().

If you don't have a size guarantee on the array, you'll have to write a couple helper functions to generate your SQL with the proper number of "?"'s and bind statements.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Indeed I do not know the size in advance. If I re-write the prepared statement for each iteration of the loop (to change the number of ?s), then it defeats the purpose of prepared statements.
I'm not quite sure what you want to accomplish in the loop. Will the values bound to the statement be changing each time? Are the number of values bound changing? Are you executing an identical statement multiple times (don't know why you'd do this)?
the number of values IN(?,?,?) change each loop iteration sometimes it could be (1,2,3,4), or (1,2,3,4,5,6,7,8,9) ...
@nute. It may slightly defeat the speed advantage, but it still has the advantage of being safe from from malicious sql injection. You can always gain back the speed advantage of pre-preparing one statement per length-of-in clause.
3

If you have a list of variables that differs in size every call that you wanto to bind to an IN-statement, the most simple way would be to generate the SQL string programatically and use a loop to bind the variables:

/**
 * @param  array  $values
 * @param  mysqli $db
 * @return mysqli_stmt
 */
function bindInValues(array $values, mysqli $db)
{
    $sql = sprintf('SELECT blabla FROM foo WHERE id IN (%s)',
        implode(', ', array_fill(0, count($values), '?'))
    );
    $stmt = $db->prepare($sql);
    foreach ($values as $value) {
        $stmt->bind_param('s', $value);
    }
    return $stmt;
}

If you like call_user_func_array you can use dynamic method invocation and go along without a loop.

/**
 * @param  array  $values
 * @param  mysqli $db
 * @return mysqli_stmt
 */
function bindInValues(array $values, mysqli $db)
{
    $sql = sprintf('SELECT blabla FROM foo WHERE id IN (%s)',
        implode(', ', array_fill(0, count($values), '?'))
    );
    $stmt = $db->prepare($sql);
    array_unshift($values, implode('', array_fill(0, count($values), 's')));
    call_user_func_array(array($stmt, 'bind_param'), $values);
    return $stmt;
}
Improve this answer

3 Comments

this of course means the use of Prepared Statements isn't really efficient anymore, since you're "preparing" at every iteration, instead of just once. Because I mean to send that SELECT statement many times in a row, with different number of params every time.
You're right - that's the inevitable consequence... But there is no other way around.
If you're sending a different query (related to the SQL structure not the values) every time, you can skip the preparation step and directly build the SQL including the IN values.
-1

What about doing this: 

$sql = sprintf("SELECT blabla FROM foo WHERE id IN(%s) ", $ids);
$stmt = $mysqli->prepare($sql);
if (!$stmt) {
    $this->throwException();
}

if (!$stmt->execute()) {
    $this->throwException();
}

If it is not good, please tell me why so I can learn from my errors. Thanks!

Improve this answer

2 Comments

Can someone explain me why is a -1 ?
I think it might be the same as the comments in this answer, stackoverflow.com/a/1534520/1253832
-4

I think I found the answer to my question:

->prepare("SELECT stuff FROM table_name WHERE id IN (?)");

$itemList = implode(',',$items);
$children->bind_param('s',$itemList);

Seems to be working fine when using a string with coma-separated values. I'm still checking if the results are really accurate ...

Improve this answer

2 Comments

Hmm, that shouldn't be working. The prepared statement should wind up being SELECT stuff FROM table_name WHERE id IN ('1, 2, 3, 4, 5') in that situation, which shouldn't work as you expect...
However it does seem to work fine so far ... If you've got a better idea, please let me know!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.