Page 1 of 1

Mysql - Horribly troublesom problem!!!!

Posted: Wed Sep 16, 2009 7:47 am
by weasel512
G'day,

I have a problem which has been giving me trouble for some time.
I'm trying to query my DB, but i need to find how many times a comparison returns true.

For example.. i have the following query:
SELECT distinct(drinkName) FROM ingredientamount WHERE ingredientID='11' XOR ingredientID='14' XOR ingredientID='6' XOR ingredientID='7' XOR ingredientID='101' XOR ingredientID='111' XOR ingredientID='114' XOR ingredientID='118' XOR ingredientID='97' XOR ingredientID='47' XOR ingredientID='48' XOR ingredientID='50' XOR ingredientID='51' XOR ingredientID='52' XOR ingredientID='53' XOR ingredientID='55' XOR ingredientID='58' XOR ingredientID='62' XOR ingredientID='63' XOR ingredientID='83' XOR ingredientID='86' XOR ingredientID='89' XOR ingredientID='92'
The above returns only distinct drinks that have hits, noting specific.


I need to know how many times a given drink name returns true.

I require an output similar to:
"Bay Breeze (5/5 ingredients)"
"Bloody Mary (4/10 ingredients)"
If someone could please get back to me ASAP, it would be VERY MUCH appreciated !!!

Cheers

Re: Mysql - Horribly troublesom problem!!!!

Posted: Wed Sep 16, 2009 7:49 am
by Eric!
Try checking your result with mysql_num_rows http://us2.php.net/manual/en/function.m ... m-rows.php for how many are returned.

I don't know quite what you mean by it only shows hits but nothing specific. You just have to make your query more specific.

Re: Mysql - Horribly troublesom problem!!!!

Posted: Wed Sep 16, 2009 7:51 am
by weasel512
I've tried that, all i get is the number of drinks returned.. eg 12.

I dont get the number of times a particular drink returns a true

Re: Mysql - Horribly troublesom problem!!!!

Posted: Wed Sep 16, 2009 7:51 am
by VladSun
Are you absolutely sure you meant XOR (maybe just OR) ?

Re: Mysql - Horribly troublesom problem!!!!

Posted: Wed Sep 16, 2009 7:56 am
by weasel512
I chose to use XOR because it returns a range of results more so than what OR does.

I guess it makes little difference in the long run to what I need it for

Re: Mysql - Horribly troublesom problem!!!!

Posted: Wed Sep 16, 2009 8:09 am
by VladSun
I'm still not convinced you want to XOR the results ;)
What are you trying to achieve with this query (business logic)?

Re: Mysql - Horribly troublesom problem!!!!

Posted: Wed Sep 16, 2009 8:18 am
by Eric!
I think I get your query. You need to total the number of ingredients for each drink, maybe with a count (is ingredientsamount the number of ingredientID's?). I'm not sure how to do it. But I'm thinking out load here. Then you'll probably need to loop through the searches looking for 5/5 matches using AND for ingredientID, 4/5 matches on down switching ANDS to ORs one at a time and then clean up the duplicates. There might be a cleaner way to do it, but I think you'll probably just have to loop through your search criteria.

How is the database structured?

EDIT: Maybe instead of cleaning up the duplicates, you could create an array with the drinknames and the number of hits your search results return for them. Then when you look for 4/5 hits, you could first do in_array() to see if the drink was already added as a 5/5....