Making mysqli easier to use with parameterised queries

Hi Kamil,

Thank you for your initial thoughts, adding the syntax highlighting, and
the suggestion to use the splat operator, it's really appreciated.

Good to know about mysqli_execute() not actually being deprecated, I'll try
to think of a different name, maybe mysql::run(), to keep it short
(suggestions welcome).

It's a shame supporting both libraries would be tricky; I'd like to avoid
an argument about dropping support for libmysql client (tbh, I'm not sure
what the advantages are, maybe automatic reconnect? licensing?).

With binding type guessing, that can be removed, 's' is fine for most
values. I'm just going on the basis that a provided integer could stay as
an integer as it's sent to the server. My limited understanding is that it
would save a few bytes in the request; might avoid some type conversion
issues (int>string>int); and, if an integer was provided for a text field,
the MySQL server can do the conversion anyway.

As to the state of mysqli code being written (and similar issues), I do
have an idea on how to fix, but that's a bit more of a long term thing
(where I need to convince everyone it's worth it), and I need the "right
way" to be so much easier to do.

Thanks again,
Craig

Hi Craig,

This is a great proposal, and I am delighted that someone is still
interested in mysqli and wants to improve it. I will discuss the
proposal below, but first some notes about the RFC itself:

1. mysqli_execute() is not deprecated despite what the PHP manual said
   for the past 14 years. It is just an alias of mysqli_stmt_execute().
   It's beyond me why we even have a separate page in the manual for that
   alias. The mysqli documentation is in such a bad state that there are
   lies/problems pretty much on every page.
2. Introducing mysqli::execute() would be a great idea, but the name
   would be terribly confusing with mysqli_stmt::execute(). We need a
   better name.
3. Please use syntax highlighting. I have already added that for you.

I like the proposal, and I think that having a shorthand notation for
prepared statement for one-time execution is a brilliant idea. The
implementation would not be too difficult, but it would obviously work
with mysqlnd only. This in itself is a problem. Ideally, we should
drop support for libmysql client but apparently some people think it
is still useful. We could emulate the same for libmysql but that would
make the implementation more complex.
Your implementation example in PHP has a major problem. The binding
types should never be guessed based on the value. The type is
specified by the column definition. Guessing the type will lead to
very undesirable behaviour. 99.99% of the time the values should be
bound as strings. If an explicit type cast needs to be performed by
mysqli then the long-way can be used instead.
Also, rather than binding the variables by reference explicitely, you
can use splat operator. This makes the implementation in PHP much
simpler:

class mysqli_rfc extends mysqli {
function execute($sql, $parameters = []) {
// Preparing...
$statement = $this->prepare($sql);
// Binding...
if ($parameters) {
$statement->bind_param(str_repeat("s",
count($parameters)), ...$parameters);
}
// Executing...
$statement->execute();

// Fetching mysqli_result or null (only with mysqlnd)
return $statement->get_result();
}
}

In C code, this could be easily achieved by creating a function that
is just a mash-up of all 4 of these functions. I think this would
definitely be a nice addition to mysqli, even though I doubt that it
will improve the state of mysqli code written by people. After all, we
can't force them to use parameter binding.

Regards,
Kamil

On Sat, 26 Dec 2020 at 11:23, Craig Francis craig@craigfrancis.co.uk
wrote:

Hi,

Could the mysqli extension be tweaked to make parameterised queries
easier?

I've started an RFC at:

https://wiki.php.net/rfc/mysqli_execute_parameters

I'm going on the basis that some developers use mysqli directly, often
because they want a small stand-alone script that has no dependencies,
and
the current mysqli extension doesn't exactly help them make the right
choices.

Craig