PHP & MySQL
Posted: Sat May 20, 2006 7:53 pm
Well, it's mostly about MySQL though, but I couldn't find a subforum about it. The things is, I'm building a small forum for my website and am having a few problems with sorting the topics and reply's on date. My forum works as following (well, as soon as I get things worked):
Mainforum
- Subforums
-- Topics
--- Topic message + reply's
When you click on one of the subforums, you will get a list with all the topics in that subforum. They have to be sorted by date & time from the last post. My tables are as following:
TOPIC
subforum_id
topic_id
title
post
date
time
sticky
REPLY
subforum_id
topic_ud
post
date
time
I had the following query:
But it doesn't work at all! And I don't know how to solve it, since I haven't worked much with multiple tables in one query. I do have another solution if there is no way to solve this, but it involves changing my tables and a hell lot of code. Now, start breaking your heads over this! 
Mainforum
- Subforums
-- Topics
--- Topic message + reply's
When you click on one of the subforums, you will get a list with all the topics in that subforum. They have to be sorted by date & time from the last post. My tables are as following:
TOPIC
subforum_id
topic_id
title
post
date
time
sticky
REPLY
subforum_id
topic_ud
post
date
time
I had the following query:
Code: Select all
SELECT *
FROM topic AS topic, reply AS reply
WHERE topic.forum_id = '$subforum_id' OR // not AND, because reply can be empty
reply.forum_id = '$subforum_id'
ORDER BY topic.sticky DESC, // first the sticky ones on top
topic.datum DESC, // then sorting dates (topic or reply, doesn't matter which one first)
reply.datum DESC,
topic.tijd DESC, // then sorting on times
reply.tijd DESC