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

[Dakis]

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:

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]

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]

And that, ladies and gentlemen is the difference between a complete novice that knows what php and mysql is, and a professional.

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

 

[Dakis]

Eh, I missed out the most important part, the email address is the one I'm after obviously, not the member name

So I guess the query above becomes this

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!