Search code examples
phpmysqlsqlwhere-clausesql-like

Where Like Dependent on User ID

I have been using a query which checks the users input from a search field and gets the relevant data back. This query is using multiple likes to check various fields (Name, Reference, Order ID etc.).

I noticed that the query is ignoring where it checks the Users ID to bring up records from that user only. And instead brings back records from all users. This is the current query:

SELECT  ORDER_ID, ORDER_DATE, ORDER_TIME, ORDER_REF, ORDER_RECNAME 
FROM ORDERS 
WHERE USER_ID = '$userID' AND ORDER_ID LIKE '%$SearchData%' 
    OR ORDER_RECNAME LIKE '%$SearchData%' OR ORDER_REF LIKE '%$SearchData%' 
    OR ORDER_POSTCODE LIKE '%$SearchData%' 
GROUP BY ORDER_ID, ORDER_DATE, ORDER_TIME, ORDER_REF, ORDER_RECNAME

Is there somewhere im simply going wrong? It is using the Likes correctly but not the WHERE USER_ID part.

Solution

  • You are mixing and and or operators in the where criteria without parentheses and this causes the issue. Encapsulate the like operators connected by or to apply the user_id restriction to all of them. Remember, and operator has higher priority than or!

    USER_ID = '$userID' AND (ORDER_ID LIKE '%$SearchData%' 
OR ORDER_RECNAME LIKE '%$SearchData%' OR ORDER_REF LIKE '%$SearchData%' 
OR ORDER_POSTCODE LIKE '%$SearchData%')