Re: Making mysqli easier to use with parameters
| From: | Levi Morrison via internals | Date: | Sun, 22 Mar 2020 15:28:27 +0000 |
| Subject: | Re: Making mysqli easier to use with parameters | ||
| References: | 1 | Groups: | php.internals |
| Request: | Send a blank email to internals+get-109203@lists.php.net to get a copy of this message | ||
> <?php
>
> $in_sql = implode(',', array_fill(0, count($ids), '?'));
>
> $sql = 'SELECT id, name FROM user WHERE id IN (' . $in_sql . ')';
>
> if ($statement = $db->prepare($sql)) {
>
> $params = [str_repeat('i', count($ids))];
> foreach ($ids as $key => $value) {
> $params[] = &$ids[$key]; // Must be a reference, not ideal.
> }
> call_user_func_array(array($statement, 'bind_param'), $params);
>
> $statement->execute();
>
> $result = $statement->get_result();
>
> while ($row = mysqli_fetch_assoc($result)) {
> print_r($row);
> }
>
> }
>
> ?>
Written in my email client without error handling code, so apologies
if it's not quite correct:
$in = join(',', array_fill(0, count($ids), '?'));
$select = "SELECT id, name FROM user WHERE id IN ({$in});";
$statement = $mysqli->prepare($select);
$statement->bind_param(str_repeat('i', count($ids)), ...$ids);
$statement->execute();
This is part of my [highest score answer on StackOverflow][1].
Critically, applying the ids array as
... will handle the
by-reference passing.
I don't think we need to improve the ergonmics of mysqli for this case
specifically, as it's always going to require some dynamic SQL
generation because of the variable number of parameters to bind.
[1]: https://stackoverflow.com/a/23641033/538216