MySQL query

cmike

I'd like some help with SQL query, which help me to show 5 discussions with newest comments (but discussion must be DISTINCT).

Help somebody??

ithcy

er...

SELECT DiscussionID, Name FROM `LUM_Discussion` ORDER BY `DateLastActive` DESC LIMIT 5

?

ithcy

wait, you also need to make sure the query only returns discussions that the user is privileged to view...
ugh
i don't have time to think it up now, but i'l come back to this thread later and if nobody else has done it, i'll post the answer

jimw

I think he wants 5 different discussions that have new comments. You might have to use:

SELECT DISTINCT DiscussionID FROM ... LIMIT 5

ithcy

that query i posted would return the 5 different discussions with the newest comments. no distinct is needed as DicussionID is a primary key.

however, there needs to be some php involved (or else a stored procedure in mysql) to do what i think he really wants, which is to show only discussions that the user is allowed to see. i still don't have time to look at the discussions page code, which does exactly what is needed here except returns the 30 or 50 discussions with the newest comments instead of 5.

cmike

Thanks, All!

Max_B

well, if we talk about real life case, things are more complicated, especially if you want to account for whispers :
$sql = "SELECT d.Name , c.Body, c.CommentID, c.DiscussionID FROM LUM_Comment AS c, LUM_Discussion AS d WHERE c.Deleted = '0' AND d.Active='1' AND d.DiscussionID=c.DiscussionID AND c.DateCreated >= d.DateLastActive #whispers does not touch DateLastActive AND (d.WhisperUserID=$num_util OR d.WhisperUserID!=0 AND d.AuthUserID=$num_util OR d.WhisperUserID=0 AND (c.WhisperUserID=0 OR c.WhisperUserID=$num_util OR c.AuthUserID=$num_util)) #whisper author of recipient GROUP BY d.DiscussionID ORDER BY c.DateCreated DESC LIMIT 5";

Edit: I copied/edited/pasted the request above from some code of mine. It was made to get most recent comments. It can be simplified a bit. I posted it to show the whisper stuff.