Combined Where Clause in Mysql PHP Query

Question

I have 2 table called Classifieds and Classifieds_meta below is the screenshot of classifieds_meta table

im writing a search filter function with muliple where clause like

SELECT *,classifieds.id as id 
FROM classifieds,classifieds_meta 
WHERE (category=2 OR category=3 OR category=4 OR category=5) 
AND ((meta_key='vehicle_make' and meta_value=3 OR meta_key='vehicle_make' and meta_value=4)
AND meta_key='vehicle_mileage' and meta_value=9 OR meta_key='vehicle_mileage' and meta_value=10 OR meta_key='vehicle_mileage' and meta_value=11 ) 
AND classifieds.id=classifieds_meta.classifieds_id 
GROUP BY classifieds.id

But the above sql statement IGNORES vehicle_make, meta_value and meta_key field condition, and displays incorrect result, what i want to achieve exactly is i want to get vehicles where category is in (2,3,4 or 5) and meta_key is vehicle_make and meta_value is 3 or 4 and meta_key is vehicle_mileage and meta_value is 9,10 or 11.

can someone please help me to form better SQL statement to get right result

A screen dump doesn't allow for easy setup of a test case. Could you add some sample data and a desired result as text directly to your question? — Joachim Isaksson, Jun 14 '14 at 15:28
Possible duplicate http://stackoverflow.com/q/24123489/1741542 The database design seems similar `meta_key/value` vs `propertyname/value`. `meta_key` can't be `vehicle_make` *and* `vehicle_mileage` at the same time. — Olaf Dietsche, Jun 14 '14 at 15:39

Sjoerd222888 · Answer 1 · 2014-06-14T16:10:03.477

I suppose you are looking for vehicles where category is 2,3,4 or 5 and (meta_key is vehicle_make and (meta_value is 3 or 4)) or (meta_key is vehicle_mileage and (meta_value is 9,10 or 11)).

In this case the query should be:

SELECT *,classifieds.id as id
FROM classifieds,classifieds_meta 
WHERE (category=2 OR category=3 OR category=4 OR category=5) 
AND  ((meta_key='vehicle_make' AND ( meta_value=2 OR meta_value=4)) OR (meta_key='vehicle_mileage' AND (meta_value=9 OR meta_value=10 OR meta_value=11)))

Use parenthesis to make your statement understandable and finally use the same parenthesis in your SQL query.

EDIT:

If you want to use IN the query would be:

SELECT *,classifieds.id as id
FROM classifieds,classifieds_meta 
WHERE category IN (2, 3, 4, 5) 
AND  ((meta_key='vehicle_make' AND  meta_value IN (2,4)) OR (meta_key='vehicle_mileage' AND meta_value IN (9, 10, 11)))

i want to fullfill both the condition vehicle_make and vehicle_mileage but you have used OR condition, if i replace it with AND it returns empty result even though i have rows matching both the condition — Sam, Jun 14 '14 at 16:17

Gordon Linoff · Accepted Answer · 2014-06-14T16:12:15.403

1

I think you are looking for a having clause instead of a where. You are trying to look for conditions across multiple rows:

SELECT *, c.id as id 
FROM classifieds c join
     classifieds_meta  cm
     on c.id = cm.classifieds_id 
GROUP BY c.id
HAVING sum(meta_key = 'vehicle_make' and meta_value in (3, 4)) > 0 and
       sum(meta_key = 'vehicle_mileage' and meta_value in (9, 10, 11)) > 0 and
       sum(category in (2, 3, 4, 5)) > 0

The > 0 is saying that at least one row matches each condition.

Note that I also fixed the join to be use explicit join syntax.

edited Jun 14 '14 at 16:12

answered Jun 14 '14 at 15:50

Gordon Linoff

1,242,037
58
646
786

Thanks for helping, but where is the condition for meta_key = 'vehicle_make' and meta_value in (2,3)? anyways your sql returns empty results – Sam Jun 14 '14 at 15:55
Thanks alot, but still your sql statement returns empty set even if there is rows matching the condition – Sam Jun 14 '14 at 16:03
Are you looking for all conditions being true or for any of them? The above returns `id`s where all are true. If you want any to be true, change the `AND` to `OR`. – Gordon Linoff Jun 14 '14 at 16:05
When looking at the image, I suspect `meta_value` is of type char and some of the values are CSV. – Olaf Dietsche Jun 14 '14 at 16:08
@OlafDietsche . . . That is a good point, but MySQL should handle the comparisons correctly, unless there are unusual characters in the value. Using bare integers is how the OP originally framed the query;. – Gordon Linoff Jun 14 '14 at 16:11
Yes, ur answer actually works, but i dint understand why did u use sum() – Sam Jun 14 '14 at 16:12
`sum()` is counting the number of rows that match each condition. The `> 0` is checking that there is at least one row for each `id`. – Gordon Linoff Jun 14 '14 at 16:21
@GordonLinoff MySQL does the comparison, but when you have `9,10,11` as value, the clause `meta_value in (10, 11)` won't match anything. – Olaf Dietsche Jun 14 '14 at 16:22
@OlafDietsche . . . Very true. The screen shot only has a comma-delimited list for something called "property_features", which is not used in this logic. Because all the other key names are singular, I don't expect that they would have multiple values. If they can, I encourage the OP to ask *another* question. – Gordon Linoff Jun 14 '14 at 16:25
@Gordon Linoff... I have multiple values in other meta_key like "vehicle_make", but its not there in the above screenshot but i have logic in the sql query – Sam Jun 14 '14 at 16:29

bloodyKnuckles · Answer 3 · 2014-06-14T15:45:33.410

0

This is essentially what you have (excluding table connection and category filter):

((meta_key='vehicle_make' and meta_value IN (3,4)) AND meta_key='vehicle_mileage' and meta_value=9)
    OR 
(meta_key='vehicle_mileage' and meta_value IN (10, 11))

Therefore the vehicle_make portion is impossible due to meta_value NEVER being both (3 OR 4) AND 9.

I think what you're going for is:

(meta_key='vehicle_make' and meta_value IN (3,4))
    OR 
(meta_key='vehicle_mileage' and meta_value IN (9, 10, 11))

All together now:

WHERE classifieds.id=classifieds_meta.classifieds_id AND category IN (2, 3, 4, 5)
AND (
    (meta_key='vehicle_make' and meta_value IN (3,4))
        OR 
    (meta_key='vehicle_mileage' and meta_value IN (9, 10, 11))
)

The takeaway from this is operator precedence. AND pairs get evaluated before OR.

edited Jun 14 '14 at 15:45

answered Jun 14 '14 at 15:40

bloodyKnuckles

11,551
3
29
37

Oops left out some parens. :S Updated. – bloodyKnuckles Jun 14 '14 at 15:46
Now its working like charm, thanks alot bro... now if i want to add another condition called meta_key=vehicle_year and meta_value=2010, i just need to add OR (meta_key='vehicle_year' and meta_value IN (2010) after OR (meta_key='vehicle_mileage' and meta_value IN (9, 10, 11)) right? – Sam Jun 14 '14 at 15:50

Combined Where Clause in Mysql PHP Query

3 Answers3