ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools

Trace:
v5:userguide:active_record

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
userguide:active_record [2015/12/04 20:03] – mnewnhamv5:userguide:active_record [2019/05/08 16:32] (current) – [Setting the Table Name] ADODB_ASSOC_CASE not $ADODB_ASSOC_CASE dregad
Line 15: Line 15:
   * Works with PHP4 and PHP5 and provides equivalent functionality in both versions of PHP.   * Works with PHP4 and PHP5 and provides equivalent functionality in both versions of PHP.
   * ADOdb_Active_Record works when you are connected to multiple databases. Zend's only works when connected to a default database.   * ADOdb_Active_Record works when you are connected to multiple databases. Zend's only works when connected to a default database.
-  * Support for [[reference:$ADODB_ASSOC_CASE]]. The field names are upper-cased, lower-cased or left in natural case depending on this setting.+  * Support for [[v5:reference:adodb_assoc_case]]. The field names are upper-cased, lower-cased or left in natural case depending on this setting.
   * No field name conversion to camel-caps style, unlike Zend's implementation which will convert field names such as 'first_name' to 'firstName'.   * No field name conversion to camel-caps style, unlike Zend's implementation which will convert field names such as 'first_name' to 'firstName'.
   * NewADOConnection::GetActiveRecords() and ADOConnection::GetActiveRecordsClass() functions in adodb.inc.php.   * NewADOConnection::GetActiveRecords() and ADOConnection::GetActiveRecordsClass() functions in adodb.inc.php.
Line 152: Line 152:
 We provide two ways to define your own table: We provide two ways to define your own table:
  
-1. Use a constructor parameter to override the default table naming behaviour. +  - Use a constructor parameter to override the default table naming behaviour. <code php> 
- +class person extends ADOdb_Active_Record {} 
- class person extends ADOdb_Active_Record{} +$person = new person('People'); 
- $person = new person('People'); +</code> 
-2. Define it in a class declaration:+  - Define it in a class declaration: <code php> 
 +class person extends ADOdb_Active_Record 
 +{ 
 +    var $_table = 'People'; 
 +} 
 +$person = new person(); 
 +</code>
  
- class person extends ADOdb_Active_Record +==== ADODB_ASSOC_CASE ====
- { +
- var $_table = 'People'; +
- } +
- $person = new person(); +
-$ADODB_ASSOC_CASE+
  
-This allows you to control the case of field names and properties. For example, all field names in Oracle are upper-case by default. So you can force field names to be lowercase using $ADODB_ASSOC_CASE. Legal values are as follows:+This allows you to control the case of field names and properties. For example, in Oracle all field names are uppercase by default, so you can force them to lowercase using ''define('ADODB_ASSOC_CASE', ADODB_ASSOC_CASE_LOWER);''.
  
- 0: lower-case +For further details on valid values and usage, see [[v5:reference:adodb_assoc_case]] reference.
- 1: upper-case +
- 2: native-case +
-So to force all Oracle field names to lower-case, use+
  
-$ADODB_ASSOC_CASE = 0;+<code php> 
 +// ADODB_ASSOC_CASE ==  ADODB_ASSOC_CASE_LOWER
 $person = new person('People'); $person = new person('People');
 $person->name = 'Lily'; $person->name = 'Lily';
-$ADODB_ASSOC_CASE = 2;+ 
 +// ADODB_ASSOC_CASE ==  ADODB_ASSOC_CASE_UPPER
 $person2 = new person('People'); $person2 = new person('People');
 $person2->NAME = 'Lily'; $person2->NAME = 'Lily';
-Also see $ADODB_ASSOC_CASE.+</code>
  
-ADOdb_Active_Record::Save()+==== ADOdb_Active_Record::Save() ====
  
 Saves a record by executing an INSERT or UPDATE SQL statement as appropriate. Saves a record by executing an INSERT or UPDATE SQL statement as appropriate.
Line 188: Line 188:
 Returns 0 on failed UPDATE, and 1 on UPDATE if data has changed, and -1 if no data was changed, so no UPDATE statement was executed. Returns 0 on failed UPDATE, and 1 on UPDATE if data has changed, and -1 if no data was changed, so no UPDATE statement was executed.
  
-ADOdb_Active_Record::Replace()+==== ADOdb_Active_Record::Replace() ====
  
 ADOdb supports replace functionality, whereby the record is inserted if it does not exists, or updated otherwise. ADOdb supports replace functionality, whereby the record is inserted if it does not exists, or updated otherwise.
 +<code php>
 $rec = new ADOdb_Active_Record("product"); $rec = new ADOdb_Active_Record("product");
 $rec->name = 'John'; $rec->name = 'John';
Line 197: Line 197:
 $ok = $rec->replace(); // 0=failure, 1=update, 2=insert $ok = $rec->replace(); // 0=failure, 1=update, 2=insert
 ADOdb_Active_Record::Load($where) ADOdb_Active_Record::Load($where)
 +</code>
  
 Sometimes, we want to load a single record into an Active Record. We can do so using: Sometimes, we want to load a single record into an Active Record. We can do so using:
  
 +<code php>
 $person->load("id=3"); $person->load("id=3");
  
Line 206: Line 208:
 $person->load("id=?", array(3)); $person->load("id=?", array(3));
 Returns false if an error occurs. Returns false if an error occurs.
 +</code>
  
-ADOdb_Active_Record::Find($whereOrderBy, $bindarr=false, $pkeyArr=false)+==== ADOdb_Active_Record::Find($whereOrderBy, $bindarr=false, $pkeyArr=false) ====
  
 We want to retrieve an array of active records based on some search criteria. For example: We want to retrieve an array of active records based on some search criteria. For example:
 +<code php>
 class person extends ADOdb_Active_Record { class person extends ADOdb_Active_Record {
-var $_table = 'people';+    var $_table = 'people';
 } }
  
 $person = new person(); $person = new person();
 $peopleArray = $person->Find("name like ? order by age", array('Sm%')); $peopleArray = $person->Find("name like ? order by age", array('Sm%'));
-Quoting Identifiers+</code> 
 + 
 +===== Quoting Identifiers =====
  
 You can force column names to be quoted in INSERT and UPDATE statements, typically because you are using reserved words as column names by setting You can force column names to be quoted in INSERT and UPDATE statements, typically because you are using reserved words as column names by setting
  
-ADODB_Active_Record::$_quoteNames = true;+  ADODB_Active_Record::$_quoteNames = true; 
 +  
 Default is false. Default is false.
  
-Error Handling and Debugging+===== Error Handling and Debugging =====
  
 In PHP5, if adodb-exceptions.inc.php is included, then errors are thrown. Otherwise errors are handled by returning a value. False by default means an error has occurred. You can get the last error message using the ErrorMsg() function. In PHP5, if adodb-exceptions.inc.php is included, then errors are thrown. Otherwise errors are handled by returning a value. False by default means an error has occurred. You can get the last error message using the ErrorMsg() function.
  
 To check for errors in ADOdb_Active_Record, do not poll ErrorMsg() as the last error message will always be returned, even if it occurred several operations ago. Do this instead: To check for errors in ADOdb_Active_Record, do not poll ErrorMsg() as the last error message will always be returned, even if it occurred several operations ago. Do this instead:
 +<code php>
 # right! # right!
 $ok = $rec->Save(); $ok = $rec->Save();
Line 237: Line 243:
 $rec->Save(); $rec->Save();
 if ($rec->ErrorMsg()) echo "Wrong way to detect error"; if ($rec->ErrorMsg()) echo "Wrong way to detect error";
 +
 +</code>
 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. 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.
  
Line 243: Line 251:
 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. 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.
  
 +<code php>
 $row = $db->GetRow("select * from tablex where id=$id"); $row = $db->GetRow("select * from tablex where id=$id");
  
Line 262: Line 271:
  echo $e->getMessage();  echo $e->getMessage();
 } }
-Primary Keys+</code> 
 + 
 +===== Primary Keys =====
  
 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. 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.
Line 268: Line 279:
 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: 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');+<code php> 
 +$pkeys = array('category','prodcode');
  
- // set primary key using constructor +// set primary key using constructor 
- $rec = new ADOdb_Active_Record('Products', $pkeys);+$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')); 
 +    } 
 +}
  
- // or define a new class +$rec = new Product(); 
- class Product extends ADOdb_Active_Record { +</code> 
- function __construct() +===== Retrieval of Auto-incrementing ID =====
- { +
- parent::__construct('Products', array('prodid')); +
- } +
- } +
- +
- $rec = new Product(); +
-Retrieval of Auto-incrementing ID+
  
 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 ADOdb_Active_Record::LastInsertID() function in this case. 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 ADOdb_Active_Record::LastInsertID() function in this case.
Line 288: Line 300:
  
 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: 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:
 +<code php>
 $db = NewADOConnection(...); $db = NewADOConnection(...);
 $db2 = NewADOConnection(...); $db2 = NewADOConnection(...);
Line 297: Line 309:
  
 foreach($activeRecs as $rec) { foreach($activeRecs as $rec) {
- $rec2 = new ADOdb_Active_Record('table2',$db2); +   $rec2 = new ADOdb_Active_Record('table2',$db2); 
- $rec2->id = $rec->id; +   $rec2->id = $rec->id; 
- $rec2->name = $rec->name;+   $rec2->name = $rec->name;
  
- $rec2->Save();+    $rec2->Save();
 } }
 +</code>
 If you have to pass in a primary key called "id" and the 2nd db connection in the constructor, you can do so too: 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); $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. 
  
 +You can now give a named label in SetDatabaseAdapter, allowing to determine in your class definition which database to load, using var $_dbat.
 +<code php>
 $db1 = NewADOConnection(...); // some ADOdb DB $db1 = NewADOConnection(...); // some ADOdb DB
 ADOdb_Active_Record::SetDatabaseAdapter($db1, 'mysql'); ADOdb_Active_Record::SetDatabaseAdapter($db1, 'mysql');
Line 318: Line 332:
 ... ...
 } }
-$ADODB_ACTIVE_CACHESECS+ 
 +</code> 
 + 
 +===== $ADODB_ACTIVE_CACHESECS =====
  
 You can cache the table metadata (field names, types, and other info such primary keys) in $ADODB_CACHE_DIR (which defaults to /tmp) by setting the global variable $ADODB_ACTIVE_CACHESECS to a value greater than 0. This will be the number of seconds to cache. You should set this to a value of 30 seconds or greater for optimal performance. You can cache the table metadata (field names, types, and other info such primary keys) in $ADODB_CACHE_DIR (which defaults to /tmp) by setting the global variable $ADODB_ACTIVE_CACHESECS to a value greater than 0. This will be the number of seconds to cache. You should set this to a value of 30 seconds or greater for optimal performance.
  
-Active Record Considered Bad?+===== Active Record Considered Bad? =====
  
 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%: 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'"); +<code php> 
- foreach($recs as $rec) {+$recs = $db->GetActiveRecords("Products","category='Furniture'"); 
 +foreach($recs as $rec) {
     $rec->price *= 1.1; // increase price by 10% for all Furniture products     $rec->price *= 1.1; // increase price by 10% for all Furniture products
     $rec->save();     $rec->save();
- }+} 
 +</code> 
 Of course an UPDATE statement is superior because it's simpler and much more efficient (probably by a factor of x10 or more): 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'");+<code php> 
 +$db->Execute("update Products set price = price * 1.1 where category='Furniture'"); 
 +</code> 
 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. 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.
  
-Transactions+===== Transactions =====
  
 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. 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.
Line 341: Line 364:
 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: 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:
  
 +<code php>
 $conn->StartTrans(); $conn->StartTrans();
 $parent->save(); $parent->save();
 $child->save(); $child->save();
 $conn->CompleteTrans(); $conn->CompleteTrans();
-One to Many Relations+</code> 
 + 
 +===== One to Many Relations =====
  
 Since ADOdb 5.06, we support parent child relationships. This is done using the ClassBelongsTo() and ClassHasMany() functions. Since ADOdb 5.06, we support parent child relationships. This is done using the ClassBelongsTo() and ClassHasMany() functions.
  
-ClassHasMany+===== ClassHasMany =====
  
-To globally define a one-to-many relationship we use the static function ADODB_Active_Record::ClassHasMany($class, $relation, $foreignKey = '', $foreignClass = 'ADODB_Active_Record'). 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:+To globally define a one-to-many relationship we use the static function ADODB_Active_Record::ClassHasMany($class, $relation, $foreignKey = ' ', $foreignClass = 'ADODB_Active_Record'). 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{} +<code php> 
- ADODB_Active_Record::ClassHasMany('person', 'children','person_id');+class person extends ADOdb_Active_Record{} 
 +ADODB_Active_Record::ClassHasMany('person', 'children','person_id');
  
- $person = new person(); +$person = new person(); 
- $person->Load("id=1"); +$person->Load("id=1"); 
- foreach($person->children as $c) { +foreach($person->children as $c) { 
- echo " $c->name_first "; +    echo " $c->name_first "; 
- $c->name_first .= ' K.'; +    $c->name_first .= ' K.'; 
- $c->Save();  ## each child record must be saved individually +    $c->Save();  ## each child record must be saved individually 
- }+} 
 +</code>
 If no data is loaded, then children is set to an empty array: If no data is loaded, then children is set to an empty array:
  
- $person2 = new person(); +<code php> 
- $p = $person2->children;  ## $p is an empty array() +$person2 = new person(); 
-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.+$p = $person2->children;  ## $p is an empty array() 
 +</code> 
 + 
 +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  <code php>__get()</code>), 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: To create and save new parent and child records:
  
 +<code php>
 +class person extends ADOdb_Active_Record{}
 +class children extends ADOdb_Active_Record{}
 +ADODB_Active_Record::ClassHasMany('person', 'children','person_id');
  
- class person extends ADOdb_Active_Record{} +$person = new person();
- 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');
  
- for ($i=0; $i<10; $i++) +// modify fields of $person, then... 
- $person->children[0] = new children('children'); +$person->save();
- +
-  // modify fields of $person, then... +
- $person->save();+
  
- foreach($person->children as $c) { +foreach($person->children as $c) { 
- // modify fields of $c then... +    // modify fields of $c then... 
- $c->save(); +    $c->save(); 
- }+} 
 +</code>
 You can have multiple relationships (warning: relations are case-sensitive, 'Children' !== 'children'): You can have multiple relationships (warning: relations are case-sensitive, 'Children' !== 'children'):
  
- ADODB_Active_Record::ClassHasMany('person', 'children','person_id'); +<code php> 
- ADODB_Active_Record::ClassHasMany('person', 'siblings','person_id'); +ADODB_Active_Record::ClassHasMany('person', 'children','person_id'); 
- $person = new person(); +ADODB_Active_Record::ClassHasMany('person', 'siblings','person_id'); 
- $person->Load('id=1'); +$person = new person(); 
- var_dump($person->children); +$person->Load('id=1'); 
- var_dump($person->siblings); +var_dump($person->children); 
-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:+var_dump($person->siblings); 
 +</code>
  
- class person extends ADOdb_Active_Record{} +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 child extends ADOdb_Active_Record { .... some modifications here ... } +<code php> 
- ADODB_Active_Record::ClassHasMany('person', 'children','person_id', 'child'); +class person extends ADOdb_Active_Record{} 
-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: +class child extends ADOdb_Active_Record { .... some modifications here ... } 
- +ADODB_Active_Record::ClassHasMany('person', 'children','person_id', 'child'); 
- ADODB_Active_Record::ClassHasMany('person', 'children','person_id'); +</code> 
- $p = new person(); +Lastly some troubleshooting issues. We use the <code php>__get()</code> 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: 
- $p->Load('id=1'); +<code php> 
- # $p->children points to person_id = 1 +ADODB_Active_Record::ClassHasMany('person', 'children','person_id'); 
- var_dump($p->children); +$p = new person(); 
- +$p->Load('id=1'); 
- $p->Load('id=2'); +# $p->children points to person_id = 1 
- # $p->children still points to person_id = 1 +var_dump($p->children); 
- var_dump($p->children);+$p->Load('id=2'); 
 +# $p->children still points to person_id = 1 
 +var_dump($p->children); 
 +</code>
 The solution to the above is to unset($p->children) before $p->Load('id=2'). The solution to the above is to unset($p->children) before $p->Load('id=2').
  
-TableHasMany+===== TableHasMany =====
  
 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. 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($table, $relation, $foreignKey = '', $foreignClass = 'ADODB_Active_Record') like this:+Then you use the following static function ADODB_Active_Record::TableHasMany($table, $relation, $foreignKey = ' ', $foreignClass = 'ADODB_Active_Record') like this:
  
 ADODB_Active_Record::TableHasMany('people', 'children', 'person_id') ADODB_Active_Record::TableHasMany('people', 'children', 'person_id')
-TableKeyHasMany+ 
 +===== TableKeyHasMany =====
  
 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. 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.
-Then you use the following static function ADODB_Active_Record::TableKeyHasMany($table, $tablePKey, $relation, $foreignKey = '', $foreignClass = 'ADODB_Active_Record') like this:+ 
 +Then you use the following static function ADODB_Active_Record::TableKeyHasMany($table, $tablePKey, $relation, $foreignKey = ' ', $foreignClass = 'ADODB_Active_Record') like this:
  
 ADODB_Active_Record::TableKeyHasMany('people', 'pid', 'children', 'person_id') ADODB_Active_Record::TableKeyHasMany('people', 'pid', 'children', 'person_id')
Line 429: Line 467:
  
 Here is sample usage using mysql: Here is sample usage using mysql:
 +<code php>
 +include_once('../adodb.inc.php');
 +include_once('../adodb-active-record.inc.php');
  
- include_once('../adodb.inc.php'); +$db = NewADOConnection('mysql://root@localhost/northwind'); 
- include_once('../adodb-active-record.inc.php');+ADOdb_Active_Record::SetDatabaseAdapter($db);
  
- $db = NewADOConnection('mysql://root@localhost/northwind'); +$db->Execute("CREATE TEMPORARY TABLE `persons` ( 
- ADOdb_Active_Record::SetDatabaseAdapter($db);+               `id` int(10) unsigned NOT NULL auto_increment, 
 +                `name_first` varchar(100) NOT NULL default '', 
 +                `name_last` varchar(100) NOT NULL default '', 
 +                `favorite_color` varchar(100) NOT NULL default '', 
 +                PRIMARY KEY  (`id`) 
 +            ) ENGINE=MyISAM; 
 +           ");
  
- $db->Execute("CREATE TEMPORARY TABLE `persons` ( +$db->Execute("CREATE TEMPORARY TABLE `children` ( 
-                 `id` int(10) unsigned NOT NULL auto_increment, +                `id` int(10) unsigned NOT NULL auto_increment, 
-                 `name_first` varchar(100) NOT NULL default '', + `person_id` int(10) unsigned NOT NULL, 
-                 `name_last` varchar(100) NOT NULL default '', + `gender` varchar(10) default 'F', 
-                 `favorite_color` varchar(100) NOT NULL default '', +                `name_first` varchar(100) NOT NULL default '', 
-                 PRIMARY KEY  (`id`) +                `name_last` varchar(100) NOT NULL default '', 
-             ) ENGINE=MyISAM; +                `favorite_pet` varchar(100) NOT NULL default '', 
-            ");+                PRIMARY KEY  (`id`) 
 +            ) ENGINE=MyISAM; 
 +           ");
  
- $db->Execute("CREATE TEMPORARY TABLE `children` ( +$db->Execute("insert into children (person_id,name_first,name_last) values (1,'Jill','Lim')"); 
-                 `id` int(10) unsigned NOT NULL auto_increment, +$db->Execute("insert into children (person_id,name_first,name_last) values (1,'Joan','Lim')"); 
- `person_id` int(10) unsigned NOT NULL, +$db->Execute("insert into children (person_id,name_first,name_last) values (1,'JAMIE','Lim')");
- `gender` varchar(10) default 'F', +
-                 `name_first` varchar(100) NOT NULL default '', +
-                 `name_last` varchar(100) NOT NULL default '', +
-                 `favorite_pet` varchar(100) NOT NULL default '', +
-                 PRIMARY KEY  (`id`) +
-             ) ENGINE=MyISAM; +
-            ");+
  
- $db->Execute("insert into children (person_id,name_first,name_last) values (1,'Jill','Lim')"); +class person extends ADOdb_Active_Record{} 
- $db->Execute("insert into children (person_id,name_first,name_last) values (1,'Joan','Lim')"); +ADODB_Active_Record::ClassHasMany('person', 'children','person_id');
- $db->Execute("insert into children (person_id,name_first,name_last) values (1,'JAMIE','Lim')");+
  
- class person extends ADOdb_Active_Record{} +$person = new person(); 
- ADODB_Active_Record::ClassHasMany('person', 'children','person_id');+$person->name_first     = 'John'; 
 +$person->name_last      = 'Lim'; 
 +$person->favorite_color = 'lavender'; 
 +$person->save(); // this save will perform an INSERT successfully
  
- $person = new person();+$person2 = new person(); # no need to define HasMany() again, adodb remembers definition 
 +$person2->Load('id=1');
  
- $person->name_first     = 'John'; +$c = $person2->children; 
- $person->name_last      = 'Lim'; +if (is_array($c) && sizeof($c) == 3 && $c[0]->name_first=='Jill' && $c[1]->name_first=='Joan' 
- $person->favorite_color = 'lavender'; + && $c[2]->name_first == 'JAMIE') echo "OK Loaded HasMany<br>"; 
- $person->save(); // this save will perform an INSERT successfully +else { 
- +    echo "Error loading hasMany should have 3 array elements Jill Joan Jamie<br>"; 
- $person2 = new person(); # no need to define HasMany() again, adodb remembers definition +}
- $person2->Load('id=1');+
  
- $c = $person2->children; +===== HasMany =====
- 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>"; +
- } +
-HasMany+
  
 This older method is deprecated and ClassHasMany/TableHasMany/TableKeyHasMany should be used. 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:+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: 
 +<code php> 
 +class person extends ADOdb_Active_Record{}
  
- 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 
 +} 
 +</code>
  
- $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. 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.
 +<code php>
 +$person = new person();
 +$person->HasMany('children','person_id');
  
- $person = new person(); +$person2 = new person(); 
- $person->HasMany('children','person_id'); +$person->Load("id=1"); 
- +$p = $person2->children; 
- $person2 = new person(); +</code> 
- $person->Load("id=1"); +===== ClassBelongsTo =====
- $p = $person2->children; +
-ClassBelongsTo+
  
 You can define the parent of the current object using ADODB_Active_Record::ClassBelongsTo($class, $relationName, $foreignKey, $parentPrimaryKey = 'id', $parentClass = 'ADODB_Active_Record'). 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: You can define the parent of the current object using ADODB_Active_Record::ClassBelongsTo($class, $relationName, $foreignKey, $parentPrimaryKey = 'id', $parentClass = 'ADODB_Active_Record'). 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:
Line 518: Line 559:
  
 Also if no data is loaded into the child instance, then $p will return null; Also if no data is loaded into the child instance, then $p will return null;
 +<code php>
 +ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id');
  
- ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id'); +$ch = new kid(); 
- +$p = $ch->person; # $p is null 
- $ch = new kid(); +</code>
- $p = $ch->person; # $p is null+
 Another way to define the class of the parent (which otherwise defaults to ADODB_Active_Record) as follows: Another way to define the class of the parent (which otherwise defaults to ADODB_Active_Record) as follows:
  
 +<code php>
 +class kid extends ADOdb_Active_Record{};
 +class person extends ADOdb_Active_Record{... your modifications ... };
 +ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id', 'person');
 +</code>
  
- class kid extends ADOdb_Active_Record{}; +===== TableBelongsTo =====
- class person extends ADOdb_Active_Record{... your modifications ... }; +
- ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id', 'person'); +
-TableBelongsTo+
  
 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($childTable, $relationName, $foreignKey, $parentPrimaryKey = 'id', $parentClass = 'ADODB_Active_Record'). 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($childTable, $relationName, $foreignKey, $parentPrimaryKey = 'id', $parentClass = 'ADODB_Active_Record').
Line 535: Line 579:
 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: 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'); + ADODB_Active_Record::TableBelongsTo('children','person','person_id','id'); 
-TableKeyBelongsTo+ 
 +===== TableKeyBelongsTo ====
  
 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($childTable, $childKey, $relationName, $foreignKey, $parentPrimaryKey = 'id', $parentClass = 'ADODB_Active_Record'). 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($childTable, $childKey, $relationName, $foreignKey, $parentPrimaryKey = 'id', $parentClass = 'ADODB_Active_Record').
Line 542: Line 587:
 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: 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'); +ADODB_Active_Record::TableKeyBelongsTo('children','ch_id', 'person','person_id','id'); 
-BelongsTo+ 
 +===== BelongsTo =====
  
 The following is deprecated. Use ClassBelongsTo/TableBelongsTo/TableKeyBelongsTo instead. The following is deprecated. Use ClassBelongsTo/TableBelongsTo/TableKeyBelongsTo instead.
  
 The older way to define the parent of the current object is using BelongsTo($relationName, $foreignKey, $parentPrimaryKey = 'id'). In the example below, we have a child table children, and a parent table person. We have a link children.person_id = persons.id. We create a child first, then link it to the parent: The older way to define the parent of the current object is using BelongsTo($relationName, $foreignKey, $parentPrimaryKey = 'id'). In the example below, we have a child table children, and a parent table person. We have a link children.person_id = persons.id. We create a child first, then link it to the parent:
- +<code> 
- class Child extends ADOdb_Active_Record{}; +class Child extends ADOdb_Active_Record{}; 
- $ch = new Child('children',array('id')); +$ch = new Child('children',array('id')); 
- $ch->BelongsTo('person','person_id','id');  ## this can be simplified to $ch->BelongsTo('person') +$ch->BelongsTo('person','person_id','id');  ## this can be simplified to $ch->BelongsTo('person') 
-                                             ## as foreign key defaults to $table.'_id' and +                                            ## as foreign key defaults to $table.'_id' and 
-                                             ## parent pkey defaults to 'id' +                                            ## parent pkey defaults to 'id' 
- $ch->Load('id=1'); +$ch->Load('id=1'); 
- $p = $ch->person; +$p = $ch->person; 
- if (!$p || $p->name_first != 'John') echo "Error loading belongsTo<br>"; +if (!$p || $p->name_first != 'John') echo "Error loading belongsTo<br>"; 
- else echo "OK loading BelongTo<br>";+else echo "OK loading BelongTo<br>"; 
 +</code>
 You only need to define BelongsTo() once in a script as it is global for all instances. You only need to define BelongsTo() once in a script as it is global for all instances.
  
-LoadRelations +===== LoadRelations =====
- +
-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($relation, $whereOrderBy = '', $offset = -1, $limit = -1):+
  
- # assume this has been called: +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($relation, $whereOrderBy = ' ', $offset = -1, $limit = -1): 
- #   ADODB_Active_Record::ClassHasMany('person', 'children','person_id'); +<code php> 
- $person = new person(); +# assume this has been called: 
- $person->Load('id=23'); +#   ADODB_Active_Record::ClassHasMany('person', 'children','person_id'); 
- # Load doesn't load children until $person->children is accessed or LoadRelations is called: +$person = new person(); 
- $person->LoadRelations('children',"gender='F' order by name");+$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"); 
 +</code>
 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: 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:
  
 +<code php>
 +# assume this has been called:
 +#  ADODB_Active_Record::ClassHasMany('Account', 'transactions','account_id');
 +#acc = new Account();
 +$acc->Load('id=23');
  
- # assume this has been called: +$start = 0; 
- #  ADODB_Active_Record::ClassHasMany('Account', 'transactions','account_id'); +while(true) { 
- $acc = new Account(); +    $acc->LoadRelations('transactions',"tx_done=0 order by trxdate", $start, $start+100); 
- $acc->Load('id=23'); +    if (!$acc->transactions) break; 
- +    foreach ($acc->transactions as $k => $trx) { 
- $start = 0; + ## process 
- while(true) { + $trx->tx_done = 1; 
- $acc->LoadRelations('transactions',"tx_done=0 order by trxdate", $start, $start+100); + $trx->save(); 
- if (!$acc->transactions) break; +    } 
- foreach ($acc->transactions as $k => $trx) { +    $start += 100; 
- ## process +    unset($acc->transactions);
- $trx->tx_done = 1; +
- $trx->save(); +
- } +
- $start += 100; +
- unset($acc->transactions);+
  
- +} 
- }+</code>
 The $offset is 0-based, and $limit is the number of records to retrieve. The default is to ignore $offset (-1) and $limit (-1). The $offset is 0-based, and $limit is the number of records to retrieve. The default is to ignore $offset (-1) and $limit (-1).
  
-Acknowledgements+===== Acknowledgements =====
  
 Thanks to Chris Ravenscroft for original one-to-many code (chris#voilaweb.com). Thanks to Chris Ravenscroft for original one-to-many code (chris#voilaweb.com).
  
-ADOConnection Supplement+===== ADOConnection Supplement =====
  
-ADOConnection::GetActiveRecords()+==== ADOConnection::GetActiveRecords() ====
  
 This allows you to retrieve an array of ADOdb_Active_Records. Returns false if an error occurs. This allows you to retrieve an array of ADOdb_Active_Records. Returns false if an error occurs.
  
 +<code php>
 $table = 'products'; $table = 'products';
 $whereOrderBy = "name LIKE 'A%' ORDER BY Name"; $whereOrderBy = "name LIKE 'A%' ORDER BY Name";
Line 611: Line 660:
  $rec->save();  $rec->save();
 } }
 +</code>
 And to retrieve all records ordered by specific fields: And to retrieve all records ordered by specific fields:
 +<code php>
 $whereOrderBy = "1=1 ORDER BY Name"; $whereOrderBy = "1=1 ORDER BY Name";
 $activeRecArr = $db->GetActiveRecords($table); $activeRecArr = $db->GetActiveRecords($table);
-To use bind variables (assuming ? is the place-holder for your database):+//To use bind variables (assuming ? is the place-holder for your database):
  
 $activeRecArr = $db->GetActiveRecords($tableName, 'name LIKE ?', $activeRecArr = $db->GetActiveRecords($tableName, 'name LIKE ?',
Line 624: Line 674:
  array('A%'), array('id'));  array('A%'), array('id'));
 ADOConnection::GetActiveRecordsClass() ADOConnection::GetActiveRecordsClass()
 +</code>
 This allows you to retrieve an array of objects derived from ADOdb_Active_Records. Returns false if an error occurs. This allows you to retrieve an array of objects derived from ADOdb_Active_Records. Returns false if an error occurs.
  
 +<code php>
 class Product extends ADOdb_Active_Record{}; class Product extends ADOdb_Active_Record{};
 $table = 'products'; $table = 'products';
Line 637: Line 688:
  $rec->save();  $rec->save();
 } }
 +</code>
 To use bind variables (assuming ? is the place-holder for your database): To use bind variables (assuming ? is the place-holder for your database):
 +<code php>
 $activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?', $activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?',
  array('A%'));  array('A%'));
Line 645: Line 697:
 $activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?', $activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?',
  array('A%'), array('id'));  array('A%'), array('id'));
 +
 +</code>
 ADOConnection::ErrorMsg() ADOConnection::ErrorMsg()
  
Line 655: Line 709:
 ===== ActiveRecord Code Sample ===== ===== ActiveRecord Code Sample =====
  
-<?php+<code php>
 include('../adodb.inc.php'); include('../adodb.inc.php');
 include('../adodb-active-record.inc.php'); include('../adodb-active-record.inc.php');
Line 959: Line 1013:
 PHP5 specific: Make GetActiveRecords*() return an Iterator. PHP5 specific: Make GetActiveRecords*() return an Iterator.
  
-PHP5 specific: Change PHP5 implementation of Active Record to use __get() and __set() for better performance.+PHP5 specific: Change PHP5 implementation of Active Record to use <code php>__get() and __set()</code> for better performance.
v5/userguide/active_record.1449255828.txt.gz · Last modified: 2017/04/21 11:39 (external edit)