# [web] Mysql thread priority query problem

This topic is 3656 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

$Get = mysql_query("SELECT forums.ThreadID,forums.ThreadName,forums.ThreadStarter,forums.LockedSticky,forums.CreatedOn FROM forums INNER JOIN posts ON posts.ThreadID=forums.ThreadID WHERE forums.Catergory='$Catergory' GROUP BY posts.ThreadID ORDER BY posts.CreatedOn ASC ")
Or die(mysql_error());
Encase it is needed this is my table structure: forums: ThreadID | Catergory | more fields... | posts: PostID | ThreadID | UserID | Message | CreatedOn (timestamp) | Hope you can help show me where I have gone wrong.

##### Share on other sites
I see what you're trying to do... and this is really not the best way to approach this problem. Rather, you need to add a column called 'LastPost' in the forums table and update that field each time a user posts a new thread. You can then sort by LastPost and accomplish the same effect.

##### Share on other sites
Yeh i had that idea in mind, when i mentioned it on a php forum i got about 10 replies going :

"thats just duplicated information on the database and is wasteful" lol But looks like its my only choice.

Thanks for confirming this, I will have to re-structure my scripts.

Thankyou.

##### Share on other sites
Well, as a general rule, you do want to avoid duplicate information. However, in this specific case, the overhead and storage require for the extra date/time stamp is worth the computing power you save.

The only other option would be to flag the last post in the "posts" table. Ie, you have a column called "LastThreadPost" that is either true or false. When a user posts, it sets all "LastThreadPost" values to 0 for the associated PostID, and stores the new post as a 1. You could then combine the two tables using a "where LastThreadPost = 1" clause, and then sort by date.

However, I still feel the first option is better as you are not storing an excess of extra data.