I mentioned before, when talking about my Step-By-Step Regex article in php|a magazine, that the same issue (August 2009) had some other interesting articles.
One is about Adminer, an alternative to phpMyAdmin which is designed to be very compact and exists in just one file (so it is easy to upload temporarily to a production server for instance); the article is mainly about how Adminer was written, which was interesting.
But the article - and library - I most want to talk about is Doctrine ORM. The main problem this library is solving is this: when you add a field to your database you have to make the change in at least two places, your PHP script and your database. That sucks. The article describes itself as a tour around Doctrine's more advanced features, but I found I didn't need any other introduction, and it sold the features in a very clear and logical way.
The first thing I liked was you can describe your database schema in any of three ways: SQL, PHP, or the compact YAML files, and utilities are provided to convert between all three. By SQL I mean an existing database, that you could have been creating interactively from phpMyAdmin, or it could be a legacy database that has been around for years and now needs to be connected to a php script.
What only the Yaml and PHP schema formats can specify are what Doctrine calls behaviours. By adding the Timestampable behaviour it will add created_at and updated_at fields to the database and keep them up to date for you. The Sluggable behaviour is for making URLs and is demonstrated in the php|a article, but others that caught my eye are SoftDelete (records just get the deleted_at field set when you delete, rather than physically being removed), and Versionable (each time a record is changed the previous record is saved, and can be reverted to). NestedSet helps build trees, I18n is for holding translations (see below), Searchable does indexing, Geographical can find nearby places.
Incidentally I covered I18n in a previous article and Doctrine is using type 1, but without the language table to store collation. There is no consideration of different collations, and no concept of default language. It is sufficient for most purposes though.
Looking at some behaviours not in the Doctrine core distribution, Taggable looks interesting, allowing you to add blog-style tags. Locatable apparently ties in with Google Maps to get latitude and longitude automatically given an address (it could then be used, for instance, with the Geographical behaviour to automatically find all people within 10km of you based on just their address). EventLoggable logs all actions (select, update, delete, etc.) to a disk file. Blameable records (in the table) who last made any change to a record.
Doctrine's behaviours feature is powerful, portable, the existing ones are customizable (all the above seem to have sensible defaults but also can have their behaviour fine-tuned) and new ones look easy to write.
The third big feature of Doctrine that I like is the way data is retrieved on demand, and how that ties in with table relations (see listing 6 in the php|a article). And if this becomes inefficient you can optimize without having to change much code (listing 7). I'm not a big fan of using PHP to write SQL queries. Generally to do anything interesting you end up dropping down to writing SQL anyway. But I was impressed with the example in the article, and you only need to start explicitly describing joins if you have profiled and found you need to optimize.
(By the way, it goes without saying that Doctrine ORM is database-neutral, not tying you into MySQL or any other database.)
The final cool Doctrine feature is migrations. When you make a change to the schema in either your YAML file or your PHP file you run a script and it will make a migration file. This can then be run to move a database between versions (backwards or forwards). I admit I currently do this with some hand-crafted SQL scripts, which are one way only, and painful. And sometimes I'm not even that organized.
I've not used Doctrine ORM for a real project, but I expect I will try it out very soon.