Group_concat()

[bilibytes]

Hi,

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:

Wine
wine_id | name
 
Grape
grape_id | name
 
WineToGrape
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:

SELECT 
    w.wine_id AS wineId, 
    w.name AS wineName,
    g.grape_id AS grapeId, 
    g.name AS grapeName, 
    wtg.percentage AS grapePercentageInWine 
FROM 
    WineToGrape AS wtg
        INNER JOIN Wine AS w ON (wtg.wine_id = w.wine_id) 
        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:

$wines = array();
while($row = mysql_fetch_array($resultset)){
    //if the wine name was not already stored, store it
    if (! isset($wines[$row['wineId']])) {
        $wines[$row['wineId']]['name'] = $row['wineName'];
    }
    //any time there is a new record it the grape name and percetage has
    //to be added to the wine element in the array
        $wines[$row['wineId']]['grapes'][$row['grapeId']]['name'] = $row['grapeName'];
        $wines[$row['wineId']]['grapes'][$row['grapeId']]['percentage'] = $row['grapePercentageInWine'];
    }
}

So an example result array would be:

array(3=>array('name'=>'wine3',
                  'grapes'=> array(26=>array('name'=>'grape26',
                                            'percentage'=>40),
                                  34=>array('name'=>'grape26',
                                            'percentage'=>60)
                                 )
                  ),
     7=>array('name'=>'wine7',
              'grapes'=> array(31=>array('name'=>'grape31',
                                         'percentage'=>15),
                              34=>array('name'=>'grape34',
                                         'percentage'=>40),
                              15=>array('name'=>'grape15',
                                         'percentage'=>45)
                             )
 
              ),
     23=>array('name'=>'wine23',
              'grapes'=> array(31=>array('name'=>'grape31',
                                         'percentage'=>100),
                             )
              )
);

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:

SELECT 
    w.wine_id AS wineId, 
    w.name AS wineName,
    GROUP_CONCAT(g.grape_id) AS grapeId, 
    g.name AS grapeName, 
    wtg.percentage AS grapePercentageInWine 
FROM 
    WineToGrape AS wtg
        INNER JOIN Wine AS w ON (wtg.wine_id = w.wine_id) 
        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!

regards

bilibytes

[labmonkey111]

In GROUP_CONCAT() you can put any expression you want, so you should be able to do something like this:

SELECT 
    w.wine_id AS wineId, 
    w.name AS wineName,
    GROUP_CONCAT(CONCAT(g.name,' ',wtg.percentage)) AS grapeId
FROM 
    WineToGrape AS wtg
        INNER JOIN Wine AS w ON (wtg.wine_id = w.wine_id) 
        INNER JOIN Grape AS g ON (wtg.grape_id = g.grape_id);
 

I think that will do what you need it to.