Creating multivalue search, struggling with SQL

Question

Based on user input, which could be a single or multiple values, and using the following table

+------+--------+
| seed |  item  |
+------+--------+
|    1 | apple  |
|    1 | grapes |
|    2 | apple  |
|    3 | grapes |
|    3 | banana |
+------+--------+

I want to return

• 1 when the user entered (apple, grape),
• [1, 2] for (apple), and
• nothing for (apple, banana).

My current PHP code

$keyword = Input::get('keyword');
        $searchTerms = explode(",", $keyword);
        $query = DB::table('items');
        foreach($searchTerms as $term)
        {   
            $query->where('item', 'LIKE', '%'.$term.'%');
        }
        $results  = $query->distinct()->get(array('seed'));

works for single values. Iterating in the loop, I'm just appending more search terms to the current $query. At the end, I need to find the intersection of all the queries. This is currently my main concern.

Answer 1

With the input available try

SELECT seed
FROM Items
WHERE item IN ('apple', 'grapes')
GROUP BY seed
HAVING COUNT(item) >= 2
;

SELECT seed
FROM Items
WHERE item IN ('apple')
GROUP BY seed
HAVING COUNT(item) >= 1
;

SELECT seed
FROM Items
WHERE item IN ('apple', 'banana')
GROUP BY seed
HAVING COUNT(item) >= 2
;

The total to compare against in the HAVING clause is the count of items, you are checking in the respective batch.

SQL Fiddle

Please comment, if further detail or adjustment is required.