Sign in to follow this  

[web] Mysql thread priority query problem

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

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hey guys, Was linked to this site via a friend of mine! I am surprised Google never showed me this place, when I have searched for game forums of this nature... Any way, I have this query which grabs two tables "Posts" and "forums" Now the forums table holds the "ThreadID". With this ID i use it to link the posts relevant to the thread. Now the post table as a "createdon" field. This is the date of when the post was made. Now what I have been trying to do is on my forum page, when i display all the threads the order of of the threads is meant to go in the same order as the date set in the "createdon" field in the posts table. In more simpler terms... the thread which has the most recent post (of all the posts and threads) will be moved to the top of the threads list. Similar to all forums where by when someone posts it gets bumped back to the top of the list. The annoying thing is i don't have a syntax error, i just have a working query that does not do what I am trying to get it to do. My query is:
$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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this