How to display all comments per article (PHP & SQL)?

Question

So I have two tables, article and comments (which has one-to-many relationship (1 article - many comments)). This is how the table is structured:

Articles - id (prikey), title, publicationDate, content

Comments - com_id (prikey), author, comment, id (foreign key)

I used this to query the two tables:

SELECT * FROM articles as a INNER JOIN comments as c ON a.id = c.id

Previously, I was only displaying the articles table using this:

<?php 
while($row = mysqli_fetch_array($query)) {

echo "
<div id='article'> 
<header>
    <hgroup>
         <h2>".$row['title']."</h2>
         <h4>Posted on ".$row['publicationDate']."</h4>
    </hgroup>
</header><p>".$row['content']."</p></div>";
}
?>

This displays all articles (with date, title, content, etc.). Now there are comments. How do I edit the php code (or if my query is incorrect, how to write the query), so that it shows all articles and all comments per article as in:

Article One
 -- Comment 1
 -- Comment 2, etc.

Article Two
 -- Comment 1
 -- Comment 2, etc.

Answer 1

An alternative would be to split the query into two.

The first would bring back the articles you want...

SELECT * FROM article;

Once you have those, you can get all the IDs and use something like the following

SELECT * FROM comments WHERE article_id IN (".$list.");

This restricts the MySQL queries to 2 whilst getting all the data you need. After this loop around the article data, and in that loop, loop around the comments data.

This also means that, unlike using GROUP_CONCAT, you will also have author data to use.

It's not a very eloquent solution, but should work.

Answer 2

Query:

    SELECT c.author, c.comment, 
           a.id article_id, a.title, a.publicationDate, a.content
      FROM comments c
RIGHT JOIN articles a
        ON c.id = a.id

PHP:

<?php
$lastArticleId  = 0;
$isNewArticle   = false;
while($row = mysqli_fetch_array($query)) {
    $isNewArticle = ($lastArticleId != $row['article_id']) ? true : false;
    if($isNewArticle) {
        $lastArticleId = $row['article_id']; ?>
        <div class="article">
            <header>
                <hgroup>
                    <h2><?php echo $row['title']; ?></h2>
                    <h4>Posted on <?php echo $row['publicationDate']; ?></h4>
                </hgroup>
            </header>
            <p><?php echo $row['content']; ?></p>
        </div>
<?php
    }
    if($row['comment'] != '') { ?>
        <p><strong><?php echo $row['author']; ?></strong> - <?php echo $row['comment']; ?></p>
<?php
    } ?>
<?php
} ?>