Zend database prioritize Where statements

Question

I use zend 1.12 to query the database for an overview. Now I can use several filters and a search query. These all come into one query like this:

SELECT `x`.`id`, `x`.`ref_id`, `x`.`start_date`, `x`.`regard`, `x`.`project_code`, `o`.`name` AS `contact`, `o`.`id` AS `contact_id`, `d`.`name` AS `department`, `c`.`name` AS `company`, `is`.`name` AS `status`, SUM(ip.price*ip.amount*(100-ip.discount)/100) AS `price_ex`, SUM(ip.price*ip.amount*(100-ip.discount)*(100+ip.tax)/10000)-IFNULL(p.payment,0) AS `price_open` FROM `invoice` AS `x`
 LEFT JOIN `contact` AS `o` ON x.recipient_id=o.id
 LEFT JOIN `department` AS `d` ON o.department_id=d.id
 LEFT JOIN `company` AS `c` ON d.company_id=c.id
 LEFT JOIN `invoice_status` AS `is` ON x.status=is.id
 LEFT JOIN `invoice_part` AS `ip` ON x.id=ip.invoice_id
 LEFT JOIN (SELECT SUM(amount) AS `payment`, `payment`.`invoice_id` FROM `payment` GROUP BY `invoice_id`) AS `p` ON x.id=p.invoice_id 
 WHERE (month(x.start_date) = '6') AND (year(x.start_date) = '2013') OR (LOWER(x.regard) LIKE '%test%') OR (LOWER(o.name) LIKE '%test%') OR (LOWER(c.name) LIKE '%test%') OR (LOWER(ref_id) LIKE '%test%') OR (LOWER(start_date) LIKE '%test%') GROUP BY `x`.`id` ORDER BY `x`.`updated_at` DESC LIMIT 50

Now all the filters get added with AND but when I searched on a column this gets added as or, but now everything is found. what I want is to do [filter] AND [filter] AND ([search] or [search]). Is it possible to add these ( ) in my zend query?

I think the above question should help. Just need to construct it properly. — JohnP, Aug 16 '13 at 14:45
thx, doesnt really look protected against sql injetion though — Joel Harkes, Aug 16 '13 at 14:54
I haven't worked on Zend in a while so I'm not sure about it. You can always quote the partial query yourself of course - http://stackoverflow.com/a/985316/295508 — JohnP, Aug 16 '13 at 15:05

Tim Fountain · Answer 1 · 2013-08-16T15:43:57.040

Yes, just put all the OR bits into the same where call:

$select->where('MONTH(x.start_date) = ?', $start_date_month)
       ->where('YEAR(x.start_date) = ?', $start_date_year)
       ->where('LOWER(x.regard) LIKE ? OR LOWER(o.name) LIKE ? OR LOWER(c.name) LIKE ? OR LOWER(ref_id) LIKE ? OR LOWER(start_date) LIKE ?', array($search, $search, $search, $search, $search));

Also, MySQL string comparisons are case insensitive by default unless you've used a case sensitive collation; so the LOWER() is probably unnecessary (and might prevent the query from using an index).

Zend database prioritize Where statements

1 Answers1