Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:109217
Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.60.0.2.5\))
In-Reply-To: <CAAKU0ukCOxNbCa1vdi0NRFcfqsL2190-1_xqLoiwjRSyiWDzhg@mail.gmail.com>
Date: Sun, 22 Mar 2020 23:33:34 +0000
Cc: PHP internals <internals@lists.php.net>
Content-Transfer-Encoding: quoted-printable
Message-ID: <10B08096-5A36-4F69-B60F-3CCC8AF7F66D@craigfrancis.co.uk>
References: <3CE6E742-33E9-4686-93C4-2B1DB1E405B7@craigfrancis.co.uk>
 <CAAKU0ukCOxNbCa1vdi0NRFcfqsL2190-1_xqLoiwjRSyiWDzhg@mail.gmail.com>
To: Levi Morrison <levi.morrison@datadoghq.com>
Subject: Re: [PHP-DEV] Making mysqli easier to use with parameters
From: craig@craigfrancis.co.uk (Craig Francis)

On 22 Mar 2020, at 15:28, Levi Morrison <levi.morrison@datadoghq.com> =
wrote:
> applying the ids array as `...` will handle the by-reference passing.



That does help, thanks.

But I still wonder if the ability to pass in a single array of =
parameters to `$statement->execute()` would remove a step, and be a bit =
easier to use for all queries.

If this was for a search form, where it dynamically creates a SELECT =
with a variety of different parameters, it gets tricky again.

And there was the thing I tacked onto the end, where I would like to use =
a `$statement->result` property to skip the use of =
`$statement->get_result()`:

    while ($row =3D mysqli_fetch_assoc($statement->result)) {
    }

Craig




> On 22 Mar 2020, at 15:28, Levi Morrison <levi.morrison@datadoghq.com> =
wrote:
>=20
>> <?php
>>=20
>>    $in_sql =3D implode(',', array_fill(0, count($ids), '?'));
>>=20
>>    $sql =3D 'SELECT id, name FROM user WHERE id IN (' . $in_sql . =
')';
>>=20
>>    if ($statement =3D $db->prepare($sql)) {
>>=20
>>        $params =3D [str_repeat('i', count($ids))];
>>        foreach ($ids as $key =3D> $value) {
>>            $params[] =3D &$ids[$key]; // Must be a reference, not =
ideal.
>>        }
>>        call_user_func_array(array($statement, 'bind_param'), =
$params);
>>=20
>>        $statement->execute();
>>=20
>>        $result =3D $statement->get_result();
>>=20
>>        while ($row =3D mysqli_fetch_assoc($result)) {
>>            print_r($row);
>>        }
>>=20
>>    }
>>=20
>> ?>
>=20
> Written in my email client without error handling code, so apologies
> if it's not quite correct:
>=20
>    $in =3D join(',', array_fill(0, count($ids), '?'));
>    $select =3D "SELECT id, name FROM user WHERE id IN ({$in});";
>    $statement =3D $mysqli->prepare($select);
>    $statement->bind_param(str_repeat('i', count($ids)), ...$ids);
>    $statement->execute();
>=20
> This is part of my [highest score answer on StackOverflow][1].
> Critically, applying the ids array as `...` will handle the
> by-reference passing.
>=20
> 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.
>=20
>  [1]: https://stackoverflow.com/a/23641033/538216