Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:109248
MIME-Version: 1.0
References: <3CE6E742-33E9-4686-93C4-2B1DB1E405B7@craigfrancis.co.uk>
 <CAAKU0ukCOxNbCa1vdi0NRFcfqsL2190-1_xqLoiwjRSyiWDzhg@mail.gmail.com>
 <10B08096-5A36-4F69-B60F-3CCC8AF7F66D@craigfrancis.co.uk> <BN8PR18MB27248F6A4E0C60B9A6B6572BEDF00@BN8PR18MB2724.namprd18.prod.outlook.com>
In-Reply-To: <BN8PR18MB27248F6A4E0C60B9A6B6572BEDF00@BN8PR18MB2724.namprd18.prod.outlook.com>
Date: Mon, 23 Mar 2020 19:29:36 +0000
Message-ID: <CAFv4g+HzvrVvhtiCkXYB9vt4+eT0VJmqKaZooEJ_4DkYBiO_LQ@mail.gmail.com>
To: Joel Hutchinson <Joel.Hutchinson@onlinecommercegroup.com>
Cc: "internals@lists.php.net" <internals@lists.php.net>
Content-Type: multipart/alternative; boundary="000000000000766f4205a18aa8c1"
Subject: Re: [PHP-DEV] Making mysqli easier to use with parameters
From: craig@craigfrancis.co.uk (Craig Francis)

--000000000000766f4205a18aa8c1
Content-Type: text/plain; charset="UTF-8"

Hi Joel,

I assume you're under the handle Machavity in Room 11:

https://chat.stackoverflow.com/transcript/message/48927577#48927577
https://gist.github.com/machavity/c84dad59bbc4d7d37b2d6e6bfd654df3

If not, I'd be up for your proposal as well, anything to make mysqli easier
to use.

Personally I'd still prefer being able to build up an array of parameters,
as I'm creating the SQL string (same as PDO).

But that's only because the parameter positions will change... which could
be handled by $i++, or as the second link suggests, make the parameter
argument optional (and presumably reset after execute, so the statement can
be reused).

Craig




On Mon, 23 Mar 2020 at 12:19, Joel Hutchinson <
Joel.Hutchinson@onlinecommercegroup.com> wrote:

> 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
> basis. This seems like low hanging fruit to make mysqli easier to use
>
> -----Original Message-----
> From: Craig Francis <craig@craigfrancis.co.uk>
> 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
> 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 = mysqli_fetch_assoc($statement->result)) {
>     }
>
> Craig
>
>
>
>
> > On 22 Mar 2020, at 15:28, Levi Morrison <levi.morrison@datadoghq.com>
> wrote:
> >
> >> <?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
>
> --
> PHP Internals - PHP Runtime Development Mailing List To unsubscribe,
> visit: http://www.php.net/unsub.php
>
> --
> PHP Internals - PHP Runtime Development Mailing List
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--000000000000766f4205a18aa8c1--