php - MYSQL数据从纵向到横向PHP

Question

所以，我有这样的数据库:

+----------+------------+-------+
| TIME     | Date       | A0    |
+----------+------------+-------+
| 17:00:00 | 2015-06-23 | 100   |
| 17:05:00 | 2015-06-23 | 120   |
| 17:10:00 | 2015-06-23 | 200   |
| 17:00:00 | 2015-06-24 | 200   |
| 17:05:00 | 2015-06-24 | 190   |
| 17:10:00 | 2015-06-24 | 200   |
| 17:00:00 | 2015-06-25 | 90    |
| 17:05:00 | 2015-06-25 | 100   |
| 17:10:00 | 2015-06-25 | 200   |
| .        | .          | .     |
| .        | .          | .     |
| .        | .          | .     |
| .        | .          | .     |
| TIME(n)  | Date(n)    | A0(n) |
+----------+------------+-------+

我希望它在我的主页上显示如下:

+----------+------------+------------+------------+--------------+
|  TIME    | 2015-06-23 | 2015-06-24 | 2015-06-25 | .....Date(n) |
+----------+------------+------------+------------+--------------+
| 17:00:00 | 100        | 200        | 90         | .....A0(n)   |
| 17:05:00 | 120        | 190        | 100        | .....A0(n)   |
| Time(n)  | A0(n)      | A0(n)      | A0(n)      | .....A0(n)   |
+----------+------------+------------+------------+--------------+

我使用 (n) 因为我的主页允许我选择任意日期和时间点并显示该日期和时间的 A0 值。

我应该对每个方法使用组连接，还是其他方法？

Answer 1

希望有帮助。

<?php

/*
$rows = array(
array('time' => '17:00:00', 'date' => '2015-06-23', 'a0' => 100),
array('time' => '17:05:00', 'date' => '2015-06-23', 'a0' => 120),
array('time' => '17:00:00', 'date' => '2015-06-24', 'a0' => 10),
array('time' => '17:05:00', 'date' => '2015-06-24', 'a0' => 20),
array('time' => '17:00:00', 'date' => '2015-06-25', 'a0' => 10),
array('time' => '17:05:00', 'date' => '2015-06-26', 'a0' => 20),
);*/

$db = new mysqli("localhost", "user", "password", "database"); // connecting

$result = $db->query("SELECT time, date, a0 FROM define_your_table_name_here"); // query-in database

$data = array();
$dates = array();
while ($row = $result->fetch_assoc()) { // fetching result
    if(!isset($data[$row['time']])) {
        $data[$row['time']] = array();
    }    

    if(!isset($data[$row['time']][$row['date']])) {
        $data[$row['time']][$row['date']] = array();
    }    

    if(!in_array($row['date'], $dates)) {
        $dates[] = $row['date'];
    }

    $data[$row['time']][$row['date']] = $row['a0'];
}
?>

<table border="1">
<thead>
    <th>TIME</th>

    <?php foreach($dates AS $date) : ?>
        <th><?=$date?></th>
    <?php endforeach; ?>
</thead>
<tbody>

    <?php foreach($data AS $time => $entries) : ?>
    <tr>
        <td><?=$time?></td>

        <?php foreach($dates AS $date) : ?>
            <td><?=(isset($data[$time][$date])?$data[$time][$date]:'')?></td>
        <?php endforeach; ?>
    </tr>
    <?php endforeach; ?>

</tbody>
</table>