mysql & php - query for record with all or fewer values, but not more

Question

(SOLVED, SEE END)

I need hints on the PHP or MySQL code structure for the following:

Let's say you have X unique boxes, and Y types of fruit. Each box can contain one of each type of fruit. Some boxes might have only 2 types, some might have every type. All boxes have at least one type.

For the query, the user has a list of checkboxes, one for each kind of fruit. (I assume these all feed into the same query array?)

My problem starts here:

If a user checks

apples, oranges, and grapes

how do I construct a query to return the following:

Box01 (apples, oranges)
Box02 (grapes)
Box03 (apples, oranges, grapes)

but NOT:

Box04 (apples, oranges, grapes, pears, bananas)

How do I limit the returns to records that contain the all given values in $fruit[] or less, but NOT any OTHER values even if the requested values are present? Also, does this sort of query have a special name I can use for future reference?

This is a real tough one for me, and I appreciate your time.

• A

(PS - you needn't worry about formatting the output or anything like that, that was just to illustrate the concept. I'm interested strictly in the query code.)

EDIT - two have asked, sorry for not going into specific dbase details. I've got 3 tables: one describing each box, one describing each fruit, and a many-to-many table with 2 columns - boxids & fruitids. I believe that's what you both assumed anyhow.

Thank you everyone!

MY SOLUTION

Anax's code below helped me along, here's what I ended up doing:

table "box" has columns "boxid" (primary key) & "box description" table "fruit" has columns "fruitid" (primary key) & "fruit description" table "box_has" has columns "boxid" & "fruitid"

SELECT boxid FROM box WHERE
    boxid NOT IN (
        SELECT boxid FROM box_has WHERE fruitid NOT IN ('F01' , 'F02'))

Looking at the code from back to front, the innermost SELECT finds all the boxes that have fruit OTHER than the ones we want, excluding troublesome boxes that have what we want AND others.

The outer select then uses the box table, in which each boxid only appears once, to get a clean return of individual boxes without repeats that contain any of the fruit selected.

Thank you for the help, everyone!

Answer 1

Select * from box where boxId NOT IN (Select boxId from boxes where fruitType = 'grape' or fruitType = 'apples' or fruitType='oranges');

This might not be the exact code you are looking for but this idea might help you out...

Answer 2

<?php
$boxes = array(
    array(
        'apples',
        'oranges'
    ),
    array(
        'grapes',
    ),
    array(
        'apples',
        'oranges',
        'grapes'
    ),
    array(
        'apples',
        'oranges',
        'grapes',
        'pears',
        'bananas'
    )
);

$search = array(
    'apples',
    'oranges',
    'grapes'
);

foreach ($boxes AS $box)
{
    $notinsearch = array_diff($box, $search);

    if (empty($notinsearch))
    {
        print_r($box);
    }
}

Answer 3

How about

SELECT * FROM box WHERE boxId NOT IN (
  SELECT * boxId FROM box WHERE fruit NOT IN ("apples", "oranges", "grapes"));

Answer 4

Question is how is your MySQL database layed out?

Assuming you have a table boxes and a table fruits and a linker table box_ fruit_linker that links the contents:

SELECT * FROM boxes
    LEFT JOIN box_fruit_linker USING (box_id)
    LEFT JOIN fruits USING (fruit_id)
    WHERE (fruits.name = 'apples' OR fruits.name = 'grapes' OR fruits.name = 'oranges')
    GROUP BY boxes.box_id;

The WHERE part of your query can easily be composed in PHP by iterating over the array of checked boxes and using implode(' OR ', $checked_boxes).

Answer 5

You need three tables: one to hold the baskets, one to hold the fruits and a third one which joins baskets with fruits (many-to-many).

Given this, you get the desired results by running a query such as the following:

SELECT baskets.* FROM baskets, fruits, baskets_and_fruits

WHERE baskets.id = baskets_and_fruits.basketID AND
    fruits.id = baskets_and_fruits.fruitID AND
    AND baskets.id NOT IN
    (
        SELECT baskets.id FROM baskets, fruits, baskets_and_fruits
        WHERE baskets.id = baskets_and_fruits.basketID AND
            fruits.id = baskets_and_fruits.fruitID AND
            fruits.name NOT IN ('apples', 'oranges', 'grapes')
    )