query help - ordering results according to most recent item

Littlered

I'm trying to create my own forum in php and mysql and I've run into a query problem which I was hoping someone could help me with:

I have a table with every post in - which has a column for the time the post was created and a column for the thread that this post is located in.

My threads table has a column for which forum this thread is in and a column for whether the thread is a sticky or not.

On the forum display page, I'd like to display every thread that is in that forum, but ordering them so that any Sticky threads show first (ordered by date created), and then the non-sticky threads are displayed after those ordered according to the time of the most recent post within that thread.

Any help would be really appreciated! (if I've not explained it clearly, please let me know!) thanks

jheitz

so what is the problem? You need to order by 2 columns, sticky and DateCreated depending on the nature of the sticky column (ie: does it hold values Y,N or 0,1 or ....?) this would be done slightly different, but in a nutshell something like:

SELECT ...
FROM ...
WHERE ....
ORDER BY Sticky (ASC or DESC, you choose), DateCreated

Littlered

thanks for the reply - I need to establish which post from any given thread is the most recent, and then order the threads based on these times - so do I need two queries (one to establish the most recent post per thread, and then one to find the threads in a forum and order them by the previous query's results?

jheitz

If you want to order the THREADS depending on which one received the last post and then need within a thread a list of all posts starting with the sticky ones I reckon you need 2 queries. 1 to establish the thread order and then one for when you drill down and want to get the detail of all POSTS for that thread. This site must work on a similar logic (me thinks)

HeyRay2

Let's assume each thread in your database contains the following fields:

DateCreated
The date and time the thread was created. Populated when you create a new thread in the database.

DateLastPost
The date and time the thread was last posted to. Populated with a new value each time a new post is added to the database.

ThreadType
The type of thread, "sticky" or "normal"

ForumID
The ID number of the forum the thread belongs to

Using multiple queries and the values above, you can order your threads like you want to:

// "sticky" query
"SELECT * FROM threads WHERE ForumID = $ForumID AND ThreadType = "sticky" ORDER BY DateCreated";

// "normal" query
"SELECT * FROM threads WHERE ForumID = $ForumID AND ThreadType = "normal" ORDER BY DateLastPost";

You can consolidate these queries into one with almost the same success. All you would need to do is change your ThreadType to be numeric, with "sticky" threads having a higher value than "normal" threads. Give a "sticky" thread a value of 1, and "normal" a value of 0.

Your query would look like this:

// all theads query
"SELECT * FROM threads WHERE ForumID = $ForumID ORDER BY ThreadType DESC, DateLastPost DESC";

With this query your threads will be first sorted with "sticky" threads first followed by the "normal" threads. However,
the drawback is that each group of threads will be ordered by the last time they were posted to.

Littlered

thanks for the reply - I was hoping to be able to derive the time of the most recent post with a query without having to store it separately in the Threads table, but it does seem the most straight forward way