PHP with MySQL query not returning expected results

Question

I'm new to PHP and I'm trying to select records (for search functionality) in my table where the month is static and other columns vary.

Below is the query:

SELECT * 
FROM issue 
WHERE month = "Apr 2019" 
    AND issue LIKE "%ekh%" 
    OR issue_type LIKE "%ekh%" 
    OR facility LIKE "%ekh%" 
    OR issue_id LIKE "%ekh%" 
    OR priority LIKE "%ekh%" 
ORDER BY issue_id DESC

This is rather returning all rows that satisfy the like clauses, even when month isnt = "Apr 2019".

Put all the like statements inside parentheses, so you have month="XX" and (issues like "XX" etc). — Philip
– Philip, Commented Apr 9, 2019 at 21:54

Arnaud Peralta · Accepted Answer · 2019-04-09 21:56:58Z

3

In algebra, the AND operation is done before the OR operation. This is the same rule in your WHERE clause.

You can fix this with parentheses like this :

SELECT *
FROM issue
WHERE month = "Apr 2019"
    AND (issue LIKE "%ekh%"
        OR issue_type LIKE "%ekh%"
        OR facility LIKE "%ekh%"
        OR issue_id LIKE "%ekh%"
        OR priority LIKE "%ekh%")
ORDER BY issue_id DESC

answered Apr 9, 2019 at 21:56

Arnaud Peralta

1,3051 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kevin Kopf · Accepted Answer · 2019-04-09 22:03:51Z

1

When mysql hits first or it automatically returns everything because or something is true. Therefore you should use parentheses:

SELECT *
FROM issue
WHERE month = "Apr 2019" AND
(issue like "%ekh%" OR
 issue_type like "%ekh%" OR
 facility like "%ekh%" OR
 issue_id like "%ekh%" OR
 priority like "%ekh%")
ORDER BY issue_id DESC

edited Apr 9, 2019 at 22:03

answered Apr 9, 2019 at 21:57

Kevin Kopf

14.3k15 gold badges57 silver badges69 bronze badges

Collectives™ on Stack Overflow

PHP with MySQL query not returning expected results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related