Previous Thread
Next Thread
Print Thread
Hop To
Joined: Jan 2004
Posts: 2,474
Likes: 3
D
Pooh-Bah
Pooh-Bah
D Offline
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? smile


Ideally, I would want...

User# | UserName | emailaddress | #posts


...but just the email address alone would be fine wink

Thanks laugh

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
SQL Query
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
D
Pooh-Bah
Pooh-Bah
D Offline
Joined: Jan 2004
Posts: 2,474
Likes: 3
Cheers Rick, I appreciate that smile

Joined: Jan 2004
Posts: 2,474
Likes: 3
D
Pooh-Bah
Pooh-Bah
D Offline
Joined: Jan 2004
Posts: 2,474
Likes: 3
I just added this tiny bit to the end...

SQL Query
ORDER BY USER_TOTAL_POSTS DESC

..couldn't have made the original query though, so thanks again smile

Joined: Jan 2004
Posts: 2,474
Likes: 3
D
Pooh-Bah
Pooh-Bah
D Offline
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 smile


Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
SQL Query
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
D
Pooh-Bah
Pooh-Bah
D Offline
Joined: Jan 2004
Posts: 2,474
Likes: 3
Brilliant laugh

Cheers matey smile

Joined: Jan 2004
Posts: 2,474
Likes: 3
D
Pooh-Bah
Pooh-Bah
D Offline
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


Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
1 members (Havenofsobriety), 522 guests, and 99 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)