INSERT, UPDATE, DELETE statements with Zend

Continuing the Zend_DB article series, we are stopping now at DML statements.
DML (Data Manipulation Language) statements are statements that change data values in database tables. There are 3 primary DML statements:

INSERT - Inserting new rows into database tables.
UPDATE - Updating existing rows in database tables .
DELETE - Deleting existing rows from database tables.

Note*:

$db = Zend_Db::factory('Pdo_Mysql', $dbConnect);

INSERT

INSERT INTO user(email, password, firstName, lastName, active)
       VALUES ('$email', '$password', '$firstName', '$lastName', 1);

The above SQL INSERT statement is translated in Zend_Db as follow:

$data = array( 'email' => $email,
            'password' => $password,
            'firstName' => $firstName,
            'lastName' => $lastName,
            'active' => '1');
$db->insert('user', $data);

UPDATE

UPDATE user
   SET password = '$password',
       firstName = '$firstName',
       lastName = '$lastName',
       accountUpdate = (accountUpdate +1)
 WHERE id = '$id'

The above SQL UPDATE statemnet is translated in Zend_Db as follow:

$data = array('password' => $password,
              'firstName' => $firstName,
              'lastName' => $vlastname,
              'accountUpdate' => new Zend_Db_Expr('accountUpdate+1'));
$db->update('user', $data, 'id = '.$id);

DELETE

DELETE FROM user WHERE id = '$id'

The above SQL DELETE statemnet is translated in Zend_Db as follow:

$db->delete('user', 'id = '.$id);

Frequently Asked Questions

What are DML statements? +

DML (Data Manipulation Language) statements are statements that change data values in database tables. There are 3 primary DML statements: INSERT, UPDATE, and DELETE.

How do you insert a new row with Zend_Db? +

Build an associative array of column names to values (e.g. email, password, firstName, lastName, active) and pass it to $db->insert('user', $data), which corresponds to an SQL INSERT INTO ... VALUES statement.

How do you update rows with Zend_Db, including incrementing a column? +

Build a $data array of the columns to update, using a Zend_Db_Expr for expressions such as incrementing accountUpdate (new Zend_Db_Expr('accountUpdate+1')), then call $db->update('user', $data, 'id = '.$id).

How do you delete a row with Zend_Db? +

Call $db->delete('user', 'id = '.$id), which is equivalent to the SQL statement DELETE FROM user WHERE id = '$id'.

Zend Framework Zend_Db

INSERT, UPDATE, DELETE statements with Zend_Db

Frequently Asked Questions