Many to many tables and querying on the same variable with arrays

Question

SOLVED - see Bish below

I've got a list of checkboxes that all dump to $fruitid[], and a many-to-many table1:

BoxID  FruitID
 01      01
 01      02
 02      01
 02      03
 02      04
 03      02
 etc.     etc.

I want a user to check the box next to each fruit they want, and then query to pull up a list of each box that contains all of the fruit selected (and other fruit is ok, it's a minimum requirement not an exact contents search).

My PHP is working nicely, but I'm puzzled by what I thought would be an easy MySQL string. Basically if I

SELECT boxid FROM table1 WHERE fruitid=$fruitid1 AND fruitid=$fruitid2 AND etc.

until all the checked data is entered I end up with no results because no SINGLE row contains more than one fruitid. Is this making sense?

The only solution I can think of is a bunch of nested select statements, each one narrowing the results pool from the previous subset until you've search for all the values in the original array. This seems both CPU intensive and cumbersome to code.

Is there a better way?

Answer 1

If you change your ANDs to ORs I think you'd be doing what you're trying to do. Conceptually, read the select as "select rows with both property1 AND property2 AND...", not "select rows with property1 AND also rows with property2, etc."

EDIT: More compactly you could use

SELECT boxid FROM table1 WHERE fruitid IN ($fruitid1,$fruitid2,...)

Answer 2

You might want to rethink your database (or I'm not sure what you are attempting). But from the SQL you posted it looks like you could do something like this:

SELECT boxid FROM table1 WHERE fruitid in ($fruitid1,$fruitid2);

Answer 3

Building on the current suggestions, you should be able to get what you're looking for by simply adding GROUP BY and HAVING clauses:

SELECT boxid FROM table1 WHERE fruitid IN ($fruitid1, $fruitid2, ...) GROUP BY boxid HAVING COUNT(*) = $selectedFruitCount;

By grouping by boxid and adding the COUNT(), you get the number of 'interesting' fruits that each box contains. If that count equals the total number of fruits that were selected, then that box must contain each of the selected fruits (and possibly more). Keep in mind that this assumes that the combination of boxid and fruitid is unique.