SQL queries using Zend_Db – SELECT

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