Because of the relational database’s pivotal role in driving Web applications, a great deal of time and effort has been put into creating tools that not only simplify the task of mapping database tables to a programming language’s object-oriented class structure, but also facilitate the management of your data and schemas over the project lifecycle. The Java and .NET communities have benefited from two such solutions in particular for years, namely Hibernate and NHibernate.
The PHP community also has a powerful database integration tool at their disposal: a project known as Doctrine. This open source project was inspired by Hibernate and Rails’ Active Record implementation, and both the symfony and Zend frameworks have adopted it as a primary database solution. This article introduces Doctrine and shows you how to begin mapping your database tables to PHP objects.
Doctrine requires PHP 5.2.3 or newer configured with PHP Data Objects (PDO) support. You’ll need to get those two requirements in order if your system doesn’t already meet this criterion. Next, head over to the Doctrine website and either download the latest 1.2 package or check out the latest stable version from the listed Git or Subversion repositories.
Doctrine can be somewhat of a bear to configure — a subject worthy of an entire article by itself — so I’ve opted to just introduce you to Doctrine using a handy sandbox environment, which is packaged with the download. The sandbox environment allows you to get acquainted with Doctrine features with minimal configuration. In fact, you won’t even need to install a database, because the sandbox uses a self-contained version of SQLite. Therefore, when the package has downloaded, unzip it, open a terminal window, and navigate to the project’s
Generating a Schema
Much of your interaction with Doctrine will take place via a convenient command-line interface, which is used for tasks such as creating the database schema, loading sample data (known as fixtures), and generating the models that will serve as an object-oriented bridge between the database and application. You can view a list of available commands by executing the following command from within the
%>./doctrine ./doctrine generate-sql ./doctrine build-all-load ./doctrine build-all ./doctrine migrate ... ./doctrine drop-db
In the interest of space I’ve removed some of the commands from this listing. In total you’ll find 21 different commands used to carry out a wide variety of tasks. One of the first commands you’ll use is
build-all-reload, which you’ll use to create the project database and load the schemas and starter data.
Because a database is useless without tables, let’s start by creating the schema that defines the table structure. Create a new file named
schema.yml and save it to the
tools/sandbox/schema directory, adding the following contents:
User: columns: id: primary: true autoincrement: true type: integer(4) email: string(255) first_name: string(255)
This YAML-formatted document defines the schema that will be used to both create the database tables and the models that will serve as the application’s database interface. With the schema defined, you can generate the schema and models using the following command:
%>./doctrine build-all-load build-all-load - Generated models successfully from YAML schema build-all-load - Successfully created database for connection named 'doctrine' build-all-load - Created tables successfully build-all-load - Data was successfully loaded
sandbox directory anew, you’ll see that a file named
sandbox.db has been created. This is a SQLite database. You can peer into the database by loading the database into the SQLite console (presuming SQLite3 is installed on your system):
%>sqlite3 sandbox.db SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>.tables user sqlite> .schema user CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT, email VARCHAR(255), first_name VARCHAR(255));
Loading Data Fixtures
Inserting sample data into the database using a custom HTML form or an administration application such as PHPMyAdmin can be tedious and error-prone. To facilitate the rapid loading of sample data, you can instead use Doctrine’s data fixtures feature, using the same YAML format to create sample records. Create a file named
data.yml and save it to the
User: wjgilmore: email: email@example.com first_name: Jason jimspammer: email: firstname.lastname@example.org first_name: Jim
When this file is in place, load it into the database using the following command:
Entering SQLite anew you’ll see that the data has indeed been inserted:
sqlite> select * from user; email@example.com|Jason firstname.lastname@example.org|Jim
Querying the Database
The third piece of this introductory puzzle involves querying the database from within the application. Doctrine is an object-relational mapper, meaning it facilitates the often tedious and difficult translation of a database’s table structures into PHP objects, which can easily be manipulated from within the application. Because Doctrine generates the models that form this bridge for you, all you need to do is take advantage of Doctrine’s special object-oriented syntax to query the database. Open the
index.php file located in the
sandbox directory and modify it so it looks like this:
<?php require_once('config.php'); $models = Doctrine_Core::loadModels('models'); $userTable = Doctrine_Core::getTable('User'); $user = $userTable->findByEmail('email@example.com'); echo $user->first_name;
This example uses one of Doctrine’s magic methods to query the database for the user identified by the e-mail address
firstname.lastname@example.org, creating an array of objects containing any records that match this criteria. The example subsequently references the returned record’s
first_name attribute, which returns the name
The Future of Doctrine
With Doctrine 2 barreling towards an autumn release, and popular frameworks such as the Zend Framework working towards adoption in a future release, Doctrine’s future as the de facto ORM solution for the PHP community is indeed bright.
- The Doctrine 1.2 manual: You should start here if you’d like to immediately begin integrating Doctrine into a project headed for production. Alternatively, you can purchase the documentation in print format at Amazon.com
- Doctrine 2 documentation: Although still months from an official release, Doctrine 2 is already very well documented. Check out this link to learn more about the fantastic features the forthcoming version has to offer.
Doctrine is such a powerful solution that this tutorial serves as little more than a taste of what’s possible. Even so, the material presented here is indicative of Doctrine’s ability to positively impact almost every aspect of your interaction with the project database. As always, we’d love to hear about what other database-oriented tools you’re using to simplify your programming life! Tell us about them in the comments.
About the Author
Jason Gilmore is the founder of EasyPHPWebsites.com. He also is the author of several popular books, including “Easy PHP Websites with the Zend Framework,” “Easy PayPal with PHP,” and “Beginning PHP and MySQL, Third Edition.”