[RESOLVED] Ordering by combing 2 values?

Channel5

I just started coding a simple forum, kind of like this one for fun.

The problem I'm running into is when trying to order the threads by time desc within a specific forum because they need to be ordered by both threads and replies, but only display the thread name back:

First, I started with a table for threads and a table for replies.

Now, I've combined them into one, hoping it would make this task easier but I find, from my knowledge, I don't know how to run a query where the threads have different id values than those for the replies.

Example of thread:

id = 1
fid = 1 (forum id)
tid = null (since it's for the replies)
date = sooner

Example of reply for the above thread:

id = 2
fid = 1
tid= 1 (matches to id above)
date = later

Is there a way to do this the way I'm heading here... or would it be better to code the entire thing differently?

I suppose I could make tid match id for the threads, but how would I display back only the threads and not the replies, if the replied are newer than the threads?

I'm confusing myself trying to explain this, so I hope it makes little sense. 🙂

xeel

Could you give an example, I'm not sure I understand how you want to order the data...

As for the query for the DB you could just order the list using ORDER BY field1,field2 ASC. See more info here: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Channel5

I'll see if I can explain it better...

For every thread there are replies "attached" to it.

The threads need to be ordered by most recent, which includes the replies even though they are not displayed in the forum index.

Maybe using this thread is a good example I can use:

In the forum index for "datebases", it just displays the headline of this thread but that headline moves up to the top when someone makes a new reply in the thread.

So I'm a little confused about how I would order this...

Because the data needs to be ordered by the threads and the replies but then only prints the threads to the user according to both of them.

Hopefully I'm making more sense this time. 🙂

xeel

The data is ordered by the most recent post except the sticky threads. Lets forget about the last ones for now. There are several ways to order the threads by the most recent post:

You could add an extra field in the DB in the threads table with the id or datetime of the last post. This would make the search query much easier and quicken the process.
Just write a direct query searching through every post datetime of every thread. This would keep the DB a bit smaller, but the query slower as well. In this case just group the threads by another select through their posts (a complex query).

Personally I'd prefer the first variant. An additional field wouldn't increase the DB size drastically, still the query would be much, much quicker and simpler.

Channel5

Thanks, I went with suggestion #1 and things are working out.

Also, I took a peek at the tables of a popular forum script and that's exactly how they did it too.