PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

Inserting Rows to a Table

You can use the Table object to insert rows into the database table on which the Table object is based. Use the insert() method of your Table object. The argument is an associative array, mapping column names to values.

Example 276. Example of inserting to a Table

<?php
$table 
= new Bugs();

$data = array(
    
'created_on'      => '2007-03-22',
    
'bug_description' => 'Something wrong',
    
'bug_status'      => 'NEW'
);

$table->insert($data);

By default, the values in your data array are inserted as literal values, using parameters. If you need them to be treated as SQL expressions, you must make sure they are distinct from plain strings. Use an object of type Zend_Db_Expr to do this.

Example 277. Example of inserting expressions to a Table

<?php
$table 
= new Bugs();

$data = array(
    
'created_on'      => new Zend_Db_Expr('CURDATE()'),
    
'bug_description' => 'Something wrong',
    
'bug_status'      => 'NEW'
);

In the examples of inserting rows above, it is assumed that the table has an auto-incrementing primary key. This is the default behavior of Zend_Db_Table_Abstract, but there are other types of primary keys as well. The following sections describe how to support different types of primary keys.

Using a Table with an Auto-incrementing Key

An auto-incrementing primary key generates a unique integer value for you if you omit the primary key column from your SQL INSERT statement.

In Zend_Db_Table_Abstract, if you define the protected variable $_sequence to be the Boolean value TRUE, then the class assumes that the table has an auto-incrementing primary key.

Example 278. Example of declaring a Table with auto-incrementing primary key

<?php
class Bugs extends Zend_Db_Table_Abstract
{
    protected 
$_name 'bugs';

    
// This is the default in the Zend_Db_Table_Abstract class;
    // you do not need to define this.
    
protected $_sequence true;
}

MySQL, Microsoft SQL Server, and SQLite are examples of RDBMS brands that support auto-incrementing primary keys.

PostgreSQL has a SERIAL notation that implicitly defines a sequence based on the table and column name, and uses the sequence to generate key values for new rows. IBM DB2 has an IDENTITY notation that works similarly. If you use either of these notations, treat your Zend_Db_Table class as having an auto-incrementing column with respect to declaring the $_sequence member as TRUE.

Using a Table with a Sequence

A sequence is a database object that generates a unique value, which can be used as a primary key value in one or more tables of the database.

If you define $_sequence to be a string, then Zend_Db_Table_Abstract assumes the string to name a sequence object in the database. The sequence is invoked to generate a new value, and this value is used in the INSERT operation.

Example 279. Example of declaring a Table with a sequence

<?php
class Bugs extends Zend_Db_Table_Abstract
{
    protected 
$_name 'bugs';

    protected 
$_sequence 'bug_sequence';
}

Oracle, PostgreSQL, and IBM DB2 are examples of RDBMS brands that support sequence objects in the database.

PostgreSQL and IBM DB2 also have syntax that defines sequences implicitly and associated with columns. If you use this notation, treat the table as having an auto-incrementing key column. Define the sequence name as a string only in cases where you would invoke the sequence explicitly to get the next key value.

Using a Table with a Natural Key

Some tables have a natural key. This means that the key is not automatically generated by the table or by a sequence. You must specify the value for the primary key in this case.

If you define the $_sequence to be the Boolean value FALSE, then Zend_Db_Table_Abstract assumes that the table has a natural primary key. You must provide values for the primary key columns in the array of data to the insert() method, or else this method throws a Zend_Db_Table_Exception.

Example 280. Example of declaring a Table with a natural key

<?php
class BugStatus extends Zend_Db_Table_Abstract
{
    protected 
$_name 'bug_status';

    protected 
$_sequence false;
}

Note

All RDBMS brands support tables with natural keys. Examples of tables that are often declared as having natural keys are lookup tables, intersection tables in many-to-many relationships, or most tables with compound primary keys.

Zend Framework