MySQL query for "AND" search form with multiple checkboxes and select menus

Question

I have a table:

item_id  | property_id | value |
================================
1        |      100    |   1   |
1        |      101    |   1   |
1        |      102    |   0   |
2        |      100    |   1   |
2        |      101    |   1   |
2        |      102    |   1   |
2        |      120    | black |
3        |      100    |   1   |
3        |      101    |   0   |
3        |      102    |   1   |
4        |      121    |  big  |
...

I would like to perform "AND" search by a form with multiple checkboxes and select menus (each checkbox and select menu have name like 'property_id').

Example:

When I check checkbox 100 and 101, desire result of query is item_id = 1, 2.
When I check checkbox 100, 101 and choose 'black' from select menu 120, desire result is item_id = 2.
When I check checkbox 100, 101, choose 'black' from select menu 120, and choose 'big' from select menu 121, desire result is item_id = NULL.

Number of checked properties (checkboxes and selectmenus) may vary.

I tried:

SELECT item_id
FROM yourtable
WHERE property_id IN (100, 101)
AND value = 1
GROUP BY item_id
HAVING COUNT(DISTINCT property_id) = 2

But it's only for checkboxes and values 0 or 1. I have problem to implement it with select menus (example 2. or 3.)

I hope that explains what I'm trying to do. Thanks in advance!

I'm not the best SQL writer but have you tried "WHERE property_id = 100, 101"? — Matt
– Matt, Commented Nov 25, 2012 at 13:51
This is a common question, look at the suggestions on the right. I remember it coming by in stackoverflow.com/questions/12029608/select-query-in-sql and stackoverflow.com/questions/12399950/… , but surely there are many others. — LSerni
– LSerni, Commented Nov 25, 2012 at 13:51

ypercubeᵀᴹ · Accepted Answer · 2012-11-25 14:05:06Z

1

With minimum changes to your query:

SELECT item_id
FROM yourtable
WHERE property_id IN (100, 101)
  AND value = 1
   OR property_id = 120 
  AND value = 'black'
GROUP BY item_id
HAVING COUNT(DISTINCT property_id) = 3 ;

which is equivalent to:

SELECT item_id
FROM yourtable
WHERE property_id = 100  AND  value = 1 
   OR property_id = 101  AND  value = 1
   OR property_id = 120  AND  value = 'black'
GROUP BY item_id
HAVING COUNT(DISTINCT property_id) = 3 ;

edited Nov 25, 2012 at 14:05

answered Nov 25, 2012 at 13:58

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

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

Collectives™ on Stack Overflow

MySQL query for "AND" search form with multiple checkboxes and select menus

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related