In PHP, database abstraction is a fairly well known concept, a number of abstraction libraries such as PEAR::DB and ADOdb, or even PHP‘s dbx extension providing a partial mechanism to make PHP code database independent.

“Partial” because it’s not just about being able to connect and run queries on anywhere - SQL query syntax also varies from database to database. As we saw when looking at the Adapter Pattern the difference between MySQL and Oracle when fetching a “limited” result set is significant. It’s also awkward to determine the number of rows in a SELECT statement with PHP‘s Oracle OCI extension, without running a query twice - compare ocirowcount() with mysql_num_rows(). Finally there’s MySQL’s allowing the use of slashes as an escape character in SQL statements (commonly used with PHP‘s mysql_escape_string() or addslashes()) which doesn’t conform with ANSI SQL (single quotes being escaped by another single quote).

In other words, to be database independent, we need not only to be able to connect our code to any database but also seperate it from SQL statements. Here’s the first reason for the DAO pattern.

When we looked at the MVC pattern, in the “Model” classes where we placed our “Application Logic” (as opposed to Presentation Logic), we placed SQL statements in those classes. For the simple application we built there, this wasn’t a problem but what happens as our application grows? We start placing more and more SQL statements in our Model classes, no doubt frequently reproducing the same statement in multiple classes. Aside from any resultant finger strain, if we later need to change the underlying database table structure, we’ll need to update all the SQL statements in our code to reflect the change.

The Data Access Object pattern is a strategy for constructing a single API which encapsulates all data fetching operations (such as SQL queries), allowing our Application logic to be a client to that API without needing to be concerned with SQL syntax.

Further more, when we looked at the Adapter Pattern, we created an adapter for our Model classes to allow us to fetch data from multiple sources. Doing so may result in a lot of reproduction of code - we really only wanted to make data fetching abstract from the rest of our code. Implementing a DAO pattern would have made the use of the Adapter pattern, in that example, easier.

Referring to our MVC design, the DAO pattern sits between the Model and Data Access classes. In the PHP application coming below, here’s a Dao pattern between the classes that set up the database connection and the “Model” class from the MVC pattern;

Now we have an idea why we might want to use the Data Access Object pattern and where it sits in an applications architecture, it’s time to lay down some PHP.

In earlier pattern examples, we used a class DataAccess to dealing with connecting to MySQL and performing functions like mysql_query(). The first version of this class suited our earlier purposes but now, as we’re implementing a further layer of abstraction in data fetching operations, it needs a little modification;

/** *  A simple class for querying MySQL */class DataAccess {    /**    * Private    * $db stores a database resource    */    var $db;     //! A constructor.    /**    * Constucts a new DataAccess object    * @param $host string hostname for dbserver    * @param $user string dbserver user    * @param $pass string dbserver user password    * @param $db string database name    */    function DataAccess ($host,$user,$pass,$db) {        $this->db=mysql_pconnect($host,$user,$pass);        mysql_select_db($db,$this->db);    }     //! An accessor    /**    * Fetches a query resources and stores it in a local member    * @param $sql string the database query to run    * @return object DataAccessResult    */    function & fetch($sql) {        return new DataAccessResult($this,mysql_query($sql,$this->db));    }     //! An accessor    /**    * Returns any MySQL errors    * @return string a MySQL error    */    function isError () {        return mysql_error($this->db);    }} /** *  Fetches MySQL database rows as objects */class DataAccessResult {    /**    * Private    * $da stores data access object    */    var $da;    /**    * Private    * $query stores a query resource    */    var $query;     function DataAccessResult(& $da,$query) {        $this->da=& $da;        $this->query=$query;    }     //! An accessor    /**    * Returns an array from query row or false if no more rows    * @return mixed    */    function getRow () {        if ( $row=mysql_fetch_array($this->query,MYSQL_ASSOC) )            return $row;        else            return false;    }     //! An accessor    /**    * Returns the number of rows affected    * @return int    */    function rowCount () {        return mysql_num_rows($this->query);    }     //! An accessor    /**    * Returns false if no errors or returns a MySQL error message    * @return mixed    */    function isError () {        $error=$this->da->isError();        if (!empty($error))            return $error;        else            return false;    }}?>

The thing to notice now is we’ve seperated the placing of a query to a MySQL database from the result fetching. DataAccess now returns an instance of the DataAccessResult class, which we can use to fetch rows from the query. 2) The reason for doing this, rather than returning the data itself, is our application only needs deal with a single row at a time, MySQL doing the work of “keeping track” of the result set, rather than having to store the entire result set in a PHP variable. When we perform a “SELECT * FROM table”, holding the entire result set in memory is likely to bring our application to a crashing halt.

Now we need to build the Data Access Object, which will be a client to DataAccess classes.

We’ll implement this by starting with a parent Dao class;

/** *  Base class for data access objects */class Dao {    /**    * Private    * $da stores data access object    */    var $da;     //! A constructor    /**    * Constructs the Dao    * @param $da instance of the DataAccess class    */    function Dao ( & $da ) {        $this->da=$da;    }     //! An accessor    /**    * For SELECT queries    * @param $sql the query string    * @return mixed either false if error or object DataAccessResult    */    function & retrieve ($sql) {        $result=& $this->da->fetch($sql);        if ($error=$result->isError()) {            trigger_error($error);            return false;        } else {            return $result;        }    }     //! An accessor    /**    * For INSERT, UPDATE and DELETE queries    * @param $sql the query string    * @return boolean true if success    */    function update ($sql) {        $result=$this->da->fetch($sql);        if ($error=$result->isError()) {            trigger_error($error);            return false;        } else {            return true;        }    }}?>

Notice how simple this is? We provide only two methods, the first for retrieving data which will return the instance for DataAccessResult from the DataAccess class. The second method is used for anything that changes the database, i.e. INSERT, UPDATE and DELETE.

Now let’s say we have a table called “log” described by;

CREATE TABLE log (  id int(11) NOT NULL auto_increment,  host char(100) NOT NULL default '',  address char(100) NOT NULL default '',  agent char(100) NOT NULL default '',  date datetime default NULL,  country char(50) NOT NULL default '',  provider char(100) NOT NULL default '',  os char(50) NOT NULL default '',  wb char(50) NOT NULL default '',  PRIMARY KEY  (id),  KEY id (id)) TYPE=MyISAM;

To access this table, we’ll provide a specific Data Access Object which extends the Dao class we’ve already defined.

/** *  Data Access Object for Log Table */class LogDao extends Dao {    //! A constructor    /**    * Constructs the LogDao    * @param $da instance of the DataAccess class    */    function LogDao ( & $da ) {        Dao::Dao($da);    }     //! An accessor    /**    * Gets a log files    * @return object a result object    */    function & searchAll ($start=false,$rows=false) {        $sql="SELECT * FROM log ORDER BY date DESC";        if ( $start ) {            $sql.=" LIMIT ".$start;            if ( $rows )                $sql.=", ".$rows;        }        return $this->retrieve($sql);    }     //! An accessor    /**    * Searches logs by IP address    * @return object a result object    */    function & searchByAddress ($address) {        $sql="SELECT * FROM log WHERE address='".$address."'".            " ORDER BY date DESC";        return $this->retrieve($sql);    }     //! An accessor    /**    * Searches logs by country    * @return object a result object    */    function & searchByCountry ($country) {        $sql="SELECT * FROM log WHERE country='".$country."'".            " ORDER BY date DESC";        return $this->retrieve($sql);    }     //! An accessor    /**    * Searches logs by id    * @return object a result object    */    function & searchByID ($id) {        $sql="SELECT * FROM log WHERE id='".$id."'";        return $this->retrieve($sql);    }     function & totalRows () {        $sql="SELECT count(*) as count FROM log";        return $this->retrieve($sql);    }}?>

LogDao will now provide the sole point of access to the log table.

We’ll see this in action in a moment, when we write an MVC pattern which will be a client to LogDao.

3)

Data access objects are an area of application design where it’s easy to go into overkill as a developer. In general it’s best to keep Dao classes as simple as possible.

The easiest approach to adopt is to define an empty Dao class for every table in a database, extending the parent Dao. Then get to work on the application logic classes (the “Models” in MVC) and every time a new query, against some table, is needed, simply add it to the Dao class for that table.

Notice in the LogDao class we have the methods searchByAddress() and searchByCountry() - we could easily combine them into a single method but we don’t for two main reasons.

Firstly say the need for the searchByAddress() method makes itself apparent long before the need for searchByCountry(). If we merge the two methods into one, we’re probably going need to modify LogDao’s API, which may break any client code using it.

Secondly, we make life alot easier for other developers using our Dao, if we use names that make it clear exactly what’s happening.

Although we may end with Dao classes containing many methods (which means more work if we change the table structure), this general “down to earth” will suit applications where the underlying database schema is “small” (i.e. a typical PHP application). For bigger projects, there are further techniques for adding yet more abstraction, using DaoFactories for example, which are well documented for use with Java.

As far as maintaining foreign key relationships between tables goes, it’s generally best to keep this to the SQL statements placed in our Dao classes, the class “owning” the query being the one that contains a foreign key. There’s a whole science of Object Relational Mapping which may or may not be a good thing, depending on who’s talking. For typical PHP apps, this will likely be overkill.

Going back the code, we’ll now implement an MVC pattern as a client to our Dao. The code download shows all - we’ll just look at a few classes here.

First a LogModel class which deals with transforming data into something specific to our application;

/** *  Modelling log data */class LogModel {    /**    * Private    * $dao stores data access object    */    var $dao;      /**    * Private    * $result stores result object    */    var $result;     /**    * Private    * $rowCount stores number of rows returned    */    var $numPages;     //! A constructor    /**    * Constructs the LogModel    * @param $da instance of the DataAccess class    */    function LogModel ( & $dao ) {        $this->dao=& $dao;    }     //! An accessor    /**    * Gets a paged result set    * @param $page the page to view from result set    * @return void    */    function listLogs ($page=1) {        $rows=20;               $start=$rows*$page;        $this->result=& $this->dao->searchAll($start,$rows);        $numRowsRes=$this->dao->totalRows();        $numRow=$numRowsRes->getRow();        $numRows=$numRow['count'];        $this->numPages=floor($numRows/$rows);    }     //! An accessor    /**    * Returns the number of pages in result set    * @return int     */    function getNumPages () {        return $this->numPages;    }     //! An accessor    /**    * Searches for logs by different conditions    * @param $searchBy type of search to perform    * @param $searchString string to use in search    * @return void    */    function searchLogs($searchBy='address',$searchString) {        switch ($searchBy) {            case "country":                $this->result=& $this->dao->searchByCountry($searchString);                break;            default:                $this->result=& $this->dao->searchByAddress($searchString);                break;        }    }     //! An accessor    /**    * Gets a single log row by it's id    * @param $id of the log row    * @return void    */    function listLog ($id) {        $this->result=& $this->dao->searchByID($id);    }     //! An accessor    /**    * Gets the data from a single row    * @return array a single log row    */    function getLog() {        return $this->result->getRow();    }}?>

It’s worth reminding ourselves that (something this example won’t fully demonstrate) seperating data fetching operations from Model type classes allows us to construct multiple models all using a single Dao. This becomes clear in more complex applications.

Look looking at a section of the Controller code we have;

/** *  Controls the application */class LogController {    /**    * Private    * $model an instance of LogModel    */    var $model;     /**    * Private    * $view an instance of LogView    */    var $view;     //! A constructor.    /**    * Constucts a new LogController object    * @param $model an instance of the LogModel class    * @param $getvars the incoming HTTP GET method variables    */    function LogController (& $da) {        $logDao=& new LogDao($da);        $this->model=& new LogModel($logDao);    }     //! An accessor    /**    * Returns the view bound with data    * @return string    */    function & getView () {        return $this->view;    }} // ... class LogTableController extends LogController {   //! A constructor.    /**    * Constucts a new LogTableController object    * @param $model an instance of the LogModel class    * @param $getvars the incoming HTTP GET method variables    */    function LogTableController (& $da,$getvars=null) {        LogController::LogController($da);        if ( !isset ($getvars['page']) )            $getvars['page']=1;        $this->view=& new LogTableView($this->model,$getvars['page']);    }} // ...?>

We’ve seen “widgets” when looking at DOM and also with the Singleton Pattern. This time we’ll use widgets in our views.

To start off here’s a section of the widget library;

/* DOM Widget Library Sample usage $page=new PageWidget();$page->addTitle('Test Page');$page->cssLink('/style.css');$heading=new HeadingWidget('object',3);$heading->addText('Log Files');$heading->addAttribute('align','center');$page->addBody($heading);$link=new LinkWidget('object');$link->addLink('http://www.php.net');$link->addText('Powered by PHP');$page->addBody($link);$table=new TableWidget('object');$row=new RowWidget('object');$cell=new CellWidget('object');$cell->addText('A test cell');$row->addCell($cell);$table->addRow($row);$page->addBody($table); echo ( $page->fetch());*/ // A function to create static instances of the DOM APIfunction staticDom($type=null,$source=null) {    // Declare a static variable to hold the dom object    static $dom;     // If the instance is not there, create one    if(!isset($dom)) {        // Deal with the possible ways DOM can be constructed        switch ( $type ) {            case "file":                $dom=domxml_open_file($source); // $source: path to file                break;            case "mem":                $dom=domxml_open_mem($source); // $sounce: XML as string                break;            default:                $dom=domxml_new_doc('1.0'); // create a new one                break;        }    }    return($dom);} /** *  Base Widget class */class Widget {    /**    * Private    * $dom an instance of the DOM API    */    var $dom;     /**    * Private    * $out whether to return a DOM object or an XML string    */    var $out;     /**    * Private    * $widget stores a widget object    */    var $widget;     //! A Constructor    /**    * Constucts an abstract Widget object    * @param $out switch between XML as string or DOM object    */    function Widget ($out='string') {        $this->dom=& staticDom(); // Construct DOM from static instance        $this->out=$out;    }     //! A manipulator    /**    * Abstract widget creation method    * @return void    */    function createWidget () {        // Empty    }     //! A manipulator    /**    * Abstract widget finalization method    * @return void    */    function finalizeWidget () {        // Empty    }     //! An accessor    /**    * Change the current value of $this->out    * @return void    */    function setOut ($out) {        $this->out=$out;    }     //! An accessor    /**    * Adds a generic widget to the current widget    * @return void    */    function addWidget($newWidget) {        $newWidget->setOut('object');        $this->widget->append_child($newWidget->fetch());    }     //! An accessor    /**    * Adds a generic attibute to the current widget    * @return void    */    function addAttribute($name,$value) {        $this->widget->set_attribute($name,$value);    }     //! An accessor    /**    * Places text in the widget    * @return void    */    function addText($text) {        $text=$this->dom->create_text_node($text);        $this->widget->append_child($text);    }     //! An accessor    /**    * Adds a class="" attribute to the current widget    * @return void    */    function addClass($class) {        $this->widget->set_attribute('class',