Display comments for each post in PHP/mySQL site

Question

I'm trying to display multiple posts, each with its own multiple comments. This, below, works for the first post (or, at least shows 2 of the 3 comments), but not for the rest. I searched but couldn't find (or didn't recognize) an answer to this exact problem on this site, or elsewhere. Thanks.

$result1 = @mysql_query('select * from posts,comments where comment_post_ID = ID and post_status="publish" and comment_approved="1" ORDER BY post_date,category,subcat1,subcat2 ASC');
$result2 = @mysql_query('select * from comments where comment_approved="1" ORDER BY comment_date');
$row = mysql_fetch_array($result1);
$row2 = mysql_fetch_array($result2);
while ($row = mysql_fetch_array($result1)) {
    $post = $row['post_content'];
    $id = $row['ID'];
    $title = $row['post_title'];
    $content = "<br /><h3 align=\"left\">" . $title . "</h3>\n";
    $content .= "<h4 align=\"left\">by " . $row['post_author'] . " - " . $row['post_date'] . "</h4>\n";
    $content .= "<p align=\"left\">" . var_export($post, true) . "</p>\n<p>\n";
    echo $content; 
    while ($row2 = mysql_fetch_array($result2)) {
            $comment = "<blockquote>";
            $comment .= "<h4 align=\"left\">" . $row2['comment_author'] . " commented on " . $row2['comment_date'] . "</h4>\n";
            $comment .= "<p align=\"left\">" . $row2['comment_content'] . "</p>\n<p>\n";
            $comment .= "</blockquote>";
            if($row2['comment_post_ID'] == $id) {
                echo $comment;
            }
        }
    }

What are you doing. You should get all the post and then for each post (in loop) you should get all the comments associated with that post by using post_id. I assume that in your comment table there is post_id which establishes the relationship between post and comment table. You can use join too, But for clear and simple understanding get all post first and loop through all the post to display post and inside loop get all comment by passing post ID. loop through the comments and display. (Use Nested loop). If your requirement is display all posts with comments then I can rewrite your code. — BetaDev, Dec 22 '16 at 16:46
I thought my loops were correct, but apparently not. I want to display ALL posts, and all comments associated with any post that has comments. Yes, comment_post_ID from the comments table corresponds to the ID from the posts table. Thanks. — user2444243, Dec 22 '16 at 18:14
so do the first loop through all the post and inside that loop search for all the comments associated with that particular post. — BetaDev, Dec 22 '16 at 18:16
If you're writing new code, **_please_ don't use the `mysql_*` functions**. They are old and broken, were deprecated in PHP 5.5 (which is so old it no longer even receives security updates), and completely removed in PHP 7. Use [`PDO`](https://secure.php.net/manual/en/book.pdo.php) or [`mysqli_*`](https://secure.php.net/manual/en/book.mysqli.php) with _prepared statements_ and _parameter binding_ instead. See http://stackoverflow.com/q/12859942/354577 for details. — ChrisGPT was on strike, Dec 22 '16 at 21:44

score 0 · Accepted Answer · answered Jan 04 '17 at 16:35

This, below, finally worked as hoped:

    $postData = mysqli_query($conn,"SELECT * FROM wp_posts ORDER BY post_date ASC) or die(mysqli_error()); 
    $commentData = mysqli_query($conn,"SELECT * FROM wp_comments WHERE comment_approved = '1' ORDER BY comment_date ASC") or die(mysqli_error()); 
    $posts = array();     
    while($row = mysqli_fetch_assoc($postData)) {
        $posts[] = $row; 
        $commentrow = mysqli_fetch_assoc($commentData);
        $comments[] = $commentrow; 
        echo '<h3>' . $row['post_title'] . '</h3>';
        echo '<h5>' . $row['post_author'] . ', ' . $row['post_date'] . '</h5>';
        echo '<p>' . $row['post_excerpt'] . '... <a href="' . $row['permalink'] . '">read more</a></p>';
        if($row['comment_count'] > 0) {
            echo '<blockquote>';
            echo '<b>Comments</b><br />';
            foreach($comments as $comment) {
                if($row['ID']==$comment['comment_post_ID']) {
                    $comment_excerpt = substr($comment['comment_content'],0,100);
                    echo '<br>' . $comment_excerpt . ' - <b>' . $comment['comment_author'] . '</b>, ' . $comment['comment_date'] . '<br>'; 
                    }
                }            
            echo '</blockquote>';
            }
        }

Display comments for each post in PHP/mySQL site

1 Answers1