ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools

Trace:
v5:activerecord:active_record_tutorial

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
activerecord:active_record_tutorial [2015/12/08 04:58] – [Retrieval of Auto-incrementing ID] mnewnhamv5:activerecord:active_record_tutorial [2022/06/09 18:37] (current) – ↷ Links adapted because of a move operation 3.83.52.90
Line 6: Line 6:
 </WRAP> </WRAP>
 ===== Overview ===== ===== Overview =====
-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 [[activerecord:code_snippet_1|here]], in order to initialize the tables.+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 [[v5:activerecord:code_snippet_1|here]], in order to initialize the tables.
  
  
Line 34: Line 34:
   "favorite_color".    "favorite_color". 
      
-Each of these fields is now a property of the $person object. To see all these properties, use the [[activerecord:getattributenames|ADOdb_Active_Record::getAttributeNames()]] method+Each of these fields is now a property of the $person object. To see all these properties, use the [[v5:activerecord:getattributenames|ADOdb_Active_Record::getAttributeNames()]] method
  
 <code php> <code php>
Line 58: Line 58:
 <WRAP right box round> <WRAP right box round>
 == See Also == == See Also ==
-[[userguide:error_handling|Error Handling In ADOdb]]+[[v5:userguide:error_handling|Error Handling In ADOdb]]
 </WRAP> </WRAP>
 ===== Inserting and Updating a Record ===== ===== Inserting and Updating a Record =====
Line 64: Line 64:
 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". 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 [[activerecord:save|save()]] method:+To insert a new record into the database, change the object's properties and then call the [[v5:activerecord:save|save()]] method:
  
 <code php> <code php>
Line 78: Line 78:
   1048: Column 'favorite_color' cannot be null   1048: Column 'favorite_color' cannot be null
    
-This error occurred because MySQL rejected the INSERT query that was generated by ADOdb_Active_Record.+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 [[activerecod:save()]] method: 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 [[activerecod:save()]] method:
Line 105: Line 105:
 </code> </code>
  
-From this point on, updating it is simply a matter of changing the object's properties and calling the [[activerecord:save()]] method again:+From this point on, updating it is simply a matter of changing the object's properties and calling the [[v5:activerecord:save]] method again:
  
 <code php> <code php>
Line 132: Line 132:
   $person = new person();   $person = new person();
  
-===== Using $ADODB_ASSOC_CASE ==== +===== Using ADODB_ASSOC_CASE ==== 
-You can use [[reference:$ADODB_ASSOC_CASE]] to control the field names+ 
 +You can define the [[v5:reference:adodb_assoc_case]] constant to control the field names' case
  
 <code php> <code php>
-$ADODB_ASSOC_CASE = 0;+define('ADODB_ASSOC_CASE', ADODB_ASSOC_CASE_LOWER); 
 +require "adodb.inc.php";
 $person = new person('People'); $person = new person('People');
 $person->name = 'Lily'; $person->name = 'Lily';
-$ADODB_ASSOC_CASE = 2;+ 
 +define('ADODB_ASSOC_CASE', ADODB_ASSOC_CASE_UPPER); 
 +require "adodb.inc.php";
 $person2 = new person('People'); $person2 = new person('People');
 $person2->NAME = 'Lily'; $person2->NAME = 'Lily';
Line 146: Line 150:
 ===== Saving Records ==== ===== Saving Records ====
  
-The method [[activerecord:save()]] saves a record by executing an INSERT or UPDATE SQL statement as appropriate.+The method [[v5:activerecord:save]] saves a record by executing an INSERT or UPDATE SQL statement as appropriate.
  
 ==== Replacing Records ==== ==== Replacing Records ====
  
-The method [[activerecord:replace()]] supports replace functionality, whereby the record is inserted if it does not exists, or updated otherwise.+The method [[v5:activerecord:replace]] supports replace functionality, whereby the record is inserted if it does not exists, or updated otherwise.
 <code php> <code php>
 $rec = new ADOdb_Active_Record("product"); $rec = new ADOdb_Active_Record("product");
Line 162: Line 166:
 <WRAP right box 200px> <WRAP right box 200px>
 == More Information == == More Information ==
-For more information on bind variables, see [[reference:execute()]]+For more information on bind variables, see [[v5:reference:connection:execute]]
 </WRAP> </WRAP>
 ===== Loading Individual Records ===== ===== Loading Individual Records =====
  
-Sometimes, we want to load a single record into an Active Record using the [[activerecord:load()]] method.+Sometimes, we want to load a single record into an Active Record using the [[v5:activerecord:load]] method.
  
 <code php> <code php>
Line 175: Line 179:
  
 ====== Loading Record Sets ====== ====== Loading Record Sets ======
-An array of active records based on some search criteria can be loaded using the [[activerecord:find()]] method.+An array of active records based on some search criteria can be loaded using the [[v5:activerecord:find]] method.
  
 <code php> <code php>
Line 187: Line 191:
 ===== Quoting Identifiers ===== ===== Quoting Identifiers =====
  
-You can force column names to be quoted by use of the [[activerecord:$_quoteNames]] variable.+You can force column names to be quoted by use of the [[v5:activerecord:quotenames]] variable.
  
 ===== Error Handling and Debugging ===== ===== Error Handling and Debugging =====
-The ADOdb Active Record can be used in conjunction with [[userguide:error_handling|ADOdb error handling]].+The ADOdb Active Record can be used in conjunction with [[v5:userguide:error_handling|ADOdb error handling]].
  
 <code php> <code php>
Line 200: Line 204:
 </code> </code>
  
-The ADOConnection::Debug property is obeyed. So if [[reference:debug|$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 [[v5:userguide:debug|$db->debug]] is enabled, then ADOdb_Active_Record errors are also outputted to standard output and written to the browser.
  
 ===== Converting a recordset to an Active Record object ===== ===== Converting a recordset to an Active Record object =====
-You can convert an array to an ADOdb_Active_Record using [[activerecord: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 [[v5:activerecord: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> <code php>
Line 250: Line 254:
 ===== Retrieval of Auto-incrementing ID ===== ===== 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 [[reference:insert_id()]] function call (check ''$connection->hasInsertID''). In this case we perform a +When creating a new record, the retrieval of the last auto-incrementing ID is not reliable for databases that do not support the [[v5:reference:connection:insert_id]] function call (check ''$connection->hasInsertID''). In this case we perform a 
  
   SELECT MAX($primarykey) FROM $table   SELECT MAX($primarykey) FROM $table
      
-which will not work reliably in a multi-user environment. You can override the [[activerecord:lastinsertid|lastInsertID()]] function in this case.+which will not work reliably in a multi-user environment. You can override the [[v5:activerecord:lastinsertid|lastInsertID()]] function in this case.
  
 ===== Dealing with Multiple Databases ===== ===== Dealing with Multiple Databases =====
Line 280: Line 284:
   $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 [[activerecord:setDatabaseAdaptor()]], allowing to determine in your class definition which database to load, using var [[activerecord:$_dbat]].+You can now give a named label in [[v5:activerecord:setdatabaseadapter]], allowing to determine in your class definition which database to load, using var [[v5:activerecord:dbat]].
  
 <code php> <code php>
Line 315: Line 319:
 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 =====
 +<WRAP right box 200px> 
 +More about [[v5:reference:reference_index#transaction_scoping|Transaction Handling]] 
 +</WRAP>
 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 334: Line 341:
 ===== 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 [[v5:activerecord: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:
  
 <code php> <code php>
Line 405: Line 412:
 var_dump($p->children); var_dump($p->children);
 </code> </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 =====
Line 411: Line 421:
 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 [[activerecord:tablehasmany|ADODB_Active_Record::TableHasMany()]] like this:+Then you use the following static function [[v5:activerecord:tablehasmany|ADODB_Active_Record::TableHasMany()]] like this:
  
   ADODB_Active_Record::TableHasMany('people', 'children', 'person_id')   ADODB_Active_Record::TableHasMany('people', 'children', 'person_id')
Line 417: Line 427:
 ===== 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. This issue can be managed with the [[activerecord:tableKeyHasMany()]] method+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 [[v5:activerecord:tablekeyhasmany]] method
  
 Here is sample usage using mysql: Here is sample usage using mysql:
 <code php> <code php>
  
-$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)  
-$db->Execute("insert into children (person_id,name_first,name_last) values (1,'Joan','Lim')"); +              values (1,'Jill','Lim')"); 
-$db->Execute("insert into children (person_id,name_first,name_last) values (1,'JAMIE','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{} class person extends ADOdb_Active_Record{}
Line 435: Line 448:
 $person->save(); // this save will perform an INSERT successfully $person->save(); // this save will perform an INSERT successfully
  
-$person2 = new person(); # no need to define HasMany() again, adodb remembers definition+/* 
 +* no need to define HasMany() again, adodb remembers definition 
 +*/ 
 +$person2 = new person(); 
 $person2->Load('id=1'); $person2->Load('id=1');
  
 $c = $person2->children; $c = $person2->children;
-if (is_array($c) && sizeof($c) == 3 && $c[0]->name_first=='Jill' && $c[1]->name_first=='Joan' +if (is_array($c)  
- && $c[2]->name_first == 'JAMIE') echo "OK Loaded HasMany<br>";+&& sizeof($c) == 3  
 +&& $c[0]->name_first=='Jill'  
 +&& $c[1]->name_first=='Joan' 
 +&& $c[2]->name_first == 'JAMIE')  
 +    echo "OK Loaded HasMany<br>";
 else { else {
-    echo "Error loading hasMany should have 3 array elements Jill Joan Jamie<br>";+    echo "Error loading hasMany should have  
 +          3 array elements Jill Joan Jamie<br>";
 } }
 </code>  </code> 
Line 449: Line 470:
 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> <code php>
 class person extends ADOdb_Active_Record{} class person extends ADOdb_Active_Record{}
Line 476: Line 501:
 ===== ClassBelongsTo ===== ===== 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 [[v5:activerecord: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: 
 <code php> <code php>
 class kid extends ADOdb_Active_Record{}; class kid extends ADOdb_Active_Record{};
Line 511: Line 537:
 ===== TableBelongsTo ===== ===== 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: [[activerecord:tablebelongsto|ADODB_Active_Record::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: [[v5:activerecord:tablebelongsto|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: 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:
Line 519: Line 545:
 ===== 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: [[activerecord:tablekeybelongsto|ADODB_Active_Record::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: [[v5:activerecord:tablekeybelongsto|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: 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:
Line 527: Line 553:
 ===== 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 [[activerecord: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 [[v5:activerecord:loadrelations]]:
 <code php> <code php>
  
Line 534: Line 560:
 $person = new person(); $person = new person();
 $person->Load('id=23'); $person->Load('id=23');
-# Load doesn't load children until $person->children is accessed or LoadRelations is called:+/* 
 +*  Load doesn't load children until $person->children 
 +* is accessed or LoadRelations is called: 
 +*/
 $person->LoadRelations('children',"gender='F' order by name"); $person->LoadRelations('children',"gender='F' order by name");
 </code> </code>
Line 548: Line 577:
 $start = 0; $start = 0;
 while(true) { while(true) {
-    $acc->LoadRelations('transactions',"tx_done=0 order by trxdate", $start, $start+100); +    $acc->LoadRelations('transactions', 
-    if (!$acc->transactions) break; +                        "tx_done=0 order by trxdate",  
-    foreach ($acc->transactions as $k => $trx) {+                        $start,  
 +                        $start+100); 
 +     
 +    if (!$acc->transactions)  
 +        break; 
 +    foreach ($acc->transactions as $k => $trx)  
 +    {
  ## process  ## process
  $trx->tx_done = 1;  $trx->tx_done = 1;
Line 563: Line 598:
  
  
-All of the code snippets above have been pulled together into a single procedure [[activerecord:code_snippet_2|Here]].+All of the code snippets above have been pulled together into a single procedure [[v5:activerecord:code_snippet_2|Here]].
 ====== Extended Active Record ====== ====== Extended Active Record ======
-See the [[activerecord:Extended Active Record Tutorial]]+See the [[v5:activerecord:extended_active_record_tutorial]]
  
  
v5/activerecord/active_record_tutorial.1449547100.txt.gz · Last modified: 2017/04/21 11:22 (external edit)