Johannes Schluter has posted a look at a handy little script that shows an interface between the returned MySQLi results and an SPL iterator.

When using MySQLi's multi_query to send queries which return multiple result sets you have to use a rather unintuitive API which can certainly be improved. Recently I sat down and cooked up a small improvement for that, being an iterator fan I, of course, had to use an iterator for that and implemented the following class.

The class extends the standard Iterator and provides the interfaces to work through the results of the query in your choice of Iterator-supporting looping structure. Example code for the class and its usage are both included.

On the Dev-Explorer blog there's a post made recently about using stored procedures in your MySQL database via the mysqli interface in PHP.

On a new project I am working on I decided to take a look at the MySQLi (MySQL Improved) library. Most of the functions remain the same but it can now be used in object orientated programming which seemed to me as big advantage. Below I look at implementing MySQLi and extending it with your own custom code, along with using it to execute stored procedures.

He shows how to create both pieces of the puzzle - the class extending the mysqli functionality and a simple stored procedure on the database side (to insert users into a table). They're tied together with a PHP class with a "storedProcedure" method that runs a query() with a "CALL" to the procedure name.

From the Procurios blog there's a recent post looking at a method letting you use a foreach on the results from a MySQLi request - SPL Iterators.

Ever wondered why you can't use foreach() on MySQLi Results, and instead have to write less convenient while() loops with fetch_row? Actually, you can use foreach() on MySQLi Results. All it takes is some SPL Iterator magic.

The code examples show how to create an Iterator interface (with rewind, current, key, next and valid methods) to create a ResultIterator class for moving back and forth between the values in the result. This allows you to define the new Iterator object and use the foreach structure like you would a normal result set.

They also show how to bypass this whole problem by using a IteratorAggregate in an extension of the MySQLi interface.

DevShed.com has posted the seventh article in their series looking at the Active Record design pattern in PHP applications. In this latest tutorial they show how to replace the older MySQL client libraries and use the newer mysqli client instead.

Well, as you'll surely recall, I built this class by using the old MySQL library included with the PHP distribution, which is good and efficient. But it's worth making the effort to see how this sample class can be rewritten by using the newer, revamped "mysqli" extension. Thus, this last episode will be dedicated exclusively to doing this, so you can have at your disposal an enhanced versions of the class.

For most of the code, you won't see too much of a change - the methods are named similarly and the results are references differently, but you shouldn't have to make too much of a change.

The Internet Super Hero blog has posted some statistics comparing the connections per second that can be made with the newly introduced persistent connection support coming with PHP 5.3 in the mysqli (ext/mysqli) driver.

Persistent Connections have been a mixed bag. They can give you a significant performance boost by caching (pooling) connections although MySQL is already comparatively fast at establishing connections. However,connections are stored "as-is" in the cache. They are not "cleaned up".

The ext/mysqli driver takes care of this and a few other problems surrounding the persistent connections by cleaning up things like rolling back active transactions, unlocking tables, closing prepared statements and closing handlers. The trick is in a call to the C-API function mysql_change_user() (= COM_CHANGE_USER).

On the TotalPHP site there's a new tutorial showing how to pull data from your database and export it as a CSV file that tools like Excel can read in and use.

There are some occasions where you would want to export your site's information in CSV or similar text format. You might want to do this so you can view reports in a spreadsheet, or you might want an export of your product information to upload to a service like Google Products. Either way the method and end result are essentially the same.

The tutorial shows how to grab the information (via the mysqli functionality in PHP5) and formatting each row with the correct values in a certain order. Finally, the entire contents are echoed back out with the correct header() to force a download on the user's browser.

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.

As Alexey Zakhlestin points out today, Lukas Smith has made the announcement that the MySQL group is finally recognizing the efforts of the PDO developers and supporting them:

Ok, it seems that MySQL AB is finally committing to fix up PDO_MySQL and to generally accept the fact that PDO is the future. Of course mysqli will also be actively maintained. But they will also make mysqlnd play nicely with PDO etc.

There's budgets and time lines for the project so things are already moving along and developers to the project will soon be assigned. Lukas also mentions updated on PHP's side - validating that the documentation for the extensions (mysql and mysqli) are up to date and correct.

With all of the good news they've posted about the mysqlnd driver, the developers behind the Internet Super Hero blog know that there will be bugs that come up in the driver. So, they've addressed the right way to find and deal with these issues in a new blog entry.

he bad news: mysqlnd might have bugs. How to report and debug these bugs - using mysqli_debug() - is subject of this posting.

They step you through the process for finding out what's causing the problems (internal versus external debugging) and how to make client traces with the mysqi extension to make it even easier for the developers to track down the problem. They also suggest a few things to send along with your bug report - like the PHP code calling it or the SQL you're using to select/update/insert/delete the data from your database.

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.