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
This entry was posted in Best Practice and tagged Zend Framework, Zend_Db. Bookmark the permalink.
Pingback: What are returning the FETCH functions from Zend_Db » DotKernel
Pingback: Subquerys with Zend_Db » DotKernel
Pingback: INSERT, UPDATE, DELETE statements with Zend_Db » DotKernel
Pingback: Using LIKE wildcards with Zend_Db | DotKernel PHP Application Framework