Introduction

Introduction – What DB_DataObject can do

Introduction

Zend Optimizer: due to a bug in the optimizer, you will have to either reduce the optimization level, or define the constant DB_DATAOBJECT_NO_OVERLOAD = 0 otherwise PHP may segfault

Pass by Reference, due to a unfixable bug in PHP4, you can not use overload with pass-by-reference arguments (It works OK in PHP5), If you need pass-by-reference, define the constant DB_DATAOBJECT_NO_OVERLOAD = 0

DB_DataObject is a SQL Builder and Data Modeling Layer built on top of PEAR::DB. Its main purpose is to

Build SQL and execute statements based on the objects variables.
Group source code around the data that they relate to.
Provide a simple consistent API to access and manipulate that data.

So what does that mean in English? Well, if you look around at some of the better written PHP applications and frameworks out there, you will notice a common approach to using classes to wrap access to database tables or groups. The prime example of this is the person object, which would frequently look something like this.

A Classic Data Object or Container


<?php
require_once 'DB.php';
$db = DB::connect('mysql://someuser:somepass@localhost/pear_dbdo');
$db->setFetchMode(DB_FETCHMODE_ASSOC);

class MyPerson
{

    // gets an array of data about the seleted person
    function getPerson($id)
    {
        global $db;
        $result = $db->query('SELECT * FROM person WHERE id=' . $db->quote($id));
        return $result->fetchRow();
    }

    // example of checking a password.
    function checkPassword($username, $password)
    {
        global $db;

        $hashed = md5($password);
        $result = $db->query(
            'SELECT name FROM person WHERE name=' . $db->quote($username)
            . ' AND password = ' . $db->quote($hashed)
        );
        return $result->fetchRow();
    }

}

// get the persons details..
$array = MyPerson::getPerson(12);
echo $array['name'] . "\n";
?>

The examples operate on the following SQL database table:

CREATE TABLE IF NOT EXISTS `person` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL,
  `password` varchar(32) NOT NULL,
  `birthDate` date NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;

INSERT INTO `person` (`id`, `name`, `password`, `birthDate`) VALUES
(12, 'John', '098f6bcd4621d373cade4e832627b4f6', '1984-02-23');

The key benefit of this approach is that you are grouping similar actions on a single table in one place, and are more likely to spot duplicated code (eg. two methods that do similar things). You will also notice the global $db variable used here - the fact is that most of the time you will use a common database connection for all your classes, so how should this be dealt with?

The next step on this road is to use the objects variables as a storage mechanism.

A Classic Data Object or Container


<?php
require_once 'DB.php';
$db = DB::connect('mysql://someuser:somepass@localhost/pear_dbdo');
$db->setFetchMode(DB_FETCHMODE_ASSOC);

class MyPerson
{
    var $id;
    var $name;
    var $birthDate;

    // gets an array of data about the seleted person
    function get($id) {
        global $db;
        $result = $db->query('SELECT * FROM person WHERE id=' . $db->quote($id));
        $array = $result->fetchRow();
        foreach ($array as $key => $value) {
            $this->$key = $value;
        }
    }

    function getAge() {
        return date('Y') - date('Y', strtotime($this->birthDate));
    }

}
// now get the person and display the age.
$person = new MyPerson();
$person->get(12);
echo "{$person->name} is ". $person->getAge() . " years old\n";
?>

As you can see, the current row of data is now stored in the Data Container, and additional methods can be added to manipulate the data in the object or even call other related objects (eg. tables relationships in databases)

As a next step, why not utilize the member variables to perform searches or gets on the database.

A Classic Data Object or Container


<?php
require_once 'DB.php';
$db = DB::connect('mysql://someuser:somepass@localhost/pear_dbdo');
$db->setFetchMode(DB_FETCHMODE_ASSOC);

class MyPerson
{
    var $id;
    var $name;
    var $birthDate;

    // does the query based on the value of $this->name
    function find() {
        global $db;
        $this->result = $db->query('SELECT * FROM person WHERE name=' . $db->quote($this->name));
    }

    // fetches a row of data and sets the object variables to match it.
    function fetch() {
        $array = $this->result->fetchRow();
        if (empty($array)) {
            return false;
        }
        foreach ($array as $key=>$value) {
            $this->$key = $value;
        }
        return true;
    }

}

// now get the person and display the age.
$person = new MyPerson();
$person->name = "John";
$person->find();
while ($person->fetch()) {
    echo "a {$person->name} has a birthday on {$person->birthDate}<br/>\n";
}
?>

As you can see, by assigning values to the object before the find method is called, you can set conditions for the query. DB_DataObjects behaves in a similar way to this, however, you can also add more conditions with the whereAdd() method, or restrict the selection using the selectAdd() method.

Obviously you can carry on down this road and create lots of little containers for each table in your database, and all the code will nicely relate to each table. However, you can see from the examples above that all classes are likely to end up with a common set of methods.

to fetch data, like get, find, fetch
to update, insert and delete from the data store
to automate fetching related objects

So to improve on this, DB_DataObject was born, it started life as a common answer to the issues above, however as most problems grow in complexity as the problem is examined in finer detail, so has DataObjects. It has grown to include

A common simple configuration method (for setting database connections)
A fast and simple store for database descriptions, so primary keys can be used to locate data quickly
a debugger that enables you to see what exactly it is doing.
basic data validation - so the strings and integers can be checked.
Posibility to build complex joins or get related data by secondary calls (links) .
Ability to create and update your Table specific classes with the current database table variables (see autobuilding)
Simple to integrate with other packages, with setFrom() and toArray() methods

So what do my classes look like?

At last some real DataObject Code..


<?php
// this is the common configuration code - place in a general site wide include file.

    // this  the code used to load and store DataObjects Configuration.
    $options = &PEAR::getStaticProperty('DB_DataObject','options');

    // the simple examples use parse_ini_file, which is fast and efficient.
    // however you could as easily use wddx, xml or your own configuration array.
    $config = parse_ini_file('example.ini',TRUE);

    // because PEAR::getstaticProperty was called with an & (get by reference)
    // this will actually set the variable inside that method (a quasi static variable)
    $options = $config['DB_DataObject'];




     // this is normally contained in your DataObjects file (autogenerated by the generator)

    require_once 'DB/DataObject.php';


    // by extending the base class DB_DataObject - you inherit all the common methods defined in it.

    class DataObjects_Person extends  DB_DataObject {

        var $id; // this is a primary id (it's specified in a config file - explained later)
        var $name;
        var $friend;

        // this is a simple function to get the persons friends..?

        function getFriends()
        {

            $personObject = $this->factory('person');

            // look for all people with their friend number matching this persons id.
            $personObject->friend = $this->id;

            // do the select query.
            $personObject->find();
            $array = array();

            // fetch the results into the object.
            while ($personObject->fetch()) {
                // use the clone to copy - not really needed but get used to it for PHP5

                $array[] = clone($personObject);
            }
            // return the results.
            return $array;

        }
    }



    // and this goes on your display code

    // create a new person class..
    $person = DB_DataObject::Factory('person');

    // get the person using the primary key.
    $person->get(12);
    // get the friends.
    $friends = $person->getFriends();

    //  DB_DataObjects is designed to make print_r useable to debug your applications.
    print_r($friends);
?>

The above example illustrates the components of the DB_DataObject, by setting the options, all the core objects will be able to auto load the data definitions from a generated ini file, and know how to access the database. (multiple databases are supported - see section on configuration)

The class definition illustrates how you only need to define the data specific code in your class, ignoring all the common methods, along with showing one of the methods for retrieveing multiple rows of data.

The later half illustrates how to query and get the result for a single row. The $person->get() would connect to the database, perform the query, fetch the result and assign the objects variables to the data returned from the query.

In the above example, this query would be performed.

SELECT * FROM person WHERE id=12;
SELECT * FROM person WHERE friend=12;

To make a change to the Database you would just change the value of the objects variables and call the update method.


<?php
$person = DB_DataObject::factory('person');
        $person->get(12);
        $person->name = 'Fred';
        $person->update();
?>

As a general rule of thumb method names are usually the same as the SQL statement they relate to.

Introduction

Introduction

User Notes: