|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
I'm trying to find out how many 'active' users I have. Would anyone know an SQL query for the number of posters in the last (say) 60 days? Thanks 
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
SELECT Count( * ) AS UserPosts, POST_POSTER_NAME
FROM ubbt_POSTS
WHERE POST_POSTED_TIME > UNIX_TIMESTAMP( '2008-04-20 00:00:00' ) -86400 *60
GROUP BY POST_POSTER_NAME
ORDER BY UserPosts DESC change 2008-4-20 to whatever date you would want the 60 days to be counted back from.. *note* this is off top of my head.. i did try it to see if it passed mysql parser at least..  ps: this is a little diff than waht you asked for -- if you just want it simple (like you posted), i'll give that a whirl
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
simplified version -- will count the number of unique posters (assumes you have mysql 5) SELECT count(posters.POST_POSTER_NAME) from (SELECT Count(up.POST_ID) AS UserPosts, up.POST_POSTER_NAME
FROM ubbt_POSTS up
WHERE up.POST_POSTED_TIME > UNIX_TIMESTAMP( '2008-04-20 00:00:00' ) -86400 *60
GROUP BY up.POST_POSTER_NAME) posters many other ways to 'skin a cat', but this works too 
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
Oh these are very good Thanks Sd!
|
|
|
|
|
Joined: Jun 2006
Posts: 3,839 Likes: 1
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,839 Likes: 1 |
Thanks SD 1037 - could be worse Now can you or anyone help on converting the top posters in the last 30 days? I have tried but failed 
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
Converting them to what? Surely this is a secular forum ?
|
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
SELECT Count( * ) AS UserPosts, POST_POSTER_NAME
FROM ubbt_POSTS
WHERE POST_POSTED_TIME > UNIX_TIMESTAMP( '2008-04-20 00:00:00' ) -86400 *30
GROUP BY POST_POSTER_NAME
ORDER BY UserPosts DESC
LIMIT 10
(Modifying SD's code) gives you the top 10 posters from the last 30 days
|
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
I guess that's actually the top 10 from the previous 30 days prior to April 20th.  Adjustment to use current time (whatever time that is?)?
|
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
SELECT Count( * ) AS UserPosts, POST_POSTER_NAME
FROM ubbt_POSTS
WHERE POST_POSTED_TIME > UNIX_TIMESTAMP( CURRENT_DATE ) -86400 *30
GROUP BY POST_POSTER_NAME
ORDER BY UserPosts DESC
LIMIT 10
Seems to work fine, MySQL 5+
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
^^ Allen figured it out.. sorry for my not explaining the variables ah and CURRENT_DATE is nice to know!! ty Allen!
|
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
You're welcome, though my little piece was a matter of googling dev.mysql.com I've been wanting to write a mod like this forever for a custom island - I'd still need to figure out the $a = something $b = somethingelse echo "$a $b" part for the CI
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
lemme know specifically what you want (via PM if you want) and i can whip that up for you for zero Cents i've done quite a few already like that (i think) and it should be like rolling off a log now..
|
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
It's just a mod/custom island that shows "Top 10 Posters" for the last 30 days. I know there's been lots of requests for it. Would this be close?
/* PHP CODE HERE */
$top10 = $db->query ("
SELECT Count( * ) AS UserPosts, POST_POSTER_NAME
FROM ubbt_POSTS
WHERE POST_POSTED_TIME > UNIX_TIMESTAMP( CURRENT_DATE ) -86400 *30
GROUP BY POST_POSTER_NAME
ORDER BY UserPosts DESC
LIMIT 10
");
/* BODY HERE */
$body = <<<EOF
$top10
EOF;
|
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
And would it "thump" a server if used as a custom island and queried with every page load?
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
 well, you are semi-close -- but um.. not there.. lemme gin one up for you and paste here..
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
the idea is more like this, btw Linky Poothat idea needs to be modified for your case with the above query involved instead.. then build the HTML and pump it out to the body i'll clean up a better example for you though btw: i did a custom island (sorry didn't remember before) that lists Top: All time | Month | Week | Today (in the right/lefttdheader) then the users just click on each as a 'link' and the javascript swaps in the appropriate hidden div.. so you essentially get 4 islands in one.. and the code allows the coder (you) to decide which one is displayed as a default on page initial load.. i can post that over @ ubbdev
|
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
That would be cool, grazie
|
|
|
|
|
Joined: Jul 2006
Posts: 4,057
|
|
Joined: Jul 2006
Posts: 4,057 |
yeh post and linky poo SD brings it to the table once again The option one sound cool
BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
The option one sound cool The option one sound cool 4 me 2
|
|
|
|
|
Joined: Jul 2006
Posts: 4,057
|
|
Joined: Jul 2006
Posts: 4,057 |
Do they take Blue Peter Badges in 37.0622,-95.677061 ?
BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
i need to 7.3-ize it, then i'll pop over @ ubbdev /me googles 'Blue Peter Badge' -- sounds kinky
|
|
|
|
|
Joined: Jun 2006
Posts: 3,839 Likes: 1
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,839 Likes: 1 |
Not half as much as Valerie Singleton Although I preferred John Noakes & Peter Purves
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
Val - well known lezza If only I had any ideas where her fingers had been before she started making stuff with sticky backed plastic.... Although I preferred John Noakes & Peter Purves Not too much, I hope
|
|
|
|
|
Joined: Jun 2006
Posts: 16,367 Likes: 126
|
|
Joined: Jun 2006
Posts: 16,367 Likes: 126 |
I'm not even going to ask...
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
No wonder we're all so warped after watching all that kids TV
|
|
|
|
|
Joined: Jun 2006
Posts: 3,839 Likes: 1
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,839 Likes: 1 |
can you even buy sticky backed plastic now?
|
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
|
|
|
|
|
Joined: Jun 2006
Posts: 3,839 Likes: 1
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Jun 2006
Posts: 3,839 Likes: 1 |
That is sticky tape or sellotape - not sticky backed plastic
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
--- what was this thread about again ? --- :shrug:
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
I was sorted by post #3
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
After running this... SELECT Count( * ) AS UserPosts, POST_POSTER_NAME
FROM ubbt_POSTS
WHERE POST_POSTED_TIME > UNIX_TIMESTAMP( CURRENT_DATE ) -86400 *60
GROUP BY POST_POSTER_NAME
ORDER BY UserPosts DESC I notice in one case... UserPosts=2 POST_POSTER_NAME = i.e. blank / empty. Any thoughts on why this could be? (I've deleted one user but he had many more posts than that.)
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
Hey Rick, I like the SQL tags (First time I've seen them.)
|
|
|
|
|
Joined: Jul 2006
Posts: 4,057
|
|
Joined: Jul 2006
Posts: 4,057 |
We should have some default tools in there Or saved query's. I just keep saying SD is our hero and he delivers Every time. (Dont you lol)
BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
We should have some default tools in there Or saved query's. I just keep saying SD is our hero and he delivers Every time. (Dont you lol) Agreed on both points.. Mah herooooh - swoons I think it would be good to save the queries with a 'descriptive' name. Also an 'are you sure' message before you delete (doh  )
|
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
i added a description field to saved queries in the admin cpanel for 7.3 -- if that's what you were alluding to.. and quit swooning on me!!
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
ok clever guy - well I haven't got 7.3 yet - but soon  ...and hopefully gold as well  As for swooning, you know you love it; so stop pretending
|
|
|
|
|
Joined: Jun 2006
Posts: 16,367 Likes: 126
|
|
Joined: Jun 2006
Posts: 16,367 Likes: 126 |
And they say I'm good at derailing topics... seems driv is better at getting them back on track ...
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
Can't have that, can we? Sorry mate it was an accident. Won't happen again
|
|
|
|
2 members (Ruben, SenecaFlyer),
929
guests, and
67
robots. |
|
Key:
Admin,
Global Mod,
Mod
|
|
|
|
