Niklas Tech Blog

A small cheat sheet with simple examples for the Zend_Db syntax in Zend Framework. A smooth way to write sql that will work on many different databases

Connect to database

$db = Zend_Db::factory('Pdo_Mysql', array(
    'host'     => '127.0.0.1',
    'username' => 'myUser',
    'password' => 'myPassword',
    'dbname'   => 'myDatabase'
));

SELECT statement
eg.

SELECT fname, course, grade FROM students 
   WHERE student_id = {$student_id} ORDER BY grade DESC

is written in Zend_Db syntax like this:

$db->select()
  ->from('students', array('fname', 'course', 'grade'))
  ->where('student_id = ?', $student_id)
  ->order('grade DESC');

If you want to fetch all columns (*), just remove the array(‘name’,…) in the from function

INSERT statement
eg.

INSERT INTO students(id, email, passwrd, fname, address, active) 
   VALUES (id + 1, '{$email}', '{$passwrd}', '{$fname}', '{$address}', 1);

Zend_Db syntax:

$student = array( 'id' => new Zend_Db_Expr('id + 1'),
                  'email' => $email,
		          'passwrd' => $passwrd,
		          'fname' => $fname,
		          'address' => $address,
		          'active' => '1');

$db->insert('students', $student);

UPDATE statement
eq.

UPDATE students
   SET passwrd = '{$passwrd}',
       fname = '{$fname}',
       address = '{$address}',
       active = 1
WHERE id = '{$student_id}'

Zend_Db syntax:

$student = array('passwrd' => $passwrd,
                 'fname' => $fname,
                 'address' => $address,
                 'active' => '1');

$db->update('students', $student, 'id = ' . $student_id);

DELETE statement
eq.

DELETE FROM students WHERE id = '{$student_id}'

Zend_Db syntax:

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

Tested in Zend Framework 1.10.8 on OSX 10.7.4