Charles Rowe dot com

A coworker and I were discussing the benefits and drawbacks of mysqli this week. I found it to be very convenient timing considering all the talk on the blogosphere about mysql vs mysqli.
There still seems to be a lot of confusion over the differences between the two extensions despite the length of time that mysqli has been in the wild. I wanted to briefly review the four major benefits of mysqli.

Prepared Statements
The strongest reason to use mysqli should be the prepared statements. A prepared statement is a special query template sent up to the server for validation and storage. From that point forward, the developer can bind input and output parameters to the prepared statement in order to interact with the server.


$mysqli = new mysqli('localhost','dbuser','passwd','my_db');
$stmt = $mysqli->prepare("select `user_id` from `users` where `employee_id` = ?");

$stmt->close();
$mysqli->close();


Outside of the preparation itself, this makes for some very speedy querying. If you have a lot of similar queries to run, you will notice a performance boost by submitting the prepared statement once and using using it over and over again for your query blocks. All that aside, the best reason to use prepared statements is that it makes it extremely difficult if not impossible for a sql injection attack to occur because the query the sql statement and the user input no longer interact in the same way they would with a normal mysql function. The query template is sent to the server before any data can be associated with it. Given the continued problems we are still experiencing with sql injection, this could be a life saver if it became a standard.

Secure MySQL connections
This feature speaks for itself. The ability to easily use mysqli_ssl_set() to establish a secure connection by providing certificate details as input parameters. I don’t know why this didn’t make it into the mysql extension, but I am glad to see it now.

Multi query

Using the mysqli_multi_query() method/function a programmer can send multiple semi colon delimited queries at once to the server and use each individual result set that gets returned. I am not sold on this feature (because you can no longer prepate statements) but I like having the ability if desired. I don’t see anything wrong with it, provided input parameters are validated.

Object Oriented Interface

Last but not least, mysqli’s OO interface allows a programmer to extend the mysqli object to serve more specialized purposes. I find this particularly exciting for one very simple reason:
I can separate operational errors (failure to connect, access errors, etc) from development errors like query syntax errors. This way, the errors can be routed to the correct groups for resolution without gunking up the code.

I believe we will be seeing an increased use of mysqli as the need to support PHP4 wanes. If your code is not PHP4 dependent, and you have a version of MySQL greater than 4.1.3, you should take a more in depth look at mysqli.

For further information:

• ext/mysqli: Part I - Overview and Prepared Statements
• Using ext/mysqli: Part II - Extending mysqli
• Converting To MySQLi
• Mirrors to the MySQLi Converter

This entry was posted on Friday, June 15th, 2007 at 7:41 am by Charles and is filed under Uncategorized, programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.