我的头整晚都撞在墙上,但还没有解决方案,假设我有这样的 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.
foreach()
遍历结果集对象——不,您不需要调用获取函数来访问数据,因为结果对象是可迭代的.array_unique()
.-
指定为默认值.name
添加到包含唯一年份的数组的前面——这将用于填充表格的标题行.implode()
来制作可变单元格表格行.printf()
是一种将文字文本与变量混合的干净方式——它避免了插值/串联语法.implode()
表示.if ($resultObject) { ... }
块中.foreach()
-- no, you don't need to call a fetching function to access the data because the result object is iterable.array_unique()
later.-
as the default value.name
to the front of the array containing unique years -- this will be used to populate the header row of the table.implode()
to craft a variable-celled table row.printf()
is a clean way of blending literal text with variables -- it avoids interpolation/concatenation syntax.implode()
.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>
这篇关于透视 mysql 结果集并创建 html 表/矩阵的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持html5模板网!