Why doesn't the MySQLi library natively support named parameters?

Question

Correct 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 do this:

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

In my opinion, named parameters substitution is a reasonable feature to implement at the API level. To my surprise, MySQLi only implements unnamed parameters in the library.

Is there a valid reason? Seeing how PDO, DQL, ORM all take named parameters in queries, this doesn't make sense to me.

I hope MySQLi developers don't get into the "we're lazy and don't want to" situation. I believe there must be a good reason, and I'm looking for that reason, or a way to find that reason. The reason why named parameters are not implemented in the MySQLi extension library.

Answer 1

Traditionally, MySQLi is the MySQL API. It doesn't add anything by itself, and there's a reason for that: adding functionality like named placeholders would require (if you think about it) a whole behemoth of SQL query parsing. Of course, this is not the job of the database API. Like said in other answers, the API is not DAL or DBAL; they serve different purposes.

PDO is a feat of greatness that you rarely see in a language again, and Wes Furlong is a genius who almost single-handedly took on the task. But PDO is another story. It's a database access abstraction layer, and to achieve that, whether you like it or not, you need a query parser. Since you already have a query parser and one of the drivers already supports named placeholders, it's natural to add it to all supported drivers. As you can see, everything changes with MySQLi.

To put it simply, it is not "lazy", but "lazy". It's about following the norm.

Answer 2

MYSQLi There are two main reasons why named parameters are not supported:

1. It is "intended" (I use this term loosely) to be used with wrappers, and
2. Its counterpart PDO does - and there's no need to reinvent the wheel

To elaborate on point 1: mysqli, despite having many disadvantages compared to PDO, is easily comparable to a good wrapper - i.e. named parameters (among other things )) are supported by wrappers rather than mysqli itself. This is by design and for one reason only:

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

If developers incorporate more functionality into the base library, counterintuitively, it becomes less flexible and takes longer to load/execute.

mysqli and pdo were both released with PHP 5 (I believe the PDO version was 5.3) and therefore serve different purposes.

Do you want faster execution time? Use mysqli without wrapper. Do you want named parameters? Use PDO or build a mysqli wrapper to handle this - but be aware that this will hinder your execution time.