CODEWITHSUNDEEP
phpsqlselectcommentsposts
Maor Polak
Maor Polak

Reputation: 25

Displaying comments related to posts using PHP & MySQL

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.

Upvotes: 1

Views: 2236

Answers (1)

AlexeyKa
AlexeyKa

Reputation: 558

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;

Upvotes: 1

Related Questions