How to delete individual row from a GROUP_CONCAT?

Question

I'm programming a news update system with comments. I have three tables.

1. news_tbl
2. users_tbl (authors of news posts)
3. comments_tbl (is connected with a foreign key - news_id)

I have tried two methods with, and without GROUP_CONCAT.

I have tried this:

SELECT *, COUNT(comments_tbl.comments_id) AS `comments_count` 
FROM news_tbl AS n
LEFT JOIN users_tbl AS u
ON n.user = u.username 
LEFT JOIN comments_tbl AS c
ON c.news_id = n.news_id
GROUP BY n.news_id;

It works, but I can only display the first written comment, however, I want to display all the comments, attached to the news update.

I have also used GROUP_CONCAT for the comments, so there will be no duplicates of the news post, for each of comments. I'm not sure whether it is the right method, but it works.

My problem is, I have a login system, and I want the user to be able to delete the comments individually, with a delete button for each of them. But I do not know how to seperate the comments individually, and fetch the id from each comment. I have tried with the PHP's explode, but it didn't work for me.

This is my code:

<?php
require_once('connect.inc.php');
$conn = dbConnect('pdo') or die('no connection');

$sqlquery = "SELECT news_tbl.*, users_tbl.*,
GROUP_CONCAT(comments_tbl.comments_id) AS commentsid,
GROUP_CONCAT(CONCAT ('<hr><h6>Navn: ', comments_tbl.name,'</h6>','<p>',comments_tbl.comment, '</p>') SEPARATOR '<br><br>') AS comments, 
COUNT(comments_tbl.comments_id) AS comments_count
FROM news_tbl
LEFT JOIN comments_tbl ON comments_tbl.news_id = news_tbl.news_id
LEFT JOIN users_tbl ON news_tbl.user = users_tbl.username
GROUP BY news_tbl.news_id
ORDER BY news_tbl.news_id DESC LIMIT 5";

$result = $conn->query($sqlquery);
foreach($result as $row) {
  // AS
  $commentsid = $row['commentsid'];
  $comments = $row['comments'];
  $count = $row['comments_count'];

  //users_tbl
  $username = $row['username'];

  //news_tbl
  $newsid = $row['news_id'];
  $headline = $row['headline'];
  $bodytext = $row['bodytext'];
  $picture = $row['picture'];
  $date = $row['date'];

  //comments_tbl
  $commentid = $row['comments_id'];
  $name = $row['name'];
  $comment = $row['comment'];
?>

  <div class="row-fluid">
    <div class="news">
      <div class="text-left">
        <h3 class="pull-top"><?php echo $headline ?></h3>
          <div class="row-fluid span12">
            <img src="<?php echo 'upload/'.$picture ?>" class="span9 img-news"/>
          </div>
          <div class="row-fluid">
            <div class="span3">
              <p class="author"><i><span class="red">Skrevet af: </span> </i><?php echo $username ?></p>
            </div>
            <div class="span4">
              <p><i ><span class="red">Oprettet:</span> </i> <?php echo $date ?></p>
            </div>
          </div><!--/ End author-row -->
        <!--<p class="author"><i><span class="red">Skrevet af: </span> </i><?php echo $username ?>&nbsp;&nbsp;|&nbsp;&nbsp;<i ><span class="red">Oprettet:</span> </i> <?php echo $date ?></p>-->
        <p class="push-top-bottom"><?php echo $bodytext ?></p>
      </div>
      <hr>
      <p class="text-left pull-top3"> <b><?php echo $count ?> kommentar</b></p>
      <hr class="grey">
      <div class="row-fluid pull-top2">
        <div class="pull-left span12 text-left well">
          <?php echo $comments; ?>
        </div>
        <!-- Opret kommentar -->
        <div class="row-fluid pull-left">
          <form action="insert.php?<?php echo 'news_id=' . $newsid; ?>" method="post" class="pull-left leave-comment">
            <input type="text" name="name" id="name" class="comment" placeholder="Navn" required/> <br> <br>
            <textarea type="text" name="comment" id="comment" class="comment" placeholder="Skriv kommentar" rows="5" required></textarea> <br> <br>
            <input type="submit" value="Opret kommentar" class="btn pull-left"/>
          </form>
        </div>
      </div><!--/ End comments-row -->
    </div><!--/ End news-container -->
  </div><!--/ End news row -->
<?php  
  if (isset($_SESSION['valid_user'])) {
    // onClick slet nyhed med alert box
    echo '<div class="row-fluid">';
    echo '  <div class="pull-left">';
    echo '    <a class="btn btn-danger" onclick="deleteNews('. $newsid.')" style="margin-right:20px;">Slet nyhed</a>';
    echo '    <a href=update.php?news_id='.$newsid.'&username='. $username .' class="btn btn-info">Updater nyhed</a>';
    echo '    <br><br><br><br>';
    echo '  </div>';
    echo '</div>';
  }
?>
  <hr>
<?php  
  //End foreach
}
?> 

Answer 1

The function GROUP_CONCAT has a limit to how long the resulting concatenated string can be. You are very likely to reach this limit quickly by trying to concatenate HTML or potentially long comments.

I have found it is better to run the first query from the news_tbl, and build an array in PHP with all the unique ids from news_tbl. Next, run a second query to get all comments for the news records. For example:

$sqlquery = "SELECT * FROM news_tbl LIMIT 10";

$result = $conn->query($sqlquery);
$newsIds = array();
foreach($result as $row) {
    $newsIds[] = $row['id'];
}
$sql = "SELECT * FROM comments_tbl WHERE news_id IN (".implode(', ', $newsIds).")";
$result2 = $conn->query($sql);

// process each comment individually from $result2