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.

We know nothing of your comments table schema, how can we suggest anything? — Shef, Sep 09 '11 at 16:04

score 2 · Accepted Answer · answered Sep 09 '11 at 16:20

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.

I used this solution for now as I am still learning the basics. But why is it not an eloquent solution? Is it not advisable to have two queries (one looped inside another)? — catandmouse, Sep 12 '11 at 02:33

Johan · Answer 2 · 2011-09-09T19:01:59.870

Use something like

SELECT a.article
       ,GROUP_CONCAT(CONCAT('<p>', c.comment, '</p>') SEPARATOR "\n") as comments
FROM
article a
INNER JOIN comment c ON (c.article_id = a.id)
WHERE a.id = '12454';

You may have to fiddle a bit with the separator.

See: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Do note however:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

See here how to change max_allowed_packet
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet

You can change `GROUP_CONCAT(CONCAT('
', c.comment, '
') SEPARATOR "\n")` — ajreal, Sep 09 '11 at 18:42

score 1 · Answer 3 · answered Sep 09 '11 at 16:52

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
} ?>

score 0 · Answer 4 · answered Sep 09 '11 at 16:08

0

Look into MySQL GROUP_CONCAT which will return a comma delimited list of items. You can then explode that for your comments section.

answered Sep 09 '11 at 16:08

John

701
6
10

score -1 · Answer 5 · answered Sep 09 '11 at 16:09

once a person will comment on a article insert article id with that comment and later get them accordingly something like this

once a person will select an article to read send article id in the $_GET to your article page so you can excess the article id.Once a person will comment on that article insert it as follows

$sql = mysql_query("INSERT INTO comments_table (subject,article_id,comments) VALUES ('$subject','$_GET['id']','$comments')");

and later when you pulling them do it the same way as you have the article id in the $_GET you can access it run a query like this

$fetch = mysql_query("SELECT * FROM comments WHERE article_id = $_GET['id'] ORDER BY id DESC") or die(mysql_error());

Hope this will work

@Henry, please read this question and it's answers and never post code like this agian: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain This will get your php app pwnd. — Johan, Sep 09 '11 at 16:19

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

5 Answers5