This tutorial shows basic usage of the ADODB Active Record Feature. It relies on a MySQL database, and each script needs to include the code shown here, in order to initialize the tables.

ADOdb_Active_Records are object representations of table rows. Each table in the database is represented by a class in PHP. To begin working with a table as a ADOdb_Active_Record, a class that extends ADOdb_Active_Record needs to be created for it.

class person extends ADOdb_Active_Record{}
$person = new person();

In the above example, a new ADOdb_Active_Record object $person was created to access the “persons” table.

Zend_Db_DataObject obsolete takes the name of the class, pluralizes it (according to American English rules), and assumes that this is the name of the table in the database. Also note that with MySQL, table names are case-sensitive under Linux, so your class name must match the table name's case. With other databases with case-insensitive tables, your class can be capitalized differently.

This kind of behavior is typical of ADOdb_Active_Record. It will assume as much as possible by convention rather than explicit configuration. In situations where it isn't possible to use the conventions that ADOdb_Active_Record expects, options can be overridden as we'll see later.

When the $person object was instantiated, ADOdb_Active_Record read the table metadata from the database itself, and then exposed the table's columns (fields) as object properties.

Our “persons” table has three fields:

"name_first", 
"name_last", 
"favorite_color". 

Each of these fields is now a property of the $person object. To see all these properties, use the ADOdb_Active_Record::getAttributeNames() method

var_dump($person->getAttributeNames());
 
/**
 * Outputs the following:
 * array(4) {
 *    [0]=>
 *    string(2) "id"
 *    [1]=>
 *    string(9) "name_first"
 *    [2]=>
 *    string(8) "name_last"
 *    [3]=>
 *    string(13) "favorite_color"
 *  }
 */

One big difference between ADOdb and Zend's implementation is we do not automatically convert to camelCaps style.

An ADOdb_Active_Record object is a representation of a single table row. However, when our $person object is instantiated, it does not reference any particular row. It is a blank record that does not yet exist in the database. An ADOdb_Active_Record object is considered blank when its primary key is NULL. The primary key in our persons table is “id”.

To insert a new record into the database, change the object's properties and then call the save() method:

$person = new person();
$person->name_first = 'Andi';
$person->name_last  = 'Gutmans';
$person->save();
 

The above code snippet fails because of the constraint in the table previously defined

1048: Column 'favorite_color' cannot be null

This error occurred because MySQL rejected the INSERT statement that was generated.

To insert a new ADOdb_Active_Record in the database, populate all of ADOdb_Active_Record's properties so that they satisfy the constraints of the database table, and then call the save() method:

/**
 * Calling the save() method will successfully INSERT
 * this $person into the database table.
 */
$person = new person();
$person->name_first     = 'Andi';
$person->name_last      = 'Gutmans';
$person->favorite_color = 'blue';
$person->save();

Once this $person has been INSERTed into the database by calling save(), the primary key can now be read as a property. Since this is the first row inserted into our temporary table, its “id” will be 1:

var_dump($person->id);
 
/**
 * Outputs the following:
 * string(1)
 */

From this point on, updating it is simply a matter of changing the object's properties and calling the save method again:

$person->favorite_color = 'red';
$person->save();

The code snippet above will change the favorite color to red, and then UPDATE the record in the database.

The default behaviour on creating an ADOdb_Active_Record is to pluralize the class name and use that as the table name. Often, this is not the case. For example, the person class could be reading from the “People” table.

We provide two ways to define your own table:

1. Use a constructor parameter to override the default table naming behaviour.

class person extends ADOdb_Active_Record{}
$person = new person('People');

2. Define it in a class declaration:

class person extends ADOdb_Active_Record
{
    var $_table = 'People';
}
$person = new person();

You can define the ADODB_ASSOC_CASE constant to control the field names' case

define('ADODB_ASSOC_CASE', ADODB_ASSOC_CASE_LOWER);
require "adodb.inc.php";
$person = new person('People');
$person->name = 'Lily';
 
define('ADODB_ASSOC_CASE', ADODB_ASSOC_CASE_UPPER);
require "adodb.inc.php";
$person2 = new person('People');
$person2->NAME = 'Lily';

The method save saves a record by executing an INSERT or UPDATE SQL statement as appropriate.

The method replace supports replace functionality, whereby the record is inserted if it does not exists, or updated otherwise.

$rec = new ADOdb_Active_Record("product");
$rec->name = 'John';
$rec->tel_no = '34111145';
$ok = $rec->replace(); // 0=failure, 1=update, 2=insert
ADOdb_Active_Record::Load($where)

Sometimes, we want to load a single record into an Active Record using the load method.

$person->load("id=3");
or
$person->load("id=?", array(3));

An array of active records based on some search criteria can be loaded using the find method.

class person extends ADOdb_Active_Record {
  var $_table = 'people';
}
 
$person = new person();
$peopleArray = $person->find("name like ? order by age", array('Sm%'));

You can force column names to be quoted by use of the $_quoteNames variable.

The ADOdb Active Record can be used in conjunction with ADOdb error handling.

include 'adodb/adodb-errorhandler.inc.php';
 
$ok = $rec->Save();
if (!$ok) 
    $err = $rec->ErrorMsg();

The ADOConnection::Debug property is obeyed. So if $db->debug is enabled, then ADOdb_Active_Record errors are also outputted to standard output and written to the browser.

You can convert an array to an ADOdb_Active_Record using set. The array must be numerically indexed, and have all fields of the table defined in the array. The elements of the array must be in the table's natural order too.

$row = $db->GetRow("select * from tablex where id=$id");
 
# PHP4 or PHP5 without enabling exceptions
$obj = new ADOdb_Active_Record('Products');
if ($obj->ErrorMsg()){
	echo $obj->ErrorMsg();
} else {
	$obj->Set($row);
}
 
# in PHP5, with exceptions enabled:

include('adodb-exceptions.inc.php');
try {
	$obj = new ADOdb_Active_Record('Products');
	$obj->Set($row);
} catch(exceptions $e) {
	echo $e->getMessage();
}

ADOdb_Active_Record does not require the table to have a primary key. You can insert records for such a table, but you will not be able to update nor delete.

Sometimes you are retrieving data from a view or table that has no primary key, but has a unique index. You can dynamically set the primary key of a table through the constructor:

$pkeys = array('category','prodcode');
 
// set primary key using constructor
$rec = new ADOdb_Active_Record('Products', $pkeys);
// or define a new class
class Product extends ADOdb_Active_Record {
    function __construct()
    {
	parent::__construct('Products', array('prodid'));
    }
}
 
$rec = new Product();

When creating a new record, the retrieval of the last auto-incrementing ID is not reliable for databases that do not support the insert_Id function call (check $connection→hasInsertID). In this case we perform a

SELECT MAX($primarykey) FROM $table

which will not work reliably in a multi-user environment. You can override the lastInsertID() function in this case.

Sometimes we want to load data from one database and insert it into another using ActiveRecords. This can be done using the optional parameter of the ADOdb_Active_Record constructor. In the following example, we read data from db.table1 and store it in db2.table2:

$db = NewADOConnection(...);
$db2 = NewADOConnection(...);
 
ADOdb_Active_Record::SetDatabaseAdapter($db2);
 
$activeRecs = $db->GetActiveRecords('table1');
 
foreach($activeRecs as $rec) {
   $rec2 = new ADOdb_Active_Record('table2',$db2);
   $rec2->id = $rec->id;
   $rec2->name = $rec->name;
 
    $rec2->Save();
}

If you have to pass in a primary key called “id” and the 2nd db connection in the constructor, you can do so too:

$rec = new ADOdb_Active_Record("table1",array("id"),$db2);

You can now give a named label in setDatabaseAdapter, allowing to determine in your class definition which database to load, using var $_dbat.

$db1 = NewADOConnection(...); // some ADOdb DB
ADOdb_Active_Record::SetDatabaseAdapter($db1, 'mysql');
$db2 = NewADOConnection(...); // some ADOdb DB
ADOdb_Active_Record::SetDatabaseAdapter($db2, 'oracle');
 
class FooRecord extends ADOdb_Active_Record
{
var $_dbat = 'mysql';  // uses 'mysql' connection
...
}

Although the Active Record concept is useful, you have to be aware of some pitfalls when using Active Record. The level of granularity of Active Record is individual records. It encourages code like the following, used to increase the price of all furniture products by 10%:

$recs = $db->GetActiveRecords("Products","category='Furniture'");
foreach($recs as $rec) {
    $rec->price *= 1.1; // increase price by 10% for all Furniture products
    $rec->save();
}

Of course an UPDATE statement is superior because it's simpler and much more efficient (probably by a factor of x10 or more):

$db->Execute("update Products set price = price * 1.1 where category='Furniture'");

For performance sensitive code, using direct SQL will always be faster than using Active Records due to overhead and the fact that all fields in a row are retrieved (rather than only the subset you need) whenever an Active Record is loaded.

The default transaction mode in ADOdb is autocommit. So that is the default with active record too. The general rules for managing transactions still apply. Active Record to the database is a set of insert/update/delete statements, and the db has no knowledge of active records.

Smart transactions, that does an auto-rollback if an error occurs, is still the best method to multiple activities (inserts/updates/deletes) that need to be treated as a single transaction:

$conn->StartTrans();
$parent->save();
$child->save();
$conn->CompleteTrans();

Since ADOdb 5.06, we support parent child relationships. This is done using the ClassBelongsTo() and ClassHasMany() functions.

To globally define a one-to-many relationship we use the static function classHasMany. For example, we have 2 tables, persons (parent table) and children (child table) linked by persons.id = children.person_id. The variable $person→children is an array that holds the children. To define this relationship:

class person extends ADOdb_Active_Record{}
ADODB_Active_Record::ClassHasMany('person', 'children','person_id');
 
$person = new person();
$person->Load("id=1");
foreach($person->children as $c) {
    echo " $c->name_first ";
    $c->name_first .= ' K.';
    $c->Save();  ## each child record must be saved individually
}

If no data is loaded, then children is set to an empty array:

$person2 = new person();
$p = $person2->children;  ## $p is an empty array()

By default, data returned by HasMany() is unsorted. To define an order by clause (or define a SELECT LIMIT window), see LoadRelations() below. Another point is that all children are loaded only when the child member is accessed (in

__get()

), and not when the Load() function of the parent object is called. This helps to conserve memory.

To create and save new parent and child records:

class person extends ADOdb_Active_Record{}
class children extends ADOdb_Active_Record{}
ADODB_Active_Record::ClassHasMany('person', 'children','person_id');
 
$person = new person();
 
for ($i=0; $i<10; $i++)
    $person->children[0] = new children('children');
 
// modify fields of $person, then...
$person->save();
 
foreach($person->children as $c) {
    // modify fields of $c then...
    $c->save();
}

You can have multiple relationships (warning: relations are case-sensitive, 'Children' !== 'children'):

ADODB_Active_Record::ClassHasMany('person', 'children','person_id');
ADODB_Active_Record::ClassHasMany('person', 'siblings','person_id');
$person = new person();
$person->Load('id=1');
var_dump($person->children);
var_dump($person->siblings);

By default, the child class is ADOdb_Active_Record. Sometimes you might want the child class to be based on your own class which has additional functions. You can do so using the last parameter:

class person extends ADOdb_Active_Record{}
class child extends ADOdb_Active_Record { .... some modifications here ... }
ADODB_Active_Record::ClassHasMany('person', 'children','person_id', 'child');

Lastly some troubleshooting issues. We use the

__get()

method to set $p→children below. So once $p→children is defined by accessing it, we don't change the child reference, as shown below:

ADODB_Active_Record::ClassHasMany('person', 'children','person_id');
$p = new person();
$p->Load('id=1');
# $p->children points to person_id = 1
var_dump($p->children);
$p->Load('id=2');
# $p->children still points to person_id = 1
var_dump($p->children);

The solution to the above is to

unset($p->children) 

before

$p->Load('id=2').

For some classes, the mapping between class name and table name (which is the pluralised version) might not match. For example, the class name might be person, but the table name might be people. So we have 2 tables, people (parent table) and children (child table) linked by people.id = children.person_id.

Then you use the following static function ADODB_Active_Record::TableHasMany() like this:

ADODB_Active_Record::TableHasMany('people', 'children', 'person_id')

For some classes, the mapping between class name and table name (which is the pluralised version) might not match or the primary key is not the default id. For example, the class name might be person, but the table name might be people. So we have 2 tables, people (parent table) and children (child table) linked by people.pid = children.person_id. This issue can be managed with the tableKeyHasMany method

Here is sample usage using mysql:

$db->Execute("insert into children (person_id,name_first,name_last) 
              values (1,'Jill','Lim')");
$db->Execute("insert into children (person_id,name_first,name_last) 
               values (1,'Joan','Lim')");
$db->Execute("insert into children (person_id,name_first,name_last)
               values (1,'JAMIE','Lim')");
 
class person extends ADOdb_Active_Record{}
ADODB_Active_Record::ClassHasMany('person', 'children','person_id');
 
$person = new person();
$person->name_first     = 'John';
$person->name_last      = 'Lim';
$person->favorite_color = 'lavender';
$person->save(); // this save will perform an INSERT successfully
 
/*
* no need to define HasMany() again, adodb remembers definition
*/
$person2 = new person(); 
$person2->Load('id=1');
 
$c = $person2->children;
if (is_array($c) 
&& sizeof($c) == 3 
&& $c[0]->name_first=='Jill' 
&& $c[1]->name_first=='Joan'
&& $c[2]->name_first == 'JAMIE') 
    echo "OK Loaded HasMany<br>";
else {
    echo "Error loading hasMany should have 
          3 array elements Jill Joan Jamie<br>";
}

This older method is deprecated and ClassHasMany/TableHasMany/TableKeyHasMany should be used.

The older way to define a one-to-many relationship is to use

$parentobj->HasMany($relation, $foreignKey = ''). 

For example, we have 2 tables, persons (parent table) and children (child table) linked by persons.id = children.person_id. The variable $person→children is an array that holds the children. To define this relationship:

class person extends ADOdb_Active_Record{}
 
$person = new person();
$person->HasMany('children','person_id');
$person->Load("id=1");
foreach($person->children as $c) {
    echo " $c->name_first ";
    $c->name_first .= ' K.';
    $c->Save();  ## each child record must be saved individually
}

This HasMany() definition is global for the current script. This means that you only need to define it once. In the following example, $person2 knows about children.

$person = new person();
$person->HasMany('children','person_id');
 
$person2 = new person();
$person->Load("id=1");
$p = $person2->children;

You can define the parent of the current object using classBelongsTo. In the example below, we have a child table kids, and a parent table person. We have a link kids.person_id = persons.id. We create a child first, then link it to the parent:

class kid extends ADOdb_Active_Record{};
ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id');
 
/*
* default tablename will be 'kids', with primary key 'id'
*/
$ch = new kid(); 
$ch->Load('id=1');
$p = $ch->person;
if (!$p || $p->name_first != 'John') 
    echo "Error loading belongsTo<br>";
else 
    echo "OK loading BelongTo<br>";
 

Also if no data is loaded into the child instance, then $p will return null;

ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id');
 
$ch = new kid();
$p = $ch->person; # $p is null

Another way to define the class of the parent (which otherwise defaults to ADODB_Active_Record) as follows:

class kid extends ADOdb_Active_Record{};
class person extends ADOdb_Active_Record{... your modifications ... };
ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id', 'person');

If the child table differs from the convention that the child table name is the plural of the child class name, use this function: ADODB_Active_Record::TableBelongsTo().

E.g. the class is child, but the table name is children, and the link between the two tables is children.person_id = person.id:

ADODB_Active_Record::TableBelongsTo('children','person','person_id','id');

If the child table differs from the convention that the child table name is the plural of the child class name or the primary key is not 'id', use this function: ADODB_Active_Record::TableKeyBelongsTo().

E.g. the class is child, but the table name is children and primary key is ch_id, and the link between the two tables is children.person_id = person.id:

ADODB_Active_Record::TableKeyBelongsTo('children','ch_id', 'person','person_id','id');

Sometimes you want to load only a subset of data in a relationship. For example, you could load all female children sorted by children.name using loadRelations:

# assume this has been called:
#   ADODB_Active_Record::ClassHasMany('person', 'children','person_id');
$person = new person();
$person->Load('id=23');
/*
*  Load doesn't load children until $person->children
* is accessed or LoadRelations is called:
*/
$person->LoadRelations('children',"gender='F' order by name");

Lastly, if you have lots of child data, you can define a window of data of records to load. In the following example, we load a window of 100 records at a time:

# assume this has been called:
#  ADODB_Active_Record::ClassHasMany('Account', 'transactions','account_id');
#acc = new Account();
$acc->Load('id=23');
 
$start = 0;
while(true) {
    $acc->LoadRelations('transactions',
                        "tx_done=0 order by trxdate", 
                        $start, 
                        $start+100);
 
    if (!$acc->transactions) 
        break;
    foreach ($acc->transactions as $k => $trx) 
    {
	## process
	$trx->tx_done = 1;
	$trx->save();
    }
    $start += 100;
    unset($acc->transactions);
 
}

The $offset is 0-based, and $limit is the number of records to retrieve. The default is to ignore $offset (-1) and $limit (-1).

All of the code snippets above have been pulled together into a single procedure Here.

See the Active Record eXtended

Check _original and current field values before update, only update changes. Also if the primary key value is changed, then on update, we should save and use the original primary key values in the WHERE clause!

PHP5 specific: Make GetActiveRecords*() return an Iterator.

PHP5 specific: Change PHP5 implementation of Active Record to use

__get() and __set()

for better performance.