When Carson McDonald tried to get multiple prepared statements to work in his MySQLi code for his application, he got a "commands out of sync" error. Luckily, he's found a solution thanks to the store result.

Details about this error can be found in the mysql docs. Reading those details makes it clear that the result sets of a prepared statement execution need to be fetched completely before executing another prepared statement on the same connection.

He gives code snippets that are "before" and "after" examples of what he had to change to get things working. Each time its executed, the "store_result" call is made and the result set is pulled out of the prepared statement.

From Greebo.net there's a recent post that looks at converting the current database functionality in your application over to the MySQLi functionality and making use of prepared statements as a later of protection for your queries.

Okay, you've got like a zillion SQL queries in your PHP app, and probably 95% of them have a WHERE clause, and you need to make them safe so people will still download and use your app. Because if you don't fix your injection issues, I will rain fire on your ass. These are the steps you need to take to convert to prepared statements.

The guide is two steps you'll need to make the transition - "PHP 4 is dead. Upgrade to PHP 5" and "make sure your hoster has MySQLi". The major part of the update is under the first point where he gives code examples and suggestions to follow about how to "harden" your environment to prevent and issues that lax SQL methods might have caused and a simple example of a move from MySQL to MySQLi.

In a recent post to his blog Rubayeet Islam compared the more traditional way of running a query in MySQL versus a prepared statement with the MySQLi extension.

Consider a PHP-MySQL application where the information of 1000 users is being retrieved from the database by running a for loop [...] in each iteration, the first thing the MySQL engine does is to parse the query for syntax check. Then it sets up the query and runs it. Since the query remains unchanged during each iteration(except for the value of user_id), parsing the the query each time is definitely an overhead. In such cases use of prepared statements is most convenient.

He explains what prepared statements are and some of the advantages around them and includes some benchmarking examples to show the differences - about a five second jump in favor of MySQLi.

On the Internet Super Hero blog, they take a quick look at what's new in the MySQL native driver version that will be included in the upcoming PHP version, PHP 5.3.

PDO_MYSQLND is in the PHP CVS repository at php.net: PDO_MYSQL has been patched (PHP 5.3, PHP 6.0). Try out PDO_MYSQL with the MySQL native driver for PHP (mysqlnd). Its has new features.

They do a short recap of what the native driver libraries are all about and some of the advantages to using them. They look at some of the "memory tricks" supported by the driver and a look at the prepared statement and procedure support.

Charles Rowe shares four reasons/benefits he's come up with that should make you think about choosing MySQLi over the normal MySQL libraries for PHP for your application.

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.

Here's the list

• Prepared Statements
• Secure MySQL connections
• Multi query
• Object Oriented Interface

He also includes a few more links to further information (besides the explanation for each of the topics listed above) including an article from the Zend Developer Zone and a tutorial covering making the switch to MySQLi.

DevShed concludes their series looking at the use of PDO objects in PHP5 with this last tutorial focusing on the use of prepared queries in your PHP application.

In this last tutorial in the series I'm going to show you how to run prepared queries, but in this case we're going to include named parameters into them. Additionally, I'm going to take a quick look at some additional methods bundled with this extension. These new methods can be really useful for working with transactions, in case you want to use this feature with the database system of your choice.

They start with a simple approach to using prepared queries before moving on to an alternate method - binding parameters to the query via bindParam. They wrap up the article with a look at using transactions, both commits and rollbacks.

In a new post to the Internet Super Hero blogtoday, there's an official announcement about the release of the latest MySQL native driver for PHP - version mysqlnd-5.0.1-beta.

Dear Developers, we are proud to announce the beta version of the MySQL native driver for PHP: mysqlnd 5.0.1-beta. The software is available for download on http://dev.mysql.com/downloads/connector/php-mysqlnd.

The driver provides an alternate way to connect to a MySQL server (4.1 or newer) and is a replacement for the standard libmysql library usually used to compile support into PHP. Updates in this new release include the addition of support for prepared statements and support for Unicode strings.

In the constant pursuit of exploring what else is out there, Jeff Moore took a look at using PDO and MDB2 in some of his scripts and tried it out in a simple test program:

I was just putting together a small test program and I thought I would try using PDO. I really haven't done anything serious with PDO, just try it a couple times. Unfortunately, this didn't work and it took me a few minutes to figure out why. Actually, I still don't know exactly why it doesn't work, but I did find a way to make it work.

He found PDO slightly different to work with than what he was used to so he moved on to MDB2 to see how the same code would fare there. Things worked smoothly there, handling the prepared statements he wanted to use perfectly.

On the "Ramblings of a web guy" blog by Brian Moon, there's a good look at some benchmarks run with PDO, normally versus with the "turbo button" as talked about by Wez.

So, a while back, I did some tests with the mysql, mysqli and PDO extensions. In those tests, I found PDO to be much slower for selects than mysql and mysqli. Half as slow in fact.

So, I decided to try Wez's trick of emulating prepared statements to see how PDO did. The results were interesting. Not sure if these are the "fair comparisons" that Wez wants to see, but I gave it my best shot.

His first set of stats was pretty close to his originals back when he first tested, and the second set of tests (with the "button" on) seem to be a bit hit and miss on the performance upgrade using this method.

I ran this several times. Maybe Wez or someone that works on PDO can shed some light on this. Seems like it would be tough to keep track of when to use this flag and when not to.

In this new post from Wez Furlong, he looks ar some of the things that the native MySQL client library does (the silly things) and recommends an alternative with the PDO libraries.

Among the things he mentions functionality (dealing with prepared statements) like "native prepared statements cannot take advantage of the query cache" and "native prepared statements cannot execute certain types of queries". The gives a simple line of code to make the database functionality (in PHP 5.1.3 and later) use the native PDO query parser to be loaded versus the one for the standard MySQL client libraries.