用于客户数据的 PHP MySQL 嵌套列表

Question

我有一个嵌套列表，将显示客户及其电话号码，如下所示:

杰森

555 111-1111
555 222-2222

克里斯汀

555 333-3333

约翰

555 444-4444
555 555-5555
555 656-6666

不过，我在添加他们的电子邮件时遇到了问题。此代码只是复制每个电话号码下的电子邮件，但我需要将电话号码和电子邮件分组到每个客户下，例如:

杰森

555 111-1111
555 222-2222
jason@jason.com
jason.alternative@jason.com 

克里斯汀

555 333-3333
kristen@kristen.com
kristen.alternative@kristen.com
kristen.personal@kristen.com

约翰

555 444-4444
555 555-5555
555 656-6666
john@john.com

PHP 代码:

<?php
$result = mysqli_query($dbc,"
SELECT 
fname, 
phone, 
email
FROM 
customer, 
phone, 
email, 
customer_phone, 
customer_email
WHERE
customer.id=customer_phone.customer_id
AND
phone.id=customer_phone.phone_id
AND
customer.id=customer_email.customer_id
AND
email.id=customer_email.email_id
order by fname
");

$oldname = null;
while($row = mysqli_fetch_array($result))
{
    if($oldname != $row['fname'])
    {
        echo "<h3>".$row['fname']."</h3>";
        $oldname = $row['fname'];
    }
    echo "<p>".$row['phone']."</p>";
    echo "<p>".$row['email']."</p>";
}
?>

Answer 1

此查询将通过先获取电话号码，然后获取电子邮件，将电子邮件置于电话号码之后。包含“1-电话”和“2-电子邮件”的列可确保电子邮件按电话号码排序。

SELECT fname, '1-Phone' AS InfoType, phone AS TheInfo
  FROM Customer
  INNER JOIN phone ON phone.id=customer_phone.phone_id
  INNER JOIN customer_phone ON customer.id=customer_phone.customer_id
UNION SELECT fname, '2-Email', email
  FROM Customer
  INNER JOIN email ON email.id=customer_email.email_id
  INNER JOIN customer_email ON customer.id=customer_email.customer_id
ORDER BY fname, InfoType

我将连接更改为 ANSI 语法，因为它使我更容易处理此问题；没有其他原因。如果您愿意，可以将连接条件推回 WHERE 子句 - 我对此没有强烈的感觉:)

对渲染代码的唯一更改是替换此...

echo "<p>".$row['phone']."</p>";
echo "<p>".$row['email']."</p>";

...这样:

echo "<p>".$row['TheInfo']."</p>";