Manual stats of users

[matfish]

Hi, I was wondering if anyone could help me with the following:

 

* Got a table of members and trying to get the last user who registered. Would this be the last userID as this is an auto_increment or I do have a registered field which is a timestamp but how to find the latest date?

 

* I want to find the member who has posted the most comments? Dont know where to start with this one. How would you like distinct the usernames in the comments table and bring back the highest count?

 

Any help would be appreciated.

 

Thanks

[Daniel0]

Got a table of members and trying to get the last user who registered. Would this be the last userID as this is an auto_increment or I do have a registered field which is a timestamp but how to find the latest date?

Just use userID since the latest user will have the highest id if it is auto incrementing.

SELECT * FROM users ORDER BY userID DESC LIMIT 1;

 

I want to find the member who has posted the most comments? Dont know where to start with this one. How would you like distinct the usernames in the comments table and bring back the highest count?

I'd just increment a field called something like num_comments in the users database each time a user comments. Decrement it if you delete a comment. Then just do this:

SELECT * FROM users ORDER BY num_comments DESC LIMIT 1;

[cmgmyr]

I would add in some sort of "start date" in the users table that you can query off of. This can also be helpful later on down the road too for other things.

 

for the count you can do something like:

 

SELECT userid, count(userid) as total FROM comments ORDER BY total DESC LIMIT 1

[matfish]

Hey, thanks for the replies.

 

Daniel0's suggestions work - thank you, but didnt fancy a table just to increment a number. I will do this suggestion if I get stuck.

 

I do have a registered date (timestamp) - is it the same thing as order by registered_date DESC LIMIT 1? Just implemeting the userID thingy...

 

Thanks again

 

[per1os]

Hey, thanks for the replies.

 

Daniel0's suggestions work - thank you, but didnt fancy a table just to increment a number. I will do this suggestion if I get stuck.

 

I do have a registered date (timestamp) - is it the same thing as order by registered_date DESC LIMIT 1? Just implemeting the userID thingy...

 

Thanks again

 

 

Yes the register_date will work just as well.

[Daniel0]

Daniel0's suggestions work - thank you, but didnt fancy a table just to increment a number. I will do this suggestion if I get stuck.

 

Not a table, a field.

[matfish]

Many thanks

 

SELECT userID, COUNT(userID) as total FROM comments GROUP BY userID ORDER BY total DESC LIMIT 1

 

worked a treat.

 

Thanks both