Blog2Works

This is the blog of freelance website designer and developer Ben Johnson, with thoughts on design, programming, frameworks, jquery and the latest technology.
Viewing entries tagged with 'Zend_Db'
  • SQL INSERT, UPDATE and DELETE queries Zend_Db

    Author: Ben Johnson

    Tags: , ,

    A list of common SQL comands using MYSQL and Zend Framework - Zend_Db   INSERT, UPDATE, DELETE statements with Zend_Db 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. INSERT INSERT INTO user(email, password, firstName, lastName, active)VALUES ('$email', '$password', '$firstName', '$lastName', 1); The above SQL INSERT statement in Zend_Db as follow: $data = array( 'email' => $email,'password' => $password,'firstName' => $firstName,'lastName' => $lastName,'active' => '1');$db->insert('user', $data); UPDATE UPDATE userSET password = '$password',       firstName = '$firstName',       lastName = '$lastName',       accountUpdate = (accountUpdate +1)WHERE id = '$id' The above SQL UPDATE statemnet 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 in Zend_Db as follow: $db->delete('user', 'id = '.$id);


  • SQL SELECT queries Zend_Db

    Author: Ben Johnson

    Tags: , ,

    Zend_Db and its related classes provide a simple SQL database interface for Zend Framework. To connect to MySql database using Pdo_Mysql adapter: $db = Zend_Db::factory('Pdo_Mysql', $dbConnect);    SELECT query – WHERE clause First one is simple, the second one use INNER JOIN keyword SELECT a.id, a.name, b.order_id  FROM users AS a, orders AS bWHERE a.id = b.user_idAND a.id = {$userId}    SELECT `a`.`id`, `a`.`name`, `b`.`order_id`FROM `users` AS `a` INNER JOIN `orders` AS `b` ON a.id = b.user_idWHERE (a.id = '{$userId}')  The above querys in Zend_Db style:   $select = $db->select()->from(array('a'=>'users'),array('a.id', 'a.name'))->join(array('b'=>'orders'), 'a.id = b.user_id', array('b.order_id'))->where('a.id = ?', $userId)  To select any column from the second table, the 3rd parameter of join() method should be an empty string SELECT a.id, a.nameFROM users AS a, orders AS bWHERE a.id = b.user_idAND a.id = {$userId}    $select = $db->select()->from(array('a'=>'users'),array('a.id', 'a.name'))->join(array('b'=>'orders'), 'a.id = b.user_id', '')->where('a.id = ?', $userId)    SELECT a.id, a.name, b.*FROM users AS a, orders AS bWHERE a.id = b.user_idAND a.id = {$user_id}    $select = $db->select()->from(array('a'=>'users'),array('a.id', 'a.name'))->join(array('b'=>'orders'), 'a.id = b.user_id')->where('a.id = ?', $userId)    SELECT query – WHERE IN clause SELECT id FROM users WHERE aff_id IN ('1','2','3')   $select = $db->select()->from('users', array('id'))->where('aff_id IN (?)', array(1,2,3)); Echo your query to visualize it: echo $select->__toString();exit;