Defining Columns

The $col property is an associative array in which each key is a column name, and each value is an associative array containing a column definition. The value of the required 'type' element of a column definition must be the name of one of the DB_Table data types, e.g., 'integer', 'decimal', 'boolean', etc. The 'char' and 'varchar' string types, and the 'decimal' numerical type, all require a 'size' element, for which the value is an integer number of characters or digits. The 'decimal' type also requires a 'scope' element, which is the number of digits after the decimal point. A 'require' element with a boolean true value is equivalent to NOT NULL in SQL, and indicates that NULL values are not allowed in that column.

As an example, let's say we're going to create a GuestBook table. This table will store the first and last name of visitors, their email address, and the date and time they signed the guestbook. In addition, we're going to want a unique ID for each row. The columns in our table will be:

• id -- a sequential integer that is unique for every row (required; i.e., no nulls allowed)

• fname -- a string of up to 32 characters, the first name of the visitor

• lname -- a string of up to 64 characters, the last name of the visitor

• email -- a string of up to 255 characters, the visitor's email address (required; i.e., no nulls allowed)

• signdate -- a date for when the visitor signed the guestbook (required; i.e., no nulls allowed)

These column definitions must be declared in the $col property array. Shown below is the required declaration of $col for a subclass of DB_Table, named GuestBook_Table, that is associated with a database table named GuestBook:

<?php
 class GuestBook_Table extends DB_Table
{

    var $col = array(
             
        // unique row ID
        'id' => array(
            'type'    => 'integer',
            'require' => true
        ),
                                                                     
        // first name
        'fname' => array( 
            'type' => 'varchar', 
            'size' => 32
        ),

        // last name 
        'lname' => array( 
            'type' => 'varchar', 
            'size' => 64
        ),

        // email address 
        'email' => array( 
            'type' => 'varchar', 
            'size' => 128, 
            'require' => true
        ),

        // date signed 
        'signdate' => array( 
            'type' => 'date', 
            'require' => true)
     );
?>

Defining Indices

The $idx property array is used to declare indices. Each index can be declared to be of type 'primary', 'unique', or 'normal'. Primary and unique indices both define constraints that require that each key value be unique within the table. Normal indices are introduced purely for efficiency. Both single- and multi-column indices may be defined.

In our GuestBook example, we want two single column indices: First, we will define a primary index for the "id" identifier column. Second, for purposes of the example, we'll define a normal index on the "signdate" column, because we expect to search for visitors by date and time. The required $idx property declaration is given below:

<?php
 class GuestBook_Table extends DB_Table
{
    
    // Column definitions - see above 
    // var $col = array(...);
       
    // Index definitions             
    var $idx = array( 
    
        'id' => array( 
            'type' => 'primary', 
            'cols' => 'id'
        ), 
        
        'signdate' => array( 
            'type' => 'normal', 
            'cols' => 'signdate'
        )
    ); 
} 
?>

The key of each element in the $idx array is an index name, and the value is generally an array containing a definition for the index. An index definition array must contain a 'type' element, for which the value must be 'primary', 'unique' or 'normal', and 'cols' element. The value of 'cols' element must be either the name of a single column, for a single-column array, or a sequential array of column names, for a multi-column index. There is also an alternative shorthand syntax for single-column indices, which is discussed below.

As an example of a multi-column index, here is the definition for a multi-column index named "namefl" on the fname and lname columns.

<?php
 class GuestBook_Table extends DB_Table
{
    // Column definitions
    // var $col = array( ... )

    // Index definitions
    var $idx = array(

            // above single-column indices, plus

            'namefl' = array(
                'type' => 'normal',
                'cols' => array('fname', 'lname')
            )
        );
}
?>

There is also a shorthand way to declare a single-column index: If you want single-column index for which the index name is the same as the column name, use the column name as the key of $idx, and the index type string (rather than an index definition array) as the value.

<?php
 class GuestBook_Table extends DB_Table
{
    
    // Column definitions 
    // var $col = array(...);
       
    // Index definitions             
    var $idx = array( 
   
        // primary index called 'id' based on the 'id' column 
        'id' => 'primary',
        
        // normal index called 'signdate' based on the 'signdate' column 
        'signdate' => 'normal'

    ); 
} 
?>

Declaring an Auto-Increment Column

One integer column of each table may be declared to be an auto-increment column. This column is normally a primary key identifier. The only effect of this declaration is to change the behavior of the insert() method: If the insert() method is used to insert a row in which the value of an auto-increment column is not set or NULL, then an auto-incremented integer will be inserted automatically.

A column is declared to be auto-increment by setting the value of the $auto_inc_col property to the column name. In the following example, we declare the "id" primary key column of the GuestBook table to be auto-increment:

<?php
 class GuestBook_Table extends DB_Table
{
    
    // Column definitions 
    // var $col = array(...);
       
    // Index definitions             
    var $idx = array(...);

    // Auto-increment declaration
    var $auto_inc_col = 'id';

}

?>

Inserting Rows

To insert a row into the GuestBook table, you use the insert() method. The only parameter is an associative array in which the keys are column names, and the values are column values to be inserted.

<?php

// [snip] create the $GuestBook object

// assign fields and values
$row = array(
    'fname'    => 'Thomas',
    'lname'    => 'Anderson',
    'signdate' => '2003-10-12',
    'email'    => 'neo@matrix.net'
);

// insert into the table and print results
$result = $GuestBook->insert($row);
if (PEAR::isError($result)) {
    // Error handling code
}

?>

By default, the insert method will automatically validate that the data you are inserting is of the expected type, and that values have been provided for all required columns other than auto-increment columns. If either of these validations fails, the method returns a PEAR Error, and does not attempt to insert a row. In the above example, no value has been provided for the required 'id' column because this is an auto-increment column, as discussed below.

Auto-Increment Columns and Sequences

DB_Table can use sequences created by the underlying DB or MDB2 layer to generate auto-increment integer identifiers. If the value of a column that has been declared to be auto-increment is not set or set to PHP NULL in the array of values that is passed to the insert method, then an auto-incremented sequence value will be generated and inserted for that column. In the above example, a sequence value is generated and inserted for the 'id' column. If an integer value is set for such a column, however, the provided value will instead be inserted.

The sequence generation features of DB or MDB2 may also be accessed explictly via the DB_Table::nextID() method. This method is simply a wrapper that calls the corresponding DB or MDB2 method internally. The following example shows how to use nextID() to explicitly generate and insert an auto-incremented value for an identifier.

<?php

// [snip] create the $GuestBook object

// get the next ID in a sequence associated with the table
$id = $GuestBook->nextID();

// assign fields and values
$row = array(
    'id'       => $id,
    'fname'    => 'Thomas',
    'lname'    => 'Anderson',
    'signdate' => '2003-10-12',
    'email'    => 'neo@matrix.net'
);

// insert into the table and print results
$result = $GuestBook->insert($row);
if (PEAR::isError($result)) {
    // ... error handling code ...
}

?>

Updating Rows

The update() method is used to update the data in a row or a set of rows. The method takes two parameters. Its first parameter is an associative array in which keys are names of columns to be updated, and values are new data values. The second is the text of the WHERE clause of the corresponding SQL UPDATE statement, excluding the WHERE keyword.

For example to change all the rows with the last name "Smith" to "Jones":

<?php

// [snip] create the $GuestBook object

$values = array(
    'lname' => 'Jones'
    );

// assign the WHERE clause
$where = "lname = 'Smith'";

// attempt the update 
$result = $GuestBook->update($values, $where);
if (PEAR::isError($result)) {
    // ... error handling code ...
}

?>

As for insertion, if you attempt to update with values that do not validate against the declared column types, then the update will fail, and the method will return a PEAR Error.

Deleting Rows

The delete() method is used to delete a row or set of rows specified by a logical condition. Its only parameter is a string containing the logical condition of the WHERE clause needed to identify which rows to delete, excluding the WHERE keyword.

For example, to delete all rows that were entered before today:

<?php

// [snip] create the $GuestBook object

// a WHERE clause
$today = date('Y-m-d'); // formatted as yyyy-mm-dd
$where = "signdate < '$today'";

// attempt the update and print the results
$result = $GuestBook->delete($where);
if (PEAR::isError($result)) {
    // ... error handling code ...
}

?>

Query Arrays

Each SQL select statement is defined by a "query array". For most of the allowed elements of a query array, the key is a lower case version of the keyword that begins a clause in the SELECT statement (e.g., 'select', 'from', 'where', etc.), and the value is the text of the remainder of that clause, excluding the keyword. The allowed keys of a query array that define clauses of the SELECT command are:

• 'select' - the SELECT clause (defaults to '*')

• 'from' - the table(s) to select from. (Defaults to the name of this table, $this->table)

• 'join' - any join clauses that should be added to the FROM clause.

• 'where' - the WHERE clause

• 'group' - the GROUP BY clause

• 'having' - the HAVING clause

• 'order' - the ORDER BY clause

The values of the 'select', 'from', 'where', 'group', 'having', and 'order' elements are strings that contain the text of corresponding clause of the desired SQL statement, excluding the keywords SELECT, FROM, WHERE, GROUP BY, HAVING, or ORDER, respectively. The value of the 'join' element, if present, is simply concatenated onto the end of the 'FROM' element, and should include the keywords, e.g., 'JOIN', 'INNER JOIN', or 'LEFT JOIN'.

The other allowed keys of a query array may be used to specify how the results of query should be returned by the select() method. These are:

• 'get' - determines how the select() method will return results. See below for a list of allowed values. (Defaults to 'all').

• 'fetchmode' - determines how rows of a result set will be returned by select() if 'get' is 'all' or not set.

• 'fetchmode_object_class' - The name of the class of the objects that should be used to encapsulate rows if rows are returned as objects.

• 'all' - return all rows of a return set as a sequential array of rows (this is the default). Each row can be returned as either an associative array in which keys are column names (the default) or a sequential array.

• 'assoc' - returns an associative array in which the key is the first column of the return set and the value is the second column.

• 'col' - return only the first column as a sequential array

• 'row' - return only the first row as an associative array in which the keys are column names and the values are the column values.

• 'one' - return only the value of the first column in the first row.

When the 'get' element is set to 'all', explicitly or by default, each row in the array returned by the DB_Table::select() method returned as an associative array in which keys are column names, as a sequential array, or as an object in which column names map to property names. The choice of data structure for each row may be controlled by the 'fetchmode' element of the query or, if this is not is set, by the $fetchmode property of the DB_Table object. Similarly, when rows are returned as objects, the name of the object class may be specified by the 'fetchmode_object_class' element of the query or, if this is not set, by the $fetchmode_object_class property of the DB_Table object.

The values of the 'fetchmode' and 'fetchmode_object_class' elements of the query or the $fetchmode and $fetchmode_object_class properties of DB_Table are used to temporarily reset the values of the 'fetchmode' and 'fetchmode_object_class' properties (for DB) or options (for MDB2) of the underlying DB or MDB2 object. These values should be set equal to the desired DB_FETCHMODE_* or MDB2_FETCHMODE_* constant appropriate for the underlying backend. The original values of the DB/MDB2 object properties or options are restored before the select() method returns.

Storing Queries: $sql property array

Commonly used queries, and "baseline" queries that are useful as a starting point for construction of more complicated ones, may be stored in the $sql public property array. The $sql property is an associative array in which keys are query names, and values are query arrays. Stored queries can be submitted by passing the name key for the query as an argument to any of the select*() methods.

The $sql property is inherited from the DB_Table_Base class. Queries may thus be stored either the $sql property of a DB_Table that provides an interface to a specific table, or in the $sql property of a DB_Table_Database object that provides an interface to the entire database. For applications that involve only one table, or queries that involve only one table, it may be convenient to use the $sql property of the DB_Table object, as in the above example, and to then use the select*() methods of that object to access these queries by name. For more complicated queries and applications, however, it may be more convenient to store all queries in the $sql property of a DB_Table_Database object, and submit them by name via the select*() methods of that object.

<?php
 class GuestBook_Table extends DB_Table
{
 
    // [snip] var $col = array( ... );
    // [snip] var $idx = array( ... );
         
    var $sql = array( 
    
        // multiple rows for a list 
        'list' => array( 
            'select' => "id, signdate, CONCAT(fname, ' ', lname) AS fullname", 
            'order'  => 'signdate DESC'
        ), 

        // one row for an item detail
        'item' => array( 
            'select' => 'id, fname, lname, email, signdate', 
            'get' => 'row'
        ),

        // email => fullname (unique rows only) 
        'emails' => array( 
            'select' => "DISTINCT email, CONCAT(fname, ' ', lname) AS fullname", 
            'order' => 'lname, fname', 
            'get' => 'assoc'
        )           
    );

}

?>

Submitting Queries: select*() Methods

The select() method submits a query and returns the result set as an array. The selectResult() method returns the result as a DB_Result/MDB2_Result_Common object. The selectCount() method returns an integer equal to the number of rows that would be returned by a query, without returning the actual result set.

The interface is the same for all three of these methods. In each, the first parameter, which is required, can be either a query array or a key of the $sql property array, for which the corresponding value is a stored query array. The remaining parameters, which are all optional, may be used to modify the query before submission to the database. The second parameter, $filter, is a string that contains an SQL logical expression that will be ANDed with the WHERE clause of the query before the SELECT command is submitted. The third parameter, $order, if present, is used to construct the 'ORDER BY' clause, and may be used to override the 'order' element of the query. The fourth and fifth parameters, $start and $count, are integers that may be used to specify the first row of the result set that should be returned, and the maximum number of rows to be returned.

<?php

// [snip] create a DB/MDB2 object and connect to the database
// [snip] create the GuestBook_Table object $GuestBook

// Submit the stored 'list' query, which returns an array of rows
$rows = $GuestBook->select('list');

// Print the result set
print_r($rows);

?>

The next example shows how to modify a stored query with the filter, order, start and count parameters:

<?php

$filter = "signdate = '2003-08-14'";
$order = 'lname, fname';
$start = 7;
$count = 12;

// Return results as an array of rows
$rows = $GuestBook->select($view, $filter, $order, $start, $count);
print_r($rows);

// Return results as a DB_Result/MDB2_Result_Common object
$result = $GuestBook->selectResult($view, $filter, $order, $start, $count);
print_r($result);

?>

To have select() return rows as associative arrays, we must set either the 'fetchmode' element of a specific query array or the $fetchmode property of the DB_Table object to the DB_FETCHMODE_ASSOC or MDB2_FETCHMODE_ASSOC constant values, as appropriate:

<?php

// [snip] create the $GuestBook GuestBook_Table object

// set the fetch mode to "associative"
$GuestBook->fetchmode = DB_FETCHMODE_ASSOC;

// now all rows arrays will be returned by DB_Table::select() as associative
// arrays with the column names as the array keys
?>

To have select() return rows as objects, with properties that correspond to column names, we must set either the 'fetchmode' element of the query array or the $fetchmode property of the DB_Table object to the DB_FETCHMODE_OBJECT or MDB2_FETCHMODE_OBJECT constant. If no user-defined class is specified, all rows are returned as instances of stdClass, as in the following example:

<?php

// [snip] create the $GuestBook GuestBook_Table object

// set the fetch mode to the "object" constant for DB or MDB2
$GuestBook->fetchmode = DB_FETCHMODE_OBJECT;

// now each row in the results array will be a stdClass object,
// and the object properties will be named for the columns

// now all column arrays will come with the column names as the array keys
?>

To specify a class to be used to encapsulate rows of a return set, set the value of the 'fetchmode_object_class' element of the query array or the $fetchmode_object_class property to the name of the desired class.

<?php

// [snip] create the $GuestBook GuestBook_Table object

// set the fetch mode to "object"
$GuestBook->fetchmode = DB_FETCHMODE_OBJECT;

// set the fetched row class to "myRowClass" ...
// of course, you will need to include the "myRowClass" file
// before this.
$GuestBook->fetchmode_object_class = 'myRowClass';

// now each row in the results array will be a myRowClass object,
// and the object properties will be named for the columns

?>