[SOLVED] Add new line in CONCAT command

[rdrews]

I have the following line...

 

$query = "UPDATE pics SET Comments = CONCAT(COALESCE(Comments, ''),'$Comm') WHERE Date = '$Day'";

 

I want to append $Comm to whatever is already in the "Comments" field where the Date of that record matches $Day.  The code works fine but I want to add a new line each time something is appended to the field.  I need to do this because when I display the comments I want to be able to distinguish between each entry. How can I do this?  I can't figure out where to put a \n or <br /> or <p> in the string.

 

Thanks in advance,

Ryan

[trq]

Instead of adding to an already existing record, why aren't you actually creating a new record for each comment? Seems a funny way to go about things to me.

[rdrews]

Well, admittedly, I am fairly new to mysql so I am certainly open to suggestions but it just made more sense to me to keep all the comments in one record.  Basically, I have a table with a Date field and Comment field.  The Date corresponds to the date the game was played and I want to allow the players to add comments to a given game if they want to. So...many comment entries would correspond to a single game.  It just makes more sense to me to do it this way but I suppose I could create a new record for each comment entered if need be.  Is adding a new line in the CONCAT command complicated? 

[Fadion]

I assume your game have a database entry, with an id and such, so inserting a new row for comments is way better. Basically your comments table will look like:

 

Games Table

------------

id* game description

 

Comments Table

----------------

id date comment game_id*

 

comments.game_id references games.id. That way its faster and simpler to manage.

[rdrews]

Thanks a lot for your help.  That makes perfect sense, I just wasn't really thinking.

[rdrews]

Okay, so I figured out why I wanted to append the new comment to the old comment and leave it all in the same record...I can't figure out how to display the comments how I want them now. So now I have a database with a record for every comment entered. Each record has a Date and Comment field. A lot of the Date entries are, obviously, the same because I am inserting a new record for each new comment and multiple people can make a comment for a single game. I'm trying now to get a nice pretty table that displays all of the comments along with the date of the game the comment was for. Which give me this:

 

$querydisp = 'SELECT * FROM comments ORDER BY Date DESC';

$result = mysql_query($querydisp);

echo "<table border='1' cellspacing='3' cellpadding='5'><tr><th>Date</th><th>Comments</th></tr>";

while($row = mysql_fetch_array($result)){

echo "<tr><td>" . $row['Date'] . "</td><td>" .$row['Comments']. "</td><tr>";

}

echo "</table>";

 

Problem is, now I get the Date displayed for every comment even though the date is the same for say...the first 4 then the next 3 then the next 6 and so on. I just want the date to be displayed once per set of comments. So I wrote this up:

 

$querydisp = 'SELECT * FROM comments ORDER BY Date DESC';

$result = mysql_query($querydisp);

echo "<table border='1' cellspacing='3' cellpadding='5'><tr><th>Date</th><th>Comments</th></tr>";

$DateCheck = "a";

while($row = mysql_fetch_array($result)){

if($row['Date'] == $DateCheck){

$DateCheck = $row['Date'];

echo "<tr><td></td><td>" .$row['Comments']. "</td><tr>";}

else{

$DateCheck = $row['Date'];

echo "<tr><td>" . $row['Date'] . "</td><td>" .$row['Comments']. "</td><tr>";

}

}

echo "</table>";

 

This isn't BAD except now, because I have a border set I get a bunch of funky looking empty cells. I think rowspan is probably what I am looking at to fix my problem but I can't figure out how to change the rowspan dynamically with how many dates match the previous date.

 

$querydisp = 'SELECT * FROM comments ORDER BY Date DESC';

$result = mysql_query($querydisp);

echo "<table border='1' cellspacing='3' cellpadding='5'><tr><th>Date</th><th>Comments</th></tr>";

$DateCheck = "a";

$span = 1;

while($row = mysql_fetch_array($result)){

if($row['Date'] == $DateCheck){

$DateCheck = $row['Date'];

$span = $span +1;

echo "<tr><td rowspan = '" . $span . "'></td><td>" .$row['Comments']. "</td><tr>";}

else{

$span = 1;

$DateCheck = $row['Date'];

echo "<tr><td>" . $row['Date'] . "</td><td>" .$row['Comments']. "</td><tr>";

}

}

echo "</table>";

 

I tried the above but didn't get the results that I was looking for at all because I assume the table is getting created as the code is run and not at the end after reading all the code and adding up all the $span. Does this make sense? Should I go a different route? or can I make this work? I am very open to suggestions. Also, sorry for all the code iterations. I just wanted to show you guys that I am working on figuring it out and not just counting on you all to write my pages for me. Thanks again!

[rdrews]

Nevermind, I got something figured out.  Thanks for your help.