switch to mysqli a good idea?

Question

I'm considering switching to mysqli for all my php projects. The way my code is written (I run very simple websites and built my own basic framework which I use across all of them) I shouldn't have too many problems modifying the functions and classes.

However, i've only heard positive things about prepared statements, bar a few grumblings about the php functions available, most notably the lack of a simple replacement for using mysql_fetch_array in a while.

This sounds a bit too good to be true, so I wondered if anyone could highlight some of the issues with using prepared statements, such as speed and resource usage.

Answer 1

Programming for prepared statements takes a bit of getting used to if you're used to just appending variables to query strings. MySQL uses positional parameters (your query will contain question marks where the replace vars belong). The best bet is to put this into your existing database abstraction. If that abstraction was written properly, you shouldn't be calling mysql_fetch_array outside the wrapper anyway.

The solution to this problem is just to collect all the rows in advance, but of course that assumes that you don't retrieve 1000 rows and just ask for the first one. This is a change that you should make regardless of mysqli.

Finally, some statements are not easily replaced by parameters, such as queries using the in('x', 'y', 'z') syntax with variable numbers of arguments. It can be done, but you'll probably want to enrich your database abstraction to allow it to create the queries as well as execute them.

The tradeoff, though, is definitely worth it, in terms of performance and safety. The additional processing on the PHP side is usually outweighed by the cached execution plans for queries on the MySQL side, and you are immune to many of the most common SQL injection vulnerabilities.

Hope that helps, Joe

Answer 2

Prepared statements are so good that once you get used to them it's painful to use escaping functions again. Period.

However, all DB libraries I've ever used (including oci8 and sqlsrv...) introduce one quirk or another. So I basically encapsulate whatever library I use with a simple set of custom classes that provide the features in the way I like:

• Name based parameters: WHERE foo = :foo
• Parameter passed by value in associative array (rather than binding to individual PHP variables): $params = array('foo' => 33)
• One line execution: $res = $Db->query($sql, $params);
• Resultsets are objects that implement the Iterator interface, so I can loop with foreach($res as $row)

Adopting such policy makes the exact syntax or function set less important.

Whatever, while this can be accomplished with almost any library, it helps if it provides native param binding so (for instance) you don't have to guess the data type. Also, some advanced functionality like transactions can't simply be done with plain mysql functions.

PDO could have been a nice alternative but most of its drivers are basically abandoned so you actually loose the benefit of having a DB-agnostic abstraction layer while you enjoy it pitfalls.

IMHO, the mere fact that you are asking suggest that you should give mysqli a chance.