Paul M Paul M - 3 months ago 23
MySQL Question

PHP Mysql Display and join rows

a little dilemma for me here. my comments for broken over many lines as show by the line number. Unfortunately I have no control over this so I am left with trying to output this they way I want.

My data is as follow

Firstname | Surname | Linenum |CommentID | Comment
--------------------------------------------------------------------------
Bill | Jean | 0 |8876 | A Simple comment here
--------------------------------------------------------------------------
Bob |Jones | 0 |7345 | This is a very long comment
--------------------------------------------------------------------------
Bob |Jones | 1 |7345 | And is over many rows.
--------------------------------------------------------------------------
Bob |Jones | 2 |7345 | Thank you for reading.
--------------------------------------------------------------------------
Tim |Burton | 0 |3719 | The quick brown fox jumps
--------------------------------------------------------------------------
Tim |Burton | 1 |3719 ! over the lazy dog
--------------------------------------------------------------------------


So what I want to end up with is echo.....

Firstname: Tim
Surname : Burton
Comment : The quick brown fox jumps over the lazy dog


I am really stuck on the sql for this and don't know where to start.

I can call and display for MySQL which I would call normally ! but when It calls joining from several rows I am at a loss.

Thank you for looking !

Answer Source

You need query like this:

 SELECT firstname, surname, 
     GROUP_CONCAT( comment) as comment from table 
    where commentid=3719
   group by firstname, surname