Search code examples
mysqlsqloperators

SQL "OR" operator not evaluating as expected in php

The problem occurs with wo.status = 'in_progress' OR 'ongoing' part executes. If 'in_progress' returns true but 'ongoing' returns false the overall result with be false (i.e. wo.status is neither in_progress or ongoing). The result I'm looking for would be that if either 'in_progress' or 'ongoing' are true then the overall result would be true.

I'm querying a database as follows:

SELECT wo.*, c.address 
FROM WorkOrder AS wo 
LEFT JOIN Customer AS c 
ON c.id = wo.customer_id 
LEFT JOIN AssignedTechs AS at 
ON at.work_order_id = wo.id 
WHERE at.technician_id = ? 
&& wo.status = 'in_progress' 
OR 'ongoing' AND at.active";
Solution

  • You have two problems; first, OR and = do not work together; you are saying

    wo.status = 'in_progress'
OR
'ongoing'

    This first tests if wo.status is in_progress. If it is, the result is true; if it is not, it checks the second operand of OR, which casts 'ongoing' to a number and simply tests if it is non-zero, and has nothing to do with the value of wo.status. Since it has no leading digits, it casts to 0 which is false. You want either:

    wo.status = 'in_progress' OR wo.status = 'ongoing'

    or

    wo.status IN ('in_progress','ongoing')

    Your second problem is that OR is lower precedence than &&/AND. So you are doing:

    (at.technician_id = ? && wo.status = 'in_progress')
OR
('ongoing' AND at.active)

    You want to parenthesize your OR condition (unless you use the IN operator as above, which avoids OR):

    at.technician_id = ?
&& (wo.status = 'in_progress' OR wo.status = 'ongoing')
AND at.active