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
0 Comments.