1. 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

Discussion in 'XenForo Questions and Support' started by Dakis, Dec 16, 2013.

  1. Dakis

    Dakis Well-Known Member

    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?
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    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 likes this.
  3. Dakis

    Dakis Well-Known Member

    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:
     
  4. Dakis

    Dakis Well-Known Member

    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!
     
    Chris D likes this.

Share This Page