/dev/weblog

I applaud the efforts of the GoPHP5 campaign. They're getting commitments from lots of projects to adhere to minimum requirements of PHP 5.2; PHP 4 is end-of-life very soon. In addition to offering projects the chance to refactor and improve their application design, PHP 5 offers many things PHP 4 just doesn't. This series of posts will deal with things projects can get their fingers into that will benefit everyone.

The first: you have no excuse to support only one database.

PHP and MySQL for many years have gone together like bread-and-butter. Some applications still only solidly support MySQL. Nothing is inherently wrong with MySQL (shush, trolls), but not everyone can or will run it. I can't imagine many of these PHP applications are using super-proprietary MySQL features that can't be done with other systems.

Use an abstraction layer for your data access. PDO is a fantastic addition to PHP; it's been stable and in the core distribution for a good two years. If you have the extensions for each system, PDO can out-of-the-box support MySQL, Sqlite, PostgreSQL, MS SQL Server, Oracle, and recently DB2. In the Zend Framework, Zend_Db is a great tool as well. It's fast, well-thought-out, has many convenient features, and provides some more abstraction than PDO does. For instance, listing all the tables in a database, describing a table, and performing limit/offset queries.

All that's left to you the application developer is writing the SQL for creating your tables in each database system. As far as I'm concerned (Yes, and my opinion matters), you should support the "Big 4" (MySQL, PostgreSQL, MS SQL, Oracle) out of the gate; these have the widest install base.

Lastly, since you're going to the trouble of using such a data access layer, make sure you take advantage of value binding. Binding your values to placeholders in the SQL statement greatly reduces the risk of SQL injections. It also lets the DB worry about how to escape the value.

So stop being database system elitists. You'll have a wider and happier user install base if your system supports a few databases.