Andrew Heath Andrew Heath - 4 months ago 10
MySQL Question

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

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

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.