Complex WHERE clause with Zend_Db using multiple AND OR operators
php, sql, zend-db, zend-framework
Solution
This would be the 'official' way to get you the parentheses as specified (see Example #20 in the `Zend_Db_Select` documentation):
$a1 = 'one';
$a2 = 'two';
$select->from('product');
$select->where('status = ?', $status);
$select->where("attribute = $a1 OR attribute = $a2");
So, what you are doing does seem reasonable, given that you do not know how many attributes you have ahead of time.
Problem
I want to generate this complex WHERE clause in Zend_Db: ``` SELECT * FROM 'products' WHERE status = 'active' AND ( attribute = 'one' OR attribute = 'two' OR [...] ) ; ``` I've tried this: ``` $select->from('product'); $select->where('status = ?', $status); $select->where('attribute = ?', $a1); $select->orWhere('attribute = ?', $a2); ``` and that produced: ``` SELECT `product`.* FROM `product` WHERE (status = 'active') AND (attribute = 'one') OR (attribute = 'two') ; ``` I did figure out one method of making this work but I felt it was sort of 'cheating' by using PHP to combine the "OR" clauses first and then combine them using Zend_Db where() clause. PHP code: ``` $WHERE = array(); foreach($attributes as $a): #WHERE[] = "attribute = '" . $a . "'"; endforeach; $WHERE = implode(' OR ', $WHERE); $select->from('product'); $select->where('status = ?', $status); $select->where($WHERE); ``` That produced what I was looking for. But I'm curious if there's an "official" way of getting that complex WHERE statement (which really isn't too complex, just adding some parenthesis) with using the Zend_Db tool, instead of combining it in PHP first. Cheers!