how to get data collectively from table columns

Question

i have such a table:

r_id     date       recipe_name

 1   2012-05-20  Cheese Bread
 1   2012-05-21  Cheese pie
 2   2012-05-20  Spanish Bread

I would like to get all the data under r_id 1 to be in a single row how can i do that using Sql.I need to achieve something like this:

 r_id      recipe_name

 1    Cheese Bread,Cheese pie
 2    Spanish Bread

how can i do this using php too?

Assuming MySQL, this is one of the [standard duplicates](http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql). — Clockwork-Muse, Jul 21 '14 at 09:17

score 1 · Answer 1 · answered Jul 21 '14 at 08:50

1

Use GROUP_CONCAT

SELECT r_id, GROUP_CONCAT(recipe_name)
FROM yourTable
GROUP BY r_id

answered Jul 21 '14 at 08:50

Hanky Panky

46,730
8
72
95

thanks it worked well,but now How can I display the recipe_names separately as links using php.+2 for chinese speed – user3253289 Jul 21 '14 at 08:58
For PHP version you can see the answer by FuzzyTree, no need to re-invent the wheel by writing another answer on top of that. – Hanky Panky Jul 21 '14 at 09:02
Fuzzy uses fetch assoc().Iam trying to use $rows=$result->fetch_object( ),how can i access the recipe names this time – user3253289 Jul 21 '14 at 09:28

score 0 · Answer 2 · answered Jul 21 '14 at 09:01

0

Here's the php version

$query = "SELECT id, recipe_name FROM myTable";
$rs = mysqli_query($query);
$results = array();

while($r = mysqli_fetch_assoc($rs)) {
    $results[$r['id']][] = $r['recipe_name'];
    //$results[$r['id']][] = "<a href=''>".$r['recipe_name']."</a>";
}

foreach($results as $id => $recipes) {
    print $id . ' ' . implode(',', $recipes) . "<br>";
}

answered Jul 21 '14 at 09:01

FuzzyTree

32,014
3
54
85

,thanks but can you please use the resultset from HankyPanky that uses GROUP_CONCAT().Will be greatly appreciated instead of rewriting the query again – user3253289 Jul 21 '14 at 09:08
why did you not include the foreach inside the while loop??Is this possible? – user3253289 Jul 21 '14 at 09:14
can you repeat with a $rows=$result->fetch_object( ) instead.Thanks for your effort. – user3253289 Jul 21 '14 at 09:29

how to get data collectively from table columns

2 Answers2