XF 1.2 Help with query to select users based on custom fields

Dakis

Well-known member
#1
Ok so I have an external script that I use to send out my newsletters (sendy.co), and the problem I'm facing is I have to manually export my userlist from the forum and import it to the newsletter system.

I have come up with this query so far:

Code:
SELECT u.username, ufv.field_value
FROM xf_user_field_value AS ufv
LEFT JOIN xf_user AS u ON (u.user_id = ufv.user_id)
WHERE ufv.field_id = 'ggf_s_newsletter';
that returns the userlist together with this custom field and its values.

So far so good, but what I need is a userlist that will return not just the username but also the gender of each user.

And as a second step to make it even more complicated, to return also the value of the "receive site mailings" option.

Any ideas anyone?
 

Chris D

XenForo developer
Staff member
#2
Code:
SELECT u.username, ufv.field_value, u.gender, uo.receive_admin_email
FROM xf_user_field_value AS ufv
LEFT JOIN xf_user AS u ON (u.user_id = ufv.user_id)
LEFT JOIN xf_user_option AS uo ON (uo.user_id = u.user_id)
WHERE ufv.field_id = 'ggf_s_newsletter'
    AND uo.receive_admin_email = 1;
:)
 

Dakis

Well-known member
#3
And that, ladies and gentlemen is the difference between a complete novice that knows what php and mysql is, and a professional. :D

Thanks Chris lemme try this out see what else I can throw your way :ROFLMAO:
 

Dakis

Well-known member
#4
Eh, I missed out the most important part, the email address is the one I'm after obviously, not the member name :p

So I guess the query above becomes this

Code:
SELECT u.email, ufv.field_value, u.gender, uo.receive_admin_email
FROM xf_user_field_value AS ufv
LEFT JOIN xf_user AS u ON (u.user_id = ufv.user_id)
LEFT JOIN xf_user_option AS uo ON (uo.user_id = u.user_id)
WHERE ufv.field_id = 'ggf_s_newsletter'
    AND uo.receive_admin_email = 1;
and I'm good to go :)

Thanks Chris!
 
Top