group by & order by

[PRSBOY]

Hey there,

 

I'm trying to whip up some forums, only basic and secure. Here are my involved tables:

 

forumthread

threadid 	 	 
threadviews	 
groupid	 
forumid 	 
threadtitle	 
threadsticky 	 
userid

 

threadid is the primary key, auto_inc.

 

forumpost

postid 	 
threadid	 	 
forumid 	 
userid 	 	 
groupid	 	 
postbody 	 
postdate

 

postid is the primary key, auto_inc.

threadid is the foreign key linking to a specific thread.

 

What I am trying to do, is print the each thread, listing its number of replies (count(postid)), last poster details (ORDER BY forumpost.postdate DESC)

 

The SQL I has was:

SELECT
p.*
t.*
FROM forumpost p
LEFT JOIN forumthread t ON p.threadid = t.threadid
GROUP BY p.threadid
ORDER BY p.postdate DESC

 

The problem is: the query selects the correct number of rows, however, it does not return the correct data for the last poster.

 

I'm really in need of some help, if you need any more info just let me know.

 

Cheers!

[Yesideez]

SELECT p.*,t.*,count(p.id) AS postcount FROM forumpost AS p
LEFT JOIN forumthread AS t ON p.threadid=t.threadid
WHERE p.userid=$userid
ORDER BY p.dateposted DESC

 

It's very early just started work not sure if that'll work.

[PRSBOY]

sorry, I missed out the where clause!

 

should be:

 

WHERE t.forumid = $forumid

 

I'm getting all threads/posts that are in a certain forum (e.g. $forumid = 2).

 

There is what i'd like to do:

Step 1: Get all threads from forumthread (there is no postdate here, so I cant order by postdate to get them in the correct order

Step 2: Join forumpost on forumthread.threadid = forumpost.threadid, then order all of the data on forumpost.postdate so that the thread with the latest post is at the top, then in order of last postdate (DESC)

 

That should make it more clear, thank you for your reply!

[Yesideez]

SELECT p.*,t.*,count(p.id) AS postcount FROM forumpost AS p
LEFT JOIN forumthread AS t ON p.threadid=t.threadid
WHERE t.forumid=$forumid
ORDER BY p.id DESC

 

Will that work?

[PRSBOY]

I have 3 rows in forumthread - therefore I've made three posts.

I have 7 rows in forumpost - therefore I have made 4 replies (7-3).

 

Nope, that's not working - I changed some parts of the query but it still returns 1 row, and it does not order it correctly.

 

What I was trying to do was to group by threadid then order that group on postdate DESC.

[Yesideez]

Let's try removing the count part:

SELECT p.*,t.* FROM forumpost AS p
LEFT JOIN forumthread AS t ON p.threadid=t.threadid
WHERE t.forumid=$forumid
ORDER BY p.postid DESC

[PRSBOY]

Thanks for your reply.

 

I've done that, and it does work/order correctly, however, it's listing every post. I only need it to list each thread, so there should be three results with the latest post selected from forumpost.

[Yesideez]

See how this works...

SELECT t.*,p.* FROM forumthread AS t
LEFT JOIN forumpost AS p ON p.threadid=t.threadid
WHERE t.threadid=$id

[PRSBOY]

That doesn't order, you must have missed ORDER BY - also, it still lists all 7 of the posts, not just the three i'm looking for. I'm sure there should be a group by somewhere.. Hmm - this is really annoying me.

[Yesideez]

No idea then.

 

Lost just as much as you are now.

[PRSBOY]

Thank a lot for your help anyway!

 

Anyone else have any ideas?

[fenway]

First grab the most recent of each, and THEN deal with getting the rest of the details.