mysql php forum list

Question

here are my tables

topics -id -title -category_id

posts -id -topic_id -user_id -date -time -txt

user -id -username

i want to list for a category every topic with the following information the author's username (who posted first), number of replies,the name of the last reply's author, and the date of last post sorted by the date of posts.

i could do it with some nested loop and bunch of queries but i was wondering if it possible whit one or maybe two mysql query?

the code i have sofar...

$query = "SELECT * FROM user u,topics t,posts p WHERE t.category_id=1 AND t.id=p.topic_id AND p.user_id=u.id ORDER BY p.date DESC, p.time DESC";

Answer 1

$sql = "SELECT * FROM user u,topics t,posts p WHERE t.category_id=1 AND t.id=p.topic_id AND p.user_id=u.id ORDER BY p.date DESC, p.time DESC";
$query = mysql_query($sql);
while ($result = mysql_fetch_array($query)) {
    // do some stuff with $result
}

(changed variables to make a little more sense)

Answer 2

Just add first_topic_id and last_topic_id columns to the topics table. It is common practice and it will help you to get simple and fast queries.