17

Proper MySQLi parameterized query syntax from http://php.net/manual/en/mysqli.quickstart.prepared-statements.php:

$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)");
$stmt->bind_param("i", $id);

But never something like:

$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (:id_value)");
$stmt->bind_param("i", "id_value", $id);

It appears to me that named parameter substitution is a reasonable feature to be implemented at the API level. I am surprised that MySQLi only implemented unnamed parameters in the library.

Is there a valid reason? It doesn't make sense to me, seeing how PDO, DQL, ORM all have adopted named parameters in their queries.

I hope it was not the case of "We were lazy & don't wanna" on the part of MySQLi developers. I believe there must've been a good reason and I am looking for that reason, or a way to seek out that reason. The reason for named parameters not being implemented in MySQLi extensions library.

Dennis
  • 7,907
  • 11
  • 65
  • 115
  • 2
    The mysql database engine doesn't? It has prepared queries but not named parameters. – Ryan Vincent Sep 13 '16 at 17:03
  • mm off-topic, too broad, and opinion based flags. I did not expect such diversity in close flags. – Dennis Sep 13 '16 at 17:06
  • 1
    Well Dennis; you'd surely get an answer from someone who's familiar with PHP.net's core development, and its underlying reason as to why they did that That's the only way you'll get a concrete answer. I for one, am not one of those persons ;-) At least I gave you the right time of day here. – Funk Forty Niner Sep 13 '16 at 17:14
  • To somewhat answer this in what seems logical to me would be: The colon `:` placeholder would be a (piping) character that differentiates from the MySQLi_ prepared statement placeholder `?`, where PDO is more transportable across different platforms then the MySQLi_ API is. However, and it being more "transportable", the core developers (probably) wanted people to be able to use both `:` and `?` placeholders as a probable mean of familiarlty; did I come close to what you were looking for as an answer? @Dennis – Funk Forty Niner Sep 13 '16 at 17:23
  • 1
    Addendum to the above: [The (PDO) manual on a prepared statement](http://php.net/manual/en/pdo.prepare.php) states: *"PDO will emulate prepared statements/bound parameters for drivers that do not natively support them, and can also rewrite named or question mark style parameter markers to something more appropriate, if the driver supports one style but not the other."* Plus, MySQL also supports it as a core character, so that's probably why they chose the colon placeholder. See contributor notes in the manual also. – Funk Forty Niner Sep 13 '16 at 17:25
  • @Fred, as I see it `:` does not have a specific SQL meaning known to me. My argument would be that other libraries have successfully implemented mechanisms to use either `:` or `?`. I myself can implement a wrapper for MySQLi to do the named parameter substitution. But why does end-user (me) would have to write that wrapper, and not MySQLi developers? I see it as *convenience factor* and I would love to use it if it was available. Sure I can use PDO instead as well but ... why not MySQLi? I don't see transportability as an effective show-stopper for MySQLi unless there was a good reason? – Dennis Sep 13 '16 at 17:29
  • MySQL's `:=` assignment operator http://dev.mysql.com/doc/refman/5.7/en/non-typed-operators.html seems to be something that might be related to PDO, if that's what the (core) developers had in mind at the time. We/I don't know what went on in their heads or during board meetings, but it seems to make "some" sense as to why they chose that. As to why you/we have to write those wrappers; that I couldn't say. Again; it's a question aimed more at the core developers, and/or someone on Stack who could better answer that. That's about all I can say on the subject Dennis. Good question though. ;-) – Funk Forty Niner Sep 13 '16 at 17:35
  • Addendum to the above. Since MySQL didn't come up with a `?=` assignment operator, well... it kind of looks to make sense to me in regards to using a different wrapper, since the `?` wasn't part of the core MySQL code. This is a kind of a guess though ;-) – Funk Forty Niner Sep 13 '16 at 17:36
  • Thanks, Fred. My existing codebase is all mysqli. I could port it to PDO but that would be a different issue/question. In the mean time, I find it more *flexible* to use named parameters, because: starting with `WHERE a = ?, c = ?`, if I have to add `b = ?` between `a` and `c`, I now need to update my `bind_param` respectively, and be *careful* with the ordering of my parameters, as to not cause a shift in the matching algorithm. With named parameters I would not have to worry about the shift. I'd just tack on a new statement like in PDO – Dennis Sep 13 '16 at 17:41
  • You're welcome Dennis. Yes, I agree that named placeholders are much more easier to read and track and is probably another reason why the (core) PHP developers chose that as an additional method. Again; just another "guess". You see, PDO works in Windows under MSSQL, but the MySQLi_ API does not work with MSSQL because of them being two different animals altogether ;-) – Funk Forty Niner Sep 13 '16 at 17:42

2 Answers2

7

MYSQLi doesn't support named parameters for two main reasons:

  1. It is "intended" (I use this term loosely) to be used with a wrapper and
  2. It's counterpart, PDO, does - and there is no point re-inventing the wheel

To elaborate on point 1: mysqli, despite its many downfalls when compared to PDO, becomes easily comparable with a good wrapper - that is, named parameters (among others) are supported by the wrapper rather than mysqli itself. This is by design for one sole reason:

  1. Mysqli is designed to be a fast and flexible library.

If the developers incorporated many more features into the base library, it becomes, counter intuitively, less flexible and requires longer load/execution times.

Both mysqli and pdo were released with PHP 5 (PDO with version 5.3, I believe) and as such are intended for different uses.

You want faster execution times? use mysqli without a wrapper. You want named parameters? use PDO or build a mysqli wrapper to handle such - but be warned, this will hinder your execution times.

Mark
  • 691
  • 7
  • 20
4

MySQLi, traditionally, is a very thin wrapper for the MySQL API. It doesn't add anything on its own and for a reason: adding such features as named placeholders will require, if you think of it, a whole leviathan of SQL query parsing. Definitely, it is not a job for a database API. Like it is said in the other answer, API is not a DAL or DBAL; they serve for different purposes.

PDO was a great feat you hardly would see again in the language and Wes Furlong is a genius who undertook the task almost single-handedly. But again, PDO is a different story. It's a database access abstraction layer, and to achieve this goal you need a query parser, like it or not. And as you already have a query parser and one of drivers already supports named placeholders, it would be natural to add it to all supported drivers. As you can see, with MySQLi it is all different.

To put it short, it is not about "laziness"; it is about following the specification.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • whos fault is it then that PDO returns only strings when PDO::ATTR_EMULATE_PREPARES is set to true? – Gaby_64 Dec 16 '19 at 20:08
  • @You To remove the ambiguity of the acronym "DAL", please explain or link that you are referring to a Data Access Layer or Database Abstraction Layer. I assumed it was the latter, but then when I Googled to verify, I found the former. – mickmackusa May 10 '20 at 12:50