Page 1 of 1
grouping and ordering it?
Posted: Thu Dec 07, 2006 1:52 pm
by elecktricity
okay so basicially I have a forum im trying to make, at the homepage you click the catagory and it shows you all the topics in that forum, on that page it does a query grouping all the topics together which is working allright but what its doing is its grouping it only by the first post so if you post a new topic itll show up at the top and all but if you post a reply it wont move at all? Im not really sure how I would fix this either. here is the query im using:
Code: Select all
<?PHP
mysql_query("SELECT * FROM posts WHERE postid=1 GROUP by id ORDER by rid DESC");
/*
////////
postid = the forum catagory like here there "general discussions", "site news", "suggestions"
and id is the post number per topic so each topic has a "1" somebody replies and they get a "2" and so on
rid is the unique auto increasing number
////////
*/
?>
Posted: Thu Dec 07, 2006 2:16 pm
by ok
Why you don't add a timestamp column which changes everytime someone is posting and when you select the posts from the DB, you just need to order them by the timestamp column.
Posted: Thu Dec 07, 2006 9:12 pm
by elecktricity
I cant order them at all, if the order by rid worked I wouldnt have this problem it would work perfect, but it groups them by the first one added to the db like lets say I make a topic "topic 01" and then I go and make another "topic 02" if you view them "topic 02" is at the top cause its newer, but if I go reply to "topic 01" then its still at the bottom of the list
Posted: Thu Dec 07, 2006 9:32 pm
by feyd
What's the group by for?
Posted: Sat Dec 09, 2006 8:24 pm
by elecktricity
it groups them by topic, so you view the forum and it only shows one post for each topic not a bunch of them.
Re: grouping and ordering it?
Posted: Sat Dec 09, 2006 8:30 pm
by califdon
elecktricity wrote:okay so basicially I have a forum im trying to make, at the homepage you click the catagory and it shows you all the topics in that forum, on that page it does a query grouping all the topics together which is working allright but what its doing is its grouping it only by the first post so if you post a new topic itll show up at the top and all but if you post a reply it wont move at all? Im not really sure how I would fix this either. here is the query im using:
Code: Select all
<?PHP
mysql_query("SELECT * FROM posts WHERE postid=1 GROUP by id ORDER by rid DESC");
/*
////////
postid = the forum catagory like here there "general discussions", "site news", "suggestions"
and id is the post number per topic so each topic has a "1" somebody replies and they get a "2" and so on
rid is the unique auto increasing number
////////
*/
?>
Your problem is that your Group By should be the category, not the post id.
Posted: Wed Dec 13, 2006 9:39 am
by elecktricity
for the index page maybe, that query there is selecting them by catagory as well, so if I group by catagory there will only be 1