Sample Code - PHP

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');
        $conn->setFetchMode(ADODB_FETCH_ASSOC);
      }
      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:

  define('BOOKMARK_TABLE_DDL', <<<EOS
  CREATE TABLE `bookmark` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `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` )
  )
  EOS
  );