Displaying comments related to posts using PHP & MySQL

Question

I have a page that displays all posts related to category X. I'm having trouble displaying the comments related to each post.

Here are the relevant database tables:

TABLE 'articles'
'article_id'
'title'
'article'
'updated'
'created'

TABLE 'categories'
'cat_id'
'category'

TABLE 'article2cat' (relates an article to a category using two primary keys)
article_id
cat_id

TABLE 'comments'
comment_id
comment
user
created

TABLE 'comment2article' relates a comment to an article using two primary keys)
comment_id
article_d

The SQL query that pulls up the articles and comments is:

SELECT articles.article_id, articles.title, articles.article, DATE_FORMAT(articles.created, "%b, %Y") AS date_created, comments.comment_id, comments.user, comments.comment 
FROM articles INNER JOIN article2cat USING (article_id), comments INNER JOIN comment2article USING (comment_id)
WHERE cat_id=4
ORDER BY articles.created DESC;

The code that displays the articles and comments is:

<table id="articles-table" cellpadding="0" cellspacing="0">
    <?php while ($row = $result->fetch_assoc()) { ?>
    <tr>
        <td id="articles-article">
            <div id="articles-article-internal">
                <?php echo format($row['article']); ?></div>
        </td>
    </tr>
    <tr>
        <td><?php echo $row['comment']; ?></td>
    </tr>
    <tr>
        <td>&nbsp;</td>
    </tr>
    <?php } ?>
</table>

The problem is when I try to echo out only the comments related to a given post/article. At the moment it's displaying all comments regardless of the post they belong to.

I found several similar questions on StackOverflow, but none that I was able to use to resolve my problem.

AlexeyKa · Accepted Answer · 2012-07-21 07:58:02Z

1

Select article and comments separately. Look at result of your query. With each of comment you get all copy of article. And what will happen when number of comments grow up? For example. You have one article about 5000 characters. When you do your query you get near 5000 bytes of data. But if your article have 200 comments? Result of your query is about 5000*200 + all comments bytes.

SELECT comments.*
FROM comments INNER JOIN comment2article USING (comment_id)
WHERE comment2article.article_d= ID of article
ORDER BY comments.created DESC;

answered Jul 21, 2012 at 7:58

AlexeyKa

5583 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Maor Polak Over a year ago

Thank you Alexey. It's already looking better. However, it doesn't solve the issue of dynamically showing only the comments related to each post. In your example, I have to define the article id that the comments are associated with. But what if I have several articles on the same page, each with a different set of comments?

Collectives™ on Stack Overflow

Displaying comments related to posts using PHP & MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related