Send email to specific users

[ibaker]

I have a custom user field called Country and users on registration have to select which country they are in.

Now I know this may be coming in v1.2 however I need to send an email now to every user that has not selected Australia as their country in that field.

Any suggestions on how I could do it?...thanks

 

[Jake Bunce]

You can select the emails with a db query. Run this query on your database in phpmyadmin:

SELECT u.email
FROM xf_user_field_value AS ufv
LEFT JOIN xf_user AS u ON (u.user_id = ufv.user_id)
WHERE ufv.field_id = 'field id'
AND ufv.field_value != 'Australia';

You need to specify the field_id of the custom field and the value you want to exclude.

If you want to save the results then click Export at the bottom of the page in phpmyadmin after you run the query.

Note that some users might not show up in this list if they have never edited their profile.

 

[ibaker]

Thanks Jake...however that sql also brought up users that were registered prior to having the custom user field of country thus don't have an entry in the custom field...

 

[Jake Bunce]

I would have to examine your data. But you may wish to exclude empty values:

SELECT u.email
FROM xf_user_field_value AS ufv
LEFT JOIN xf_user AS u ON (u.user_id = ufv.user_id)
WHERE ufv.field_id = 'field id'
AND ufv.field_value != 'Australia'
AND ufv.field_value != '';

 

[ibaker]

Thanks Jake, that worked

I have an idea that could work with this if you could help...There are some 540 users that the query came out with. I checked and all of those users don't have a secondary usergroup ID. So if I create a new usergroup for non-Australian users, run an update query adding the new usergroup ID to their secondary usergroup ID field...I could then use the XF email feature to send an email to all of them at once. Afterwards I could then delete that secondary usergroup...what would be the update query to run?