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.

Grumdrig posted first (by 48 sec) and then edited to include my answer. Using OR or IN would give you the same results. But it sounds like you are looking for something different.
–
NitroxDMNov 3 '09 at 6:00

@NitroxDM Yours appeared while I was editing. No intention to swipe; here's an upvote for you.
–
GrumdrigNov 3 '09 at 15:26

3 Answers
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.

I was just taking a stroll thinking that incorporating a count of some sort would filter out returned boxes containing less than all desired fruits, but was unsure how to code it. This looks like a clean implementation of my unclean musings, and I'll try it ASAP. Thank you, I'll let you know!
–
Andrew HeathNov 3 '09 at 6:21

I can't get this to work in the phpMyAdmin SQL box, I keep getting errors, but I'm fairly confident I could do it as a two-step process (#1 - select _ from _ where _ in _ // #2 count filter) in PHP. Unfortunately, I won't be able to try it until I get home tonight. I'll let you know.
–
Andrew HeathNov 3 '09 at 6:45

It would be nice to allow the HAVING clause to perform the filtering in step #2 for you. Without seeing the errors you are getting, I can't provide much advice aside from checking out the MySQL documentation for SELECT syntax for the version you are running. I did run a quick test that worked fine on 4.1.22.
–
bishNov 3 '09 at 8:00

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."

please see additional data in the original question
–
Andrew HeathNov 3 '09 at 5:10

@Andrew Heath - Could you provide some more examples of what you want? If the table has two columns (BoxID, FruitID) and has the data you posted above and $fruitid1 = 01 and $fruitid2 = 02. Then you would get back three rows. BoxID -- 01 02 03
–
NitroxDMNov 3 '09 at 5:57

The query I want would not return BoxIDs 2 & 3 because neither have both values. It's not an __ OR __ search, it's an ___ AND ___ query, which is the tricky part.
–
Andrew HeathNov 3 '09 at 6:38