PHP commenting system with 1 level reply. Query display issue

Question

I am trying to create a PHP commenting system similar to facebooks wall but with only 1 level of reply for each post.

I would like this format:

Post 1

Response 1
Response 2
Response 3

Add comment box

----------

Post 2
Response 1
Response 2
Response 3

Add comment box

However, my code at the moment produces this result:

Post 1
Response 1

Add comment box

Post 1
Response 2

Add comment box
----------------
Post 2
Response 1

Add comment box

---------------

Post 2
Response 1

Add comment box

---------------

I want to loop through the results so that the post message does not get printed along with the next comment in line related to that post every time it loops. So it should loop over the main message once, print it, and then post all its corresponding comments underneath by looping over them as well.

Table Structure

posts

• p_id = the number of that post
• user_id = user they are posting about
• poster_user_id = the person making the post
• post

comments

• comment_id = the number of that comment
• post_id = the number of the post the comment is related to
• commenter_user_id = the person who is making that comment
• comment

Code

$query = "SELECT posts.p_id, comments.post_id, posts.poster_id,
      posts.user_id, message, `comments.commenter_user_id, comments.comment`
    FROM posts, comments
    WHERE posts.p_id = comments.post_id";
$query_run = mysql_query($query) or die(mysql_error());

while ($query_row = mysql_fetch_assoc($query_run)) {
    $pid = $query_row['p_id'];
    $post_id = $query_row['post_id'];
    $poster_id = $query_row['poster_id'];
    $user_id= $query_row['user_id'];
    $message = $query_row['message'];
    $commenter_user_id = $query_row['commenter_user_id'];
    $comment = $query_row['comment'];

    echo "
      <div id=\"post\">
        Post $pid $post_id Poster: $poster_id  Mentions: $user_id 
        <br><br> $message <br><br> 

        <ul class=\"comment\">
            $commenter_user_id 
            <li> $comment </li>
        </ul>

        <form name=\"message\" method=\"post\" action=\"sendmessage.php\">
        <textarea name=\"message\"></textarea> <br>
        <input type=\"submit\" value=\"Send\" /></form>
      </div>
      <br>";
}

Any advice on how i would produce the desired result? I know there is probably an easy fix to this just by changing up my logic but i have just started learning. Your help on this would be greatly appreciated!

UPDATE

Thanks everyone for you help. I have incorporated aspects of your code examples and it works perfectly.

just one more question:

Each as mentioned above contains a comment. Once the a comment has been posted i would like the comment table to be updated with the new post. How would I grab the post_id and the commenter_user_id variable for each specific post when i comment so that i can then use that data to perform an insert query to the comments table like so:

INSERT INTO `comments`SET
    `post_id` = $post_id
    'commenter_user_id = $_SESSION['commenter_user_id'] " (I was thinking sessions?)

I was thinking of grabbing the data by passing variables through the url to the script page:

    while ($query_row = mysql_fetch_assoc($query_run)){

// more variable definitions
$post_id = $query_row['post_id']



echo " <form method=\"post\" action=\"add_comment.php?post_id='.$post_id.'>
                <textarea name=\"comment\"></textarea> <br>
                <input type=\"submit\" value=\"Send\" /></form> "

But I tried passing the variable to other page like so: action=\"add_comment.php?post_id='.$post_id.' but i get an undefined index: variable when i try to do that.

Any thoughts on how i can accomplish this?

Answer 1

That's what I'd expect to happen because your SQL query is SELECTing from two tables which is an inner join - so you'll get duplication of the rows.

Try out the query directly in mysql query window to see what comes out.

I'd recommend storing the value of the current post and only outputting the comments when the post hasn't changed; something like the following:

Also you're not recording the post id in the form (either as a hidden variable or in the URL) so I added a hidden variable as not having the post_id I suspect will be your next bug.

    $last_post_id = null;

    while ($query_row = mysql_fetch_assoc($query_run)){

       $post_id = $query_row['post_id'];
       if ($last_post_id == $post_id)
       {
?>
                    <ul class=\"comment\">
                    $commenter_user_id 
                    <li> $comment </li>
                </ul>    
<?php
       }
       else
       {
          if ($last_post_id !== null)
          {
?>
                <form name=\"message\" method=\"post\" action=\"sendmessage.php\">
                <textarea name=\"message\"></textarea> <br>
                <input type=\"hidden\" name="\post_id\" value=\"$last_post_id\" />       </form>
                <input type=\"submit\" value=\"Send\" /></form>
<?php
              $last_post_id = $post_id;
          }
?>
           <div id=\"post\">
                Post $pid $post_id Poster: $poster_id  Mentions: $user_id 
                <br><br> $message <br><br> 
                <ul class=\"comment\">
                    $commenter_user_id 
                    <li> $comment </li>
                </ul>
            </div>
<?php
   }
?>

---

Updated answer

in sendmessage.php (using my form above with the input type=hidden) see below. You are correct that you will need to use sessions to get the current user ID for posting purposes.

I would recommend using the POST method for submitting any form that results in modification of data.

<?php

if (isset($_POST['post_id']))
{
    $post_id = mysql_real_escape_string($_POST['post_id']);
    $user_id = $_SESSION['current_user_id'];
    $sql = "INSERT INTO `comments`SET
            `post_id` = $post_id
            'commenter_user_id = $user_id";

    if (!mysql_query($sql))
        echo "MySQL error during insert ". mysql_error();
}
else
{
    echo "Invalid form posting";
}

Answer 2

The problem with your code is that for each comment you read from the database, you also get the post from the join. There are two possible solutions I can think of right now:

The first option would be to keep your query as it is, but whenever you print a row you look at the post you print. You would store that last printed post id and if the current id is the same, you simply do not print the post but only the comment. This would obviously require to split up the markup for the post and the comment (which is a good idea anyway). Basically this would like this:

$lastPost = -1;
while ($query_row = mysql_fetch_assoc($query_run)) {
    // ...
    if ($pid != $lastPost) {
        echo "<code for the post>";
        $lastPost = $pid;
    }
    echo "<code for the comment>";
}

The other idea would be to split up your query, loading firt all the posts and then for each post you print load the comments and print them all. This obviously requires n+1 SQL queries for n posts but it reduces the amount of data that comes from your database (as you no longer have duplicates of the post coming for each comment). If you have a page for displaying only a single post then this would allow you to reuse the code completely too. It generally looks like this:

$query_run = // query only for posts, ignoring comments
while ($query_row = mysql_fetch_assoc($query_run)) {
    // ...
    echo "<code for the post>";

    $comment_query = // query only for comments with post_id = $pid
    while ($comment_row = mysql_fetch_assoc($comment_query)) {
        // ..
        echo "<code for the comment>";
    }
}

Update

You have multiple ways to include information in a form that is then passed to the target page. One would be to include them as GET parameters in the URL, as you tried yourself. The other would be to include hidden input elements that simply hold your static data. Regardless of what you use, you will have to get those values from either $_GET or $_POST on the target page. After some input validation (which you should do), you can then use those values to insert a comment just like you would normally do with just one form, except that the post id comes from a request variable as well. If you don’t know how to handle the user input correctly and create a comment, I’d suggest you to start a bit slower and just make one comment form for now to find out how it works.

The “undefined index” error means that you try to access an index in an array (probably the $query_row) that does not exist. You can only access those values that you actually requested in your sql query. So check if the variable you try to access is actually in the query.

Answer 3

What about recursion? Does it suit your needs? If yes, then all you need is the parent message id to each message (or NULL if it is not a response). Then you load all the messages and call the function with NULL messages only, also providing the whole array of messages.