Querying an array

[Ganners]

I have a table of data which has headings of:

catID and AnswerID

 

Each catID can have many answers, and those get filtered down and an array gets passed.

 

So, at the moment I have this select statement:

 

"SELECT catID FROM $mytable WHERE answerID IN (".implode(',', $answerIDArray).")";

 

This selects of course every catID which matches an item in the array. But if I have:

 

catID

AnswerID

6

64

6

72

6

77

7

64

7

73

7

81

 

I want to, for instace, if I pass an array containing 64 and 72, I want it to return only catID = 6, and if I did an array containing only 64 i'd want it to return 6 and 7.

 

Does this make any sense? I'm terrible at explaining things. Is this something I could just use php for?

 

Something I think I could use would be to keep my currect select statement, then print out any catID which which is repeated the count of the array I passed in the first place, but I'm not sure how efficient or logical that is.

 

Thanks for any help!

 

 

 

[DavidAM]

I'm not at the top of my game right now, but if I understand, you only want catID if it contains ALL of the answerID's in the list. Right? Try adding a count:

 

$sql = "SELECT catID FROM $mytable 
WHERE answerID IN (" . implode(',' , $answerIDArray) . ")
GROUP BY catID
HAVING COUNT(answerID) = " . count($answerIDArray);

 

If mySQL complains, add COUNT(answerID) to the SELECT list as well; but I don't think mySQL requires that.

[Ganners]

Wow thank you very much DavidAM, that worked a treat straight out with a copy paste! Saved me a lot of head bashing here.