如何根据多个值选择多列？

Question

I tried searching for an answer, but didn't find anything, and I'm not sure if I'm describing this in the best way, but here goes...

So, I have a MySQL database, and a website that the user can select 0-3 categories to search the database on.

For example, if I have records like this in the DB:

id|cat1|cat2|cat3
-----------------
1 |  a |  b |  c
2 |  a |    |
3 |  z |  b |  a
4 |  y |  a |
5 |  b |  c |  a 

I'm trying to figure out a way so if the user selects, for example, 1 category to search on and it's "a", then it would return records [1,2,3,4,5], and if the user selects 2 categories to search on, like "a" and "b" it would return records [1,3,5], and it the user selected 3 categories, like "a" and "c" and "b", then it would return [1,5].

There is no guarantee on sequence, neither in the DB nor in what the user enters.

I've gotten my query to where it can select records that have any of the entered categories, but I can't seem to figure out how to get to return only records that have ALL the categories provided.

Does anyone know how to do that?

Thanks!

Here's what I have so far, it's messy and I've just been kind of shooting in the dark here, haha, but here it is (in this example, the categories "Church" and "Occupation" were selected):

SELECT * FROM all_terms 
WHERE
(      (subject1 in ('Church', 'Occupation') OR subject1 IS NULL OR subject1 = '') 
   AND (subject2 in ('Church', 'Occupation') OR subject2 IS NULL OR subject2 = '') 
   AND (subject3 in ('Church', 'Occupation') OR subject3 IS NULL OR subject3 = '')
   AND (     (subject1 IS NOT NULL && subject1 != '') 
          OR (subject2 IS NOT NULL && subject2 != '') 
          OR (subject3 IS NOT NULL && subject3 != '')))

Answer 1

The general query structure you need is something like this:

Give a user selection of a:

SELECT id
FROM table1
WHERE 'a' IN (cat1, cat2, cat3)

Give a user selection of a, b:

SELECT id
FROM table1
WHERE 'a' IN (cat1, cat2, cat3)
  AND 'b' IN (cat1, cat2, cat3)

Give a user selection of a, b, c:

SELECT id
FROM table1
WHERE 'a' IN (cat1, cat2, cat3)
  AND 'b' IN (cat1, cat2, cat3)
  AND 'c' IN (cat1, cat2, cat3)

See this SQL fiddle

You could use a loop to dynamically generate the right statement depending on the user input, something like:

Given $arr is an array of user input:

$sql = 'SELECT id FROM table1 WHERE ';

$and = '';

foreach ($arr as $a) {
    $sql .= $and . "'$a' IN (cat1, cat2, cat3) ";
    $and = 'AND ';
}

For example, if $arr = array('a', 'b'), then the query generated would be:

SELECT id
FROM table1
WHERE 'a' IN (cat1, cat2, cat3)
  AND 'b' IN (cat1, cat2, cat3)

See this PHP fiddle