| Author |
Message |
Darth Oosha
Trandoshan
Joined: 24 Sep 2003
|
 Posted: Dec 05, 2007 02:44 Post subject: Member list |
   |
|
Just out of curiosity: Would it be easy to write a script to display a list of members sorted by date of most recent post, so you can see the actual current inhabitants of the board?
|
|
Tom Manning
Trandoshan
Joined: 27 Sep 2003
|
| Posted: Dec 05, 2007 02:51 Post subject: |
 |
|
I'm still here!
_________________
Tom Manning
For all that you hold dear on this good Earth, I bid you, Stand, Men of the West!
Aragorn: Return of the King |
|
Fenwar
Admiral Ackbar
Joined: 15 Sep 2003
|
| Posted: Dec 07, 2007 07:49 Post subject: |
|
|
Definitely possible, shouldn't be too hard to do. Will have a look at the code when I next have my SQL hat on.
|
|
Nottheking
Kell Dragon
Joined: 29 Sep 2003
|
| Posted: Dec 09, 2007 19:17 Post subject: |
|
|
We're counting on you, Fenwar. :p
(and yeah, I'm still here!)
_________________
Wake up, George Lucas... The Matrix has you.. |
|
The MAZZTer
Death Star
Joined: 25 Sep 2003
|
| Posted: Dec 12, 2007 00:46 Post subject: |
|
|
[Edit: Maybe it won't be so easy]
It might be easy if that data is stored in the members table. If not, you would have to make a separate query FOR EACH MEMBER to pull up their most recent post to get the timestamp from it.
A better way would be to add a field to the members table (if one doesn't already exist of course) and add an UPDATE query whenever a user makes a post to update the timestamp there. Then to populate a memberlist you just need one simple query like it does now.
_________________
http://www.mzzt.net/ | I am a respectable admin with a respectable sig. |
|
Fenwar
Admiral Ackbar
Joined: 15 Sep 2003
|
| Posted: Dec 12, 2007 09:29 Post subject: |
|
|
There's already a column in the users table for your last "visit" (I presume, last login) which would out any lurkers among us.
I don't think you need multiple queries to get the last 10 users who posted though:
| Code: |
SELECT username, poster_id, MAX(post_time) AS last_post_time
FROM `phpbb_posts` JOIN `phpbb_users` ON user_id = poster_id
GROUP BY poster_id
ORDER BY last_post_time DESC
LIMIT 0,10
|
Which in phpMyAdmin as I write this returns
| Code: |
username poster_id last_post_time
The_Mega_ZZTer 16 1197420396
Fella 376 1197395987
Fenwar 3 1197394811
DarthDoctor 32 1197294263
Nottheking 34 1197228179
ACE 373 1197071335
milo_antu 326 1197066740
CoinCollector 374 1197065913
Patrick Haslow 15 1197062024
Darth Oosha 7 1197000664
|
...looks about right to me 
|
|
Fenwar
Admiral Ackbar
Joined: 15 Sep 2003
|
| Posted: Dec 12, 2007 09:34 Post subject: |
|
|
And after that last post makes me the most recent poster, the same query gives:
| Code: |
username poster_id last_post_time
Fenwar 3 1197451784
The_Mega_ZZTer 16 1197420396
Fella 376 1197395987
DarthDoctor 32 1197294263
Nottheking 34 1197228179
ACE 373 1197071335
milo_antu 326 1197066740
CoinCollector 374 1197065913
Patrick Haslow 15 1197062024
Darth Oosha 7 1197000664
|
(PS: ugh @ phpBB code tag which converts tabs to 4 spaces - when in my edit window a tab uses 8 columns...  )
So now all I have to do is add that to the actual phpBB code somewhere. Woo.
|
|
Fenwar
Admiral Ackbar
Joined: 15 Sep 2003
|
| Posted: Dec 12, 2007 11:12 Post subject: |
|
|
It. Is. Done.
|
|
The MAZZTer
Death Star
Joined: 25 Sep 2003
|
| Posted: Dec 12, 2007 17:31 Post subject: |
|
|
I don't think the GROUP BY clause actually does anything in this query?
_________________
http://www.mzzt.net/ | I am a respectable admin with a respectable sig. |
|
Fenwar
Admiral Ackbar
Joined: 15 Sep 2003
|
| Posted: Dec 12, 2007 21:05 Post subject: |
|
|
It's so that the MAX(post_time) can select the highest value that applies to each unique user. (I think using MAX() alongside other columns is invalid SQL without it.)
It is quite a hefty query when added to the existing phpBB code for the memberlist page, as it pulls pretty much all of the columns from the users table and now I've added the JOIN which uses all the rows of the posts table.
The method you originally suggested would definitely be more efficient in the long run though (especially as our database gets bigger), but MySQL seems to be handling this OK for now, and it's marginally easier to hack the SELECT statements in the phpBB code than UPDATES 
|
|
Nottheking
Kell Dragon
Joined: 29 Sep 2003
|
| Posted: Dec 14, 2007 02:52 Post subject: |
|
|
Nice... Seems that we're not QUITE as dead as it seemed before!
_________________
Wake up, George Lucas... The Matrix has you.. |
|
Darth Oosha
Trandoshan
Joined: 24 Sep 2003
|
| Posted: Dec 15, 2007 00:24 Post subject: |
|
|
Feature: approved. Also: I'm not sure whether I should be proud or depressed that I've apparently been around longer than almost anyone still here.
|
|
Nottheking
Kell Dragon
Joined: 29 Sep 2003
|
|
Darth Oosha
Trandoshan
Joined: 24 Sep 2003
|
| Posted: Dec 16, 2007 21:16 Post subject: |
|
|
I was actually calculating based on which names I recognized from DF99, and the Code Alliance and Bespin City forums before that.
|
|
Nottheking
Kell Dragon
Joined: 29 Sep 2003
|
|
|
