Getting data from mutiple JDatabase Tables based on results from first

Question

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

Answer 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');

Answer 2

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  |   [email protected]    |
|      2     |     1     |      1     |           <           |   2  | [email protected] |
|      3     |     1     |      0     |           <           |   3  |   [email protected]    |
|      4     |     1     |      1     |           <           |   4  | [email protected] |
|      5     |     1     |      1     |           <           |   5  |   [email protected]    |
|      6     |     2     |      0     |           <           |   6  |   [email protected]    |
|      7     |     1     |      0     |           <           |   7  |   [email protected]    |
|      8     |     1     |      1     |           <           |   8  |   [email protected]    |
|      9     |     1     |      1     |           <           |   9  |   [email protected]    |
---------------------------------------                       ---------------------------

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:

[email protected],[email protected],[email protected],[email protected]

Answer 3

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();