[SOLVED] GROUP_CONCAT being weird

GROUP_CONCAT being weird

I use three tables with an inner join on 'user.ID'='email.ID' and 'user.ID'='telephone.ID'.

Every user has a name and an ID. Each email has a username, the email address itself and a corresponding userID. Each telephone number has a username, the number itself and a corresponding userID. It is possible for a user to have any amount of email addresses and telephone numbers.

I managed to output a users email addresses by selecting them like this:

GROUP_CONCAT(email.Address SEPARATOR ',') AS Address,
GROUP_CONCAT(email.Username SEPARATOR ',') AS eUsername

I then converted it into an array:

$Address = explode(",", $row["Address"]);
$eUsername = explode(",", $row["eUsername"]);

And printed it like this:

  for($i = 0;$i<count($Address);$i++) {
    echo $eUsername[$i] .': '. $Address[$i].'<br>';
  }

This is the printed table:

I then just did the same thing with the telephone numbers

Selecting:

GROUP_CONCAT(telephone.Number SEPARATOR ',') AS Number,
GROUP_CONCAT(telephone.Username SEPARATOR ',') AS tUsername

Converting:

$Number = explode(",", $row["Number"]);
$tUsername = explode(",", $row["tUsername"]);

Printing:

for($i = 0;$i<count($Number);$i++) {
    echo $tUsername[$i] .': '. $Number[$i].'<br>';
}

But this messed up the whole table:

I don't get why it prints the email addresses three times and just doesn't print anything in the 'Telephone' column although the second for loop is echoed into the most right column.

Sorry for the long post and thanks for any help.

EDIT: here is my full SQL query:

SELECT 
    user.Name AS Name, user.ID AS ID,
    GROUP_CONCAT(email.Address SEPARATOR ',') AS Address,
    GROUP_CONCAT(email.Username SEPARATOR ',') AS eUsername,
    GROUP_CONCAT(telephone.Number SEPARATOR ',') AS Number,
    GROUP_CONCAT(telephone.Username SEPARATOR ',') AS tUsername
FROM 
    user
INNER JOIN 
    email ON user.ID = email.ID
INNER JOIN 
    telephone ON user.ID = telephone.ID
WHERE 
    Name REGEXP '$searchterm'
GROUP BY 
    ID

The WHERE clause is not final. This is just for testing.

Solution

The simplest solution is to use distinct:

SELECT u.Name AS Name, u.ID AS ID,
       GROUP_CONCAT(DISTINCT e.Address SEPARATOR ',') AS Address,
       GROUP_CONCAT(DISTINCT e.Username SEPARATOR ',') AS eUsername,
       GROUP_CONCAT(DISTINCT t.Number SEPARATOR ',') AS Number,
       GROUP_CONCAT(DISTINCT t.Username SEPARATOR ',') AS tUsername
FROM mitglied u INNER JOIN
     email e
     ON u.ID = e.ID INNER JOIN
     telephone t
     ON u.ID = t.ID
WHERE u.Name REGEXP '$searchterm'
GROUP BY u.ID, u.name;

Notes:

I assume that mitglied is users.
Table aliases make the query easier to write and to read.
Qualified column names are recommended whenever you have multiple table references.