2018-07-25 15:19 阅读 62


I use two tables with an inner join on 'user.ID'='email.ID'.

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

Usually i print out the results of my queries using:


I select the data from the DB like this:

SELECT user.name AS name, user.ID as ID, email.user AS user, email.address AS address
FROM user INNER JOIN email ON user.ID=email.ID

I then display a table and output each user in one line.

$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
  while($row = mysqli_fetch_assoc($result)) {

//this snippet is shortened simplified because I use all HTML in php echoes

When I try to display the email adresses inside the corresponsing users result row I just get the user multiple times with a different email adress in every row.

What I want:


What I get:


How do I have to select/join/group the tables and columns and how do I output them correctly? Thanks for any help. I´ve been stuck here for days.



  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    duanaiguang1960 duanaiguang1960 2018-07-25 15:33
    u.name,GROUP_CONCAT(e.email SEPARATOR ',') AS email
    FROM users u
    JOIN emails e ON e.user_id=u.id

    This should return:

    | User | Email                                           |
    | Nick | email1@mail.com,email2@mail.com,email3@mail.com |

    Then in PHP split the email string by comma

    点赞 评论 复制链接分享