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
- Wine
- wine_id | name
- Grape
- grape_id | name
- WineToGrape
- wtg_id | grape_id | wine_id | percentage
I see one option which implies PHP or any other language:
Expand|Select|Wrap|Line Numbers
- 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);
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
- $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'];
- }
- }
Expand|Select|Wrap|Line Numbers
- 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),
- )
- )
- );
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
- 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);
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