CODEWITHSUNDEEP
phpmysql
ClayD
ClayD

Reputation: 71

Get data from 3 tables and display mysql php

I have 3 tables in the same mysql db

email

id      |   emailAddresses
--------+--------------
91      |    [email protected]
92      |    [email protected]
93      |    [email protected]
94      |    [email protected]"
95      |    [email protected]
96      |    [email protected]
97      |    [email protected]
98      |    [email protected]
99      |    [email protected]
100     |    [email protected]


groups

id      |   groupName 
--------+----------
10      |    Group1
11      |    Group2    
12      |    Group3    
13      |    Group4


emailGroupsMatches

id      |   emailGroup_id   |   email_id     
--------+-------------------|------------
400     |    10             |      91     
411     |    11             |      91
412     |    12             |      93
413     |    13             |      94
414     |    10             |      95
415     |    10             |      96

I am trying to pull the group names and then check for all emails associated with that group and display them under the group heading. 

Group1:    
[email protected]     
[email protected]   
[email protected] 

Group2:    
[email protected] 

Group3:
[email protected] 

Group4:
[email protected]

I think i may be overthinking this or it may be something i'm unfamiliar with. I have tried Joining tables but i may have done it wrong. Any help in a direction would be appreciated.

Thanks

UPDATE__________

I have tried getting two arrays and then trying to join them. Its close but i keep having an issue.

        $emailArray1[] = '';
        $emailArray2[] = '';
         $stmt1 = $this->db->query("SELECT emailGroupsMatches.emailGroup_id, emailGroupsMatches.email_id, emailGroups.groupName FROM emailGroups,emailGroupsMatches WHERE emailGroupsMatches.emailGroup_id = emailGroups.id");
        while ($row1 = $stmt1->fetch()) {
            $emailArray1[] = $row1;


            $emailGroup_id[] = $row1['emailGroup_id'];
            $email_id[] = $row1['email_id'];
            $groupName[] = $row1['groupName'];

            $data1[] = array(
            "emailGroup_id" => $emailGroup_id,
            "email_id" => $email_id,
            "groupName" => $groupName
            );
            echo '<pre>';
            print_r($data1);

        }
        $stmt2 = $this->db->query("SELECT * FROM email,emailGroupsMatches WHERE emailGroupsMatches.email_id = email.id");
        while ($row2 = $stmt2->fetch()) {
            $emailArray2[] = $row2;


            $emailGroup_id2[] = $row2['emailGroup_id'];
            $emailAddresses[] = $row2['emailAddresses'];

            $data2[] = array(
            "emailGroup_id" => $emailGroup_id2,
            "emailAddresses" => $emailAddresses
            );

            //echo '<pre>';
            //print_r($data2);


        }

        //$c = array_combine($emailArray1, $emailArray2);

        //print_r($c);


        $result = array();

            foreach ($data1 as $value) {
                // do not handle elements without pageclass
                if (!array_key_exists('emailGroup_id', $value) || !$value['emailGroup_id']) {
                    continue;
                }
                $result[$value['emailGroup_id']] = $value;
            }
            foreach ($data2 as $value) {
                if (
                    // do not handle elements without pageclass         
                    !array_key_exists('emailGroup_id', $value) || !$value['emailGroup_id'] ||
                    // do not handle elements that do not exist in array 1
                    !array_key_exists($value['emailGroup_id'], $result)
                    ) {
                    continue;
                }
                // merge values of this pageclass
                $result[$value['emailGroup_id']] = array_merge($result[$value['emailGroup_id']], $value);
            }
            echo '<pre>';
            print_r($result);

returns

Array

( [0] => Array ( [emailGroup_id] => Array ( [0] => 11 )

        [emailAddresses] => Array
            (
                [0] => [email protected]
            )

    )

[1] => Array
    (
        [emailGroup_id] => Array
            (
                [0] => 11
                [1] => 11
            )

        [emailAddresses] => Array
            (
                [0] => [email protected]
                [1] => [email protected]
            )

    )

[2] => Array
    (
        [emailGroup_id] => Array
            (
                [0] => 11
                [1] => 11
                [2] => 12
            )

        [emailAddresses] => Array
            (
                [0] => [email protected]
                [1] => [email protected]
                [2] => [email protected]
            )

    )

[3] => Array
    (
        [emailGroup_id] => Array
            (
                [0] => 11
                [1] => 11
                [2] => 12
                [3] => 12
            )

        [emailAddresses] => Array
            (
                [0] => [email protected]
                [1] => [email protected]
                [2] => [email protected]
                [3] => [email protected]
            )

    )

[4] => Array
    (
        [emailGroup_id] => Array
            (
                [0] => 11
                [1] => 11
                [2] => 12
                [3] => 12
                [4] => 10
            )

        [emailAddresses] => Array
            (
                [0] => [email protected]
                [1] => [email protected]
                [2] => [email protected]
                [3] => [email protected]
                [4] => [email protected]
            )

    )

[5] => Array
    (
        [emailGroup_id] => Array
            (
                [0] => 11
                [1] => 11
                [2] => 12
                [3] => 12
                [4] => 10
                [5] => 10
            )

        [emailAddresses] => Array
            (
                [0] => [email protected]
                [1] => [email protected]
                [2] => [email protected]
                [3] => [email protected]
                [4] => [email protected]
                [5] => [email protected]
            )

    )

[6] => Array
    (
        [emailGroup_id] => Array
            (
                [0] => 11
                [1] => 11
                [2] => 12
                [3] => 12
                [4] => 10
                [5] => 10
                [6] => 13
            )

        [emailAddresses] => Array
            (
                [0] => [email protected]
                [1] => [email protected]
                [2] => [email protected]
                [3] => [email protected]
                [4] => [email protected]
                [5] => [email protected]
                [6] => [email protected]
            )

    )

[7] => Array
    (
        [emailGroup_id] => Array
            (
                [0] => 11
                [1] => 11
                [2] => 12
                [3] => 12
                [4] => 10
                [5] => 10
                [6] => 13
                [7] => 13
            )

        [emailAddresses] => Array
            (
                [0] => [email protected]
                [1] => [email protected]
                [2] => [email protected]
                [3] => [email protected]
                [4] => [email protected]
                [5] => [email protected]
                [6] => [email protected]
                [7] => [email protected]
            )

    )

)

Upvotes: 1

Views: 56

Answers (1)

Santosh D.
Santosh D.

Reputation: 557

Below query will return you the groupName and comma separated list of associated emailAddresses. So you can display group name and to display associated email just loop the emailAddresses list. Hope this will help you!

SELECT groups.groupName as groupName, GROUP_CONCAT(email.emailAddresses) as emails 
FROM email 
INNER JOIN emailGroupsMatchesON emailGroupsMatches.email_id = email.id 
INNER JOIN groups ON emailGroupsMatches.emailGroup_id = groups.id 
GROUP BY groups.id

Upvotes: 1

Related Questions