UBB.Developers Forums UBB.threads 7 Development How Do I? Need SQL command that yields email addresses

Say I want to email all members who have 0 posts?

Say I want to email all members who have 0 posts and are in a certain group?

Would there be an SQL command that would yield EMAIL addresses instead of usernames? And is there a way to do these compound queries?

Or




Second statement, replace the 3 with the group number you are looking for.

The first argument resulted in this error set:
--------------------------------
SQL ERROR: Unable to do_query: SELECT U_Email FROM `w3t_users` WHERE U_Totalposts = 0
Table 'digitalw_threadtest.w3t_users' doesn't exist
Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in /home/digitalw/public_html/ubbthreads/mysql.inc.php on line 165


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/digitalw/public_html/ubbthreads/mysql.inc.php on line 131
----------------------------
The second resulted in....
----------------------------
SQL ERROR: Unable to do_query: SELECT U_Email FROM `w3t_users` WHERE U_Totalposts = 0 and (U_Groups LIKE '%7%')
Table 'digitalw_threadtest.w3t_users' doesn't exist
Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in /home/digitalw/public_html/ubbthreads/mysql.inc.php on line 165


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/digitalw/public_html/ubbthreads/mysql.inc.php on line 131
-------------------------
Am I missing something?

Yes The error is simple

Zarzal forgot he is on a windows server. They by default make table names non capital Try these commands

SELECT U_Email

FROM `w3t_Users`

WHERE U_Totalposts = 0



Or


Code:


SELECT U_Email

FROM `w3t_Users`

WHERE U_Totalposts = 0 and (U_Groups LIKE '%3%')

Is your table prefix w3t? If so, then try w3t_Users. Otherwise change w3t to whatever your table prefix is.

I forgot to mention the table prefix thing. I expect that might be the problem.

I'm on a windows server too Chuck, both those queries work for me.

This works great. Another question:
Is there an SQL command that will yield an email list of all the members in a certain group who were last on more than ..say.. two weeks ago?

SELECT t1.U_Email
FROM w3t_Users AS t1, w3t_Last AS t2
WHERE t1.U_Number = t2.L_Uid AND L_Last > UNIX_TIMESTAMP( DATE_SUB( CURDATE( ) , INTERVAL 14
DAY ) ) AND t1.U_Groups
LIKE '%-3-%'

The code in the above post yields a list of every email address in the group! And each email address is listed between 3 and 8 times.

I'm not sure if the code's wrong, or if I misstated what I'm looking for: The subset of a usergroup containing all members who haven't logged on in the past two weeks. The list I was looking for was about 30 emails. I got 500!: Everyone!

You where not clear on what you wanted I took it you needed everybody who was on in the last two weeks. Besides when checking with the last table your going to get multiple entries. I mean you could write script to cycle through and only give one instance per email.

I didn't realize that multiple email addresses should be expected. Sorry for causing the confusion, and jumping to conclusions.

What changes would I make to the code above to get emails of group members who HAVEN'T been on the forum in the past two weeks?

As far as who has not been not been on in the last two weeks HMM

SELECT U_Email
FROM w3t_Users
WHERE U_LastOn < UNIX_TIMESTAMP( DATE_SUB( CURDATE( ) , INTERVAL 14
DAY ) ) AND U_Groups
LIKE '%-3-%'

This would do what you want.

Worked like a charm-- I deeply appreciate it!