透视 mysql 结果集并创建 html 表/矩阵

2021-12-28 00:00:00 pivot matrix html-table php mysql

我的头整晚都撞在墙上，但还没有解决方案，假设我有这样的 Mysql 表结构:

Bang my head against the wall all night but no solution yet, Say I have Mysql table structure like this :

ID  name  value   year
 1  Tom     15     2018
 2  Tom     4      2019
 3  Tom     6      2020
 4  Kate    18     2018
 5  Kate    20     2019
    ...and so on...

我想通过 PHP 打印如下结果，并且年份应该是动态的，因为它会随着时间的推移而增加.请告诉我什么是一种方法谢谢

and I would like to print the result like below by PHP and the year should be dynamic as it will be increased over the years. Please shed some light on me on what would be an approach Thanks

year |2018 |2019|2020
----------------------
Tom  |  15 |  4 | 6
----------------------
Kate |  18 | 20 | ---

----- and so on ---

我的代码:

<table>

<?php
$mysqli = new mysqli('localhost', 'root', '123', 'news');
$report = array();
$columnIndex = 0;
$query = $mysqli->query("SELECT name, value, year FROM Testab");
while ($results = $query->fetch_assoc()) {
    foreach ($results as $tos => $toa) {
        $report[$tos][$columnIndex] = $toa;
    }
    $columnIndex++;
}

foreach ($report as $tos => $results) { ?>
    <tr>
        <th><?php echo $tos; ?></th>
        <?php foreach ($results as $toa) { ?>
            <th><?php echo $toa; ?></th>
        <?php } ?>
     </tr>
 <?php } ?>
</table>

推荐答案

有很多方法可以做到这一点；一些技术涉及到 sql 来准备动态数据透视.我下面的代码段将使用 php 来执行数据透视.

There will be many ways to do this; some techniques involve sql to prepare the dynamic pivot. My snippet below will use php to perform the pivot.

  1. 使用 foreach() 遍历结果集对象——不，您不需要调用获取函数来访问数据，因为结果对象是可迭代的.
  2. 创建一个多维分组数组，以名称为第一级键，然后以年份为键和值作为值的子数组.
  3. 创建一组独特的年份.我的方法将通过将年份指定为键和值来确保唯一性——因为数组不能包含重复的键，因此值将是唯一的，而无需稍后调用 array_unique().
  4. 按 ASC 排序年份
  5. 为每一年创建一个默认值数组.在本例中，我将 - 指定为默认值.
  6. 将文字词 name 添加到包含唯一年份的数组的前面——这将用于填充表格的标题行.
  7. 我更喜欢使用 implode() 来制作可变单元格表格行.
  8. printf() 是一种将文字文本与变量混合的干净方式——它避免了插值/串联语法.
  9. 在每个后续表格行中，将默认的年度值替换为相关人员的年度值，并用 implode() 表示.
  10. 如果结果集有可能为空，那么您可能希望将此代码段的大部分内容包含在 if ($resultObject) { ... } 块中.
  1. Loop through the result set object with a foreach() -- no, you don't need to call a fetching function to access the data because the result object is iterable.
  2. Create a multidimensional grouping array with names as the first level keys, then subarrays with years as keys and values as values.
  3. Create an array of unique years. My approach will ensure uniqueness by assigning the year as both the key and the value -- because arrays cannot contain duplicated keys, the values will be unique without having to call array_unique() later.
  4. Sort the years ASC
  5. Create an array of default values for every year. In this case, I am assigning - as the default value.
  6. Add the literal word name to the front of the array containing unique years -- this will be used to populate the header row of the table.
  7. I prefer to use implode() to craft a variable-celled table row.
  8. printf() is a clean way of blending literal text with variables -- it avoids interpolation/concatenation syntax.
  9. In each subsequent table row, replace the default yearly values with the relative person's yearly values and present with implode().
  10. If there is any chance that the result set is empty, then you may want to wrap most of this snippet in an if ($resultObject) { ... } block.

代码:(演示)

$grouped = [];
$columns = [];    

$resultObject = $mysqli->query("SELECT `name`, `value`, `year` FROM `Testab`");
foreach ($resultObject as $row) {
    $grouped[$row['name']][$row['year']] = $row['value'];
    $columns[$row['year']] = $row['year'];
}

sort($columns);
$defaults = array_fill_keys($columns, '-');
array_unshift($columns, 'name');

echo "<table>";
    printf(
        '<tr><th>%s</th></tr>',
        implode('</th><th>', $columns)
    );
    foreach ($grouped as $name => $records) {
        printf(
            '<tr><td>%s</td><td>%s</td></tr>',
            $name,
            implode('</td><td>', array_replace($defaults, $records))
        );
    }
echo "</table>";

输出:(添加间距/制表符以便于阅读)

Output: (with added spacing/tabbing for easier reading)

<table>
    <tr>
        <th>name</th> <th>2018</th> <th>2019</th> <th>2020</th>
    </tr>
    <tr>
        <td>Tom</td>  <td>15</td>   <td>4</td>    <td>6</td>
    </tr>
    <tr>
        <td>Kate</td> <td>18</td>   <td>20</td>   <td>-</td>
    </tr>
</table>

相关文章