|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
Hi guys, Would anyone know how to make a query to get a list of email addresses for users with X posts? Ideally, I would want... User# | UserName | emailaddress | #posts ...but just the email address alone would be fine Thanks
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
select t1.USER_ID,t2.USER_DISPLAY_NAME,t1.USER_REAL_EMAIL,t1.USER_TOTAL_POSTS
from ubbt_USER_PROFILE as t1,
ubbt_USERS as t2
where t1.USER_ID = t2.USER_ID
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
Cheers Rick, I appreciate that
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
I just added this tiny bit to the end... ORDER BY USER_TOTAL_POSTS DESC ..couldn't have made the original query though, so thanks again
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
ok then, now I have to look for a date limit! Hmmm - I was looking through the tables for 'last_on'. What is the best field to use? It would be cool to have a field with a limit of x days
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
select t1.USER_ID,t2.USER_DISPLAY_NAME,t1.USER_REAL_EMAIL,t1.USER_TOTAL_POSTS
from ubbt_USER_PROFILE as t1,
ubbt_USERS as t2,
ubbt_USER_DATA as t3
where t1.USER_ID = t2.USER_ID
AND t1.USER_ID = t3.USER_ID
AND t3.USER_LAST_VISIT_TIME > UNIX_TIMESTAMP() - (86400 * #days)
ORDER BY USER_TOTAL_POSTS DESC
In the query above change #days to an actual number of days. For example, changing that to 3 will limit it to those that have been on in the past 3 days.
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
Brilliant Cheers matey
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
Just a follow up... (You never know!)
Users with more than x posts and active in last y days
select t1.USER_ID,t2.USER_DISPLAY_NAME,t1.USER_REAL_EMAIL,t1.USER_TOTAL_POSTS from ubbt_USER_PROFILE as t1, ubbt_USERS as t2, ubbt_USER_DATA as t3 where t1.USER_ID = t2.USER_ID AND t1.USER_ID = t3.USER_ID AND t3.USER_LAST_VISIT_TIME > UNIX_TIMESTAMP() - (86400 * Y) AND USER_TOTAL_POSTS > X ORDER BY USER_TOTAL_POSTS DESC
|
|
|
|
Test
by Phun - 05/28/2024 7:31 PM
|
|
|
|
1 members (Ruben),
403
guests, and
251
robots. |
Key:
Admin,
Global Mod,
Mod
|
|
|
|
|