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

Writing Changes to the Database

You can use the Adapter class to write new data or change existing data in your database. This section describes methods to do these operations.

Inserting Data

You can add new rows to a table in your database using the insert() method. The first argument is a string that names the table, and the second argument is an associative array, mapping column names to data values.

Example 202. Inserting in a Table

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

$db->insert('bugs'$data);

Columns you exclude from the array of data are not specified to the database. Therefore, they follow the same rules that an SQL INSERT statement follows: if the column has a DEFAULT clause, the column takes that value in the row created, otherwise the column is left in a NULL state.

By default, the values in your data array are inserted using parameters. This reduces risk of some types of security issues. You don't need to apply escaping or quoting to values in the data array.

You might need values in the data array to be treated as SQL expressions, in which case they should not be quoted. By default, all data values passed as strings are treated as string literals. To specify that the value is an SQL expression and therefore should not be quoted, pass the value in the data array as an object of type Zend_Db_Expr instead of a plain string.

Example 203. Inserting Expressions in a Table

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

$db->insert('bugs'$data);

Retrieving a Generated Value

Some RDBMS brands support auto-incrementing primary keys. A table defined this way generates a primary key value automatically during an INSERT of a new row. The return value of the insert() method is not the last inserted ID, because the table might not have an auto-incremented column. Instead, the return value is the number of rows affected (usually 1).

If your table is defined with an auto-incrementing primary key, you can call the lastInsertId() method after the insert. This method returns the last value generated in the scope of the current database connection.

Example 204. Using lastInsertId() for an Auto-Increment Key

<?php
$db
->insert('bugs'$data);

// return the last value generated by an auto-increment column
$id $db->lastInsertId();

Some RDBMS brands support a sequence object, which generates unique values to serve as primary key values. To support sequences, the lastInsertId() method accepts two optional string arguments. These arguments name the table and the column, assuming you have followed the convention that a sequence is named using the table and column names for which the sequence generates values, and a suffix "_seq". This is based on the convention used by PostgreSQL when naming sequences for SERIAL columns. For example, a table "bugs" with primary key column "bug_id" would use a sequence named "bugs_bug_id_seq".

Example 205. Using lastInsertId() for a Sequence

<?php
$db
->insert('bugs'$data);

// return the last value generated by sequence 'bugs_bug_id_seq'.
$id $db->lastInsertId('bugs''bug_id');

// alternatively, return the last value generated by sequence 'bugs_seq'.
$id $db->lastInsertId('bugs');

If the name of your sequence object does not follow this naming convention, use the lastSequenceId() method instead. This method takes a single string argument, naming the sequence literally.

Example 206. Using lastSequenceId()

<?php
$db
->insert('bugs'$data);

// return the last value generated by sequence 'bugs_id_gen'.
$id $db->lastSequenceId('bugs_id_gen');

For RDBMS brands that don't support sequences, including MariaDB, MySQL, Microsoft SQL Server, and SQLite, the arguments to the lastInsertId() method are ignored, and the value returned is the most recent value generated for any table by INSERT operations during the current connection. For these RDBMS brands, the lastSequenceId() method always returns NULL.

Why Not Use "SELECT MAX(id) FROM table"?

Sometimes this query returns the most recent primary key value inserted into the table. However, this technique is not safe to use in an environment where multiple clients are inserting records to the database. It is possible, and therefore is bound to happen eventually, that another client inserts another row in the instant between the insert performed by your client application and your query for the MAX(id) value. Thus the value returned does not identify the row you inserted, it identifies the row inserted by some other client. There is no way to know when this has happened.

Using a strong transaction isolation mode such as "repeatable read" can mitigate this risk, but some RDBMS brands don't support the transaction isolation required for this, or else your application may use a lower transaction isolation mode by design.

Furthermore, using an expression like "MAX(id)+1" to generate a new value for a primary key is not safe, because two clients could do this query simultaneously, and then both use the same calculated value for their next INSERT operation.

All RDBMS brands provide mechanisms to generate unique values, and to return the last value generated. These mechanisms necessarily work outside of the scope of transaction isolation, so there is no chance of two clients generating the same value, and there is no chance that the value generated by another client could be reported to your client's connection as the last value generated.

Updating Data

You can update rows in a database table using the update() method of an Adapter. This method takes three arguments: the first is the name of the table; the second is an associative array mapping columns to change to new values to assign to these columns.

The values in the data array are treated as string literals. See this section for information on using SQL expressions in the data array.

The third argument is a string containing an SQL expression that is used as criteria for the rows to change. The values and identifiers in this argument are not quoted or escaped. You are responsible for ensuring that any dynamic content is interpolated into this string safely. See this section for methods to help you do this.

The return value is the number of rows affected by the update operation.

Example 207. Updating Rows

<?php
$data 
= array(
    
'updated_on'      => '2007-03-23',
    
'bug_status'      => 'FIXED'
);

$n $db->update('bugs'$data'bug_id = 2');

If you omit the third argument, then all rows in the database table are updated with the values specified in the data array.

If you provide an array of strings as the third argument, these strings are joined together as terms in an expression separated by AND operators.

If you provide an array of arrays as the third argument, the values will be automatically quoted into the keys. These will then be joined together as terms, separated by AND operators.

Example 208. Updating Rows Using an Array of Expressions

<?php
$data 
= array(
    
'updated_on'      => '2007-03-23',
    
'bug_status'      => 'FIXED'
);

$where[] = "reported_by = 'goofy'";
$where[] = "bug_status = 'OPEN'";

$n $db->update('bugs'$data$where);

// Resulting SQL is:
//  UPDATE "bugs" SET "update_on" = '2007-03-23', "bug_status" = 'FIXED'
//  WHERE ("reported_by" = 'goofy') AND ("bug_status" = 'OPEN')

Example 209. Updating Rows Using an Array of Arrays

<?php
$data 
= array(
    
'updated_on'      => '2007-03-23',
    
'bug_status'      => 'FIXED'
);

$where['reported_by = ?'] = 'goofy';
$where['bug_status = ?']  = 'OPEN';

$n $db->update('bugs'$data$where);

// Resulting SQL is:
//  UPDATE "bugs" SET "update_on" = '2007-03-23', "bug_status" = 'FIXED'
//  WHERE ("reported_by" = 'goofy') AND ("bug_status" = 'OPEN')

Deleting Data

You can delete rows from a database table using the delete() method. This method takes two arguments: the first is a string naming the table.

The second argument is a string containing an SQL expression that is used as criteria for the rows to delete. The values and identifiers in this argument are not quoted or escaped. You are responsible for ensuring that any dynamic content is interpolated into this string safely. See this section for methods to help you do this.

The return value is the number of rows affected by the delete operation.

Example 210. Deleting Rows

<?php
$n 
$db->delete('bugs''bug_id = 3');

If you omit the second argument, the result is that all rows in the database table are deleted.

If you provide an array of strings as the second argument, these strings are joined together as terms in an expression separated by AND operators.

If you provide an array of arrays as the second argument, the values will be automatically quoted into the keys. These will then be joined together as terms, separated by AND operators.

Zend Framework