Experienced developers eschew the original MySQL extension because of its abandoned status in PHP. Nascent web developers, however, may be completely oblivious to its dormant past and dying future. [...] It is therefore the intention of this two-part article to raise awareness among developers who still use the MySQL extension, inform them of its problems, and to help them switch over to an alternative extension.

They start with a brief look at the "what's wrong" with the MySQL extension (including its upcoming deprecation). The article then gets into the basics of MySQLi and how to do things like make a connection and run a few queries. There's also a bit about prepared statements and the built-in ability to do "multi-queries" (complete with rollbacks).

An important discussion on the PHP "internals" development mailing list is taking place. It's one that you should take some note of. It concerns the next step in transitioning PHP applications away from the very old mysql extension and towards adopting the much better mysqli extension or PDO_MYSQL driver for PDO. This would allow the mysql extension to, at some as-yet undetermined time in the future, be removed.

He links to a RFC that's been posted to help promote and push this idea forward with mentions of the "carrot" and "stick" methods for pushing users towards mysqli.

As always, there is a lot of guesswork going on as to what MySQL APIs are in current use by PHP applications, how those applications are deployed, and what their upgrade cycle is. [...] I want to repeat that no time frame for the eventual removal of the mysql extension is set. I expect it to be some years away.

Web applications are more popular today than ever with the increasing number of internet users. Most of the standard alone applications converted as web based applications or at least they try to provide a web interface for users. PHP and Mysql are two leading technologies which allow uses on rapid development of web based systems. "Transaction" is a powerful concept which comes with Mysql 4.0 and above versions. Lets explore that.

He introduces the concept of transactions first, providing an example of a bank transfer between two individuals. He uses this to create a simple code sample that turns off the autocommit for the connection (using mysqli_autocommit set to false) and running the SQL in order before the commit. He also includes an example of using the rollback function to return the data back to its original state if there's an error.

When accessing a database in PHP, we have two choices: MySQLi and PDO. So what should you know before choosing one? The differences, database support, stability, and performance concerns will be outlined in this article.

The article starts with a summary of what each of the tools offers as far as features, things like the API to work with the interface, difficulty of making connections, use of prepared statements and performance. This is followed by a few code examples showing the same actions on each side:

• Making a new connection
• Databases supported (PDO has drivers)
• Named parameters
• Object mapping
• Security
• Performance

Ultimately, PDO wins this battle with ease. With support for twelve different database drivers (eighteen different databases!) and named parameters, we can ignore the small performance loss, and get used to its API. From a security standpoint, both of them are safe as long as the developer uses them the way they are supposed to be used (read: prepared statements).

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

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.

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).

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.

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.

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.