21

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?

Community
  • 1
  • 1
Repox
  • 15,015
  • 8
  • 54
  • 79
  • 3
    I assume this uses client/server in a different context than the web model -- i.e. MySQL is the server and PHP is the client in this context. Even though they're both on the same computer. – Matt Fenwick Apr 13 '12 at 19:02
  • 2
    Most certainly this refers to [`PDO::ATTR_EMULATE_PREPARES`](http://www.php.net/manual/en/pdo.setattribute.php), a setting which simulates prepared statements with interpolation and escaping. This is done in the library, so technically client-side, before requests are transfered to the DB server. – mario Apr 13 '12 at 19:07
  • @MattFenwick Although this could make perfect sense, this doesn't explain why the same table shows support for server-side prepared statements as for all libraries, but only one of them supports client-side if we accept the premise that PHP is the client. – Repox Apr 13 '12 at 19:08

3 Answers3

28

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.

Jason McCreary
  • 71,546
  • 23
  • 135
  • 174
netcoder
  • 66,435
  • 19
  • 125
  • 142
14

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.

DCoder
  • 12,962
  • 4
  • 40
  • 62
2

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.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143