INSERT, UPDATE, DELETE statements with Zend_Db

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);