Home arrow PHP arrow Page 2 - The Active Record Pattern

Sample Code - PHP

This article, the first of two parts, helps you use design patterns to better organize how your web application interacts with a database. It is excerpted from chapter 14 of the book php|architect's Guide to PHP Design Patterns, written by Jason E. Sweat (php|architect, 2005; ISBN: 0973589825).

  1. The Active Record Pattern
  2. Sample Code
  3. Test Independence
  4. Record Creation
By: php|architect
Rating: starstarstarstarstar / 18
December 22, 2005

print this article



Any discussion of database connectivity depends on choosing both a database and an access layer. This and the following two chapters use the popular open source database MySQL (http://www.mysql.com/) and the ADOdb (http://adodb.sf.net/) access layer. I established ADOdb as a standard in my workplace because it has excellent performance and it abstracts the Oracle OCI interface and interfaces to PostgreSQL, Sybase, MySQL, and other databases in a uniform, simple-to-use PHP API, allowing you to focus on your programming and business logic.

Feel free to substitute you own database and access layer, as most of the concepts presented here readily port to other solutions.

Before looking at the Active Record pattern, let's start with basic database connectivity. It's ideal to have a central, simple way to specify connection parameters (the hostname, username, password, and database) and to create a database connection object.  A Singleton (see Chapter 4) typically suffices.

Here's a DB class with a conn() method that returns the Singleton instance of the ADOConnection class.

  // PHP5
require_once 'adodb/adodb.inc.php';
class DB {
  //static class, we do not need a constructor
private function __construct() {}
    public static function conn() {
    static $conn;
    if (!$conn) {
      $conn = adoNewConnection('mysql');
      $conn->connect('localhost', 'username', 'passwd', 'database');
      return $conn;

The DB class allows you to control the type of database and the connection parameters used in connecting to the database. At the top, the code includes the ADOdb library (you may need to adjust the include path to suit your environment); The DB constructor is private since there's no need to ever create an instance of DB; And the line $conn->setFetchMode (ADODB_FETCH_ASSOC) instructs the result set object to return rows as associative arrays of field_name => value. Using an associative array is an important best practice to adopt in working with databases, so your code remains unaffected (less brittle) by the ordering of fields in SELECT clauses of your SQL statements.

As an example application, let's create an Active Record object to maintain a table of hyperlinks. Here's the SQL to create the hyperlinks table in a MySQL database:

CREATE TABLE `bookmark` (
  `url` VARCHAR( 255 ) NOT NULL ,
  `name` VARCHAR( 255 ) NOT NULL ,
  `description` MEDIUMTEXT,
  `tag` VARCHAR( 50 ) ,
  `created` DATETIME NOT NULL ,
  `updated` DATETIME NOT NULL ,
PRIMARY KEY ( `id` )

>>> More PHP Articles          >>> More By php|architect

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: