New in the PHP manual: a mysqli quickstart. You are new to PHP but you know how to code, you know SQL, you know relational databases and MySQL? Then, I hope, this is for you. All you need is a quick overview on the concepts? The rest is in the reference section! Here you go.

It includes sections on:

• making connections
• executing statements
• stored procedures
• multiple statements

Ulf Wendel has a new post today with details on using stored procedures with mysqli - not overly difficult if you know how to handle the IN, OUT and INOUT parameters. He includes a few code examples showing how to use them.

Out of curiosity I asked another friend, a team lead, how things where going with their PHP MySQL project, for which they had planned to have most of their business logic in stored procedures. I got an email in reply stating something along the lines: "Our developers found that mysqli does not support stored procedures correctly. We use PDO.". Well, the existing documentation from PHP 5.0 times is not stellar, I confess. But still, that's a bit too much... it ain't that difficult. And, it works.

He describes the three parameters (IN, OUT and INOUT) and gives some examples of setting/getting them from your SQL statements. They're all still set up using the query method on your connection as well as handling the result sets that come back and working with prepared statements.

Volker Dusch has a new post to his blog looking at the use of references in PHP (or lack there of) and what we, as end users of the language, can do about it. His example looks at mysqli prepared statements.

Even so not every PHP Developers knows WHY we don't use references pretty much every core function and every somewhat modern framework avoids them so people adapted this best practice. The leftovers in the PHP core, like sort() or str_replace(), are exceptions to the rule. So if the common consensus is, or at least 'should be', that we should not use references then maybe we should start looking for places where they hurt and how we could fix them?

He talks about prepared statements and one thing he sees that makes it a "hard sell" to developers needing a good way to query their databases. He points out the difference in code required between the normal MySQL calls and mysqli (hint: it's more) and shows how to use an abstraction layer to make things a bit easier. He points out the downfalls of using this approach, mainly the performance hit you get (from using his fetchAll method).

In a new post to his blog Johannes Schluter suggests an alternative to using prepared statements in PHP applications using a database - creating a handler method that allows for dynamic queries as well as proper escaping of values.

Now prepared statements were a nice invention some 30 years ago abut they weren't meant for making things secure and so they do have some shortcomings: One issue is that preparing and executing a query adds a round-trip to the server where it then requires resources. [...] With prepared statements you first have to build the list of place holders (the exact amount of place holders (?) separated by a comma, without trailing comma) and then bind the values and mind the offsets when having other values - this typically becomes ugly code.

He includes the code for his alternative, a function using the mysqli extension to let you create dynamic SQL that still uses placeholders and proper escaping to prevent both SQL injection issues and resources problems caused by the multiple hops back to the database.

On his blog today Andrey Hristov has a quick post that talks about two methods to extend the functionality that the mysqli extension offers in PHP with your own custom code.

Have you ever though about extending mysqli's classes. It's pretty simple to subclass the connection class mysqli. However, subclassing mysqli_result and mysqli_stmt is not so obvious, actually I though that it's even not possible. However, never say never! After discussing mysqli's OO interface for an hour yesterday [...] I found out how one can plug his own classes.

Two code examples are provided - one for extending the mysqli_result and the other extending the mysql_stmt class. You can find more about these and other classes the mysqli extension has to offer in this section of the PHP manual.

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.

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.