Need help with mailing list query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • makaiguy
    Senior Member
    • May 2004
    • 125
    • 3.8.x

    Need help with mailing list query

    Don't know beans about structuring queries.

    What I want to do is run a query which will export its results into files that may be used elsewhere. Desired output is list of matched pairs of usernames and their associated email addresses, like:
    username1, email1
    username2, email2
    etc.

    I need to create two lists:

    a) List for a specific primary usergroup (#7)
    I believe the basic query would be
    Code:
    SELECT username, email FROM user WHERE usergroupid=7;
    but don't know how to get this exported into a file.

    b) List for a specific secondary membergroup (#12). Users may belong to multiple membergroups, so needs to be able to pick out all members of usergroup 12 even if they are also members of other membergroups.
    Don't know how to deal with this as I *think*
    Code:
    SELECT username, email FROM user WHERE membergroupids=12;
    .. would only pick out the ones that ONLY belong to 12. (Right?)

    Can anybody help me with the required query syntax?
    Last edited by makaiguy; Tue 20 Mar '07, 6:16pm. Reason: correct syntax
    Doug Wilson
    Administrator, Timeshare Users Group bbs
  • makaiguy
    Senior Member
    • May 2004
    • 125
    • 3.8.x

    #2
    Originally posted by makaiguy
    .. I *think*
    Code:
    SELECT username, email FROM user WHERE membergroupids=12;
    .. would only pick out the ones that ONLY belong to 12. (Right?)
    Looks like this is an unfounded fear. When executing via the AdminCP, it seems to catch group 12 members whether or not they are in other groups also.

    So looks like the query structure question is taken care of. Any suggestions as to how to get the output into a file?
    Doug Wilson
    Administrator, Timeshare Users Group bbs

    Comment

    • makaiguy
      Senior Member
      • May 2004
      • 125
      • 3.8.x

      #3
      Replying to my own thread, so I'll be able to find the info again later.

      Since membergroupids may contain more than one group, doing an equality check may not produce the desired results when the user is in more than one secondary group. Instead use FIND_IN_SET:
      PHP Code:
      SELECT usernameemail FROM user WHERE FIND_IN_SET(12membergroupids); 
      Doug Wilson
      Administrator, Timeshare Users Group bbs

      Comment

      widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
      Working...