Page 1 of 1

number of replies query

Posted: Mon Mar 10, 2003 12:48 am
by theclay7
hello...

i have a forum using parent id and qid(auto increment). Parent id will be set "0" if this is a new thread, otherwise, parent id = qid (reply).

In my index page, I can read the parent thread just as usual using select * from forum where parent id = 0 order by last update date.

But I want to know how do I also display the num of replies to those replies also.

I am using a while loop to generate the parent thread as

while ($row = $result->fetchRow()) {
echo" $q_id = $row[0];";
echo" $forum_type = $row[1];";
echo" $user_id = $row[2];";
echo" $title = $row[3];";
echo" $body = $row[4];";
echo" $last_upd_date = $row[5];";
}

Posted: Mon Mar 10, 2003 8:22 am
by Rob the R
You'd have to join the same table back to itself and sum the number of replies:

Code: Select all

select parent.id, parent.parent_id, parent.last_update_date,
   sum(child.id) replies
from forum parent,
left join forum child on parent.id = child.parent_id
where parent.parent_id = 0
group by parent.id, parent.parent_id, parent.last_update_date
order by parent.last_update_date
Here, I just grouped by three columns, but any column from the parent forum you want to display should be added to both the select statement and the group by statement.

Posted: Mon Mar 10, 2003 8:47 pm
by theclay7
i tried using the select thing in the while loop and when I perform...fetchRow() or just simply using numRows(), it came out as a fatal error and said that it is an undefined function call:
why? my code is as follow:

while ($row = $result->fetchRow()) {
echo" $q_id = $row[0];";
echo" $forum_type = $row[1];";
echo" $user_id = $row[2];";
echo" $title = $row[3];";
echo" $body = $row[4];";
echo" $last_upd_date = $row[5];";


$query_C = "select count(*) from game_forum where parent_id = '".$q_id."'";
echo " Query = $query_C <br>";
$result_C = $db->query($query_C);
while ($row_C = $result_C->fetchRow()){
$numOfReply = $row_C[0];
}
echo " numOfReply = ".$numOfReply."<BR>";
//$rownum_C = $result_C -> numRows();

}