dtgv52982
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:

$row["name"]

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) {
  <table>
  .
  .
  .
  while($row = mysqli_fetch_assoc($result)) {
    <tr>
      <td>$row["ID"]</td>
      <td>$row["Name"]</td>
      <td>$row["Email"]</td>
    </tr>
  }
}

//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:

correct_output_table.png

What I get:

wrong_output_table.png

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.

EDIT:

output_with_usernames.png

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

1条回答 默认 最新

  • 已采纳
    duanaiguang1960 duanaiguang1960 2018-07-25 15:33
    SELECT 
    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

    点赞 评论 复制链接分享

相关推荐