Chapter 3. MySQL

PHP 5 sports a new MySQL extension. While this extension is similar in spirit to the original MySQL extension, it has many new features and other improvements. To differentiate it from the original version, this extension is called the "Improved MySQL extension," or mysqli for short.

The changes to mysqli come from two places. The majority are from new features available in MySQL 4.1. However, PHP 5 also allows mysqli to add an object-oriented interface.

Here's a list of the major advances in mysqli :

• Compatibility with MySQL 4.1 and above

• Prepared statements and bound parameters

• Object-oriented interface

• Secure connections using SSL

Additionally, MySQL 4.1 has new SQL-level capabilities that you can use from PHP. They include:

• Subselects

• Transactions

• Fulltext searching

• Unicode support

• Geospacial support (GIS)

On the downside, there are a few wrinkles if you want to start using mysqli with your existing PHP projects:

• Its client libraries are not bundled with PHP 5.

• It does not work with MySQL 4.0 and below.

• It's missing some mysql functions.

In a nutshell , if all your projects use MySQL 4.1 and later, and you have no legacy MySQL code, then all you need to do is download the MySQL client libraries and start using mysqli . On the other hand, you'll have some work to do if you:

• Want to use MySQL 4.1, but have existing mysql -based code

• Must write code that runs under MySQL 4.0 (and below) and 4.1 (and above)

• Want to have some applications use a MySQL 4.0 database and others use a MySQL 4.1 database

The Section 3.10 at the end of this chapter discusses a few strategies for handling these types of situations. However, before discussing how to move from mysql to mysqli , this chapter covers why you'd want to make the switch.

The chapter begins by showing how to use the mysqli extension, with sections on both the procedural and object-oriented interfaces. Next comes prepared statements and bound parameters. These are database features that require a different set of functions to communicate with MySQL than before, but offer improved speed and ease-of-use.

The middle of the chapter covers subselects and transactions. These are new MySQL 4.1 features that aren't unique to mysqli and PHP, but are part of the MySQL 4.1 server. Subselects let you nest a query inside of another, and transactions allow you to group a series of SQL queries as an integrated unit. Queries wrapped inside a transaction are guaranteed to either all work or all fail.

MySQL 4.1 allows you to group multiple queries into a single request. This is useful when you're using PHP to restore a database or need to populate a database from scratch. The section Section 3.8 covers the new interface for processing the results of a multi-query, which is more complicated than a regular query.

Another MySQL 4.1 feature is the ability to encrypt the connection between PHP and MySQL using Secure Sockets Layer (SSL). The next section shows how to configure PHP and MySQL to support SSL connections and how to use mysqli to pass authentication credentials.

At the end of the chapter, there's a discussion of various migration strategies for moving your code to both mysqli and MySQL 4.1. Due to various incompatibilities, this process requires a careful plan of attack.

While this chapter covers many MySQL topics, it omits some advanced topics, such query optimization, backups , and replication. For more information on those topics, read High Performance MySQL , by Jeremy D. Zawodny and Derek J. Balling (O'Reilly). That book covers those three issues and more, including a complete chapter on securing MySQL from crackers.