Zend_Db and its related classes provide a simple SQL database interface for Zend Framework.
To connect to MySql database, we are using Pdo_Mysql adapter :

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






SELECT query – WHERE clause


The below 2 classical SQL queries are equivalent. First one is simple, the second one use INNER JOIN keyword, but the result is the same.

SELECT a.id, a.name, b.order_id  
FROM users AS a, orders AS b
WHERE a.id = b.user_id
AND 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_id
WHERE (a.id = '{$userId}')



The above querys are translated 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)



If we don’t want to select any column from the second table, the 3rd parameter of join() method should be an empty string

SELECT a.id, a.name
FROM users AS a, orders AS b
WHERE a.id = b.user_id
AND 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)



Note*: If we don’t write the 3rd parameter, it will select all the fields from that table:

SELECT a.id, a.name, b.*
FROM users AS a, orders AS b
WHERE a.id = b.user_id
AND 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));



Note*: If you are not sure if you write the correct query, before you fetch it you can echo your query to visualize it:

echo $select->__toString();exit;



Also see:
What are returning the FETCH functions from Zend_Db

Subqueries with Zend_Db

INSERT, UPDATE, DELETE statements with Zend_Db

4 Comments

  1. What are returning the FETCH functions from Zend_Db » DotKernel

    […] the Zend_DB article series, we are stopping now at FETCH methods, that are in Zend_Db_Adapter_Abstract: array fetchAll […]

  2. Subquerys with Zend_Db » DotKernel

    […] the Zend_DB article series, we are stopping now at subqueries. As you note, the below is a complicate query, with COUNT(), […]

  3. INSERT, UPDATE, DELETE statements with Zend_Db » DotKernel

    […] the Zend_DB article series, we are stopping now at DML statements. DML (Data Manipulation Language) statements are statements […]

  4. Using LIKE wildcards with Zend_Db | DotKernel PHP Application Framework

    […] the Zend_DB article series, let’s discuss the LIKE […]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>