SQL queries using Zend_Db

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

SQL queries using Zend_Db – SELECT