How to ORDER BY another table and set if function?

[MagicMedia]

I'm creating a forum like this one and I need to display my topics by last post. I want to create it so that the topic gets bumped back to the top whenever someone adds a new reply, like most forums including this one.

I have a timestamp on both my topics and replies but have no idea how to write the "SELECT" statement. I need to display the topics ordered by the last reply, or use the timestamp of the topic if there are no replies. I tried a loop to find replies but then it creates an infinite loop through the topics.

My database is set up like:
table 'forum_topic'
id
timestamp

table 'forum_reply'
id
topic_id
timestamp

// loop through topics
$topic_sql = "SELECT * FROM forum_topic";
$topic_result = mysql_query($topic_sql);
while ($topic_row = mysql_fetch_array($topic_result)) {
	$topic_id = $topic_row['id'];
	
	// find number of replies
	$reply_sql = "SELECT * FROM forum_reply WHERE topic_id = '$topic_id' ORDER BY timestamp DESC LIMIT 1";
	$reply_result = mysql_query($reply_sql);
	$reply_amount = mysql_num_rows($reply_result);
	
	// if there are no replies, order by timestamp from topic, otherwise order by timestamp from last reply
	if ($reply_amount == 0) {
		$sql = "SELECT * FROM forum_topic ORDER BY timestamp DESC";
	} else {
		
	}
}

And.... I'm completely stumped after that. I don't know how to write the "SELECT" statement to allow topics to be bumped whenever there is a new reply. The topic requires the reply and the reply requires the topic so I don't know what to do. Or if I am doing this completely wrong, please tell me the best way to do it.

[smantscheff]

Your outermost loop lacks a sort clause.
I assume that you want an order like this, so that the thread with the youngest reply or topic creation timestamp is displayed first:

TOPIC 1 (timestamp 1)
  REPLY 7 (timestamp 7)
TOPIC 3 (timestamp 3)
  REPLY 4 (timestamp 4)
  REPLY 5 (timestamp 5)
TOPIC 2 (timestamp 2)

It might be easier if you would not separate topics and replies in different tables but to pack all posts belonging to a thread into the same table - the first post, too.

[MagicMedia]

Yes, that's the order I want. But I don't get how to pack everything into the same table. Wouldn't the main forum page show everything including replies? Or should I add another column that determines if the row is a topic or reply and only show topics?

And even if I do that, it goes back to my original problem with the never ending loop. I need to loop through the topics to find the replies, but I want my replies to order my topics.

[smantscheff]

Whether a post is a reply is implicit in the data and should therefore not explicitly be stored. The first post of a thread is the topic, the others not.
When you store all posts in one table, you can sort them like this:

drop table if exists posts;
create table posts (id integer, id_topic integer, timestamp integer);
insert into posts values (1,1,1),(2,2,2),(3,3,3),(4,3,4),(5,3,5), (7,1,7);
 select * from posts;
+----+----------+-----------+
| id | id_topic | timestamp |
+----+----------+-----------+
|  1 |        1 |         1 |
|  2 |        2 |         2 |
|  3 |        3 |         3 |
|  4 |        3 |         4 |
|  5 |        3 |         5 |
|  7 |        1 |         7 |
+----+----------+-----------+
/* all topics sorted by last post*/
select p1.id from posts p1 where timestamp = (select min(timestamp) from posts p2 where id_topic=p1.id_topic) order by (select max(timestamp) from posts p3 where id_topic=p1.id_topic) desc;
+----+
| id |
+----+
|  1 |
|  3 |
|  2 |
+----+

[MagicMedia]

Your code works. Now it orders by last post. Thanks for your help.