CODEWITHSUNDEEP
phpmysql
Pranav Kaistha
Pranav Kaistha

Reputation: 295

Comment-Reply PHP Script - Only reply to 1st comment appear

The problem

I am creating a standard comment-reply script in which users can post comments in articles and can also reply to other user's comments.

I am able to post and retrieve comments through PHP and mySQL.

The problem is that I am not able to retrieve the 'replies' of all comments. ***Replies only for the 1st comment appear***. The reply to 2nd comment appears after I delete the 1st comment.

Data structure of my table

If a person posts a new comment, then his `name` and `comment` are entered into the database with Auto Incremented `id`; by default the `reply` value is **'0'**.

If someone replies to a comment then the all the details are filled in the same manner as above except the `reply` value which takes the value from the `id` of the comment whose reply is posted as shown below (The structure of mySQL table looks something like this): 
|id | name  |            comment            | reply |
|---|-------|-------------------------------|-------|
| 1 |Pranav | This is Pranav's Comment      |   0   | 
| 2 |Anita  | This is the Anita's Comment   |   0   |
| 3 |Rishab | 1st reply to Pranav's comment |   1   |
| 4 |Paul   | 1st reply to Anita's comment  |   2   |
| 5 |James  | 2nd reply to Pranav's comment |   1   |
| 6 |Rachel | This is Rachel's comment      |   0   |
| 7 |Pranshu| 1st Reply to Rachel's comment |   6   |
| 8 |Leora  | 2nd Reply to Anita's comment  |   2   |
| 9 |Pekka  | 3rd Reply to Anita's comment  |   2   |
For eg. If someone replies to Anita's comment having `id = 2`; then, the value of `reply` column of the new reply posted would automatically become 2.

PHP Code

$result hold the comments array
$result_reply holds the replies of those comments
while($row = mysqli_fetch_array($result)) loop displays the comments
Nested while($row_reply = mysqli_fetch_array($result_reply)) loop was written to display the respective replies under each comment but this doesn't happen.
if($reply_reply==$id) loop checks the reply column code with id of a particular comment so that a particular reply is only displayed under the respective comment.

Name of database is 'oxygentimes' and table is 'comments'

<?php

$dbc = mysqli_connect('localhost', 'root', '', 'oxygentimes') or die('Error connecting to mysql server');

$query = "SELECT * FROM `comments` WHERE reply=0";
$query_reply = "SELECT * FROM `comments` WHERE reply!=0";

$result = mysqli_query($dbc, $query) or die('Error querying database');

$result_reply = mysqli_query($dbc, $query_reply) or die('Error querying database');

while ($row = mysqli_fetch_array($result)) {
    $id = $row['id'];
    $name = $row['name'];
    $comment = $row['comment'];
    $reply = $row['reply'];

    if ($reply == 0) {
        echo "
        <div class='comment'>
            <div>$name</div>
            <div>$comment</div>
            <input type='hidden' value='$id'>
        </div>";

        while ($row_reply = mysqli_fetch_array($result_reply)) {
            $name_reply = $row_reply['name'];
            $comment_reply = $row_reply['comment'];
            $id_reply = $row_reply['id'];
            $reply_reply = $row_reply['reply'];

            if ($reply_reply == $id) {

                echo "
                <div class='reply-comment'>
                    <div>$name_reply</div>
                    <input type='hidden' value='$id_reply'>";
                echo "<div>Reply: $comment_reply</div>
                </div>";
            };
        };
    }
}

I only see replies under the 1st comment. Replies of 2nd comment appear after I delete the 1st comment. Please help! Can't figure out the error.

Upvotes: 3

Views: 7939

Answers (5)

Ruslanas Balčiūnas
Ruslanas Balčiūnas

Reputation: 7438

Query for replies inside first loop. I'm not suggesting it's the best solution however.

while ($row = mysqli_fetch_array($result)) {

    // construct query here

    $result_reply = mysqli_query($dbc, $query_reply)
        or die('Error querying database');
    //... etc.
}

Upvotes: 3

Chuksy
Chuksy

Reputation: 66

What I think you should do:

    //Query for all comments first i.e. where reply is 0 it is a comment not a reply
    $result_query_statement = "SELECT * FROM comments WHERE reply = 0";
    $result = mysqli_query($dbc, $result_query_statement) or die('Error querying database');
    
    //Loop through comments get the data and display it
    while ($row = mysqli_fetch_array($result)) {
    $id = $row['id'];
    $name = $row['name'];
    $comment = $row['comment'];
    $reply = $row['reply'];
    echo "
    <div class='comment'>
        <div>$name</div>
        <div>$comment</div>
        <input type='hidden' value='$id'>
    </div>";
    
    //After getting and displaying a comment from the database still in that loop use the ID of that comment from the database to look for the replies to that particular comment
    
    //Using the ID to query for comment replies
    //Note: Since the ID($id) is not 0 we would only get replies here automatically
    $result_reply_query_statement = "SELECT * FROM comments WHERE reply = $id";
    $result_reply = mysqli_query($dbc, $result_reply_query_statement) or die('Error querying database');
    
    //Finally oop through the replies gotten for that comment and display the reply
    while ($row = mysqli_fetch_array($result_reply)) {
        $name_reply = $row_reply['name'];
        $comment_reply = $row_reply['comment'];
        $id_reply = $row_reply['id'];
        $reply_reply = $row_reply['reply'];

            echo "
            <div class='reply-comment'>
                <div>$name_reply</div>
                <input type='hidden' value='$id_reply'>";
            echo "<div>Reply: $comment_reply</div>
            </div>";
    }
    //Closes the Comments Reply Loop
    }
    //Closes the Comments Loop

This is a much easier method to use which doesn't get confusing.

Please correct me If I made some sort of mistake.

Upvotes: 3

Vishal M Karnawat
Vishal M Karnawat

Reputation: 1

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
</head>
<body>
<?php
// Create connection
$conn = new mysqli('localhost', 'root', 'Jordan123', 'commentsystem2');

$sql1 = "SELECT * FROM comments WHERE reply_id = 0";
$result1 = mysqli_query($conn, $sql1);

while ($comment = mysqli_fetch_array($result1)) {
    $id = $comment['id'];
    $name = $comment['name'];
    $comment = $comment['comment'];
    
echo '
<div class="comments" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div>'.$name.'</div>
<div>'.$comment.'<br><br></div>
</div>
';

$sql2 = "SELECT * FROM comments WHERE reply_id = $id";
$result2 = mysqli_query($conn, $sql2);
while ($reply = mysqli_fetch_array($result2)) {
$id_reply = $reply['id'];
$reply_name = $reply['name'];
$reply_comment = $reply['comment'];
$reply_id = $reply['reply_id'];


echo '
<div class="replies" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div style="width:80%; text-align:center;">'.$reply_name.' replied to '.$name.'</div>
<div style="width:80%; text-align:center;">'.$reply_comment.'<br><br></div>
</div>
';

 }//end of replies while loop

}//end of comments while loop

?>
</body>
</html>

Upvotes: 0

user3005420
user3005420

Reputation: 9

@Chuksy The problem with your code is that it doesn't echo out the reply to a reply which would be a 3rd level deep. What if people have several levels deep where each person keeps replying to each other's replies? We would need a code to match indefinite levels.

Here's how I've re-written your code, but it only goes 2 levels deep where it captures only the reply to each original comment:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
</head>
<body>
<?php
// Create connection
$conn = new mysqli('localhost', 'root', 'Jordan123', 'commentsystem2');

$sql1 = "SELECT * FROM comments WHERE reply_id = 0";
$result1 = mysqli_query($conn, $sql1);

while ($comment = mysqli_fetch_array($result1)) {
    $id = $comment['id'];
    $name = $comment['name'];
    $comment = $comment['comment'];
    
echo '
<div class="comments" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div>'.$name.'</div>
<div>'.$comment.'<br><br></div>
</div>
';

$sql2 = "SELECT * FROM comments WHERE reply_id = $id";
$result2 = mysqli_query($conn, $sql2);
while ($reply = mysqli_fetch_array($result2)) {
$id_reply = $reply['id'];
$reply_name = $reply['name'];
$reply_comment = $reply['comment'];
$reply_id = $reply['reply_id'];


echo '
<div class="replies" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div style="width:80%; text-align:center;">'.$reply_name.' replied to '.$name.'</div>
<div style="width:80%; text-align:center;">'.$reply_comment.'<br><br></div>
</div>
';

 }//end of replies while loop

}//end of comments while loop

?>
</body>
</html>

Upvotes: 1

Artur Black
Artur Black

Reputation: 231

This is a solution that will cause mayhem in your database performance. It performs a database query for all the top level posts plus additional query for every immediate child of the top level. Also this solution only supports one level of depth.

Below is a solution that relies on a single database query and 2n iterations in a PHP-based loop. It also allows an infinite depth of comment tree (comment to a comment of a comment). This is only a conceptual solution so I used dummy data and CLI.

<?php

$comments = array(
  array(
    'id' => 1,
    'parent_id' => 0
  ),
  array(
    'id' => 2,
    'parent_id' => 0
  ),
  array(
    'id' => 3,
    'parent_id' => 1
  ),
  array(
    'id' => 4,
    'parent_id' => 2
  ),
  array(
    'id' => 5,
    'parent_id' => 2
  ),
  array(
    'id' => 6,
    'parent_id' => 4,
  ),
  array(
    'id' => 7,
    'parent_id' => 0
  )
);

$grouped = array();
foreach ($comments as $comment) {
  if (!isset($grouped[$comment['parent_id']])) {
    $grouped[$comment['parent_id']] = array();
  }
  $grouped[$comment['parent_id']] []= $comment;
}

function render_comment($comment, &$grouped, $depth = 0) {
  for ($i=0; $i<$depth; $i++) {
    echo "\t";
  }
  echo $comment['id'];
  echo "\n";
  foreach ($grouped[$comment['id']] as $reply) {
    render_comment($reply, $grouped, $depth+1);
  }
}

foreach ($grouped[0] as $top_level_comment) {
  render_comment($top_level_comment, $grouped);
}

?>

Upvotes: 2

Related Questions