grouping and ordering it?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
elecktricity
Forum Contributor
Posts: 128
Joined: Sun Sep 25, 2005 8:57 pm
Location: Trapped in my own little world.
Contact:

grouping and ordering it?

Post 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
////////
*/
?>
User avatar
ok
Forum Contributor
Posts: 393
Joined: Wed May 31, 2006 9:20 am
Location: The Holy Land

Post 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.
User avatar
elecktricity
Forum Contributor
Posts: 128
Joined: Sun Sep 25, 2005 8:57 pm
Location: Trapped in my own little world.
Contact:

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What's the group by for?
User avatar
elecktricity
Forum Contributor
Posts: 128
Joined: Sun Sep 25, 2005 8:57 pm
Location: Trapped in my own little world.
Contact:

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: grouping and ordering it?

Post 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.
User avatar
elecktricity
Forum Contributor
Posts: 128
Joined: Sun Sep 25, 2005 8:57 pm
Location: Trapped in my own little world.
Contact:

Post 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
Post Reply