問題描述

我的頭整晚都撞在墻上，但還沒有解決方案，假設(shè)我有這樣的 Mysql 表結(jié)構(gòu):

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 打印如下結(jié)果，并且年份應(yīng)該是動(dòng)態(tài)的，因?yàn)樗鼤?huì)隨著時(shí)間的推移而增加.請(qǐng)告訴我什么是一種方法謝謝

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>

推薦答案

有很多方法可以做到這一點(diǎn)；一些技術(shù)涉及到 sql 來準(zhǔn)備動(dòng)態(tài)數(shù)據(jù)透視.我下面的代碼段將使用 php 來執(zhí)行數(shù)據(jù)透視.

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() 遍歷結(jié)果集對(duì)象——不，您不需要調(diào)用獲取函數(shù)來訪問數(shù)據(jù)，因?yàn)榻Y(jié)果對(duì)象是可迭代的.
2. 創(chuàng)建一個(gè)多維分組數(shù)組，以名稱為第一級(jí)鍵，然后以年份為鍵和值作為值的子數(shù)組.
3. 創(chuàng)建一組獨(dú)特的年份.我的方法將通過將年份指定為鍵和值來確保唯一性——因?yàn)閿?shù)組不能包含重復(fù)的鍵，因此值將是唯一的，而無需稍后調(diào)用 array_unique().
4. 按 ASC 排序年份
5. 為每一年創(chuàng)建一個(gè)默認(rèn)值數(shù)組.在本例中，我將 - 指定為默認(rèn)值.
6. 將文字詞 name 添加到包含唯一年份的數(shù)組的前面——這將用于填充表格的標(biāo)題行.
7. 我更喜歡使用 implode() 來制作可變單元格表格行.
8. printf() 是一種將文字文本與變量混合的干凈方式——它避免了插值/串聯(lián)語法.
9. 在每個(gè)后續(xù)表格行中，將默認(rèn)的年度值替換為相關(guān)人員的年度值，并用 implode() 表示.
10. 如果結(jié)果集有可能為空，那么您可能希望將此代碼段的大部分內(nèi)容包含在 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>

這篇關(guān)于透視 mysql 結(jié)果集并創(chuàng)建 html 表/矩陣的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！