CODEWITHSUNDEEP
phpmysqlcomments
user1260310
user1260310

Reputation: 2227

PHP/MYSQL Comment-reply table structure

I have got a comment script written in PHP working pretty well. It saves the comments in a a mysql comments table with fields for commentid, subjectid, userid and timecreated. I now want to implement a reply function. Should I create a new table of replies with userid, time created, reply and a commentid field.

Or would it be better to just include the replies in the comments table as comments but with two extra fields, one denoting that this particular comment is a reply and the other the comment it is a reply to.

Leaning toward first option but it means extra queries.

Would appreciate any suggestions from those with experience!

Upvotes: 1

Views: 4379

Answers (2)

Patrick Moore
Patrick Moore

Reputation: 13344

This is not an answer to your original question, but I did want to show you a quick and dirty approach to nesting comments that I would use if this were my project. There are almost certainly more elegant methods and another member here might have suggestions.

<?php

// Retrieve ALL comments related to this subject (including all replies and nested comments)
$rs = mysql_query( 'SELECT * FROM `comments` WHERE subjectid = '7' ORDER BY timecreated ASC' );

// Process ALL comments and place them into an array based on their parent id
// Thus we end up with something like:
// $data[ 0 ][ 0 ] = array( 'commentid' => 1, 'subjectid' => 7, 'userid' => 1, 'timecreated' => '2012-05-01 12:00:00', 'parentid' => 0 );
// $data[ 0 ][ 1 ] = array( 'commentid' => 2, 'subjectid' => 7, 'userid' => 5, 'timecreated' => '2012-05-01 14:00:00', 'parentid' => 0 );
// $data[ 2 ][ 0 ] = array( 'commentid' => 3, 'subjectid' => 7, 'userid' => 1, 'timecreated' => '2012-05-01 16:00:00', 'parentid' => 2 ); This is a reply to commentid #2
// $data[ 2 ][ 1 ] = array( 'commentid' => 4, 'subjectid' => 7, 'userid' => 5, 'timecreated' => '2012-05-01 16:30:00', 'parentid' => 2 ); This is another reply to commentid #2
// $data[ 3 ][ 0 ] = array( 'commentid' => 5, 'subjectid' => 7, 'userid' => 3, 'timecreated' => '2012-05-01 17:00:00', 'parentid' => 3 ); This is a reply to the reply with commentid #3

while ( $row = mysql_fetch_assoc( $rs ) ){
    $data[ $row['parentid'] ][] = $row;
}

function output_comments( &$data_array, $parentid ){

    // Loop through all comments with matching $parentid

    foreach ( $data_array[ $parentid ] as $k=>$v ){
        // Output all comments, open .comment DIV but do not close (for nesting purposes)
        echo '<div class="comment"><strong>' . $v['username'] . ':</strong> ' . $v['message'];

        // If there are any replies to this comment, output them by recursively calling this function
        if ( count( $data_array[ $v['commentid'] ] > 0 ){
            output_comments( $data_array, $v['commentid'] );
        }

        // Close the open DIV
        echo '</div>';
    }
}


// Call the output_comments() function, which will recursively run to support unlimited nesting

output_comments( $data, 0 );

You could then style nested comments pretty simply by indenting any DIV.comment who has a parent DIV.comment

<style type="text/css">
.comment .comment {
    padding-left: 30px;
}
</style>

Upvotes: 5

Patrick Moore
Patrick Moore

Reputation: 13344

I would add two columns for referenceid, and parentid. That way, you can have nested comments, if you so choose. Much easier and more efficient than joining multiple tables in your queries. If the comment is not a reply, referenceid is 0 (or null). No need to create another column to flag whether it's a reply.

Upvotes: 9

Related Questions