Select forum thread and latest reply

Question

I am building a php/mysql forum, and it has two tables. The first is the master 'thread' table, and the second is the replies table, structures as such (omitting some columns that are not relevant to this question, such as 'title', 'body_text', etc).

thread_table Columns:

id (int, primary key)

user_id (int)

deleted (bool/tiny_int)

date_posted (datetime)

reply_table Columns:

id (int, primary key)

linked_to (int)

user_id (int)

deleted (bool/tiny_int)

date_posted (datetime)

I'm really stuck with my sql, I want to select all the 'threads', the latest 'reply' per thread where deleted does not equal 1, and also the total count of replies for each thread where deleted does not equal 1. And to top it off I also want to select the 'user_name' from my users table where the thread/reply 'user_id' is equal to the same id in the user table.

JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

I assume you use auto_increment on your id's so that the highest number is always the last reply.

Please note that the code below is not tested but it should help you along the way! I've commented the steps so you can see what's happening where. Hope this helps you!

//Fetch the thread headers and put result in array

$get_header = "SELECT thread.id, user.user_name, thread.date_posted FROM thread_table thread, user_table user WHERE thread.user_id = user.user_id AND thread.deleted != 1;";

    $Rget_header = mysql_query($get_header) or die(mysql_error());

    while($row_get_header = mysql_fetch_array($Rget_header)){

            $arr_get_header[] = array( "thread_id"   => $row_get_header['id'],

                                       "username"    => $row_get_header['user_name'],

                                       "date_posted" => $row_get_header['date_posted']

                                      );

    }

//Loop through the header array

for ($c = 0; $c < count($arr_get_header); $c++){

    //Fetch the count

    $count_replies = "SELECT COUNT(id) as reply_count FROM reply_table WHERE linked_to = '".$arr_get_header[$c]['thread_id']."';";

        $Rcount_replies = mysql_query($count_replies) or die(mysql_error());

        $num_count_replies = mysql_num_rows($Rcount_replies);

            if ($num_count_replies == 1) {

                $obj_get_reply = mysql_fetch_object($Rcount_replies);

            }

    //Get last reply

    $get_reply = "SELECT MAX(reply.id) as reply_id, user.user_name, reply.date_posted FROM reply_table reply, user_table user WHERE reply.user_id = user.id AND reply.deleted != 1 AND reply.linked_to = '".$arr_get_header[$c]['thread_id']."' ORDER BY reply_id;";

        $Rget_reply = mysql_query($get_reply) or die(mysql_error());

        $num_get_reply = mysql_num_rows($Rget_reply);

            if ($num_get_reply == 1) {

                $obj_get_reply = mysql_fetch_object($Rget_reply);

            }

    //Echo result

    echo 'Thread id: '.$arr_get_header[$c]['thread_id'].'<br />';

    echo 'Last reply id: '.$obj_get_reply->reply_id.'<br />';

    echo 'Reply count: '.$obj_count_replies->reply_count.'<br />';

}