php - MySQL 查询返回的分组结果

Question

我正在查询一个由前雇员建立的数据库，糟糕!我已经成功收集并输出了我需要的所有数据 - 但是我想将数据分组为问题编号。

此时输出是这样的......

Company 1
Issue number: 142
Total: 480.00

Company 2
Issue number: 142
Total: 46

Company 3
Issue number: 142
Total: 240.00

Company 4
Issue number: 142
Total: 420.00

Company 5
Issue number: 142
Total: 468.00

Company 6
Issue number: 142
Total: 252.00

我希望它读起来像......

**Issue number: 142**
Company 1 Total 480
Company 2 Total 468
etc etc I.e grouping them into issue numbers. 

这是我的代码...

$sql = "
SELECT bf_total_cost
     , bf_date_added
     , ib_issue_number
     , companyname
     , contacts_id
     , bf_id
     , bf_company_id
     , ib_booking_form_number 
  FROM contacts
     , booking_form
     , issues_booked 
 WHERE ib_issue_number >= 141 
   AND ib_issue_number <= 165 
   AND bf_date_added >= '2014-11-01' 
   AND bf_date_added <= '2016-08-31' 
   AND contacts_id = bf_company_id 
   AND bf_id = ib_booking_form_number 
 ORDER 
    BY ib_issue_number ASC;
";

$result = mysql_query($sql) or die (mysql_error());

$row = mysql_fetch_array($result) or die (mysql_error());
while($row = mysql_fetch_array($result)) {

echo '<ul>';
echo '<li>'.$row['companyname'] .'</li>';
echo '<li>'.$row['ib_issue_number'] .'</li>';
echo '<li>'.$row['bf_total_cost'] .'</li>';
echo '</ul>';
}

Answer 1

当您从结果中获取行时，您可以在 while 循环中创建嵌套/分组数组:

$result = mysql_query($sql) or die (mysql_error());

$groupedArray = array();
while($row = mysql_fetch_assoc($result)) {
    $groupedArray[$row['ib_issue_number']][] = $row;
}

然后您可以将其输出为嵌套 HTML 列表:

echo "<ul>";
foreach ($groupedArray as $issueNumber => $group) {
    echo "<li>issue number: $issueNumber <ul>";
    foreach ($group as $row) {
        echo "<li>Company: {$row['companyname']}; Total {$row['bf_total_cost']}</li>";
    }
    echo "</ul></li>";
}
echo "</ul>";