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.
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 196. 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 197. 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);
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 198. 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 199. 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 200. 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.
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 201. 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 202. 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 203. 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')
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.
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.