Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:109203
MIME-Version: 1.0
References: <3CE6E742-33E9-4686-93C4-2B1DB1E405B7@craigfrancis.co.uk>
In-Reply-To: <3CE6E742-33E9-4686-93C4-2B1DB1E405B7@craigfrancis.co.uk>
Reply-To: Levi Morrison <levi.morrison@datadoghq.com>
Date: Sun, 22 Mar 2020 09:28:27 -0600
Message-ID: <CAAKU0ukCOxNbCa1vdi0NRFcfqsL2190-1_xqLoiwjRSyiWDzhg@mail.gmail.com>
To: Craig Francis <craig@craigfrancis.co.uk>
Cc: PHP internals <internals@lists.php.net>
Content-Type: text/plain; charset="UTF-8"
Subject: Re: [PHP-DEV] Making mysqli easier to use with parameters
From: internals@lists.php.net ("Levi Morrison via internals")

> <?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