number of replies query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
theclay7
Forum Commoner
Posts: 50
Joined: Wed Feb 19, 2003 3:17 am

number of replies query

Post 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];";
}
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post 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.
theclay7
Forum Commoner
Posts: 50
Joined: Wed Feb 19, 2003 3:17 am

Post 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();

}
Post Reply