ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:userguide:learn_abstraction:record_insertion
 

Inserting And Updating Records

AutoExecute

ADOdb provides a helper routine, autoExecute(). It simplifies the task of inserting and updating records by providing a structured, easy to understand methodology.

Inserting A Record

In order to insert a record, it is sufficient to know:

  1. The name of the table to insert the data into
  2. The name of the fields to fill

In the following example, the table to update is called people, and the field names in the table are first_name and last_name.

/*
* We must declare a variable to hold the name of the table
*/
$table               = 'people';
 
$record = array();
$record["first_name"] = "Carol";
$record["last_name"]  = "Smith"; 
 
$db->autoExecute($table,$record,'INSERT');
 
/*
 * result executes "INSERT INTO people (first_name,last_name) 
                                values ('Carol',Smith')";
 */

AutoExecute helps, because it can automatically determine the type of the field to be created, quoting data appropriately, and can even handle special characters in the fields.

Updating A Record

To update a record, we must also know how to retrieve the record uniquely, for example there might be a sequence number on the table that uniquely identifies each one. In the following example, the requested record is identified by an id number of 305:

$table                = 'people';
 
$record               = array();
$record["first_name"] = "Caroline"; 
$record["last_name"]  = "Jones"; # Update Caroline's lastname from Smith To Jones

$db->autoExecute($table, $record, 'UPDATE', 'id = 305'); 
 
/*
 * result executes "UPDATE people SET first_name='Caroline',
*                                     last_name='Jones' 
*                                     WHERE id=305";
 */

Updating Multiple Records

If the record match statement produces a non-unique result set, then ALL records that match will be updated.

$db->autoExecute($table, $record, 'UPDATE', "last_name like 'Sm%'");

Limitations

AutoExecute is designed to be used as a UX helper, and contains multiple layers of validation which may cause performance issues. For improved performance, use can be made of getInsertSql() and getUpdateSql(). In OLTP environments, consider crafting inserts/updates programatically and using execute()

End Of Lesson

v5/userguide/learn_abstraction/record_insertion.txt · Last modified: 2016/03/20 16:48 by mnewnham