Home » Database » DB_DataObject » Manual

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.

Configuration Options

Configuration Options – Setting the defaults for database access

Configuration

DB_DataObject needs to be configured before using it and auto generating classes and definitions. The easiest way to configure DB_DataObject is to use ini files (although you may also like to consider the PEAR::Config class, or your own configuration system)

This is a typical configuration file for DB_DataObject

[DB_DataObject]

database          = mysql://user:password@localhost/vending
schema_location   = /home/me/Projects/myapplication/DataObjects
class_location    = /home/me/Projects/myapplication/DataObjects
require_prefix    = DataObjects/
class_prefix      = DataObjects_
db_driver         = MDB2 #Use this if you wish to use MDB2 as the driver
quote_identifiers = 1

To use this ini file with DB_DataObject, (and possibly any other classes that use options like this)

Setting the default options

<?php
$config = parse_ini_file('example.ini',TRUE);
foreach($config as $class=>$values) {
    $options = &PEAR::getStaticProperty($class,'options');
    $options = $values;
}


// or you can do without an ini file, and configure it in PHP..

$options = &PEAR::getStaticProperty('DB_DataObject','options');
$options = array(
    'database'          => 'mysql://user:password@localhost/vending',
    'schema_location'   => '/home/me/Projects/myapplication/DataObjects',
    'class_location'    => '/home/me/Projects/myapplication/DataObjects',
    'require_prefix'    => 'DataObjects/',
    'class_prefix'      => 'DataObjects_',
    'db_driver'         => 'MDB2', //Use this if you wish to use MDB2 as the driver
    'quote_identifiers' => true
);
?>

Configuration Options - Required

database DSN

This is the default data source name (DSN) to connect to your database. See the DSN configuration page for DB or the DSN configuration page for MDB2 for more details.

schema_location directory

The directory where the DB_DataObject database schema file is store.

DB_DataObject stores the description of the database (Tables and Columns) in an .ini file, in this directory. This information is used to determine if the column is a string and needs quotes, or should be a number (and is checked) at SQL building time. It is quite common to store the schema in the same directory as your DataObject Classes.

require_prefix directory

The Path absolute, or relative to your default include path(s), where your extended classes can be found.

This is used by the staticGet() method and the getLinks() method to auto load classes,

class_prefix string

All the generated Classes are named {class_prefix}ucfirst($table_name). Use this to alter the prefixed name, this is used by the staticGet() and getLinks() methods

Configuration Options - Optional

db_driver string

default = DB, Use this configuration option to set the database abstraction driver used by DB_DataObject.

using MDB2 as the driver

db_driver = MDB2

sequence_{table} string

To Hard code the key (autoincrement/nextval() for a table to a specific key, overriding anything in the keys definition of the file. Normally used on databases that are not able to be queried correctly for their structure

using login as the key for the person table

sequence_person = login

ignore_sequence_keys string

If you do not want to use pear's nextval(), for automatically filling in sequences, this can disable it for "ALL", or a list of tables "person,cart,group"

debug integer

The default debugging level (default 0=off), 1= basic sql logging,2=result logging, 3=everything

debug_ignore_updates boolean

default FALSE, if set, then updates on the database are disabled.

dont_die boolean

default FALSE, The standard behaviour of dataobjects is to issue a PEAR_ERROR_DIE (eg. exiting PHP), when a fatal error occurs, like database connection failure or sending an invalid object type to a method. However if you need to run it on a live server you will probably want to set this to TRUE and define a PEAR error handler to catch these errors and show a nice friendly 'sorry we are down for maintenence' message page.

quote_identifiers boolean

To force the quotation of identifiers in the SQL statements, set this to 1. This is useful if any table names use hyphens.

Statement Generated with and without quote_identifiers

quote_identifiers = 1;
SELECT 'somecol' FROM 'sometable' WHERE 'somevalue'=1;

quote_identifiers = 0;
SELECT somecol FROM sometable WHERE somevalue=1;

Note: This will not affect data sent to methods such as whereAdd(), orderBy(), and groupBy(), which expect raw data.

proxy string

This enables the building of classes and ini classes on the fly, rather than forcing you to generate the code forhand. (currently the only value supported is "full", which will generate both schema data and default classes when using factory)

Configuration Options - Multiple Databases (optional)

database_* string

When you have multiple databases you can use the database_* to specify the DSN for each database

using multiple databases - database passwords

database_authentication  = mysql://user:password@localhost/authentication
database_sales           = mysql://user:password@localhost/sales

table_* string

When you have multiple databases you can use the table_* configuration variables to map individual tables to different databases, for example

using multiple databases - table settings

table_users     = authentication
table_saleslog  = sales
table_stock     = sales

Configuration Options - Builder

class_location directory

The Directory where your DataObject extended Classes are.

Used by the Class Auto Builder when updating/writing to your class definitions.

extends string

The Name of your Base Class (usually DB_DataObject) is located.

If you wish to add a common layer of useful methods for all classes, you can set the extends_location and extends settings to a different class. the default is 'DB_DataObject'

extends_location directory

The Directory where your Base Class (usually DB_DataObject) is located.

If you wish to add a common layer of useful methods for all classes, you can set the extends_location and extends settings to a different class. the default is 'DB/DataObject.php'

generator_class_rewrite boolean

Normally when you recreate a class from the database, it will only alter the variables, and staticGet, - with this set, it will also update the extends field

build_views boolean

Postgres (and maybe some others), allow you to treat views just like normal tables (eg. insert/update/delete etc. work on them), you can use this option to generate files for all the views in the database.

Note: You will have to specify keys manually in the generated classes (eg. define the methods keys() and sequenceKey(), as the generator can not guess which ones are likely to be the key.

generator_include_regex string

If you only want to generate classes and ini entries for specific tables, you can use this to build a regex, only tables with names matching the regex will be generated, for example /mytables_.*/

generator_exclude_regex string

If you only want to explicitly prevent the generation of classes and ini entries for specific tables, you can use this to build a regex, any tables that match the regex, will not be generated, for example /private_tables_.*/

generator_strip_schema boolean

postgresql has a wierd concept of schemas which end up prefixed to the list of tables. - this makes a mess of class/schema generation setting this to 1, makes the generator strip the schema from the table name

generator_novars boolean

If True, the generator does not write a private or var's definition for the columns so you can overload get/set.

generator_add_validate_stubs boolean

If True, the generator will insert / (or add to existing files) stubs for validate methods.

Auto Building and Database Schema

Auto Building and Database Schema – creating the base Classes and Database schema

What the Auto Builder (createTables.php) does

One of the essential features of an SQL building tool is to to have some understanding of the database structure, So that Integers can be checked, and strings can be escaped. There a few ways that Querying the database for the table structure could be accomplished

• on every SQL query
• At the initialization of every web page.
• Once, while setting up the application, and store it in a file

DB_DataObject uses the last of these normally (see the section below on Alternatives), it utilizes the parse_ini_file function to read the file, so it should be reasonably fast. However this does involve a stage of setting up DB_DataObject prior to use.

The other key concept of DB_DataObject is that you work with extended classes of DB_DataObject, which do all the 'table' related work. Setting up these classes for a large database can be time consuming, so the createTables.php file will automatically build the skeletons for all these class files.

To start the auto builder simply go to the pear/DB/DataObject/ directory, and type c:\php4\php.exe createTables.php myconfig.ini this will read your configuration file and generate all the base classes, along with the data definition file.

As of Version 1.5, you can use the option "proxy = full", which will cause DataObjects to create classes and schema on the fly, rather than using an ini file or prebuilt classes.

Default Class Definition

The default generated class looks like this.

an generated extended class

<?php
/*
* Table Definition for group
*/


class DataObjects_Grp extends DB_DataObject {

    ###START_AUTOCODE
    /* the code below is auto generated do not remove the above tag */

    var $__table='group';                             // table name
    var $id;                              // int primary_key
    var $name;                            // string
    var $grp_owner;                       // int
    var $official;                        // string
    var $street;                          // string
    var $postcode;                        // string
    var $city;                            // string
    var $homepage;                        // string
    var $email;                           // string
    var $extra;                           // blob

    /* Static get */
    function staticGet($k,$v=NULL) { return DB_DataObject::staticGet('DataObjects_Grp',$k,$v); }


    /* the code above is auto generated do not remove the tag below */
    ###END_AUTOCODE
}
?>

The class defines the table name, and comments some of table columns for your reference, It also adds the staticGet() method, which can be used to quickly get single rows as Objects. You should add your table related code after the ###END_AUTOCODE.

Prior to version 1.6 a method __clone was created, since PHP5 changed to use $x = clone($y);, this method has been removed. DataObjects now creates a dummy function clone (if necessary), to enable forward compatibility

Database Schema File

The default generated database definition file looks like this, it is located in the directory set by the "schema_location" configuration option, and is named the same as the database.

If you rename the database, you will either have to regenerate the file or copy it to match the same name.

If you change the database schema (eg. add a column or change the type), you will have to regenerate the schema file, using createTables.php, At present, this is not done automatically (although this may be added in the future..)

You should not edit this file by hand (as any changes will be lost when regenerating it). You can override the keys of a table by using a configuration option "sequence_{table} = key", or by defining the sequenceKey() method in your extended class.

Database configuration file

[group]
id = 129
name = 130 
grp_owner = 129 
official = 130 
street = 130 
postcode = 130
city = 130 
homepage = 130 
email = 130 
extra = 130 

[group__keys]
id = N

The blocks indicate either tables and the type of field with binary addition (1=integer,2=string,128=not null, so 129=integer and not null), or a list of keys for each table. You should not need to edit file.

Alternatives to using a Schema File

It is possible to use DataObjects without a schema file, this can be achieved in 2 ways

• by defining the table() and keys() methods in an extended class
• by passing an array to table() and keys() when you have an instance of dataobjects

The second of this is demonstrated on the keys() and table() page.

Below is a hand coded class which does not use a schema.ini file.

It was designed to be a return value from a method, rather than an object variable, so that the output of print_r(), would not include extra information (and would be smaller when dumping a large result set.

A Hand Coded extended class

<?php
/*
* Table Definition for group
*/


class DataObjects_Grp extends DB_DataObject {

    // you can define these yourself
    
    var $__table='group';                             // table name
    var $id;                              // int primary_key
    var $name;                            // string
    var $bday;                            // string
    var $last;                            // datetime
    var $active;                          // tinyint(1)
    var $desc;                            // text
    var $photo;                           // blob
 
    // these are usefull to be consistant with a autogenerated file.
    
    /* Static get */
    function staticGet($k,$v=NULL) { return DB_DataObject::staticGet('DataObjects_Grp',$k,$v); }

  
    // now define your table structure.
    // key is column name, value is type
    function table() {
        return array(
            'id'     => DB_DATAOBJECT_INT,
            'name'   => DB_DATAOBJECT_STR,
            'bday'   => DB_DATAOBJECT_STR + DB_DATAOBJECT_DATE,
            'last'   => DB_DATAOBJECT_STR + DB_DATAOBJECT_DATE + DB_DATAOBJECT_TIME,
            'active' => DB_DATAOBJECT_INT + DB_DATAOBJECT_BOOL,
            'desc'   => DB_DATAOBJECT_STR + DB_DATAOBJECT_TXT,
            'photo'  => DB_DATAOBJECT_STR + DB_DATAOBJECT_BLOB,
        );
    }
    
    // now define the keys.
    function keys() {
        return array('id');
    }
    
    
    
}
?>

DB_DataObject::factory()

DB_DataObject::factory() – Autoload and instantate class based on table name.

Synopsis

mixed DB_DataObject::factory ( string $table )

Description

This is the recommended way to autoload a class, and instantate it. The class is loaded based on the configuration settings (class_location and class_prefix) for table to class naming.

Parameter

• string $table - the table you want to load ([From Version 1.7.2] if blank, and called on an an instance of a dataobject, it will create a new instance of that object)

Return value

object mixed - DB_DataObject_Error or the object

Throws

Note

This method can be called statically or dynamically.

Example

Simple fetch of data based on Primary Key

<?php
// set up our options
$opts = &PEAR::getStaticProperty('DB_DataObject','options');
$opts = array(
    'class_location'  => '/home/me/Projects/myapplication/DataObjects',
    'class_prefix'    => 'DataObjects_'
);


// loads the file: /home/me/Projects/myapplication/DataObjects/Person.php
// and checks that the class DataObjects_Person exists, and returns an
// instance of it.

$person = DB_DataObject::factory('person');


if ($person->get(12)) {
  print_r($person);
} else {
  echo "NO person 12 exists";
}



// it can also be used in a dynamically
class DataObjects_MyTable {
    
    function anExample() {
        $person = $this->factory('person');
        
        
        
        // supported in version 1.7.2
        $another_mytable = $this->factory();
        
        $another_person = $person->factory();
        
    }
}
?>

->get()

->get() – Simple Get (Select) request

Synopsis

int $DB_DataObject->get ( mixed Key or Value , mixed value )

Description

Get a result using key, value. Returns Number of rows located (usually 1) for success, and puts all the table columns into this classes variables. If only one parameter is used, it is assumed that first parameter is a value and get() will use the primary key.

Parameter

• mixed $key or $value - column (or value if only one parameter)
• mixed $value - value

Return value

int - Number of rows

Throws

Note

This function can not be called statically.

You should avoid calling get on the same object instance twice, as this will result in unexpected results.

Example

Simple fetch of data based on Primary Key

<?php
$person = new DataObjects_Person;
$person->get(12);
print_r($person);
?>

Resulting SQL

Simple fetch of data based on Key and Value

<?php
$person = new DataObjects_Person;
$person->get('email','test@example.com');
print_r($person);
?>

Resulting SQL

Results of example code

<?php
Object (DataObjects_Person) =>
    [N] => 1
    [id] => 12
    [group] => 5
    [has_glasses] => 1
    [name] => 'fred blogs'
    [password] => '**testing'
    [email] => 'test@example.com'
?>

DB_DataObject::staticGet()

DB_DataObject::staticGet() – Simple Get (Select) request, abbreviated and Autoload.

Synopsis

mixed DB_DataObject::staticGet ( string $class , mixed $key or $value , mixed $value )

Description

This method is depreciated, it is recommended to use ::factory() and ->get()

The static method is a combination of factory and get(). staticGet() will cache the returned data in a global variable for quick access within the same request (any data modification query will clear the cache).

Parameter

• string $class - class name
• string $key - column (or value if only 2 parameters are given)
• mixed $value - value

Return value

object mixed - FALSE or the object

Throws

Note

This method must be called statically.

Example

Simple fetch of data based on Primary Key or column and value

<?php
$person = DB_DataObject::staticGet('DataObjects_Person', 12);
print_r($person);

$person = DB_DataObject::staticGet('DataObjects_Person', 'name', 'fred');
print_r($person);
?>

{Child Class}::staticGet()

{Child Class}::staticGet() – Simple Get (Select) request, abbreviated (autogenerated)

Synopsis

object {Child Class}::staticGet ( mixed $key or $value , mixed $value )

Description

The static method is similar to the get request, however it does not require the initial instantiation of the class. staticGet() can optionally cache the results. (see the configuration section)

Note: This can only be used for tables with a single column primary key.

Parameter

• string $key - column or value if only one parameter is given
• mixed $value - value

Return value

object mixed - FALSE or the object

Throws

Note

This method must be called statically.

Example

Simple fetch of data based on Primary Key or column and value

<?php
$person = DataObjects_Person::staticGet(12);
print_r($person);

$person = DataObjects_Person::staticGet('name', 'fred');
print_r($person);
?>

->find()

->find() – find results

Synopsis

int $DB_DataObject->find ( boolean $autoFetch )

Description

The find method builds and executes the current Query, based on the object variables and any WhereAdd() conditions , If the AutoFetch is TRUE, then it will also call the fetch method automatically.

Parameter

• boolean $autoFetch - fetch first result

Return value

int - number of rows found, only if the database backend supports the numRows() method. Otherwise 1 (or in versions after 1.7.13, true will returned)

Note

This function can not be called statically.

Example

Simple find() of data based on Object Vars

<?php
$person = new DataObjects_Person;
$person->hair = 'red';
$person->has_glasses = 1;
$number_of_rows = $person->find();
?>

Resulting SQL

<?php
SELECT * FROM person WHERE hair='red' and has_glasses = 1
?>

->fetch()

->fetch() – fetch next row

Synopsis

boolean $DB_DataObject->fetch ( )

Description

The fetch method gets the next row and sets the objects variables to the rows data. It returns TRUE if data has been collected, and FALSE when there is no more data.

Return value

boolean - TRUE on success and FALSE on failure.

Note

This function can not be called statically.

Fetch is called by staticGet and get calls, so you can override this method in your classes, to add extra data to your object (like formated dates etc)

Example

Simple find and fetch of data based on object Vars

<?php
$person = new DataObjects_Person;

$person->hair = 'red';
$person->has_glasses = 1;

$number_of_rows = $person->find();

$people = array();
while ($person->fetch()) {
    /* store the results in an array */
    $people[] = clone($person);
    echo "GOT {$person->name}<BR>";
}
?>

Overriding fetch to add extra data.

<?php
function fetch() {

  $ret = parent::fetch();
  if ($ret === false) {
      return false;
  }
  $this->dateFormated = date('d/M/Y', $this->date);
  return true;
}
?>

->count()

->count() – Perform a select count() request

Synopsis

int $DB_DataObject->count ( string|boolean $countWhat|$whereAddOnly , boolean $whereAddOnly )

Description

It performs a select count() request on the tables key column and returns the number of resulting rows. The default condition applied to the count() is a combination of the object variables and whereAdd settings. If the constant DB_DATAOBJECT_WHEREADD_ONLY is passed in as the first parameter then only the whereAdd settings will be used.

Parameter

• string $countWhat - by default count will count on the primary key, if you need to count something else, if you just say DISTINCT, it will count the primiary key prefixed with distinct, or put your own value in (don't forget to escape it if necessary)
• boolean $useWhereAddOnly - use only the whereAdd conditions (by default, count will only use both the object settings and the whereAdd conditions)

Return value

int|false - number of results or false on an error

Note

This function can not be called statically.

Example

Various examples of using count()

<?php

/* using property values */
$person = new DataObjects_Person;
$person->name  = "test"
$total = $person->count();
echo "There are {$total} people with a name like test";

/* using countWhat */
$person = new DataObjects_Person;
$total = $person->count('DISTINCT name');
echo "There are {$total} names in the database";

/* using countWhat value = DISTINCT */
$person = new DataObjects_Person;
$total = $person->count('DISTINCT');
echo "There are {$total} names in the database";

/* using whereOnly */
$person = new DataObjects_Person;
$person->name = "test";
$person->whereAdd("name like '%test%'");
$total = $person->count(DB_DATAOBJECT_WHEREADD_ONLY);
echo "There are {$total} names in the database";

?>

Resulting SQL

->insert()

->insert() – Insert current objects variables into database

Synopsis

mixed $DB_DataObject->insert ( )

Description

Insert the data into the database, based on the variable values of the current object and returns the ID of the inserted element if sequences or primary keys are being used. The values are correctly quoted, and some limited type checking is done.

With mysql, the mysql_next_id() method is used, on other databases, PEAR DB sequence method is used.

Note, insert() may not return the ID correctly in quite a few situations:

• If the database backend does not support it.
• The generator did not correctly flag the correct column as autoincrement/nextval
• An error occured (turn on debugging to see it)
• The insert failed or '0' rows where affected.

Return value

mixed - Id or key

Throws

Note

This function can not be called statically.

Example

Simple insert

<?php
$person = new DataObjects_Person;
$person->name='fred';
$id = $person->insert();
?>

Resulting SQL

<?php
INSERT INTO person (name) VALUES ('fred');
?>

->update()

->update() – Update objects variables into database

Synopsis

int $DB_DataObject->update ( dataobject|boolean $original|$useWhere )

Description

Updates current objects variables into the database. if you supply it with a dataObject, as an argument, it will only update the differences between the new and old.

if called with DB_DATAOBJECT_WHEREADD_ONLY as the argument, the update request is built based on the whereAdd values, rather than the primary key. This enables global updates to be performed, rather than single row ones.

Parameter

• DataObject $original - if provided the update query will be built from the difference between the current and original dataobject.

Return value

int number of rows affected or FALSE on failure

Throws

Note

This function can not be called statically.

Example

Simple fetch and update

<?php
$person = new DataObjects_Person;
$person->get(12);
$person->name='fred';
$person->update();     

$person = new DataObjects_Person;
$person->get(12);
$original = clone($person); // clone is emulated in php4 for compatibility reasons. 
$person->name='fred';
$person->update($original);
?>

Resulting SQL

Simple fetch and update

<?php
$person = new DataObjects_Person;
$person->removed=1;
$person->whereAdd('age > 21');
$person->update();
?>

Resulting SQL

->delete()

->delete() – Delete items from table

Synopsis

int $DB_DataObject->delete ( boolean $useWhere )

Description

Deletes data from the database, either using primary key or based on a whereAdd() method call. By default the delete will base its query on the set variables, however if you wish to use the whereAdd() method you should set the $useWhere parameter to DB_DATAOBJECT_WHEREADD_ONLY.

Parameter

• boolean $use_where - use the whereAdd() conditions (by default, delete will only use primary keys)

Return value

int number of rows affected or FALSE on failure

Throws

Note

This function can not be called statically.

Example

Simple Delete

<?php
$person = new DataObjects_Person;
$person->get(12);
$person->delete();

$person       = new DataObjects_Person;
$person->name = 'test';
$person->age  = 21;
$person->delete();

$person = new DataObjects_Person;
$person->whereAdd('age < 21');
$person->delete(DB_DATAOBJECT_WHEREADD_ONLY);
?>

Resulting SQL

<?php
SELECT * FROM person WHERE person.id = 12
DELETE FROM person WHERE ( person.id = 12 )

DELETE FROM person WHERE ( person.name = 'test' ) AND ( person.age = 21 )

DELETE FROM person WHERE ( age < 21 )
?>

Selecting Specific data (SELECT)

Selecting Specific data (SELECT) – Advanced Filters - ::query(), ::SelectAdd(), ::whereAdd(), ::Limit(), ::OrderBy(), ::GroupBy(),

More advanced Querying

The basic features allow most simple queries to be done very quickly, however building more complex queries can be done using the methods listed below, which append or set conditions that build the query. You will find that there is a fine balance between using these builder methods and just using raw SQL.

The DB_DataObject can now handle JOIN queries, however please read the introduction to linking and Joining before jumping in and using the Join feature, as you may be trading the use of a cool feature, against the readibility of your code.

->query()

->query() – send a raw query

Synopsis

void $DB_DataObject->query ( string $string )

Description

Sends a raw query to database. Often you may consider that using a large number of whereAdd's and orderBy method calls are not necessary, and it would be simpler to just write the query, to make the code clearer and shorter.

Parameter

• string $string - SQL Query

Note

This function can not be called statically.

Example

raw queries on the database

<?php
$person = new DataObjects_Person;
$person->query("SELECT * FROM {$person->__table} WHERE id > 12 ORDER BY id");
while ($person->fetch()) {
  echo $person->name;
}
?>

->free()

->free() – Free resources

Synopsis

object $DB_DataObject->free ( )

Description

DataObjects stores result set's as a private global variable, normally this is free'ed after you have run through the results, or at the end of the request. However in some situations, like running queries directly, inserting data, some data is unnecessarly cached.

Using this method will Free All results sets! (so be carefull) it may break running fetch() loops..

You normally only need to use this if you are doing a large number of inserts or queries.

Note

This function can not be called statically.

Example

Freeing resources in a loop

<?php

for ($i = 0; i< 10000; $i++) {
    $person = new DataObjects_Person;
    $person->query(' ... do something ... ');
    $person->free();
}

?>

->selectAdd()

->selectAdd() – Add selected columns

Synopsis

void $DB_DataObject->selectAdd ( string $condition )

Description

Adds a selected columns. By default a select query will request all items (eg. SELECT * FROM table), to change this behavior you can first call selectAdd() without any arguments to clear the current request and then add the specific items you require.

You can also set up a default selection query by adding SelectAdd() method calls in the object constructor method (the one with the same name as the class)

Parameter

• resource $key - table column name

Note

This function can not be called statically.

Example

Using selectAdd()

<?php
$person = new DataObjects_Person;
$person->selectAdd();
$person->selectAdd('id,name');

while ($person->fetch()) {
  echo "{$person->id} {$person->name}<BR>";
}


$person = new DataObjects_Person;
$person->selectAdd("DATE_FORMAT(birthday,'%d %m %Y') as birthday_formated ");
$person->id = 12;
$person->find(TRUE);

echo "{$person->name} {$person->birthday_formated}<BR>";
?>

Resulting SQL

->whereAdd()

->whereAdd() – Add WHERE statement

Synopsis

void $DB_DataObject->whereAdd ( string $where , string $logic )

Description

Adds items to the where part of a SQL query. Calling this without any arguments clears the where condition. The default behavior is to add 'AND' to the existing conditions, use the $logic parameter to append OR conditions.

Parameter

• string $cond - condition to add, or blank to reset the conditions
• string $logic - optional logic "OR" (defaults to "AND")

Note

This function can not be called statically.

The quote_identifiers configuration option will not affect data sent to whereAdd.

See

Example

Using whereAdd()

<?php
$person = new DataObjects_Person;
$person->whereAdd('age > 12');
$person->whereAdd('age < 30');
$person->find();

while ($person->fetch()) {
    echo "{$person->id} {$person->name}<br />";
}
$person = new DataObjects_Person;
$person->whereAdd('age < 12');
$person->whereAdd('age > 30', 'OR');
$person->find();

while ($person->fetch()) {
    echo "{$person->id} {$person->name}<br />";
}
?>

Resulting SQL

->escape()

->escape() – Escape a string for use with Like queries

Synopsis

void $DB_DataObject->escape ( string $value )

Description

Similar to Pear DB's quote it will escape a value, without the quotes, so it can be used with a LIKE query.

Parameter

• string $value - the string you want to escape

Note

This function can not be called statically.

Example

Escaping a LIKE string

<?php
$person = new DataObjects_Person;
$person->whereAdd("name LIKE '%" . $person->escape("o'brian") . "%'");
$person->find();
?>

Sample SQL

->limit()

->limit() – Set limit

Synopsis

void $DB_DataObject->limit ( int $from , int $number )

Description

Sets the limit for a query. (this only works on databases that support the LIMIT clause), without parameters, it will clear the current limit.

Parameter

• int $from - limit start (or number), or blank to reset
• int $number - limit results to number

Note

This function can not be called statically.

Since postgres and mysql only really support limit directly - calling this on an unsupported database will emit a PEAR::Error and die.

Example

Setting the Limit

<?php
$person = new DataObjects_Person;
$person->limit(2);
$person->find();
while ($person->fetch()) {
    echo "{$person->id} {$person->name}<BR>";
}


$person = new DataObjects_Person;
$person->limit(2,4);
$person->find();

while ($person->fetch()) {
    echo "{$person->id} {$person->name}<BR>";
}
?>

Resulting SQL

->orderBy()

->orderBy() – Add an order by condition

Synopsis

void $DB_DataObject->orderBy ( string $order )

Description

Adds a order by condition. Calling this without any arguments clears the current order condition.

Parameter

• string $order - Order

Note

This function can not be called statically.

The quote_identifiers configuration option will not affect data sent to orderBy.

Example

Setting the order by

<?php
$person = new DataObjects_Person;
$person->orderBy('name');
$person->orderBy('age, eye');
$person->find();


// or with direction:
$person = new DataObjects_Person;
$person->orderBy('name ASC');
$person->orderBy('age DESC, eye');
$person->find();


?>

Resulting SQL

->groupBy()

->groupBy() – Add group by condition

Synopsis

void $DB_DataObject->groupBy ( string $group )

Description

Adds a group by condition. Calling this without any arguments clears the Group Condition

Parameter

• string $group - Grouping condition

Note

This function can not be called statically.

The quote_identifiers configuration option will not affect data sent to groupBy.

Example

Setting the Group by

<?php
$person = new DataObjects_Person;
$person->groupBy('name');
$person->groupBy('age, eye');
$person->find();
?>

Resulting SQL

Automatic Table Linking and Joins

Automatic Table Linking and Joins – Automatic Table Linking - ::getLink(), ::getLinks(), ::joinAdd(), ::selectAs()

Automating the collection of related data

When designing a database, often some tables are related to others - a membership table would contain a reference to a person's id and the group id that they are a member of. Using the Link methods, you can automatically fetch objects into the parents variables.

Automated links are supported by a databasename.links.ini file. which stores the relations ship between tables, maping one tables column to anothers. This databasename.links.ini file is used by the getLinks() and joinAdd() Method, to either retrieve related information of a primary object, or quickly build complex multitable queries.

The other way of using linking is via the getLink() method, which you can manually use without a database.links.ini file to specify a column, and how it relates to another table and column.

The goal of getlinks and joinAdd is to make connecting two tables as simple and fast as possible, while still ensuring that the code is reasonably comprehensable. In the example below, It is demostrated how getlinks() can be used to fetch more data about an object after the initial fetch, and it can also be used to test the links file prior to building a full blown join Query.

A simple introduction to links and joins

<?php
// just loop and fetch more information

$person = new DataObjects_Person;
$person->eyeColour = 'red';
$person->find();
while ($person->fetch()) {
    // this will look up in the cars table for the id matching the person->car value.

    $car = $person->getLink('car','cars','id');

    echo "{$person->name} has red eyes and owns a {$car->type}\n";
}





// now if you create a database.links.ini file with the car example
// the example would look like this.

$person = new DataObjects_Person;
$person->eyeColour = 'red';
$person->find();
while ($person->fetch()) {

    // use the links.ini file to automatically load
    // the car object into $person->_car
    $person->getLinks();

    echo "{$person->name} has red eyes and owns a {$person->_car->type}\n";
}






// and finally the most complex, using SQL joins and select as.
// the example would look like this.

$person = new DataObjects_Person;
$person->eyeColour = 'red';

// first, use selectAs as to make the select clauses match the column names.
$person->selectAs();

// now lets create the related element
$car = new DataObjects_Car;

// and for fun.. lets look for red eys and red cars..
$car->colour = 'red';

// add them together.
$person->joinAdd($car);

// since both tables have the column id in them, we need to reformat the query so
// that the car columns have a different name.

$person->selectAs($car,'car_%s');




$person->find();
while ($person->fetch()) {

    echo "{$person->name} has red eyes and owns a {$person->car_type}\n";
}
?>

Using link ini files for table links

DB_DataObject Version 0.3 introduced the ability to create link ini files so you can map column to other database columns using an ini file this ini file should have the name 'databasename.links.ini', and be placed in the same folder as the database schema ini file 'databasename.ini' file that is created automatically by createTables.php

The databasename.links.ini file contains a section for each table, then the column that is linked equal to the table and column that it should locate the column from. It assumes the relationships are non-primary id to primary id, as the example below shows, the person.owner is linked to grp.id. This indicates that running getLinks() on the person object, will fetch a single bit of data from 3 tables - colurs,grp,attachments.

If you use a 'full stop' in the key (link from column), getLinks() will look up in the table with the column name matching the string to the left of the 'full stop', and replace the 'full stop' with and underscore and assign the object variable to that name. Or you may wish to use the selectAs() method to decide how you want columns from different objects to be returned (when using joinAdd())

An example databasename.links.ini file

;                       for table person
[person]
;                       link value of eycolor to table colors, match name column
eyecolor = colors:name
;                       link value of owner to table grp, match id column
owner = grp:id
;                       link value of picture to table attachments, match id column
picture = attachments:id


;                       for a sales example with multiple links of a single column
[sales]
;                       for autoloading the car object into $sales->_car_id
car_id = car:id
;                       for autoloading the part number object into $sales->_car_id_partnum
car_id.partnum = part_numbers:car_id

It is also possible to create joins on keys consisting of more than one column. Use the following syntax:

Linking tables on composite keys

[table_b]
field1,field2 = table_a:field1,field2

This will lead to the following select statement (here using the INNER JOIN syntax):

Resulting SQL

<?php
SELECT * FROM table_b INNER JOIN table_a ON table_b.field1 = table_a.field1 AND table_b.field2 = table_a.field2
?>

->getLink()

->getLink() – fetch and return a related object

Synopsis

mixed $DB_DataObject->getLink ( string $column , string $table , string $key )

Description

Fetch a related object. This should be used in conjunction with a <dbname>.links.ini file configuration (see the introduction on linking for details on this).

You may also use this with all parameters to specify, the column, and related table and column.

Parameter

• string $column - either column or column.xxxxx
• string $table - name of table to look up value in
• string $link - name of column in other table to match

Return value

mixed - object on success or FALSE on failure.

Note

This function can not be called statically.

Example

Getting the related object

<?php
$person = new DataObjects_Person;
$person->get(12);
$group = $person->getLink('group_owner');
echo $group->name;
$group = $person->getLink('colourid','hair');
?>

Resulting SQL

->getLinks()

->getLinks() – load related objects

Synopsis

boolean $DB_DataObject->getLinks ( string $variableFormat )

Description

Loads the all the related objects into the main object, by using the links.ini relationships, and sets the calling objects variables with the row name prefixed with an underscode (_) to the resulting objects.

Using this with the earlier column naming convention is depreciated, and links.ini files should be used.

Parameter

• string $variableFormat - the default behavior is to assign the resulting objects to variables with the row name prefixed with an underscode (_), however, you can use this value to format the variable differently

  examples of formaters

  
  if room.occupied_by is linked to a person.id
  
  without a modifier - eg _%s
  
  
  results in the equivilant of
  $object->_occupied_by =  $object->getLink('occupied_by');
  
  
  
  with a modifier - eg link_%s 
  
  results in the equivilant of
  $object->link_occupied_by = $object->getLink('occupied_by');

Return value

boolean - TRUE on success and FALSE on failure

Note

This function can not be called statically.

See

DB_DataObject::getLink()

Example

Two Example Tables

Loading all the related objects

<?php
$person = new DataObjects_Person;
$person->get(1079);
$person->getLinks();
print_r($person);
?>

Resulting SQL

Resulting Output

Example with three tables join

<?php
/**
 * The following example show a three tables join.
 *
 * More joins can be nested as you see fit.
 */
$person = new DataObjects_Person;

$data = array();
if ($person->find()) {
    while ($person->fetch()) {
        $person->getLinks();
        // Following is another call to getLinks for the second join
        $person->_convention_id->getLinks();
        $data[] = $person->_convention_id->_room_id->ToArray();
    }
}

print_r($data);
?>

databasename.links.ini

->selectAs()

->selectAs() – Build the select component of a query (usually for joins)

Synopsis

void $DB_DataObject->selectAs ( object | array $columns_or_object , string $format , string $table_name )

Description

Auto creates select items based on either the current objects column names, the supplied object, or an array.

This is primarily used in conjunciton with joinAdd, to enable the renaming of columns into a fixed format when they are likely to have naming conflicts (like both tables have an 'id' column).

Sending no arguments to selectAs, will reset the current select (usually removing the default *), and build a select list based on the current objects column names.

Parameter

• object | array $column_or_object - a dataobject or array of column names
• string $format - the format the columns will appear, using sprintf format, the %s is replaced with the column name so car_%s would result in the SQL 'car.name as car_name' being generated.
• string $tableName - this is used either when use send an array as the first argument, or when you are joining a table 'as' another name,

Note

This function can not be called statically.

Example

Using selectAs()

<?php       
// ok lets see what is going on..
DB_DataObject::debugLevel(1);       
       
$person = new DataObjects_Person;


// to generate "person.id as id , person.name as name ......."
$person->selectAs();

// to generate a restricted list.. "person.age as age , person.name as name"
$person->selectAs(array('age','name'));

// using another object. 
$car = new DataObjects_Car;

// this is the first car (
$car->use = 'first';


// using the joinadd add the car..
$person->joinAdd($car); 

// now add all the select columns for the car eg. "car.id as car_id, car.name as car_name ...."
$person->selectAs($car, 'car_%s');


// select only a few columns from the car table.
// note you have to use the table name at the end..
$person->selectAs(array('color','topspeed'), 'car_%s','car');




// now the user can have a second car....
$secondcar = new DataObjects_Car;
$secondcar->use = 'second';

// now since we alreay have one car, the sql statement would get messy 
// so we are joining this as the second car "FROM person INNER JOIN car ..... , car as secondcar WHERE .....
$person->joinAdd($secondcar,'','secondcar'); 

// and we can now add all the columns
// "secondcar.id as secondcar_id, secondcar.name as secondcar_name ........
// note that you must use the last field as the SECONDCAR.ID format uses the 'AS' name, rather than the 
// objects real table name 'car'
$person->selectAs($secondcar, 'secondcar_%s','secondcar');

 

// ok fire of a query...
$person->find();
while ($person->fetch()) {
  ......
}


?>

->joinAdd()

->joinAdd() – add another dataobject to build a create join query

Synopsis

void $DB_DataObject->joinAdd ( object $dataobject , string $joinType , string $joinAs , string $joinCol )

Description

Builds a Join Query, by adding another dataobject to this one. Be careful when using this, raw queries may be clearer than using joinAdd.

Thanks to Stijn de Reede for the implementation of this.

Parameter

• object $obj - joining object (no value resets the join)
• string $joinType - "LEFT" | "INNER " | "RIGHT" | "" INNER is default, "" indicates just select ... from a,b,c with no join and links are added as where items. Note: 'LEFT' is the same as LEFT OUTER.
• string $joinAs - if you want to select the table as anther name useful when you want to select multiple columns from a secondary table.
• string $joinCol - The column on This objects table to match,needed if this table links to the child object in multiple places eg.

  using specific join Columns

  
  user->friend (is a id of a person)
  user->mother (is a id of another person)

Note

This function can not be called statically.

The Examples below are not tested, use DB_DataObject::debugLevel(1), to see what exactly is going on when you use this, and send the author some better examples..

Example

Simple simple Join

<?php
// (requires links.ini to be set up correctly)
// get all the images for product 24

$i = new DataObject_Image();
$pi = new DataObjects_Product_image();
$pi->product_id = 24; // set the product id to 24
$i->joinAdd($pi); // add the product_image connectoin
$i->find();
while ($i->fetch()) {
// do stuff
}
?>

Resulting SQL

More Complex Join query

<?php
// an example with 2 joins
// get all the images linked with products or productgroups
$i = new DataObject_Image();
$pi = new DataObject_Product_image();
$pgi = new DataObject_Productgroup_image();
$i->joinAdd($pi);
$i->joinAdd($pgi);
$i->find();
while ($i->fetch()) {
// do stuff
}
?>

Resulting SQL

->set*() and ->get*()

->set*() and ->get*() – Automatic Setters and Getters using overload

Synopsis

true | string $DB_DataObject->set* ( mixed $value )

mixed $DB_DataObject->get* ( )

Description

From version PHP 4.3.2RC2 onwards, DB_DataObject is automatically overloaded, providing access to all variables using $object->set{ColumnName}() and $object->set{ColumnName}($value) even if you have not defined the method.

It is assumed that set methods return strings as errors or TRUE, so that it can interact with setFrom and return array's of error strings.

The get Methods are used by toArray(), if defined they can be used to alter the appearance of columns ,like making dates human readable

The logic is very simple, if you call $object->setXXX() and it is not defined, it will just set the value, if you define a method setXXXX, that will be called instead of the default handler, same applies to getXXX().

Due to the naming conflict possiblity of a column named from, the associated method for column 'from' is set_from, rather than setFrom()

Parameter

• mixed $value - on setters only (the value to assign to the column), on getters you may like to implement date formating or sprintf formating as the argument.

Return value

mixed - setters will return TRUE from the default method, in your implementations of setters. It is expected that setXXX($value) will return a string (the error) if it is invalid or TRUE on success. getXXX may return the value or a formated value, remember though it affects $object->toArray().

Note

This function can not be called statically.

Warning: This is experimental, its behavour may change slightly in the future.

Example

Simple find and fetch of data based on Object Vars

<?php
class DataObjects_Person extends DB_DataObject {

    var $id;
    var $name;
    var $date_of_birth;
    
    // you can define this method after you implement a call to it!
    // as it is automatically implemented to return $this->date_of_birth by 
    // the overload __call() method.
    
    function getDate_of_Birth() {
        return date('d M Y', strtotime($this->date_of_birth));
    }
}
       
       
$person = new DataObjects_Person;

$person->get(12);

// now lets use the getters and setters even though some are not defined.
echo $person->getName() . ' was born on  ' . $person->getDate_of_Birth();
?>

Resulting Output

->setFrom()

->setFrom() – Copy items from Array or Object (for form posting)

Synopsis

boolean $DB_DataObject->setFrom ( array or object $from , string $format = '%s' , bool $skipEmpty = false )

Description

Copies items that are in the table definitions from an array or object into the current object (It will not override key values). This can be used to process form posts (if the field names match the database), or cloning similar objects.

You can not set the value of a key column using setFrom, It is silently ignored for security reasons. (you can still assign it manually)

setFrom will attempt to call the setters set{columnname} methods if they exist, It will also call fromValue(), which formats dates correctly.

You may realize that this method overlaps the overloaded method for the column name from, due to this, the associated methods for the column name 'from' are set_from and getFrom.

Parameter

• array or Object $from - what to copy from
• string $format - the format of the array or object variables and how they relate to this object. For example if your input array is in the format prefix_COLNAME, then you can use 'prefix_%s'.
• bool $skipEmpty - If set to true, DB_DataObject will not assign empty values if a column is empty (eg. '' / 0 etc)

Return value

array or boolean - TRUE on success or an array of set*() return values in PHP4.3.2 upwards

Note

This function can not be called statically.

Example

Using setFrom()

<?php
// Person contains name,age
// $_POST contains 'name'=>'fred', 'age'=>'22'


$person = new DataObjects_Person;
$person->get(12);
$person->setFrom($_POST);
$person->update();


// or using the formating

// person contains name,age
// $_POST contains 'person_name'=>'fred', 'person_age'=>'22'

$person = new DataObjects_Person;
$person->get(12);
$ret = $person->setFrom($_POST,'person_%s');

// use the return value from setFrom to test for errors (PHP4.3.2)
if ($ret === true) {
  $person->update();
} else {
  // $ret is an array or strings..
  echo 'There were some errors: '  . implode(', ', $ret);
}






// or copying from another object
$personA = new DataObjects_Person;
$personA->get(12);

$personB = new DataObjects_Person;
$personB->get(14);

$personA->setFrom($personB);
$person->update();
?>

->toArray()

->toArray() – Get an array of the current result

Synopsis

array $DB_DataObject->toArray ( string $format = '%s' , bool $hideEmpty = false )

Description

Allows the fetching of an associate array (with optional key formating) for use with other packages, like HTML_QuickForm.

From PHP4.2.3RC2 onwards, The values of each column are retrieved using getXXXX() methods so you can change the formating of a row by defining a getter method.

Parameter

• string $format - a sprintf string eg. 'form[%s]'
• bool $hideEmpty - If set to true, empty elements (no value/null) will not be returned

Note

This function can not be called statically.

Example

getting arrays

<?php
$person = new DataObjects_Person;
$person->get(2);
print_r($person->toArray());
print_r($person->toArray('user[%s]'));
print_r($person->toArray('user[%s]', true));
?>

Sample Output

->validate()

->validate() – check object data, and call objects validation methods.

Synopsis

array $DB_DataObject->validate ( )

Description

Check all the objects variables to see if they are valid, by default this means is a column an integer or string, if you define methods like validateEmail(), in your extended class then it will be called to validate the row called 'email'. This may be useful if called prior to an update or insert.., to generate error messages.

override this to set up your validation rules.

Return value

array - of validation results or TRUE

Note

This function can not be called statically.

the examples below utilize the PEAR validation package

Example

validate usage

<?php
$person = new DataObjects_Person;
$person->get(12);
$person->setFrom($_POST['input']);
$val = $person->validate();
if ($val === TRUE) {
  $person->update();
} else {
  foreach ($val as $k=>$v) {
    if ($v == false) {
      echo "There was something wrong with ($k)\n";
    } else {
      echo "($k) validated OK\n";
    }
  }
}
?>

validate methods

<?php
/* inside class DataObject_Person */
function validateEmail() {
  return Validate::email($this->email, true);
}

function validateHomepage() {
  return Validate::url($this->homepage, true);
}
function validateDate() {
  return Validate::date($this->date, "%d-%m-%Y", array(01,01,1970), array(01,01,2030));
?>

->tableName()

->tableName() – Get or set the table name of an object

Synopsis

object $DB_DataObject->tableName ( string $name )

Description

Without any argument, it returns the table name that the object deals with. With a string, it will set the table name for the instance of the object.

Note

This function can not be called statically.

Example

getting and setting the tablename

<?php
$person = new DataObjects_Person;
echo $person->tableName();
// echo's person


// now use the same object to query the extra_people table

$person->tableName('extra_people');
$person->id = 12;
$person->find(true);


// you can also use this in conjunction with table(), to create dataobjects for random tables..

$d = new DB_DataObject;
$d->tableName('person');
$d->table(array(
  'id'   => DB_DATAOBJECT_INT,
  'name' => DB_DATAOBJECT_STRING,
));
$d->keys(array('id'));

$d->id = 12;
$d->find(true);
// should do the same as above..!
?>

->database()

->database() – Get or set the database the object uses

Synopsis

object $DB_DataObject->database ( string $name )

Description

Without any argument, it returns the database that the object deals with. With a string, it will set the database for the instance of the object.

Note

This function can not be called statically.

Example

Getting the database name

<?php
$person = new DataObjects_Person;
echo $person->database();
// echo's mydatabase


// now use the same object to check on a mirror..

$person->database('mirror1');
$person->id = 12;
$person->find(true);
?>

->table()

->table() – Get or set the table schema

Synopsis

object $DB_DataObject->table ( array $schema )

Description

Without any arguments, table() returns the table schema that the object deals with. With an array passed as the first argument, it will set the table schema for the current instance of the object.

The default schema is normally stored in the database.ini file described in the Autobuilding section.

Note

This function can not be called statically.

Example

getting the connection

<?php
$person = new DataObjects_Person;
print_r($person->table());
//
// array(
//     'id'     =>  1  // == DB_DATAOBJECT_INT
//     'name'   =>  2  // == DB_DATAOBJECT_STR
//     'bday'   =>  6  // == DB_DATAOBJECT_STR + DB_DATAOBJECT_DATE
//     'last'   =>  14 // == DB_DATAOBJECT_STR + DB_DATAOBJECT_DATE + DB_DATAOBJECT_TIME
//     'active' =>  17 // == DB_DATAOBJECT_INT + DB_DATAOBJECT_BOOL
//     'desc'   =>  34 // == DB_DATAOBJECT_STR + DB_DATAOBJECT_TXT
//     'photo'  =>  64 // == DB_DATAOBJECT_STR + DB_DATAOBJECT_BLOB 
// )
//


 

// now use it to define a on the fly database table...

$d = new DB_DataObject;
$d->tableName('person');
$d->table(array(
  'id'   => DB_DATAOBJECT_INT,
  'name' => DB_DATAOBJECT_STRING,
));
$d->keys(array('id'));

$d->id = 12;
$d->find(true);
// should do the same as above..!
?>

->keys()

->keys() – Get or set the table keys

Synopsis

array $DB_DataObject->keys ( string $keys ... )

Description

Without any arguments, keys() returns an array of the keys used by the object (the generator builds these and guesses them based on finding things like primary key, unique, or nextval). Calling it with a value, or mulitple values, sets the keys for the current instance of the object.

The default keys are normally stored in the database.ini file described in the Autobuilding section.

Note

This function can not be called statically.

Example

getting the connection

<?php
$person = new DataObjects_Person;
print_r($person->keys());
//
// array(
//     0 => 'id',
// )
//


 

// now use it to define a on the fly database table...

$d = new DB_DataObject;
$d->tableName('person');
$d->table(array(
  'id'   => DB_DATAOBJECT_INT,
  'name' => DB_DATAOBJECT_STRING,
));
$d->keys('id');

// if you have multiple keys
// $d->keys('id','key2','key2');


$d->id = 12;
$d->find(true);
// should do the same as above..!
?>

->getDatabaseConnection()

->getDatabaseConnection() – Get the PEAR Database Object

Synopsis

object $DB_DataObject->getDatabaseConnection ( )

Description

Fetch the pear database connection that the object uses - so you can find information or send queries directly to it.

Note

This function can not be called statically.

Example

getting the connection

<?php
$person = new DataObjects_Person;
$db=  &$person->getDatabaseConnection();
echo $db->phptype;
?>

Sample Output

->getDatabaseResult()

->getDatabaseResult() – Get the PEAR Database Result Object

Synopsis

object $DB_DataObject->getDatabaseResult ( )

Description

Fetch the pear database result object - so you can use it with things like the Pager or HTML_Select classes

Note

This function can not be called statically.

Example

getting the result object

<?php
$person = new DataObjects_Person;
$person->hair = 'green';
$person->selectAdd();
$person->selectAdd('id,name');
$person->find();

$res=  $person->getDatabaseResult();
$quickFormSelect->loadDbResult($res,'id','name');
?>

DB_DataObject::debugLevel

DB_DataObject::debugLevel – set the amount of debugging output

Synopsis

void DB_DataObject::debugLevel ( integer $level )

Description

Sets and returns debug level. So you can see the queries and connections being built and executed.

Parameter

• integer $level - level, without any parameters it will disable the debugging output. 1 give a general output, 5 includes things like passwords for connections.

Note

This function can not be called statically.

See

->debug()()

Example

Using debugLevel()

<?php
// turn debugging high
DB_DataObject::debugLevel(5);
$person = new DataObjects_Person;
$person->get(12);
$person->setFrom($_POST['input']);
$person->update();
// turn debugging off
DB_DataObject::debugLevel();
?>

->debug()

->debug() – output debug information.

Synopsis

void $DB_DataObject->debug ( string $message , string $logPrefix , integer $level=1 )

Description

Debugger - you can use this in your extended classes to output debugging information. Uses DB_DataObject::DebugLevel(x) to turn it on, and can be completly turned off by using the production setting in the configuration file

Parameter

• string $message - message to output
• string $logPrefix - A bold prefix string
• integer $level - output level, 1 is general, 5 tends to reveal things like database connection passwords..

Note

This function can not be called statically.

In production mode, the debugger is disabled

See

DB_DataObject::debugLevel()

Example

Setting the debugging level

<?php
$person = new DataObjects_Person;
$person->get(12);

// always prints
$person->debug('just got the person, about to set stuff', 'my application',0); 
$person->setFrom($_POST['input']);

// only prints if debuglevel is set
$person->debug('just set the variables, about to update', 'my application',1);
$person->update();
?>

DB_DataObject::raiseError

DB_DataObject::raiseError – throw an error

Synopsis

object DB_DataObject::raiseError ( int $message , resource $type = null , resource $behaviour = null )

Description

Default error handling is to create a PEAR::Error, but never return it. If you need to handle errors you should look at setting the PEAR_Error callback this is due to the fact it would wreck havoc on the internal methods!

Parameter

• int $message - message
• resource $type - type
• resource $behaviour - behaviour (die or continue!);

Return value

object [unknown] -

Note

This function can not be called statically.

Casting - Dates, Blobs and Null

Casting - Dates, Blobs and Null – DB_DataObject_Cast ::date(), ::blob(), ::sql()

Dealing with Casting, (everything except strings and numbers)

This is experimental!, although it is documented, it currently only supports a limited amount of databases (send me fixes if you want it to support your favorite database), and the internal operations/API may change in the future..

DataObjects is a very easy way to work with databases that are focused on numbers and strings. You can also use it on date fields (although you must format your strings correctly), and you can use it with other types by using raw SQL query(), and the string value "null" is automatically converted to NULL in the database.

In an effort to provide a cleaner way to code to the richer database types, the DB_DataObject_Cast object was created. It's purpose is to simply create an object to represent some of the more unusual types. Below is an example of using it to create a few simple types.

Cast Objects can be used in both building queries, and assigning values

Cast Objects for building and assigning values

<?php

// using Cast Objects for building a query.

$person = DB_DataObject::factory('person');


// assign the value of birthday to a Cast object with a date.
$person->birthday =  DB_DataObject_Cast::date(2000,12,30);


$person->find();
while ($person->fetch()) {
    echo "{$person->name} has a birthday on 30 december 2002<BR>";
}



// use Cast Objects for assigning values.


$person = DB_DataObject::factory('person');
$person->get(12);

// set the persons's birthday to 30 december 2000
$person->birthday = DB_DataObject_Cast::date(2000,12,30);

// now update the database.
$person->update();

?>

As you can see, This component is in it"s infancy, so if you have any feature requests, ideas, please do not hesitate to contact me at alan_k at php dot net.

The blob and string type

Blobs are fields which can store large amounts of binary data in the databases.

At present only blobs is only supported in postgres using the bytea type. (please email me with code for other databases.)

Inserting a photo and a big text file

<?php


$person = DB_DataObject::factory('person');
$person->name = 'fred'

// use blob for binary data.
$person->photo =  DB_DataObject_Cast::blob(file_get_contents('xxx.jpg'));

// use string for textural data into a blob type.
$person->xmldocs =  DB_DataObject_Cast::string(file_get_contents('xxx.xml'));

// now insert into the database.
$person->insert();
 

?>

The date type

Most dates are stored in a database in ISO standard format, this method, allows you to create date types, from either Year,month,day, Human readable day/month/year, or standard iso format year-month-day. It fills in the remaining values based on simple rules.

Inserting a date in various formats

<?php


$person = DB_DataObject::factory('person');
$person->name = 'fred'

// use a human readable date

// full format
$person->birthday = new DB_DataObject_Cast::date('21/12/2003');

// use only a month/year - actually sets to 1 december 2003
$person->expires =  DB_DataObject_Cast::date('12/2003');

// use only a year only - actually sets to 1 jan 2003
$person->expires = DB_DataObject_Cast::date(2003);





// use a iso formated

// full formated
$person->birthday =  DB_DataObject_Cast::date('2003-12-21');

// use only a year-month - actually sets to 1 december 2003
$person->expires =  DB_DataObject_Cast::date('2003-12');




// using array syntax

// full formated
$person->birthday =  DB_DataObject_Cast::date(2003,12,21);

// use only a year-month  - actually sets to 1 december 2003
$person->expires =  DB_DataObject_Cast::date(2003,12);



// the real values are stored in object variables 

echo $person->birthday->year; // prints 2003
echo $person->birthday->month; // prints 12
echo $person->birthday->day; // prints 21




// you can do simple date addition (similar to mktime)

$d = DB_DataObject_Cast::date('01/12/2003');

$nextMonth = DB_DataObject_Cast::date($d->year,$d->month+1,1);



?>

The sql type

Some types are sql specific, or may even be database specific, you can use the sql type to put raw strings as part of the sql statement.

using raw sql

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

// set the birthday to null.
$person->birthday =  DB_DataObject_Cast::sql('NULL');

// do a sql cast statement (postgres specific)
$data = DB_DataObject_Cast::sql('cast("123123",datetime)');



// now insert into the database.
$person->insert();
 


?>