Make phpbb mysql query display all posts in a topic

Question

...as opposed to what it is doing at the moment, which is display the last reply from EACH topic in the speicified forum.

<?php
// How Many Topics you want to display?
$topicnumber = 10;
// Change this to your phpBB path
$urlPath = "../path/to/forum";

// Database Configuration (Where your phpBB config.php file is located)
include '../path/to/forum/config.php';

$table_topics = $table_prefix. "topics";
$table_forums = $table_prefix. "forums";
$table_posts = $table_prefix. "posts";
$table_users = $table_prefix. "users";
$link = mysql_connect("$dbhost", "$dbuser", "$dbpasswd") or die("Could not connect");
mysql_select_db("$dbname") or die("Could not select database");

$query = "SELECT t.topic_id, p.post_text, t.topic_title, t.topic_last_post_id, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username
FROM $table_topics t, $table_forums f, $table_posts p, $table_users u
WHERE t.topic_id = 7 AND
f.forum_id = t.forum_id AND
t.forum_id = 11 AND
t.topic_status <> 2 AND
p.post_id = t.topic_last_post_id AND
p.poster_id = u.user_id
ORDER BY p.post_id DESC LIMIT $topicnumber";
$result = mysql_query($query) or die("Query failed");                                   

print '<div class="news_block">';
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo  "<h4><a href=\"$urlPath/viewtopic.php?f=$row[forum_id]&t=$row[topic_id]&p=$row[post_id]#p$row[post_id]\" TARGET=\"\">" .
$row["topic_title"] .
"</a> </h4>By: " .
$row["username"] . ' - ' . date("l", $row["post_time"]) .
"<p>" .
$row["post_text"] . // <----- 
"</p>";
}
print "</div>";
mysql_free_result($result);
mysql_close($link);

/* date("l", $row["post_time"]) .  date('F j, Y, g:i a', $row["post_time"]) .*/
?>

As you might've been able to tell, I'm far from a coder. I think I've narrowed it down to p.post_id needing to be changed, but no matter what integer or variable I seem to assign to it, I can't get the desired effect. Would really appreciate some help at this point. Thanks.

Answer 1

I think you have to remove this part from the query:

AND p.post_id = t.topic_last_post_id 

Right now you are asking for the post (only one) that has the same id as the last post of the topic, but you do not want to only get the last one. You want to get all posts.

Edit: You have to look at your database and check if there is any foreign key in your post table that refers to the topic.

Think of it that way: every forum has many topics, every topic has many posts and every post has exactly one user. So you have to have a connection between the tables that you can follow to identify how the rows match together.

Joins are very helpful in that way. If you work with a finished database schema that already contains those foreign keys, joins will help you to connect your queries.

This would be a well formed query supporting my explanations.

SELECT 
    *
FROM
    $table_forums f
        LEFT JOIN
    $table_topics t ON t.forum_id = f.forum_id
        LEFT JOIN
    $table_posts p ON t.topic_id = p.topic_id
        LEFT JOIN
    $table_users u ON p.poster_id = u.user_id
WHERE
    t.topic_id = 7 AND t.forum_id = 11
        AND t.topic_status <> 2
ORDER BY p.post_id DESC
LIMIT $TOPICNUMBER