|
|
|
|
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
|
|
|
Bots
by Outdoorking - 04/13/2024 5:08 PM
|
|
|
|
|
|
|
|
|
|
|
0 members (),
868
guests, and
467
robots. |
|
Key:
Admin,
Global Mod,
Mod
|
|
|
|
|
|
