Mysql - Horribly troublesom problem!!!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
weasel512
Forum Newbie
Posts: 5
Joined: Wed Sep 02, 2009 7:58 am

Mysql - Horribly troublesom problem!!!!

Post 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
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: Mysql - Horribly troublesom problem!!!!

Post 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.
Last edited by Eric! on Wed Sep 16, 2009 7:51 am, edited 1 time in total.
weasel512
Forum Newbie
Posts: 5
Joined: Wed Sep 02, 2009 7:58 am

Re: Mysql - Horribly troublesom problem!!!!

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Mysql - Horribly troublesom problem!!!!

Post by VladSun »

Are you absolutely sure you meant XOR (maybe just OR) ?
There are 10 types of people in this world, those who understand binary and those who don't
weasel512
Forum Newbie
Posts: 5
Joined: Wed Sep 02, 2009 7:58 am

Re: Mysql - Horribly troublesom problem!!!!

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Mysql - Horribly troublesom problem!!!!

Post by VladSun »

I'm still not convinced you want to XOR the results ;)
What are you trying to achieve with this query (business logic)?
There are 10 types of people in this world, those who understand binary and those who don't
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: Mysql - Horribly troublesom problem!!!!

Post 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....
Post Reply