[介绍]PHP设计模式：DAO（数据访问对象模式）

hb_sz · 发表于 2017-4-9 11:44:27

DAO in Action
　　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.

Updating the Data Access Class
　　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;

<?php /** * 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.

The Way of the Dao
　　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;

<?php /** * 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.

<?php /** * 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)

Designing Daos
　　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.

Adding an MVC pattern
　　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;

<?php /** * 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;

<?php /** * 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']); }}// ...?>

Widgets in Action
　　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;

<?php /* DOM Widget LibrarySample 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',$class); } //! An accessor /** * Adds a style="" attribute to the current widget * @return void */ function addStyle($style) { $this->widget->set_attribute('style',$style); } //! An accessor /** * Returns either XML as a string or a DOM object * @return mixed */ function &fetch () { $this->finalizeWidget(); if ( $this->out=='string') { return $this->dom->dump_node ($this->widget); } else { return $this->widget; } }}class PageWidget extends Widget { /** * Private * $head XML object for */ var $head; /** * Private * $body XML object for */ var $body; //! A constructor /** * Constucts a new PageWidget object building head and body * @param $out switch between XML as string or DOM object */ function PageWidget($out='string') { Widget::Widget($out); $this->createWidget(); } //! A manipulator /** * Page widget creation method * @return void */ function createWidget () { $this->widget=$this->dom->create_element('html'); $this->head=$this->dom->create_element('head'); $this->body=$this->dom->create_element('body'); } //! A manipulator /** * Page widget finalization method * @return void */ function finalizeWidget () { $this->widget->append_child($this->head); $this->widget->append_child($this->body); } //! An accessor /** * Adds a title element * @return void */ function addTitle ($text) { $title=$this->dom->create_element('title'); $text=$this->dom->create_text_node($text); $title->append_child($text); $this->head->append_child($title); } //! An accessor /** * Adds a link tag for CSS files * @return void */ function cssLink ($url) { $cssLink=$this->dom->create_element('link'); $cssLink->set_attribute('href',$url); $cssLink->set_attribute('type','text/css'); $cssLink->set_attribute('rel','stylesheet'); $this->head->append_child($cssLink); } //! An accessor /** * Appends a widget to $this->body * @return void */ function addBody($widget) { $this->body->append_child($widget->fetch()); }}// ... etc. etc.
　　There’s a whole lot more than that, some of which may be regarded as unnecessary, such as a widget class for creating heading tags; <h* />. These classes are wrapping the DOM extension (which needs to be installed to use this code by the way) which already has all we need for creating (X)HTML tags, creating a class for every possible tag is unnecessary.
　　The idea is simply to provide a simplified API which reduces the amount of code we need, makes it easier to understand in terms of HTML rendering and encourages re-use of widgets.
　　Here’s a section of the LogView classes using widgets;
<?php /** * Binds log data to HTML content */class LogView { /** * Private * $model an instance of the LogModel class */ var $model; /** * Private * $output contains instance of DOM page object */ var $output; //! A constructor. /** * Constucts a new LogView object * @param $model an instance of the LogModel class */ function LogView (&$model) { $this->model=& $model; } //! A manipulator /** * Builds the top of an HTML page * @return void */ function create () { $this->output=new PageWidget(); $this->output->addTitle('IP Log Files'); $this->output->cssLink('css/style.css'); $link=new LinkWidget('object'); $link->addLink(　　from url: http://www.phppatterns.com/docs/design/data_access_object_pattern_more_widgets?s=dao
　　
Tired of writing the same SQL statements over and over again? The Data Access Object pattern provides a useful way to abstract data fetching operations. In this article we’ll implement a simple Dao, adding a layer of abstraction to further seperate our application logic from the underlying database. We’ll also see more DOM generated widgets in action...
　　1)

The Need for DAO
　　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.

DAO Positioning
　　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;

DAO in Action
　　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.

Updating the Data Access Class
　　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;

<?php /** * 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.

The Way of the Dao
　　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;

<?php /** * 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.

<?php /** * 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)

Designing Daos
　　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.

Adding an MVC pattern
　　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;

<?php /** * 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;

<?php /** * 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']); }}// ...?>

Widgets in Action
　　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;

<?php /* DOM Widget LibrarySample 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',$class); } //! An accessor /** * Adds a style="" attribute to the current widget * @return void */ function addStyle($style) { $this->widget->set_attribute('style',$style); } //! An accessor /** * Returns either XML as a string or a DOM object * @return mixed */ function &fetch () { $this->finalizeWidget(); if ( $this->out=='string') { return $this->dom->dump_node ($this->widget); } else { return $this->widget; } }}class PageWidget extends Widget { /** * Private * $head XML object for */ var $head; /** * Private * $body XML object for */ var $body; //! A constructor /** * Constucts a new PageWidget object building head and body * @param $out switch between XML as string or DOM object */ function PageWidget($out='string') { Widget::Widget($out); $this->createWidget(); } //! A manipulator /** * Page widget creation method * @return void */ function createWidget () { $this->widget=$this->dom->create_element('html'); $this->head=$this->dom->create_element('head'); $this->body=$this->dom->create_element('body'); } //! A manipulator /** * Page widget finalization method * @return void */ function finalizeWidget () { $this->widget->append_child($this->head); $this->widget->append_child($this->body); } //! An accessor /** * Adds a title element * @return void */ function addTitle ($text) { $title=$this->dom->create_element('title'); $text=$this->dom->create_text_node($text); $title->append_child($text); $this->head->append_child($title); } //! An accessor /** * Adds a link tag for CSS files * @return void */ function cssLink ($url) { $cssLink=$this->dom->create_element('link'); $cssLink->set_attribute('href',$url); $cssLink->set_attribute('type','text/css'); $cssLink->set_attribute('rel','stylesheet'); $this->head->append_child($cssLink); } //! An accessor /** * Appends a widget to $this->body * @return void */ function addBody($widget) { $this->body->append_child($widget->fetch()); }}// ... etc. etc.
　　There’s a whole lot more than that, some of which may be regarded as unnecessary, such as a widget class for creating heading tags; <h* />. These classes are wrapping the DOM extension (which needs to be installed to use this code by the way) which already has all we need for creating (X)HTML tags, creating a class for every possible tag is unnecessary.
　　The idea is simply to provide a simplified API which reduces the amount of code we need, makes it easier to understand in terms of HTML rendering and encourages re-use of widgets.
　　Here’s a section of the LogView classes using widgets;

___FCKpd___7
　　Of particular interest is this line;

 $table->addRow(LogTableWidget::getPager($this->page,$numPages));
　　Here the code for rendering the “Google-like” result pager is farmed off to another class;
<?php // ...class LogTableWidget { function getPager($page,$numPages) { $row=new RowWidget('object'); $cell=new CellWidget('object'); $cell->addAttribute('align','center'); $cell->addAttribute('colspan','5'); if ($numPages!=1&&$page!=1) { $link=new LinkWidget('object'); $link->addLink(　　from url: http://www.phppatterns.com/docs/design/data_access_object_pattern_more_widgets?s=dao
　　
Tired of writing the same SQL statements over and over again? The Data Access Object pattern provides a useful way to abstract data fetching operations. In this article we’ll implement a simple Dao, adding a layer of abstraction to further seperate our application logic from the underlying database. We’ll also see more DOM generated widgets in action...
　　1)

The Need for DAO
　　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.

DAO Positioning
　　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;

DAO in Action
　　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.

Updating the Data Access Class
　　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;

<?php /** * 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.

The Way of the Dao
　　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;

<?php /** * 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.

<?php /** * 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)

Designing Daos
　　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.

Adding an MVC pattern
　　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;

<?php /** * 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;

<?php /** * 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']); }}// ...?>

Widgets in Action
　　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;

<?php /* DOM Widget LibrarySample 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',$class); } //! An accessor /** * Adds a style="" attribute to the current widget * @return void */ function addStyle($style) { $this->widget->set_attribute('style',$style); } //! An accessor /** * Returns either XML as a string or a DOM object * @return mixed */ function &fetch () { $this->finalizeWidget(); if ( $this->out=='string') { return $this->dom->dump_node ($this->widget); } else { return $this->widget; } }}class PageWidget extends Widget { /** * Private * $head XML object for */ var $head; /** * Private * $body XML object for */ var $body; //! A constructor /** * Constucts a new PageWidget object building head and body * @param $out switch between XML as string or DOM object */ function PageWidget($out='string') { Widget::Widget($out); $this->createWidget(); } //! A manipulator /** * Page widget creation method * @return void */ function createWidget () { $this->widget=$this->dom->create_element('html'); $this->head=$this->dom->create_element('head'); $this->body=$this->dom->create_element('body'); } //! A manipulator /** * Page widget finalization method * @return void */ function finalizeWidget () { $this->widget->append_child($this->head); $this->widget->append_child($this->body); } //! An accessor /** * Adds a title element * @return void */ function addTitle ($text) { $title=$this->dom->create_element('title'); $text=$this->dom->create_text_node($text); $title->append_child($text); $this->head->append_child($title); } //! An accessor /** * Adds a link tag for CSS files * @return void */ function cssLink ($url) { $cssLink=$this->dom->create_element('link'); $cssLink->set_attribute('href',$url); $cssLink->set_attribute('type','text/css'); $cssLink->set_attribute('rel','stylesheet'); $this->head->append_child($cssLink); } //! An accessor /** * Appends a widget to $this->body * @return void */ function addBody($widget) { $this->body->append_child($widget->fetch()); }}// ... etc. etc.
　　There’s a whole lot more than that, some of which may be regarded as unnecessary, such as a widget class for creating heading tags; <h* />. These classes are wrapping the DOM extension (which needs to be installed to use this code by the way) which already has all we need for creating (X)HTML tags, creating a class for every possible tag is unnecessary.
　　The idea is simply to provide a simplified API which reduces the amount of code we need, makes it easier to understand in terms of HTML rendering and encourages re-use of widgets.
　　Here’s a section of the LogView classes using widgets;
<?php /** * Binds log data to HTML content */class LogView { /** * Private * $model an instance of the LogModel class */ var $model; /** * Private * $output contains instance of DOM page object */ var $output; //! A constructor. /** * Constucts a new LogView object * @param $model an instance of the LogModel class */ function LogView (&$model) { $this->model=& $model; } //! A manipulator /** * Builds the top of an HTML page * @return void */ function create () { $this->output=new PageWidget(); $this->output->addTitle('IP Log Files'); $this->output->cssLink('css/style.css'); $link=new LinkWidget('object'); $link->addLink(　　from url: http://www.phppatterns.com/docs/design/data_access_object_pattern_more_widgets?s=dao
　　
Tired of writing the same SQL statements over and over again? The Data Access Object pattern provides a useful way to abstract data fetching operations. In this article we’ll implement a simple Dao, adding a layer of abstraction to further seperate our application logic from the underlying database. We’ll also see more DOM generated widgets in action...
　　1)

The Need for DAO
　　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.

DAO Positioning
　　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;

DAO in Action
　　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.

Updating the Data Access Class
　　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;

<?php /** * 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.

The Way of the Dao
　　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;

<?php /** * 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.

<?php /** * 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)

Designing Daos
　　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.

Adding an MVC pattern
　　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;

<?php /** * 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;

<?php /** * 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']); }}// ...?>

Widgets in Action
　　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;

<?php /* DOM Widget LibrarySample 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',$class); } //! An accessor /** * Adds a style="" attribute to the current widget * @return void */ function addStyle($style) { $this->widget->set_attribute('style',$style); } //! An accessor /** * Returns either XML as a string or a DOM object * @return mixed */ function &fetch () { $this->finalizeWidget(); if ( $this->out=='string') { return $this->dom->dump_node ($this->widget); } else { return $this->widget; } }}class PageWidget extends Widget { /** * Private * $head XML object for */ var $head; /** * Private * $body XML object for */ var $body; //! A constructor /** * Constucts a new PageWidget object building head and body * @param $out switch between XML as string or DOM object */ function PageWidget($out='string') { Widget::Widget($out); $this->createWidget(); } //! A manipulator /** * Page widget creation method * @return void */ function createWidget () { $this->widget=$this->dom->create_element('html'); $this->head=$this->dom->create_element('head'); $this->body=$this->dom->create_element('body'); } //! A manipulator /** * Page widget finalization method * @return void */ function finalizeWidget () { $this->widget->append_child($this->head); $this->widget->append_child($this->body); } //! An accessor /** * Adds a title element * @return void */ function addTitle ($text) { $title=$this->dom->create_element('title'); $text=$this->dom->create_text_node($text); $title->append_child($text); $this->head->append_child($title); } //! An accessor /** * Adds a link tag for CSS files * @return void */ function cssLink ($url) { $cssLink=$this->dom->create_element('link'); $cssLink->set_attribute('href',$url); $cssLink->set_attribute('type','text/css'); $cssLink->set_attribute('rel','stylesheet'); $this->head->append_child($cssLink); } //! An accessor /** * Appends a widget to $this->body * @return void */ function addBody($widget) { $this->body->append_child($widget->fetch()); }}// ... etc. etc.
　　There’s a whole lot more than that, some of which may be regarded as unnecessary, such as a widget class for creating heading tags; <h* />. These classes are wrapping the DOM extension (which needs to be installed to use this code by the way) which already has all we need for creating (X)HTML tags, creating a class for every possible tag is unnecessary.
　　The idea is simply to provide a simplified API which reduces the amount of code we need, makes it easier to understand in terms of HTML rendering and encourages re-use of widgets.
　　Here’s a section of the LogView classes using widgets;

___FCKpd___7
　　Of particular interest is this line;

 $table->addRow(LogTableWidget::getPager($this->page,$numPages));
　　Here the code for rendering the “Google-like” result pager is farmed off to another class;

___FCKpd___9
　　Re-using the above “paging” widget in other views is now easy.
　　Control of appearance is all left to an external CSS file in this example. If we want to allow a designer to control layout of pages we need some kind of simple template system, not unlike ASP.NET, were we plant tags that “bind” to a widget (control) class.
　　One further side effect of our widgets (which is not unique to widgets of course) is by delivering XML compliant HTML, we’re able to use XSL to transform it to other content types, such as WML.
　　The rest is up to the code to explain...

Further Reading
　　PHP Architect Jan 2003 Edition - has an excellent article on Database Persistence in PHP. Codewalkers: A Framework for Persisting Data Relationships - inspired by the PHP Architect article, this tutorial puts some of the ideas into practice. PHPEverywhere: Object Related Mania - the counter view on Object Relational Database Mapping. Write once, persist anywhere - Implement a Data Access Object pattern framework (Java) O/R, JDO, and Database engineering tools - collection of links on Persistance Layers and Object Relational Mapping (Java) The Goag Dao Pattern useful for project management... ;)

1) Dao Sample Code

2) For an PHP class library providing database abstraction of this kind (and generally as a excellent design example) try Eclipse

3) For PHP class library which encourages use of Data Access Objects, try eXtremePHP - documentation includes a tutorial of building Daos

SERVER['PHP_SELF']); $link->addText('Start Over'); $this->output->addBody($link); } //! An manipulator /** * Abstract method with completes the page * @return void */ function finalize () { // Empty } //! An accessor /** * Returns the page * @return void */ function display () { $this->finalize(); return $this->output->fetch(); }}// ...class LogTableView extends LogView { /** * Private * $page the page we're viewing */ var $page; //! A constructor. /** * Constucts a new LogView object * @param $model an instance of the LogModel class */ function LogTableView (&$model,$page=1) { LogView::LogView($model); $this->page=$page; $this->create(); } //! A manipulator /** * Renders a log table * @return void */ function logTable() { $this->model->listLogs($this->page); $heading=new HeadingWidget('object',2); $heading->addText('Paged Log Result Set'); $heading->addAttribute('align','center'); $this->output->addBody($heading); // Build result table $table=new TableWidget('object'); $table->addAttribute('align','center'); $table->addAttribute('width','750'); // Build result pager $numPages=$this->model->getNumPages(); $table->addRow(LogTableWidget::getPager($this->page,$numPages)); // Build table rows $table->addRow(LogTableWidget::getHeader()); while ( $log = $this->model->getLog() ) { if ( $alt== '#f6f7f8' ) $alt='#ffffff'; else $alt='#f6f7f8'; $table->addRow(LogTableWidget::getRow($log,$alt)); } $this->output->addBody($table); } //! An manipulator /** * Runs the logItem method * @return void */ function finalize () { $this->logTable(); }}// ...?>　　Of particular interest is this line;

___FCKpd___8
　　Here the code for rendering the “Google-like” result pager is farmed off to another class;

___FCKpd___9
　　Re-using the above “paging” widget in other views is now easy.
　　Control of appearance is all left to an external CSS file in this example. If we want to allow a designer to control layout of pages we need some kind of simple template system, not unlike ASP.NET, were we plant tags that “bind” to a widget (control) class.
　　One further side effect of our widgets (which is not unique to widgets of course) is by delivering XML compliant HTML, we’re able to use XSL to transform it to other content types, such as WML.
　　The rest is up to the code to explain...

Further Reading
　　PHP Architect Jan 2003 Edition - has an excellent article on Database Persistence in PHP. Codewalkers: A Framework for Persisting Data Relationships - inspired by the PHP Architect article, this tutorial puts some of the ideas into practice. PHPEverywhere: Object Related Mania - the counter view on Object Relational Database Mapping. Write once, persist anywhere - Implement a Data Access Object pattern framework (Java) O/R, JDO, and Database engineering tools - collection of links on Persistance Layers and Object Relational Mapping (Java) The Goag Dao Pattern useful for project management... ;)

1) Dao Sample Code

2) For an PHP class library providing database abstraction of this kind (and generally as a excellent design example) try Eclipse

3) For PHP class library which encourages use of Data Access Objects, try eXtremePHP - documentation includes a tutorial of building Daos

SERVER['PHP_SELF'].'?page=1'); $link->addText('); $cell->addWidget($link); $cell->addText(' '); } if (($page-5)>1) { $cell->addText('...'); } for ($i=($page-5);$i($page-1);$i++) { if ($i>0) { $link=new LinkWidget('object'); $link->addLink(　　from url: http://www.phppatterns.com/docs/design/data_access_object_pattern_more_widgets?s=dao
　　
Tired of writing the same SQL statements over and over again? The Data Access Object pattern provides a useful way to abstract data fetching operations. In this article we’ll implement a simple Dao, adding a layer of abstraction to further seperate our application logic from the underlying database. We’ll also see more DOM generated widgets in action...
　　1)

The Need for DAO
　　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.

DAO Positioning
　　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;

DAO in Action
　　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.

Updating the Data Access Class
　　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;

<?php /** * 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.

The Way of the Dao
　　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;

<?php /** * 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.

<?php /** * 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)

Designing Daos
　　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.

Adding an MVC pattern
　　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;

<?php /** * 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;

<?php /** * 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']); }}// ...?>

Widgets in Action
　　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;

<?php /* DOM Widget LibrarySample 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',$class); } //! An accessor /** * Adds a style="" attribute to the current widget * @return void */ function addStyle($style) { $this->widget->set_attribute('style',$style); } //! An accessor /** * Returns either XML as a string or a DOM object * @return mixed */ function &fetch () { $this->finalizeWidget(); if ( $this->out=='string') { return $this->dom->dump_node ($this->widget); } else { return $this->widget; } }}class PageWidget extends Widget { /** * Private * $head XML object for */ var $head; /** * Private * $body XML object for */ var $body; //! A constructor /** * Constucts a new PageWidget object building head and body * @param $out switch between XML as string or DOM object */ function PageWidget($out='string') { Widget::Widget($out); $this->createWidget(); } //! A manipulator /** * Page widget creation method * @return void */ function createWidget () { $this->widget=$this->dom->create_element('html'); $this->head=$this->dom->create_element('head'); $this->body=$this->dom->create_element('body'); } //! A manipulator /** * Page widget finalization method * @return void */ function finalizeWidget () { $this->widget->append_child($this->head); $this->widget->append_child($this->body); } //! An accessor /** * Adds a title element * @return void */ function addTitle ($text) { $title=$this->dom->create_element('title'); $text=$this->dom->create_text_node($text); $title->append_child($text); $this->head->append_child($title); } //! An accessor /** * Adds a link tag for CSS files * @return void */ function cssLink ($url) { $cssLink=$this->dom->create_element('link'); $cssLink->set_attribute('href',$url); $cssLink->set_attribute('type','text/css'); $cssLink->set_attribute('rel','stylesheet'); $this->head->append_child($cssLink); } //! An accessor /** * Appends a widget to $this->body * @return void */ function addBody($widget) { $this->body->append_child($widget->fetch()); }}// ... etc. etc.
　　There’s a whole lot more than that, some of which may be regarded as unnecessary, such as a widget class for creating heading tags; <h* />. These classes are wrapping the DOM extension (which needs to be installed to use this code by the way) which already has all we need for creating (X)HTML tags, creating a class for every possible tag is unnecessary.
　　The idea is simply to provide a simplified API which reduces the amount of code we need, makes it easier to understand in terms of HTML rendering and encourages re-use of widgets.
　　Here’s a section of the LogView classes using widgets;
<?php /** * Binds log data to HTML content */class LogView { /** * Private * $model an instance of the LogModel class */ var $model; /** * Private * $output contains instance of DOM page object */ var $output; //! A constructor. /** * Constucts a new LogView object * @param $model an instance of the LogModel class */ function LogView (&$model) { $this->model=& $model; } //! A manipulator /** * Builds the top of an HTML page * @return void */ function create () { $this->output=new PageWidget(); $this->output->addTitle('IP Log Files'); $this->output->cssLink('css/style.css'); $link=new LinkWidget('object'); $link->addLink(　　from url: http://www.phppatterns.com/docs/design/data_access_object_pattern_more_widgets?s=dao
　　
Tired of writing the same SQL statements over and over again? The Data Access Object pattern provides a useful way to abstract data fetching operations. In this article we’ll implement a simple Dao, adding a layer of abstraction to further seperate our application logic from the underlying database. We’ll also see more DOM generated widgets in action...
　　1)

The Need for DAO
　　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.

DAO Positioning
　　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;

DAO in Action
　　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.

Updating the Data Access Class
　　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;

<?php /** * 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.

The Way of the Dao
　　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;

<?php /** * 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.

<?php /** * 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)

Designing Daos
　　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.

Adding an MVC pattern
　　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;

<?php /** * 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;

<?php /** * 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']); }}// ...?>

Widgets in Action
　　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;

<?php /* DOM Widget LibrarySample 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',$class); } //! An accessor /** * Adds a style="" attribute to the current widget * @return void */ function addStyle($style) { $this->widget->set_attribute('style',$style); } //! An accessor /** * Returns either XML as a string or a DOM object * @return mixed */ function &fetch () { $this->finalizeWidget(); if ( $this->out=='string') { return $this->dom->dump_node ($this->widget); } else { return $this->widget; } }}class PageWidget extends Widget { /** * Private * $head XML object for */ var $head; /** * Private * $body XML object for */ var $body; //! A constructor /** * Constucts a new PageWidget object building head and body * @param $out switch between XML as string or DOM object */ function PageWidget($out='string') { Widget::Widget($out); $this->createWidget(); } //! A manipulator /** * Page widget creation method * @return void */ function createWidget () { $this->widget=$this->dom->create_element('html'); $this->head=$this->dom->create_element('head'); $this->body=$this->dom->create_element('body'); } //! A manipulator /** * Page widget finalization method * @return void */ function finalizeWidget () { $this->widget->append_child($this->head); $this->widget->append_child($this->body); } //! An accessor /** * Adds a title element * @return void */ function addTitle ($text) { $title=$this->dom->create_element('title'); $text=$this->dom->create_text_node($text); $title->append_child($text); $this->head->append_child($title); } //! An accessor /** * Adds a link tag for CSS files * @return void */ function cssLink ($url) { $cssLink=$this->dom->create_element('link'); $cssLink->set_attribute('href',$url); $cssLink->set_attribute('type','text/css'); $cssLink->set_attribute('rel','stylesheet'); $this->head->append_child($cssLink); } //! An accessor /** * Appends a widget to $this->body * @return void */ function addBody($widget) { $this->body->append_child($widget->fetch()); }}// ... etc. etc.
　　There’s a whole lot more than that, some of which may be regarded as unnecessary, such as a widget class for creating heading tags; <h* />. These classes are wrapping the DOM extension (which needs to be installed to use this code by the way) which already has all we need for creating (X)HTML tags, creating a class for every possible tag is unnecessary.
　　The idea is simply to provide a simplified API which reduces the amount of code we need, makes it easier to understand in terms of HTML rendering and encourages re-use of widgets.
　　Here’s a section of the LogView classes using widgets;

___FCKpd___7
　　Of particular interest is this line;

 $table->addRow(LogTableWidget::getPager($this->page,$numPages));
　　Here the code for rendering the “Google-like” result pager is farmed off to another class;

___FCKpd___9
　　Re-using the above “paging” widget in other views is now easy.
　　Control of appearance is all left to an external CSS file in this example. If we want to allow a designer to control layout of pages we need some kind of simple template system, not unlike ASP.NET, were we plant tags that “bind” to a widget (control) class.
　　One further side effect of our widgets (which is not unique to widgets of course) is by delivering XML compliant HTML, we’re able to use XSL to transform it to other content types, such as WML.
　　The rest is up to the code to explain...

Further Reading
　　PHP Architect Jan 2003 Edition - has an excellent article on Database Persistence in PHP. Codewalkers: A Framework for Persisting Data Relationships - inspired by the PHP Architect article, this tutorial puts some of the ideas into practice. PHPEverywhere: Object Related Mania - the counter view on Object Relational Database Mapping. Write once, persist anywhere - Implement a Data Access Object pattern framework (Java) O/R, JDO, and Database engineering tools - collection of links on Persistance Layers and Object Relational Mapping (Java) The Goag Dao Pattern useful for project management... ;)

1) Dao Sample Code

2) For an PHP class library providing database abstraction of this kind (and generally as a excellent design example) try Eclipse

3) For PHP class library which encourages use of Data Access Objects, try eXtremePHP - documentation includes a tutorial of building Daos

SERVER['PHP_SELF']); $link->addText('Start Over'); $this->output->addBody($link); } //! An manipulator /** * Abstract method with completes the page * @return void */ function finalize () { // Empty } //! An accessor /** * Returns the page * @return void */ function display () { $this->finalize(); return $this->output->fetch(); }}// ...class LogTableView extends LogView { /** * Private * $page the page we're viewing */ var $page; //! A constructor. /** * Constucts a new LogView object * @param $model an instance of the LogModel class */ function LogTableView (&$model,$page=1) { LogView::LogView($model); $this->page=$page; $this->create(); } //! A manipulator /** * Renders a log table * @return void */ function logTable() { $this->model->listLogs($this->page); $heading=new HeadingWidget('object',2); $heading->addText('Paged Log Result Set'); $heading->addAttribute('align','center'); $this->output->addBody($heading); // Build result table $table=new TableWidget('object'); $table->addAttribute('align','center'); $table->addAttribute('width','750'); // Build result pager $numPages=$this->model->getNumPages(); $table->addRow(LogTableWidget::getPager($this->page,$numPages)); // Build table rows $table->addRow(LogTableWidget::getHeader()); while ( $log = $this->model->getLog() ) { if ( $alt== '#f6f7f8' ) $alt='#ffffff'; else $alt='#f6f7f8'; $table->addRow(LogTableWidget::getRow($log,$alt)); } $this->output->addBody($table); } //! An manipulator /** * Runs the logItem method * @return void */ function finalize () { $this->logTable(); }}// ...?>　　Of particular interest is this line;

___FCKpd___8
　　Here the code for rendering the “Google-like” result pager is farmed off to another class;

___FCKpd___9
　　Re-using the above “paging” widget in other views is now easy.
　　Control of appearance is all left to an external CSS file in this example. If we want to allow a designer to control layout of pages we need some kind of simple template system, not unlike ASP.NET, were we plant tags that “bind” to a widget (control) class.
　　One further side effect of our widgets (which is not unique to widgets of course) is by delivering XML compliant HTML, we’re able to use XSL to transform it to other content types, such as WML.
　　The rest is up to the code to explain...

Further Reading
　　PHP Architect Jan 2003 Edition - has an excellent article on Database Persistence in PHP. Codewalkers: A Framework for Persisting Data Relationships - inspired by the PHP Architect article, this tutorial puts some of the ideas into practice. PHPEverywhere: Object Related Mania - the counter view on Object Relational Database Mapping. Write once, persist anywhere - Implement a Data Access Object pattern framework (Java) O/R, JDO, and Database engineering tools - collection of links on Persistance Layers and Object Relational Mapping (Java) The Goag Dao Pattern useful for project management... ;)

1) Dao Sample Code

2) For an PHP class library providing database abstraction of this kind (and generally as a excellent design example) try Eclipse

3) For PHP class library which encourages use of Data Access Objects, try eXtremePHP - documentation includes a tutorial of building Daos

SERVER['PHP_SELF'].'?page='.$i); $link->addText($i.' '); $cell->addWidget($link); } } $link=new LinkWidget('object'); $link->addText('['.$page.'] '); $cell->addWidget($link); for ($i=($page+1);$i($page+5);$i++) { if ($i$numPages) { $link=new LinkWidget('object'); $link->addLink(　　from url: http://www.phppatterns.com/docs/design/data_access_object_pattern_more_widgets?s=dao
　　
Tired of writing the same SQL statements over and over again? The Data Access Object pattern provides a useful way to abstract data fetching operations. In this article we’ll implement a simple Dao, adding a layer of abstraction to further seperate our application logic from the underlying database. We’ll also see more DOM generated widgets in action...
　　1)

The Need for DAO
　　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.

DAO Positioning
　　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;

DAO in Action
　　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.

Updating the Data Access Class
　　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;

<?php /** * 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.

The Way of the Dao
　　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;

<?php /** * 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.

<?php /** * 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)

Designing Daos
　　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.

Adding an MVC pattern
　　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;

<?php /** * 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;

<?php /** * 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']); }}// ...?>

Widgets in Action
　　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;

<?php /* DOM Widget LibrarySample 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',$class); } //! An accessor /** * Adds a style="" attribute to the current widget * @return void */ function addStyle($style) { $this->widget->set_attribute('style',$style); } //! An accessor /** * Returns either XML as a string or a DOM object * @return mixed */ function &fetch () { $this->finalizeWidget(); if ( $this->out=='string') { return $this->dom->dump_node ($this->widget); } else { return $this->widget; } }}class PageWidget extends Widget { /** * Private * $head XML object for */ var $head; /** * Private * $body XML object for */ var $body; //! A constructor /** * Constucts a new PageWidget object building head and body * @param $out switch between XML as string or DOM object */ function PageWidget($out='string') { Widget::Widget($out); $this->createWidget(); } //! A manipulator /** * Page widget creation method * @return void */ function createWidget () { $this->widget=$this->dom->create_element('html'); $this->head=$this->dom->create_element('head'); $this->body=$this->dom->create_element('body'); } //! A manipulator /** * Page widget finalization method * @return void */ function finalizeWidget () { $this->widget->append_child($this->head); $this->widget->append_child($this->body); } //! An accessor /** * Adds a title element * @return void */ function addTitle ($text) { $title=$this->dom->create_element('title'); $text=$this->dom->create_text_node($text); $title->append_child($text); $this->head->append_child($title); } //! An accessor /** * Adds a link tag for CSS files * @return void */ function cssLink ($url) { $cssLink=$this->dom->create_element('link'); $cssLink->set_attribute('href',$url); $cssLink->set_attribute('type','text/css'); $cssLink->set_attribute('rel','stylesheet'); $this->head->append_child($cssLink); } //! An accessor /** * Appends a widget to $this->body * @return void */ function addBody($widget) { $this->body->append_child($widget->fetch()); }}// ... etc. etc.
　　There’s a whole lot more than that, some of which may be regarded as unnecessary, such as a widget class for creating heading tags; <h* />. These classes are wrapping the DOM extension (which needs to be installed to use this code by the way) which already has all we need for creating (X)HTML tags, creating a class for every possible tag is unnecessary.
　　The idea is simply to provide a simplified API which reduces the amount of code we need, makes it easier to understand in terms of HTML rendering and encourages re-use of widgets.
　　Here’s a section of the LogView classes using widgets;
<?php /** * Binds log data to HTML content */class LogView { /** * Private * $model an instance of the LogModel class */ var $model; /** * Private * $output contains instance of DOM page object */ var $output; //! A constructor. /** * Constucts a new LogView object * @param $model an instance of the LogModel class */ function LogView (&$model) { $this->model=& $model; } //! A manipulator /** * Builds the top of an HTML page * @return void */ function create () { $this->output=new PageWidget(); $this->output->addTitle('IP Log Files'); $this->output->cssLink('css/style.css'); $link=new LinkWidget('object'); $link->addLink(　　from url: http://www.phppatterns.com/docs/design/data_access_object_pattern_more_widgets?s=dao
　　
Tired of writing the same SQL statements over and over again? The Data Access Object pattern provides a useful way to abstract data fetching operations. In this article we’ll implement a simple Dao, adding a layer of abstraction to further seperate our application logic from the underlying database. We’ll also see more DOM generated widgets in action...
　　1)

The Need for DAO
　　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.

DAO Positioning
　　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;

DAO in Action
　　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.

Updating the Data Access Class
　　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;

<?php /** * 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.

The Way of the Dao
　　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;

<?php /** * 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.

<?php /** * 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)

Designing Daos
　　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.

Adding an MVC pattern
　　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;

<?php /** * 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;

<?php /** * 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']); }}// ...?>

Widgets in Action
　　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;

<?php /* DOM Widget LibrarySample 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',$class); } //! An accessor /** * Adds a style="" attribute to the current widget * @return void */ function addStyle($style) { $this->widget->set_attribute('style',$style); } //! An accessor /** * Returns either XML as a string or a DOM object * @return mixed */ function &fetch () { $this->finalizeWidget(); if ( $this->out=='string') { return $this->dom->dump_node ($this->widget); } else { return $this->widget; } }}class PageWidget extends Widget { /** * Private * $head XML object for */ var $head; /** * Private * $body XML object for */ var $body; //! A constructor /** * Constucts a new PageWidget object building head and body * @param $out switch between XML as string or DOM object */ function PageWidget($out='string') { Widget::Widget($out); $this->createWidget(); } //! A manipulator /** * Page widget creation method * @return void */ function createWidget () { $this->widget=$this->dom->create_element('html'); $this->head=$this->dom->create_element('head'); $this->body=$this->dom->create_element('body'); } //! A manipulator /** * Page widget finalization method * @return void */ function finalizeWidget () { $this->widget->append_child($this->head); $this->widget->append_child($this->body); } //! An accessor /** * Adds a title element * @return void */ function addTitle ($text) { $title=$this->dom->create_element('title'); $text=$this->dom->create_text_node($text); $title->append_child($text); $this->head->append_child($title); } //! An accessor /** * Adds a link tag for CSS files * @return void */ function cssLink ($url) { $cssLink=$this->dom->create_element('link'); $cssLink->set_attribute('href',$url); $cssLink->set_attribute('type','text/css'); $cssLink->set_attribute('rel','stylesheet'); $this->head->append_child($cssLink); } //! An accessor /** * Appends a widget to $this->body * @return void */ function addBody($widget) { $this->body->append_child($widget->fetch()); }}// ... etc. etc.
　　There’s a whole lot more than that, some of which may be regarded as unnecessary, such as a widget class for creating heading tags; <h* />. These classes are wrapping the DOM extension (which needs to be installed to use this code by the way) which already has all we need for creating (X)HTML tags, creating a class for every possible tag is unnecessary.
　　The idea is simply to provide a simplified API which reduces the amount of code we need, makes it easier to understand in terms of HTML rendering and encourages re-use of widgets.
　　Here’s a section of the LogView classes using widgets;

___FCKpd___7
　　Of particular interest is this line;

 $table->addRow(LogTableWidget::getPager($this->page,$numPages));
　　Here the code for rendering the “Google-like” result pager is farmed off to another class;

___FCKpd___9
　　Re-using the above “paging” widget in other views is now easy.
　　Control of appearance is all left to an external CSS file in this example. If we want to allow a designer to control layout of pages we need some kind of simple template system, not unlike ASP.NET, were we plant tags that “bind” to a widget (control) class.
　　One further side effect of our widgets (which is not unique to widgets of course) is by delivering XML compliant HTML, we’re able to use XSL to transform it to other content types, such as WML.
　　The rest is up to the code to explain...

Further Reading
　　PHP Architect Jan 2003 Edition - has an excellent article on Database Persistence in PHP. Codewalkers: A Framework for Persisting Data Relationships - inspired by the PHP Architect article, this tutorial puts some of the ideas into practice. PHPEverywhere: Object Related Mania - the counter view on Object Relational Database Mapping. Write once, persist anywhere - Implement a Data Access Object pattern framework (Java) O/R, JDO, and Database engineering tools - collection of links on Persistance Layers and Object Relational Mapping (Java) The Goag Dao Pattern useful for project management... ;)

1) Dao Sample Code

2) For an PHP class library providing database abstraction of this kind (and generally as a excellent design example) try Eclipse

3) For PHP class library which encourages use of Data Access Objects, try eXtremePHP - documentation includes a tutorial of building Daos

SERVER['PHP_SELF']); $link->addText('Start Over'); $this->output->addBody($link); } //! An manipulator /** * Abstract method with completes the page * @return void */ function finalize () { // Empty } //! An accessor /** * Returns the page * @return void */ function display () { $this->finalize(); return $this->output->fetch(); }}// ...class LogTableView extends LogView { /** * Private * $page the page we're viewing */ var $page; //! A constructor. /** * Constucts a new LogView object * @param $model an instance of the LogModel class */ function LogTableView (&$model,$page=1) { LogView::LogView($model); $this->page=$page; $this->create(); } //! A manipulator /** * Renders a log table * @return void */ function logTable() { $this->model->listLogs($this->page); $heading=new HeadingWidget('object',2); $heading->addText('Paged Log Result Set'); $heading->addAttribute('align','center'); $this->output->addBody($heading); // Build result table $table=new TableWidget('object'); $table->addAttribute('align','center'); $table->addAttribute('width','750'); // Build result pager $numPages=$this->model->getNumPages(); $table->addRow(LogTableWidget::getPager($this->page,$numPages)); // Build table rows $table->addRow(LogTableWidget::getHeader()); while ( $log = $this->model->getLog() ) { if ( $alt== '#f6f7f8' ) $alt='#ffffff'; else $alt='#f6f7f8'; $table->addRow(LogTableWidget::getRow($log,$alt)); } $this->output->addBody($table); } //! An manipulator /** * Runs the logItem method * @return void */ function finalize () { $this->logTable(); }}// ...?>　　Of particular interest is this line;

___FCKpd___8
　　Here the code for rendering the “Google-like” result pager is farmed off to another class;

___FCKpd___9
　　Re-using the above “paging” widget in other views is now easy.
　　Control of appearance is all left to an external CSS file in this example. If we want to allow a designer to control layout of pages we need some kind of simple template system, not unlike ASP.NET, were we plant tags that “bind” to a widget (control) class.
　　One further side effect of our widgets (which is not unique to widgets of course) is by delivering XML compliant HTML, we’re able to use XSL to transform it to other content types, such as WML.
　　The rest is up to the code to explain...

Further Reading
　　PHP Architect Jan 2003 Edition - has an excellent article on Database Persistence in PHP. Codewalkers: A Framework for Persisting Data Relationships - inspired by the PHP Architect article, this tutorial puts some of the ideas into practice. PHPEverywhere: Object Related Mania - the counter view on Object Relational Database Mapping. Write once, persist anywhere - Implement a Data Access Object pattern framework (Java) O/R, JDO, and Database engineering tools - collection of links on Persistance Layers and Object Relational Mapping (Java) The Goag Dao Pattern useful for project management... ;)

1) Dao Sample Code

2) For an PHP class library providing database abstraction of this kind (and generally as a excellent design example) try Eclipse

3) For PHP class library which encourages use of Data Access Objects, try eXtremePHP - documentation includes a tutorial of building Daos

SERVER['PHP_SELF'].'?page='.$i); $link->addText($i.' '); $cell->addWidget($link); } } if (($page+5)$numPages) { $cell->addText('...'); } if ($numPages!=1&&$page!=$numPages) { $link=new LinkWidget('object'); $link->addLink(　　from url: http://www.phppatterns.com/docs/design/data_access_object_pattern_more_widgets?s=dao
　　
Tired of writing the same SQL statements over and over again? The Data Access Object pattern provides a useful way to abstract data fetching operations. In this article we’ll implement a simple Dao, adding a layer of abstraction to further seperate our application logic from the underlying database. We’ll also see more DOM generated widgets in action...
　　1)

The Need for DAO
　　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.

DAO Positioning
　　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;

DAO in Action
　　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.

Updating the Data Access Class
　　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;

<?php /** * 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.

The Way of the Dao
　　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;

<?php /** * 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.

<?php /** * 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)

Designing Daos
　　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.

Adding an MVC pattern
　　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;

<?php /** * 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;

<?php /** * 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']); }}// ...?>

Widgets in Action
　　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;

<?php /* DOM Widget LibrarySample 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',$class); } //! An accessor /** * Adds a style="" attribute to the current widget * @return void */ function addStyle($style) { $this->widget->set_attribute('style',$style); } //! An accessor /** * Returns either XML as a string or a DOM object * @return mixed */ function &fetch () { $this->finalizeWidget(); if ( $this->out=='string') { return $this->dom->dump_node ($this->widget); } else { return $this->widget; } }}class PageWidget extends Widget { /** * Private * $head XML object for */ var $head; /** * Private * $body XML object for */ var $body; //! A constructor /** * Constucts a new PageWidget object building head and body * @param $out switch between XML as string or DOM object */ function PageWidget($out='string') { Widget::Widget($out); $this->createWidget(); } //! A manipulator /** * Page widget creation method * @return void */ function createWidget () { $this->widget=$this->dom->create_element('html'); $this->head=$this->dom->create_element('head'); $this->body=$this->dom->create_element('body'); } //! A manipulator /** * Page widget finalization method * @return void */ function finalizeWidget () { $this->widget->append_child($this->head); $this->widget->append_child($this->body); } //! An accessor /** * Adds a title element * @return void */ function addTitle ($text) { $title=$this->dom->create_element('title'); $text=$this->dom->create_text_node($text); $title->append_child($text); $this->head->append_child($title); } //! An accessor /** * Adds a link tag for CSS files * @return void */ function cssLink ($url) { $cssLink=$this->dom->create_element('link'); $cssLink->set_attribute('href',$url); $cssLink->set_attribute('type','text/css'); $cssLink->set_attribute('rel','stylesheet'); $this->head->append_child($cssLink); } //! An accessor /** * Appends a widget to $this->body * @return void */ function addBody($widget) { $this->body->append_child($widget->fetch()); }}// ... etc. etc.
　　There’s a whole lot more than that, some of which may be regarded as unnecessary, such as a widget class for creating heading tags; <h* />. These classes are wrapping the DOM extension (which needs to be installed to use this code by the way) which already has all we need for creating (X)HTML tags, creating a class for every possible tag is unnecessary.
　　The idea is simply to provide a simplified API which reduces the amount of code we need, makes it easier to understand in terms of HTML rendering and encourages re-use of widgets.
　　Here’s a section of the LogView classes using widgets;
<?php /** * Binds log data to HTML content */class LogView { /** * Private * $model an instance of the LogModel class */ var $model; /** * Private * $output contains instance of DOM page object */ var $output; //! A constructor. /** * Constucts a new LogView object * @param $model an instance of the LogModel class */ function LogView (&$model) { $this->model=& $model; } //! A manipulator /** * Builds the top of an HTML page * @return void */ function create () { $this->output=new PageWidget(); $this->output->addTitle('IP Log Files'); $this->output->cssLink('css/style.css'); $link=new LinkWidget('object'); $link->addLink(　　from url: http://www.phppatterns.com/docs/design/data_access_object_pattern_more_widgets?s=dao
　　
Tired of writing the same SQL statements over and over again? The Data Access Object pattern provides a useful way to abstract data fetching operations. In this article we’ll implement a simple Dao, adding a layer of abstraction to further seperate our application logic from the underlying database. We’ll also see more DOM generated widgets in action...
　　1)

The Need for DAO
　　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.

DAO Positioning
　　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;

DAO in Action
　　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.

Updating the Data Access Class
　　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;

<?php /** * 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.

The Way of the Dao
　　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;

<?php /** * 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.

<?php /** * 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)

Designing Daos
　　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.

Adding an MVC pattern
　　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;

<?php /** * 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;

<?php /** * 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']); }}// ...?>

Widgets in Action
　　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;

<?php /* DOM Widget LibrarySample 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',$class); } //! An accessor /** * Adds a style="" attribute to the current widget * @return void */ function addStyle($style) { $this->widget->set_attribute('style',$style); } //! An accessor /** * Returns either XML as a string or a DOM object * @return mixed */ function &fetch () { $this->finalizeWidget(); if ( $this->out=='string') { return $this->dom->dump_node ($this->widget); } else { return $this->widget; } }}class PageWidget extends Widget { /** * Private * $head XML object for */ var $head; /** * Private * $body XML object for */ var $body; //! A constructor /** * Constucts a new PageWidget object building head and body * @param $out switch between XML as string or DOM object */ function PageWidget($out='string') { Widget::Widget($out); $this->createWidget(); } //! A manipulator /** * Page widget creation method * @return void */ function createWidget () { $this->widget=$this->dom->create_element('html'); $this->head=$this->dom->create_element('head'); $this->body=$this->dom->create_element('body'); } //! A manipulator /** * Page widget finalization method * @return void */ function finalizeWidget () { $this->widget->append_child($this->head); $this->widget->append_child($this->body); } //! An accessor /** * Adds a title element * @return void */ function addTitle ($text) { $title=$this->dom->create_element('title'); $text=$this->dom->create_text_node($text); $title->append_child($text); $this->head->append_child($title); } //! An accessor /** * Adds a link tag for CSS files * @return void */ function cssLink ($url) { $cssLink=$this->dom->create_element('link'); $cssLink->set_attribute('href',$url); $cssLink->set_attribute('type','text/css'); $cssLink->set_attribute('rel','stylesheet'); $this->head->append_child($cssLink); } //! An accessor /** * Appends a widget to $this->body * @return void */ function addBody($widget) { $this->body->append_child($widget->fetch()); }}// ... etc. etc.
　　There’s a whole lot more than that, some of which may be regarded as unnecessary, such as a widget class for creating heading tags; <h* />. These classes are wrapping the DOM extension (which needs to be installed to use this code by the way) which already has all we need for creating (X)HTML tags, creating a class for every possible tag is unnecessary.
　　The idea is simply to provide a simplified API which reduces the amount of code we need, makes it easier to understand in terms of HTML rendering and encourages re-use of widgets.
　　Here’s a section of the LogView classes using widgets;

___FCKpd___7
　　Of particular interest is this line;

 $table->addRow(LogTableWidget::getPager($this->page,$numPages));
　　Here the code for rendering the “Google-like” result pager is farmed off to another class;

___FCKpd___9
　　Re-using the above “paging” widget in other views is now easy.
　　Control of appearance is all left to an external CSS file in this example. If we want to allow a designer to control layout of pages we need some kind of simple template system, not unlike ASP.NET, were we plant tags that “bind” to a widget (control) class.
　　One further side effect of our widgets (which is not unique to widgets of course) is by delivering XML compliant HTML, we’re able to use XSL to transform it to other content types, such as WML.
　　The rest is up to the code to explain...

Further Reading
　　PHP Architect Jan 2003 Edition - has an excellent article on Database Persistence in PHP. Codewalkers: A Framework for Persisting Data Relationships - inspired by the PHP Architect article, this tutorial puts some of the ideas into practice. PHPEverywhere: Object Related Mania - the counter view on Object Relational Database Mapping. Write once, persist anywhere - Implement a Data Access Object pattern framework (Java) O/R, JDO, and Database engineering tools - collection of links on Persistance Layers and Object Relational Mapping (Java) The Goag Dao Pattern useful for project management... ;)

1) Dao Sample Code

2) For an PHP class library providing database abstraction of this kind (and generally as a excellent design example) try Eclipse

3) For PHP class library which encourages use of Data Access Objects, try eXtremePHP - documentation includes a tutorial of building Daos

SERVER['PHP_SELF']); $link->addText('Start Over'); $this->output->addBody($link); } //! An manipulator /** * Abstract method with completes the page * @return void */ function finalize () { // Empty } //! An accessor /** * Returns the page * @return void */ function display () { $this->finalize(); return $this->output->fetch(); }}// ...class LogTableView extends LogView { /** * Private * $page the page we're viewing */ var $page; //! A constructor. /** * Constucts a new LogView object * @param $model an instance of the LogModel class */ function LogTableView (&$model,$page=1) { LogView::LogView($model); $this->page=$page; $this->create(); } //! A manipulator /** * Renders a log table * @return void */ function logTable() { $this->model->listLogs($this->page); $heading=new HeadingWidget('object',2); $heading->addText('Paged Log Result Set'); $heading->addAttribute('align','center'); $this->output->addBody($heading); // Build result table $table=new TableWidget('object'); $table->addAttribute('align','center'); $table->addAttribute('width','750'); // Build result pager $numPages=$this->model->getNumPages(); $table->addRow(LogTableWidget::getPager($this->page,$numPages)); // Build table rows $table->addRow(LogTableWidget::getHeader()); while ( $log = $this->model->getLog() ) { if ( $alt== '#f6f7f8' ) $alt='#ffffff'; else $alt='#f6f7f8'; $table->addRow(LogTableWidget::getRow($log,$alt)); } $this->output->addBody($table); } //! An manipulator /** * Runs the logItem method * @return void */ function finalize () { $this->logTable(); }}// ...?>　　Of particular interest is this line;

___FCKpd___8
　　Here the code for rendering the “Google-like” result pager is farmed off to another class;

___FCKpd___9
　　Re-using the above “paging” widget in other views is now easy.
　　Control of appearance is all left to an external CSS file in this example. If we want to allow a designer to control layout of pages we need some kind of simple template system, not unlike ASP.NET, were we plant tags that “bind” to a widget (control) class.
　　One further side effect of our widgets (which is not unique to widgets of course) is by delivering XML compliant HTML, we’re able to use XSL to transform it to other content types, such as WML.
　　The rest is up to the code to explain...

Further Reading
　　PHP Architect Jan 2003 Edition - has an excellent article on Database Persistence in PHP. Codewalkers: A Framework for Persisting Data Relationships - inspired by the PHP Architect article, this tutorial puts some of the ideas into practice. PHPEverywhere: Object Related Mania - the counter view on Object Relational Database Mapping. Write once, persist anywhere - Implement a Data Access Object pattern framework (Java) O/R, JDO, and Database engineering tools - collection of links on Persistance Layers and Object Relational Mapping (Java) The Goag Dao Pattern useful for project management... ;)

1) Dao Sample Code

2) For an PHP class library providing database abstraction of this kind (and generally as a excellent design example) try Eclipse

3) For PHP class library which encourages use of Data Access Objects, try eXtremePHP - documentation includes a tutorial of building Daos

SERVER['PHP_SELF'].'?page='.$numPages); $link->addText('>>'); $cell->addWidget($link); } $row->addCell($cell); return $row; }// ...?>　　Re-using the above “paging” widget in other views is now easy.
　　Control of appearance is all left to an external CSS file in this example. If we want to allow a designer to control layout of pages we need some kind of simple template system, not unlike ASP.NET, were we plant tags that “bind” to a widget (control) class.
　　One further side effect of our widgets (which is not unique to widgets of course) is by delivering XML compliant HTML, we’re able to use XSL to transform it to other content types, such as WML.
　　The rest is up to the code to explain...

Further Reading
　　PHP Architect Jan 2003 Edition - has an excellent article on Database Persistence in PHP. Codewalkers: A Framework for Persisting Data Relationships - inspired by the PHP Architect article, this tutorial puts some of the ideas into practice. PHPEverywhere: Object Related Mania - the counter view on Object Relational Database Mapping. Write once, persist anywhere - Implement a Data Access Object pattern framework (Java) O/R, JDO, and Database engineering tools - collection of links on Persistance Layers and Object Relational Mapping (Java) The Goag Dao Pattern useful for project management... ;)

1) Dao Sample Code

2) For an PHP class library providing database abstraction of this kind (and generally as a excellent design example) try Eclipse

3) For PHP class library which encourages use of Data Access Objects, try eXtremePHP - documentation includes a tutorial of building Daos

SERVER['PHP_SELF']); $link->addText('Start Over'); $this->output->addBody($link); } //! An manipulator /** * Abstract method with completes the page * @return void */ function finalize () { // Empty } //! An accessor /** * Returns the page * @return void */ function display () { $this->finalize(); return $this->output->fetch(); }}// ...class LogTableView extends LogView { /** * Private * $page the page we're viewing */ var $page; //! A constructor. /** * Constucts a new LogView object * @param $model an instance of the LogModel class */ function LogTableView (&$model,$page=1) { LogView::LogView($model); $this->page=$page; $this->create(); } //! A manipulator /** * Renders a log table * @return void */ function logTable() { $this->model->listLogs($this->page); $heading=new HeadingWidget('object',2); $heading->addText('Paged Log Result Set'); $heading->addAttribute('align','center'); $this->output->addBody($heading); // Build result table $table=new TableWidget('object'); $table->addAttribute('align','center'); $table->addAttribute('width','750'); // Build result pager $numPages=$this->model->getNumPages(); $table->addRow(LogTableWidget::getPager($this->page,$numPages)); // Build table rows $table->addRow(LogTableWidget::getHeader()); while ( $log = $this->model->getLog() ) { if ( $alt== '#f6f7f8' ) $alt='#ffffff'; else $alt='#f6f7f8'; $table->addRow(LogTableWidget::getRow($log,$alt)); } $this->output->addBody($table); } //! An manipulator /** * Runs the logItem method * @return void */ function finalize () { $this->logTable(); }}// ...?>　　Of particular interest is this line;

___FCKpd___8
　　Here the code for rendering the “Google-like” result pager is farmed off to another class;

___FCKpd___9
　　Re-using the above “paging” widget in other views is now easy.
　　Control of appearance is all left to an external CSS file in this example. If we want to allow a designer to control layout of pages we need some kind of simple template system, not unlike ASP.NET, were we plant tags that “bind” to a widget (control) class.
　　One further side effect of our widgets (which is not unique to widgets of course) is by delivering XML compliant HTML, we’re able to use XSL to transform it to other content types, such as WML.
　　The rest is up to the code to explain...

Further Reading
　　PHP Architect Jan 2003 Edition - has an excellent article on Database Persistence in PHP. Codewalkers: A Framework for Persisting Data Relationships - inspired by the PHP Architect article, this tutorial puts some of the ideas into practice. PHPEverywhere: Object Related Mania - the counter view on Object Relational Database Mapping. Write once, persist anywhere - Implement a Data Access Object pattern framework (Java) O/R, JDO, and Database engineering tools - collection of links on Persistance Layers and Object Relational Mapping (Java) The Goag Dao Pattern useful for project management... ;)

1) Dao Sample Code

2) For an PHP class library providing database abstraction of this kind (and generally as a excellent design example) try Eclipse

3) For PHP class library which encourages use of Data Access Objects, try eXtremePHP - documentation includes a tutorial of building Daos

账号		自动登录	找回密码
密码			立即注册

Centos6.5×64安装配置openmeetings3.0.3详

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

[经验分享] [介绍]PHP设计模式：DAO（数据访问对象模式）

浏览过的版块

扫码加入运维网微信交流群