| View previous topic :: View next topic |
| Author |
Message |
xtheunderdog
Joined: 15 Jun 2004
Posts: 10
|
 Posted: Wed Oct 27, 2004 11:52 pm Post subject: b2 stats help--SQL query question |
 |
|
So, I'm trying to get b2stats to list the top five authors on my b2 blog, and i'm almost there, but, it lists the author id instead of their login name like i want. i can't figure out how to get it to call user_login from tableusers and then order it by calling the_author from tableposts. I've tried a ton of different things, but they all result in errors-- this is the only thing that hasn't, and it's not calling any of the other tables.
This is what I have right now:
| Code: | | $ams = "SELECT post_author, COUNT(*) AS 'total' FROM $tableposts GROUP BY post_author ORDER BY total DESC LIMIT 5"; |
|
|
| Back to top |
|
 |
xtheunderdog
Joined: 15 Jun 2004
Posts: 10
|
| Posted: Thu Oct 28, 2004 5:03 am Post subject: |
|
|
also, what would i do to fetch how many comments and posts are being made a day.
for instance, there were 49 comments made today on my b2...i want to get the stats to say that, and then keep counting todays and then come tomorrow start over the count again, but i can't quite figure that out either. |
|
| Back to top |
|
|
Cyberian75
Joined: 26 Sep 2002
Posts: 1285
Location: Oregon
|
|
| Back to top |
|
|
Cyberian75
Joined: 26 Sep 2002
Posts: 1285
Location: Oregon
|
| Posted: Sat Oct 30, 2004 4:59 am Post subject: Re: b2 stats help--SQL query question |
|
|
| xtheunderdog wrote: | This is what I have right now:
| Code: | | $ams = "SELECT post_author, COUNT(*) AS 'total' FROM $tableposts GROUP BY post_author ORDER BY total DESC LIMIT 5"; |
|
HINT: Use the WHERE clause with author names, or you'd do a JOIN with comments and users tables.
_________________
Michael P.
 |
|
| Back to top |
|
|
Cyberian75
Joined: 26 Sep 2002
Posts: 1285
Location: Oregon
|
| Posted: Sat Oct 30, 2004 5:09 am Post subject: |
|
|
| xtheunderdog wrote: | also, what would i do to fetch how many comments and posts are being made a day.
for instance, there were 49 comments made today on my b2...i want to get the stats to say that, and then keep counting todays and then come tomorrow start over the count again, but i can't quite figure that out either. |
| Code: |
SELECT...WHERE YEAR(N0W()) = YEAR(post_date) AND MONTH(N0W()) = MONTH(post_date) AND DAYOFMONTH(N0W()) = DAYOFMONTH(post_date) ...
|
Something like that. 
http://dev.mysql.com/doc/mysql/en/index.html
_________________
Michael P.
|
|
| Back to top |
|
|
Sigg3
Joined: 03 Jul 2003
Posts: 906
Location: Oslo, Norway
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
