PHP Data Objects

DataObject is a design pattern that incapsualtes the database with a simple to use interface. This alows the Controller to interact with the database layer in an SQL’less way. To acomplish this you build a base DataObject abstract class that implements the base functionality and sets up the interface for a Data Object. Then for each of your ‘Business Objects’ (such as customer), you extend the DataObject class and implement the various methods provided by DataObject. (see attached code for details).

You’ll in the posted PHP files below that the code gets more ‘complex’ as we move down the stack (Controller->BusinessObject->DataObject). This is the beauty of OOP since we have implemented the DataObject only once in our application (it may be used by many Business Objects). As long as we maintain its interface, we are free to refactore its implementation details and add functionallity. And there is only one file to change.

Retrival of object from the database

In the simple case of the controller needing to retrieve a customer. Traditionally this might involve building a query of something like…

$query = “Select * from customer where customer_id = 1″ $result = $db->execute($query); $customer = $result->fetch();

This process involes the Controller having far too much knowelge of the Database layer. This tight coupling should be avoided.

The same process using a database object would go something like this…

$customer = new CustomerDO($db_handle); $customer->get(1);

Search the database for an Object

This is quite elegant with DO’s. Just simply create a new DO, set what you do know about what you are seaching for then execute the object find method.

$customerDO = new CustomerDO($db); $customerDO->last_name = “smith”; $customers = $customerDO->find();

Saving and Updating Objects

Again this is very simple also. For a populated customer DO object, just issue its insert method and to update, use the update method.

$customer->insert(); $customer->first_name = “Sir Charles”; $customer->update();

Here is a Proof of Concept Controller PHP script

/** * This is just a Proof Of Concept, this is not Production code. * * This is the controller script, that in a very simple * way allows you to Insert/update and delete customers * using the Data Object Class. */ require(“../includes/bootstrap.php”); require_once(‘CustomerDO.php’); $feedback = “”; // collect the id of the image to edit $customer_id = isset($_GET['customer_id']) ? hd($_GET['customer_id']) : null; /** * Create the CustomerDO object. The DB connection * is an argument of the constructor (Dependency Injection) */ $customer = new CustomerDO($db); /** * If there is a customer id provided, retrieve it from the DB */ if ($customer_id) { $customer->get($customer_id); } if (isset($_GET['submit'])) { $customer->first_name = hd($_GET['first_name']); $customer->last_name = hd($_GET['last_name']); if($customer->customer_id){ $customer->update(); $feedback = “Update Complete”; } else { $customer->insert(); $feedback = “Creation Complete”; } } else if(isset($_GET['delete']) && $customer->customer_id) { $customer->delete(); $feedback = “Delete Complete”; } // Set values for the template $first_name = $customer->first_name; $last_name = $customer->last_name; $customer_id = $customer->customer_id; // Just using simple include rather than template engine(i.e. Smarty) include(“customer/index.tpl”);

Here is the Customer Data Object that extends DataObject

require_once(‘DataObject.php’); /** * This is the Business Object class for Customer. * It extends the DataObject class and implemets * the needed interface methods of DataObject * */ class CustomerDO extends DataObject { /** * Specific C.R.U.D. queries for Customer * */ const QUERY_GET = “ SELECT * FROM customer WHERE customer_id = ?”; const QUERY_INSERT = “ INSERT INTO customer SET `customer_id` = ?, `first_name` = ?, `last_name` = ?, `create_date` = NOW(), `modified_date` = NOW()”; const QUERY_UPDATE = “ UPDATE customer SET `first_name` = ?, `last_name` = ?, `modified_date` = NOW() WHERE customer_id = ?”; const QUERY_DELETE = “ DELETE FROM customer WHERE customer_id = ?”; const QUERY_FIND = “ SELECT * FROM customer”; /** * Allow for an optional Database handle param. This * Inversion Of Control allows for ease of unit testing. i.e. you * can supplay a mock database object * * @param Object $db DatabaseHandle */ public function __construct($db=null) { $this->init(); $this->db = $db; } /** * Set up the initial values for this objects * attributes. * */ private function init() { $this->attributes = array( ‘customer_id’ => ”, ‘first_name’ => ”, ‘last_name’ => ” ); } /** * Retrieve a specific object by its primary key * * @param mixed $data_object_id */ public function get($data_object_id) { parent::get($data_object_id, self::QUERY_GET); } /** * Persist a new object to the DB * */ public function insert() { $this->customer_id = $this->db->nextId(parent::DB_SEQUENCE_TABLE); $params = array( $this->customer_id, $this->first_name, $this->last_name ); $result = $this->db->query(self::QUERY_INSERT ,$params); db_check_error($result); } /** * Update an existing Object in the DB * */ public function update() { $params = array( $this->first_name, $this->last_name, $this->customer_id ); $result = $this->db->query(self::QUERY_UPDATE ,$params); db_check_error($result); } /** * Permanently remove an object from persistence * */ public function delete() { $params = array($this->customer_id); $row = $this->db->query(self::QUERY_DELETE ,$params); db_check_error($row); } /** * Query for all records matching the state of the current * Object. * * @param boolean $as_detached_recordset * @param string $order_by_field “{fieldname} {ASC | DESC}” * @param string $group_by_fields “{fieldname1, fieldname2…}” * @return ReadOnlyResultSet or array if $as_detached_recordset = true */ public function find($as_detached_recordset=false, $order_by_field=false, $group_by_fields=false, $return_structure=false) { return parent::find(self::QUERY_FIND, $as_detached_recordset, $order_by_field, $group_by_fields,$return_structure); } /** * Utility function to retrieve the number of objects in persistance * matching a given ‘find’ * * @return unknown */ public function count() { return count($this->find(true)); } }

And here is the Base Data Object Class

/** * This is the base DataObject Class. * * @package default * @author Sam **/ abstract class DataObject { protected $attributes; protected $db; // for debugging use public $sql; protected $attrib_to_dbfield_mapping = array(); /** * Default get implementation * * @param unknown_type $newsletter_id */ protected function get($do_id, $get_query) { $params = array($do_id); $row = $this->db->getRow($get_query ,$params,DB_FETCHMODE_ASSOC); db_check_error($row); if ($row) { foreach ($this->attributes as $class_attribute => $value) { if (key_exists($class_attribute,$row)) { $this->$class_attribute = $row[$class_attribute]; } } } } protected abstract function insert(); protected abstract function update(); protected abstract function delete(); protected abstract function count(); const DB_SEQUENCE_TABLE = ’sequence_ids’; /** * @todo implement OR, right now only AND is supported… */ const CONJUNCTION_AND = ‘AND’; const CONJUNCTION_OR = ‘OR’; public function __get ($attribute) { if (! key_exists($attribute,$this->attributes)) { throw new Exception(“Attribute: [$attribute], not defined for “.__CLASS__); } return $this->attributes[$attribute]; } public function __set ($attribute, $value) { if (! key_exists($attribute,$this->attributes)) { throw new Exception(“Attribute: [$attribute], not defined for “.__CLASS__); } $this->attributes[$attribute] = $value; } public function getAttributeList() { return array_keys($this->attributes); } /** * Used to build clauses for sql queries. * Assumptions: * – All date values are supplied as UNIX timestamps * * @param string $field_name * @param string $clause The expected format of the $clause param is * – ‘{ <= | <|>= |> } {value}’ * – i.e. “>= 17645329″ * – i.e. “!= Y” * – i.e. “Y” // = (equals) is inferred by default * a space is required to seperate the boolean clause and * the value. If no boolean clause is given, ‘=’ is * assumed. */ protected function buildWhereClause($field_name, $clause) { $clause_boolean = ‘=’; //default is equals ‘=’ $clause_righthand_value = $clause; $hack_booleans = array(‘<=','>=’,’<>‘,’=',’!=’,’<','>‘); foreach ($hack_booleans as $boolean) { if (strpos($clause,$boolean)===0) { $clause_boolean = substr($clause,0,strlen($boolean)); $clause_righthand_value = trim(substr($clause,strlen($boolean)+1)); break; } } } /** * check date special case (righthand value should be a timestamp) * – be sure to use the attribute name if it exists (rather than the db field name). That way we can * rely on the ‘_date’ naming convention. */ $date_attribute_name = array_search($field_name,$this->attrib_to_dbfield_mapping) ? array_search($field_name,$this->attrib_to_dbfield_mapping) : $field_name; if (strstr($date_attribute_name,’_date’)) { if (! is_numeric($clause_righthand_value)) { throw new Exception(“The righthand value for [{$field_name}]: not in correct format. Should be {boolean} {unix timestamp}. (“ .”i.e. usage: ‘expire_date’ => ‘>= ‘.time())”); } $where = “`{$field_name}` {$clause_boolean} FROM_UNIXTIME(‘” . mysql_escape_string($clause_righthand_value) . “‘)”; // Not date, so put clause together normally } else { $where = “`{$field_name}` {$clause_boolean} ‘” . mysql_escape_string($clause_righthand_value) . “‘”; } return $where; } /** * Maps DO attribute names to DB field names. If a mapping for * the given attribute does not exist, the attribute name is simply * returned * * @param string $attribute_name * @return string */ protected function mappedAttributeName($attribute_name) { return isset($this->attrib_to_dbfield_mapping[$attribute_name]) ? $this->attrib_to_dbfield_mapping[$attribute_name] : $attribute_name; } /** * Query for all records matching the state of the current * Object. * * @param strinh $find_query The sql query (w/out the where clause) * @param boolean $as_detached_recordset * @param string $order_by_field “{fieldname} {ASC | DESC}” * @param string $group_by_fields “{fieldname1, fieldname2…}” * @return ReadOnlyResultSet or array if $as_detached_recordset = true */ public function find($find_query, $as_detached_recordset=false, $order_by_field=false, $group_by_fields=false, $return_structure=false, $limit_terms=false) { if (! $as_detached_recordset && $return_structure) { throw new Exception(‘Usage: If you requests a $return_structure, you must specify $as_detached_recordset=true’); } $where_clauses = array(); // Loop through the properties build individual where clauses foreach ($this->attributes as $attrib_key=>$attrib_values) { /* * attribute values can have multi value so force to array & * deal only with the array case. * This allows you to define something like * WHERE * username != ‘Anonymous’ AND * username != ” */ $attrib_values = is_array($attrib_values) ? $attrib_values : array($attrib_values); foreach ($attrib_values as $attrib_value) { if (trim($attrib_value)!=”) { $where_clauses[] = $this->buildWhereClause($this->mappedAttributeName($attrib_key), $attrib_value); } } } // If we have a where clause, build it $this->sql = $find_query; if (count($where_clauses)> 0){ $this->sql .= ” WHERE ” . implode(‘ ‘.DataObject::CONJUNCTION_AND.’ ‘, $where_clauses); } if ($group_by_fields) { $group_by_fields = explode(‘,’,$group_by_fields); $group_by_fields = array_map(‘trim’,$group_by_fields); $this->sql .= ” GROUP BY `”.implode(“`, `”,$group_by_fields).”`”; } if ($order_by_field) { $order_by_field = explode(‘,’,$order_by_field); foreach ($order_by_field as &$order_by) { $direction = stristr(‘DESC’,$order_by) ? “DESC” : “ASC”; $order_by = str_ireplace(array(“DESC”,”ASC”),”,$order_by); $order_by = “`”.trim($order_by).”`”.” {$direction} “; } $this->sql .= ” ORDER BY “.implode(“`, `”,$order_by_field); } if ($limit_terms) { $this->sql .= ” LIMIT “.$limit_terms; } $results = null; if ($as_detached_recordset) { $results = $this->db->getAll($this->sql,array(),DB_FETCHMODE_ASSOC); db_check_error($results); if($return_structure) { $results = $this->mapReturnStructure($results,$return_structure); } return $results; } else { $result_set = $this->db->query($this->sql); db_check_error($result_set); require_once(‘ReadOnlyResultSet.php’); return new ReadOnlyResultSet($result_set); } } /** * Takes the indexed array result of a query and re-structures it to * the supplied assocc array. * – ie * <code> * if $result_data is * [0]=>array('name'=>'foo','age'=>10, 'height'=>6.5, 'weight'=>100), * [1]=>array('name'=>'bar','age'=>13, 'height'=>6.5, 'weight'=>120) * and $return_structure is * 'name'=>array('age','weight') * then the final returned array would be... * 'foo'=>array('age'=>10,'weight'=>100), * 'bar'=>array('age'=>13,'weight'=>120) * * if there is only 1 array value in the return structure.. * i.e. 'name'=>'age' (or 'name'=>array('age') * then the final returned array would be...(no keys on value) * 'foo'=>10, * 'bar'=>13 * </code> * * @param array $result_data * @param array $return_structure */ private function mapReturnStructure($result_data, $return_structure) { $result = array(); foreach ($result_data as $datum) { $key = key($return_structure); $struct_values = is_array($return_structure[$key]) ? $return_structure[$key] : array($return_structure[$key]); foreach ($datum as $datum_key => $datum_value) { if (in_array($datum_key,$struct_values)) { if (count($struct_values)>1) { $result[$datum[$key]][$datum_key] = $datum_value; } else { $result[$datum[$key]] = $datum_value; } } } } return $result; } }