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];";
}
number of replies query
Moderator: General Moderators
You'd have to join the same table back to itself and sum the number of replies:
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.
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_datei 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();
}
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();
}