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>';
}
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' coloumn although the second for loop is echoed into the most right coloumn.
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 statement is not final. This is just for testing.