Is there some great MySQL function of flash of insight someone could recommend for the following scenario?
I have a table where I need to select and order rows by timestamp field. In this talbe I have a col called 'start': it can be 1 or 0.
I'd like all the rows ordered by date BUT only if they have a start of 1
id ¦ title ¦ desc ¦ date ¦ start
(or somthing similar)...
It's easy to get a list of 'topics' (it's for forum software) as start=1 means theat it's the first post, but I'm trying to get it ordered by last reply which means currnetly I get dupe values as I cannot restrain it by 'start = 1' .
Anyone following that? ;-)
Nick
Exactly the same. WIthout the group by I get dupes but the correct order, with it I get no dupes but wrong order...
Nick
Damn! - GROUP BY seemed to have so much potential, I've still got a truckload of dupes though...
Should I work on an array_walk() type affair to just ditch the dupes?
Nick
"GROUP BY parent" should work... it's just the ORDER BY thingy that needs to be changed...
The group by is resulting in the first posts to a thread being selected, so then I get the threads but in the order they were started.
If we don't use the group by we get all the posts in the right order but with more than we'd like (every post to every thread).
So, GROUP BY is working fine, just a little toooooo fine ;-)
Is there anyway to effect it's function?
Do you follow all that?
Nick
I'll have to think, and sleep on it ;)
Its bit of PHP and breaking into two queries instead of one.
1)SELECT postcontrols.parent, MAX(postcontrols.date) as tstamp GROUP BY postcontrols.parent ORDER BY tstamp DESC.
This should give you recordset with the thread_id with last reply date for that thread in reverse order of posting.
2) Retrieve threadids in an array...say $threadids_arr, these are in the right order.
3) using implode(',',$threadids_arr) should give you a comma separated string of thread_ids say $threadids_string.
4) SELECT postcontrols.parent,,, WHERE start=1 and thread_id IN ('$threadids_string')
5)This should give all the required records but in random order.
6) Use $threadids_arr again from step 2 as it is in the right order... to sort the records when generating html.
It should work unless there is a bug some where ;)
That actually does the whole activelist thing in one query, and no messin around...
SELECT COUNT(*) as count, postcontrols.parent as id, MAX(postcontrols.date) AS date, threads.title,
threads.description,
members.uname, members.id AS memberid, postcontrols.start FROM postcontrols, threads, members, threadcontrols WHERE postcontrols.parent=threads.id AND postcontrols.author=members.id AND threadcontrols.threadid=threads.id AND threadcontrols.forum='1' GROUP BY postcontrols.parent ORDER BY date DESC
All neat and tidy and efficient. Thanks!
Nick
