I'm trying to write a script that will first query the database to retrieve posts, and then retrieve the number of replies for each post.
The posts table contains:
post_id
message
date
The replies table contains
reply_id
post_id
message
date
I tried to nest my while loops, but the second array always comes up empty. If I print_r the results from within the while loop, it prints out as expected, so I'm sure it has something to do with the while loop. I can't figure out, though, how to accomplish what I need.
$getPosts = "SELECT *
FROM bp_forum_posts
WHERE topic='".stripslashes($_GET['subtopic'])."' ORDER BY posted_date DESC";
$resultPosts = $database->query($getPosts);
/* Error occurred, return given name by default */
if(!$resultPosts || (mysql_num_rows($resultPosts) < 1)){
return mysql_error();
}
$posts = array();
$i = 0;
while($row1 = mysql_fetch_array($resultPosts)) {
$tmp = array(
'post_id' => $row1['post_id'],
'postmessage' => $row1['message'],
'postdate' => $row1['posted_date']
);
$posts[$i++] = $tmp;
$getReply = "SELECT post_id, count(*) as counter from bp_forum_replies WHERE post_id='".$row1['post_id']."' GROUP BY post_id";
$replyResults = $database->query($getReply);
$replies = array();
$b = 0;
while($row2 = mysql_fetch_array($replyResults)) {
$tmp2 = array(
'rep_post' => $row2['post_id'],
'count' => $row2['counter']
);
$replies[$b++] = $tmp2;
}
}
UPDATED:
I changed it to:
$qa = "SELECT *
FROM bp_forum_posts
WHERE topic='".stripslashes($_GET['subtopic'])."' ORDER BY posted_date DESC";
$ra = $database->query($qa);
/* Error occurred, return given name by default */
if(!$ra || (mysql_num_rows($ra) < 1)){
return mysql_error();
}
$posts = array();
$i = 0;
$postlist = '';
while($row1 = mysql_fetch_array($ra)) {
$tmp = array(
'post_id' => $row1['post_id'],
'poster' => $row1['username'],
'posttitle' => $row1['title'],
'postmessage' => $row1['message'],
'postdate' => $row1['posted_date'],
'posttopic' => $row1['topic']
);
$posts[$i++] = $tmp;
$qb = "SELECT count(reply_id) as counter FROM bp_forum_replies WHERE post_id='".$row1['post_id']."'";
$rb = $database->query($qb);
$b=0;
$replies = array();
while($row2 = mysql_fetch_array($rb)) {
$rtmp = array(
'rep_post' => $row1['post_id'],
'count' => $row2['counter']
);
$replies[$b++] = $rtmp;
}
}
But if I print the replies array, I get this: Array ( [0] => Array ( [rep_post] => 2 [count] => 0 ) )
To be clear, the data in my tables look like this:
POSTS TABLE:
+---------+------------------------+---------------------+
| post_id | message | posted_date |
+---------+------------------------+---------------------+
| 1 | A test message | 2007-05-26 14:23:50 |
| 2 | a test message | 2007-05-26 23:43:21 |
| 3 | a second test message | 2007-05-26 23:43:32 |
| 4 | a third test message | 2007-05-26 23:44:49 |
| 5 | a fourth test message | 2007-05-26 23:44:49 |
| 6 | a fifth test message | 2007-05-26 23:44:49 |
| 7 | a sxith test message | 2007-05-26 23:44:49 |
| 8 | a seventh test message | 2007-05-26 23:44:49 |
| 9 | a eighth test message | 2007-05-26 23:44:51 |
+---------+------------------------+---------------------+
REPLIES TABLE:
+----------+---------+---------------------------+---------------------+
| reply_id | post_id | message | posted_date |
+----------+---------+---------------------------+---------------------+
| 7 | 6 | A test reply number one | 2007-05-27 12:13:00 |
| 8 | 8 | A test reply number two | 2007-05-27 12:23:00 |
| 9 | 8 | A test reply number three | 2007-05-27 08:55:00 |
| 10 | 6 | A test reply number four | 2007-05-27 08:44:00 |
| 11 | 5 | A test reply number five | 2007-05-27 12:44:00 |
| 12 | 8 | A test reply number six | 2007-05-27 12:58:00 |
+----------+---------+---------------------------+---------------------+
I want to print out a page that looks like this:
a fifth test message
- 2 replies
a sixth test message
- 0 replies
a seventh test message
- 3 replies
etc....
