• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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!