2

I'm attempting to pull user id's from users who are part of a group. I then need to take those user id's and get the email addresses for those user id's. The results need to be comma separated. When I first attempted this I thought I just needed the user id's so I wrote the below. This works great and gives me all of the id's in a comma separated list. I'm stumped on how to now use the results of the below query to get the email addresses from the database lc_users WHERE 'id' = the results of the below query

$db = JFactory::getDbo();
$db->setQuery("SELECT `memberid` FROM `lc_community_groups_members` WHERE `groupid` = 1 AND `approved` = 1 ");
$column = $db->loadColumn();
return(implode(',',$column));

Any help would be much appreciated

1

Yes, JOIN should do the trick. Here is my query:

$query->select($db->quoteName('b.email'))
        ->from($db->quoteName('#__community_groups_members', 'a'))
        ->leftJoin(
            $db->quoteName('#__users', 'b')
            . ' ON (' . $db->quoteName('b.id')
            . ' = '
            . $db->quoteName('a.memberid') . ')'
        )
        ->where($db->quoteName('a.groupid') . ' = 1 ')
        ->where($db->quoteName('a.approved') . ' = 1');
  • Thanks for the help. I've implemented this bit of code and I get the following error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND a.approved = 1' at line 4 SQL=SELECT b.email FROM lc_community_groups_members AS a LEFT JOIN lc_users AS b ON (b.id = a.memberid) WHERE a.groupid = 1 AND AND a.approved = 1 – Todd Dec 11 '14 at 20:59
  • Ok try to change ->where($db->quoteName('a.groupid') . ' = 1 ' . 'AND') to ->where($db->quoteName('a.groupid') . ' = 1 '). I forgot that AND is added automatically by the query class. I've updated the query code. – Dmitry Rekun Dec 12 '14 at 7:33
  • Dmitry, thanks a lot! That along with a few modifications worked great! I appreciate the help. – Todd Dec 12 '14 at 8:12
0

Your task desires a single string containing comma-separated email addresses -- SQL can do all of that for you plus a few convenient flourishes.

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select("GROUP_CONCAT(DISTINCT B.email ORDER BY B.email)")
                ->from("lc_community_groups_members A")
                ->innerJoin("lc_users B ON A.memberid = B.id")
                ->where("A.groupid = 1 AND A.approved = 1");
    //echo $query->dump();
    $db->setQuery($query);
    if (!$emails = $db->loadResult()) {
        echo "No Qualifying Email Addresses";
    } else {
        echo $emails;
    }
} catch (Exception $e) {
    echo "Syntax Error"; // . $e->getMessage();
}

With sample data like this:

|  lc_community_groups_members        |  JOIN ON memberid=id  |         lc_users        |
|-------------------------------------|                       |-------------------------|
|  memberid  |  groupid  |  approved  |                       |  id  |       email      |
|------------|-----------|------------|                       |-------------------------|
|      1     |     2     |      1     |           <           |   1  |   a@email.com    |
|      2     |     1     |      1     |           <           |   2  | shared@email.com |
|      3     |     1     |      0     |           <           |   3  |   c@email.com    |
|      4     |     1     |      1     |           <           |   4  | shared@email.com |
|      5     |     1     |      1     |           <           |   5  |   e@email.com    |
|      6     |     2     |      0     |           <           |   6  |   f@email.com    |
|      7     |     1     |      0     |           <           |   7  |   g@email.com    |
|      8     |     1     |      1     |           <           |   8  |   h@email.com    |
|      9     |     1     |      1     |           <           |   9  |   i@email.com    |
---------------------------------------                       ---------------------------

You would be expecting the emails from ids: 2,4,5,8,9

  • INNER JOIN is used because null rows are not desired.
  • GROUP_CONCAT() is a perfect tool for gluing together the email values with commas. It also provides the added benefit of removing duplicates (for a streamlined mail-out) and alphabetizing (in case you are manually scanning the output).
  • loadResult() is the method to call when your resultset is a single value.

The output from my code block is:

e@email.com,h@email.com,i@email.com,shared@email.com
0

Firstly, I would suggest you use the latest coding standards for your database query as shown on the documentation page.

You can actually combine everything into 1 query using a join and should also use #__ as your database prefix which will automatically be replaced with lc_.

I'm not extremely confident with joins, I'll admit, but have a try of the following:

$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query->select($db->quoteName(array('a.*', 'b.memberid')))
      ->from($db->quoteName('#__community_groups_members', 'a'))
      ->where($db->quoteName('groupid') . ' = 1')
      ->where($db->quoteName('approved') . ' = 1')
      ->join('INNER', $db->quoteName('#__users', 'b') . ' ON (' . $db->quoteName('b.email') . ' = ' . $db->quoteName('b.id') . ')')
      ->where($db->quoteName('b.id') . ' = ' . $db->quote('b.memberid'));

$db->setQuery($query);
$column = $db->loadColumn();
  • Thanks for your help. With this I get an syntax error, unexpected '$db' (T_VARIABLE) but I'm not sure where it is. – Todd Dec 11 '14 at 21:00
  • @Michael - Opps, I've updated the code. – Lodder Dec 11 '14 at 21:12
  • Thanks again Lodder, unfortunately I'm still getting errors. Now it's: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND approved = 1 AND b.id = 'b.memberid'' at line 4 SQL=SELECT a.*,b.memberid FROM lc_community_groups_members AS a INNER JOIN lc_users AS b ON (b.email = b.id) WHERE groupid = 1 AND AND approved = 1 AND b.id = 'b.memberid' – Todd Dec 11 '14 at 22:44
  • @Lodder The syntax error is because your AND is appended to the first parameter of the first where() call, while you actually intend to name it as glue (2nd parameter). The . can be changed to , or more simply remove . 'AND' (I know you already know this -- let me know when you edit and I'll remove this comment). – mickmackusa Jun 10 '18 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.