Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:109228
To: "internals@lists.php.net" <internals@lists.php.net>
Thread-Topic: [PHP-DEV] Making mysqli easier to use with parameters
Thread-Index: AQHWAEfNiOygEIXUHkiL3tGqAolHVahUvIGAgACHigCAANThUA==
Date: Mon, 23 Mar 2020 12:19:37 +0000
Message-ID:
 <BN8PR18MB27248F6A4E0C60B9A6B6572BEDF00@BN8PR18MB2724.namprd18.prod.outlook.com>
References: <3CE6E742-33E9-4686-93C4-2B1DB1E405B7@craigfrancis.co.uk>
 <CAAKU0ukCOxNbCa1vdi0NRFcfqsL2190-1_xqLoiwjRSyiWDzhg@mail.gmail.com>
 <10B08096-5A36-4F69-B60F-3CCC8AF7F66D@craigfrancis.co.uk>
In-Reply-To: <10B08096-5A36-4F69-B60F-3CCC8AF7F66D@craigfrancis.co.uk>
Accept-Language: en-US
Content-Language: en-US
received-spf: None (protection.outlook.com: onlinecommercegroup.com does not
 designate permitted sender hosts)
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-Network-Message-Id: c760cc38-4083-40da-f66a-08d7cf2474c7
X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Mar 2020 12:19:38.0089
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 733bee1d-43aa-48a2-a713-34870bf1749b
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: +Ga4hWR6/Y7wKYSV716d/niEXiYR80GDtCuotRRY/TYuC/zPVmZOJFJvNyJ0UeXf
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN8PR18MB2577
Subject: RE: [PHP-DEV] Making mysqli easier to use with parameters
From: Joel.Hutchinson@onlinecommercegroup.com (Joel Hutchinson)

Hey Craig, I think this might be in the same vein as something I'd love to =
see done (PDO already does this). Proposed it a while back and have talked =
to a few internals folks about it off and on

https://externals.io/message/107857#107857

Either way, mysqli lacks any proper way to do dynamic binds on a single bas=
is. This seems like low hanging fruit to make mysqli easier to use

-----Original Message-----
From: Craig Francis <craig@craigfrancis.co.uk>=20
Sent: Sunday, March 22, 2020 6:34 PM
To: Levi Morrison <levi.morrison@datadoghq.com>
Cc: PHP internals <internals@lists.php.net>
Subject: Re: [PHP-DEV] Making mysqli easier to use with parameters

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 t=
o `$statement->execute()` would remove a step, and be a bit easier to use f=
or 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> wro=
te:
>=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'),=20
>> $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=20
> 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=20
> by-reference passing.
>=20
> I don't think we need to improve the ergonmics of mysqli for this case=20
> specifically, as it's always going to require some dynamic SQL=20
> generation because of the variable number of parameters to bind.
>=20
>  [1]: https://stackoverflow.com/a/23641033/538216

--
PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit:=
 http://www.php.net/unsub.php