472,119 Members | 1,596 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.


128 100+

I'm wondering how to achieve the following:

well i don't know if you guys like wine but I have to explain a very little on wine to explain my example: Wine is made from grape and a wine bottle may be made from different sorts of grape. So when you have a bottle it usually says: 10% of shyra, 50% of cabernet and 40% of merlot
meaning that the wine in the bottle was made from that percent of each grape type.

that was it for wine.

now SQL

need to store all the grapes that a wine has and their respective percentage.
so i have my bottles which are stored under a table called Wine
then i have all existing types of grapes stored in Grape
and i associate these two things by a third table called WineToGrape
its a one to many relationship.
here are the tables examples:

Expand|Select|Wrap|Line Numbers
  1. Wine
  2. wine_id | name
  4. Grape
  5. grape_id | name
  7. WineToGrape
  8. wtg_id | grape_id | wine_id | percentage
Now the fun part: How do i retrieve a list of all the wines with all their respective grapes and percentage?

I see one option which implies PHP or any other language:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     w.wine_id AS wineId, 
  3.     w.name AS wineName,
  4.     g.grape_id AS grapeId, 
  5.     g.name AS grapeName, 
  6.     wtg.percentage AS grapePercentageInWine 
  7. FROM 
  8.     WineToGrape AS wtg
  9.         INNER JOIN Wine AS w ON (wtg.wine_id = w.wine_id) 
  10.         INNER JOIN Grape AS g ON (wtg.grape_id = g.grape_id);
Then i would end up with a big array that would have many duplicate wine_id's and i can't do a GROUP BY because it would get off the grapes and i want all the grapes
So from PHP i would have to loop through all the rows and if the wine_id in the row already exists then i would append the grape to the wine_id key like this:
Expand|Select|Wrap|Line Numbers
  1. $wines = array();
  2. while($row = mysql_fetch_array($resultset)){
  3.     //if the wine name was not already stored, store it
  4.     if (! isset($wines[$row['wineId']])) {
  5.         $wines[$row['wineId']]['name'] = $row['wineName'];
  6.     }
  7.     //any time there is a new record it the grape name and percetage has
  8.     //to be added to the wine element in the array
  9.         $wines[$row['wineId']]['grapes'][$row['grapeId']]['name'] = $row['grapeName'];
  10.         $wines[$row['wineId']]['grapes'][$row['grapeId']]['percentage'] = $row['grapePercentageInWine'];
  11.     }
  12. }
So an example result array would be:

Expand|Select|Wrap|Line Numbers
  1. array(3=>array('name'=>'wine3',
  2.                   'grapes'=> array(26=>array('name'=>'grape26',
  3.                                             'percentage'=>40),
  4.                                   34=>array('name'=>'grape26',
  5.                                             'percentage'=>60)
  6.                                  )
  7.                   ),
  8.      7=>array('name'=>'wine7',
  9.               'grapes'=> array(31=>array('name'=>'grape31',
  10.                                          'percentage'=>15),
  11.                               34=>array('name'=>'grape34',
  12.                                          'percentage'=>40),
  13.                               15=>array('name'=>'grape15',
  14.                                          'percentage'=>45)
  15.                              )
  17.               ),
  18.      23=>array('name'=>'wine23',
  19.               'grapes'=> array(31=>array('name'=>'grape31',
  20.                                          'percentage'=>100),
  21.                              )
  22.               )
  23. );
Once i have my $wines array i would have to loop again through all the elements and nodes in the array to display them.

That is feasable but i have heard of another possibility:
GROUP_CONCAT() sql function

group concat would give me a concatenated string of all the grapes with this sql:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     w.wine_id AS wineId, 
  3.     w.name AS wineName,
  4.     GROUP_CONCAT(g.grape_id) AS grapeId, 
  5.     g.name AS grapeName, 
  6.     wtg.percentage AS grapePercentageInWine 
  7. FROM 
  8.     WineToGrape AS wtg
  9.         INNER JOIN Wine AS w ON (wtg.wine_id = w.wine_id) 
  10.         INNER JOIN Grape AS g ON (wtg.grape_id = g.grape_id);
But what i would not have my percentages, so that's where i need help:

I need to know how to use the GROUP_CONCAT() function to concatenate all the grapes and percentages like this: 'grape23 10%, grape11 90%' for each wine.

is that feasible ?

Thank you just for the fact of having read so far!


Aug 18 '09 #1
1 5676
In GROUP_CONCAT() you can put any expression you want, so you should be able to do something like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     w.wine_id AS wineId, 
  3.     w.name AS wineName,
  4.     GROUP_CONCAT(CONCAT(g.name,' ',wtg.percentage)) AS grapeId
  5. FROM 
  6.     WineToGrape AS wtg
  7.         INNER JOIN Wine AS w ON (wtg.wine_id = w.wine_id) 
  8.         INNER JOIN Grape AS g ON (wtg.grape_id = g.grape_id);
I think that will do what you need it to.
Aug 19 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by peter stickney | last post: by
reply views Thread by gmorris | last post: by
1 post views Thread by lukemack | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.