What are client-side prepared statements?

Question

Trying to learn something new - specifically trying to choose wether to use MySQLi or PDO for future projects when working with MySQL - I stumbled upon this page which shows an overview of options available to me.

At the bottom of this page is a table comparing functionality of the three main methods of communicating with mysql. In the row "API supports client-side Prepared Statements", it says that PDO supports this and MySQLi doesn't.

I know what prepared statements are. The answer to this question is a simple example of what I believe is server-side prepared statements. And PHP is a server-side language, which in turn should mean that it doesn't matter if client-side prepared statements are available or not. But that makes me wonder why that is even listed in the PHP manual then.

So what are client-side prepared statements?

Answer 1

With PDO, you can use prepared statements (in code) whether the database that you are connecting to with PDO supports them or not.

If the server handles prepared statements, then PDO will let the server handle them (server side). If not, then PDO simply emulates prepared statements within PDO (client side), but ends up having to send each query to the server.

Answer 2

Obviously, client-side prepared statements are statements that are prepared by the client, rather than the server.

PDO is a data-access abstraction layer that supports multiple DBMS interfaces (drivers), some of which support server-side prepared statements (e.g.: MySQL 4.1+), some of which don't (e.g.: MySQL 3).

In the event where the PDO driver does not support server-side prepared statements, PDO will emulate them on the client-side and use the generic query interface to execute them.

The reason why MySQLi doesn't support them is simple: MySQLi is a MySQL-specific extension, a RDBMS that indeed supports server-side prepared statements, so there is no reason to emulate them.

Answer 3

Like was said in the comments, in this case "client" refers to PHP and "server" to MySQL. PDO supports databases other than MySQL. Not all these databases/db drivers support prepared statements natively, and in those cases PDO will emulate these statements itself. MySQLi will not (I don't know for sure when it would have to do so - maybe when it's dealing with an old MySQL driver?).

One more factor you might want to consider - certain PHP frameworks require PDO and don't support mysqli.