logo标签列表

转置MySQL查询结果并创建HTML表/矩阵

phpmysqlmatrixhtml-tablepivot
3
整晚都在头痛，但仍未找到解决方案。假设我有一个像这样的Mysql表结构：
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打印出类似下面的结果，其中年份应该是动态的，因为随着时间的推移而增加。请告诉我一个实现方法，谢谢。

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>
1
如果您的数据库中没有2019年的数据，那么您的表格是否仍应该有2018年、2019年、2020年的列，或者在这种情况下您只想留下2019年以外的列？ - CBroe
给定的代码到底哪里出了问题？你尝试过什么来使其正常工作吗？ - Nico Haase
相关链接：如何在HTML和PHP中制作PIVOT（交叉表） - undefined
4个回答
2

有很多方法可以做到这一点；有些技术涉及使用SQL准备动态旋转表。我下面的片段将使用PHP执行旋转。

  1. 使用foreach()循环遍历结果集对象--不需要调用获取函数来访问数据，因为结果对象是可迭代的。
  2. 创建一个多维分组数组，以名称作为第一层键，然后子数组以年份为键，值为值。
  3. 创建一个唯一年份的数组。我的方法将年份分配为键和值，以确保唯一性--因为数组不能包含重复的键，所以值将在后面无需调用array_unique()即可得到唯一性。
  4. 按升序排序年份
  5. 为每个年份创建一个默认值的数组。在这种情况下，我将-指定为默认值。
  6. 在包含唯一年份的数组前添加文字name--这将用于填充表格的标题行。
  7. 我喜欢使用implode()来创建一个变量单元格的表格行。
  8. printf()是将文字与变量混合的干净方式--它避免了插值/连接语法。
  9. 在每个后续表格行中，使用相对人员的年度值替换默认年度值，并使用implode()呈现。
  10. 如果有可能结果集为空，则可以将大部分代码放入if ($resultObject) { ... }块中。
代码：(演示)
$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>";

输出结果：（为了更易于阅读添加了空格/缩进）

<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>
1
请参考下面的代码，它可以帮助您找到解决方案：
基本上需要对输入数据进行额外的循环，以创建所需的输出数据的元数据。在几种数据报告场景中，需要这种数据转换。
<?PHP
//assuming mysql is giving below data
$in_data = array(
                array('ID'=>1,'name'=>'Tom','value'=>15,'year'=>2018),
                array('ID'=>2,'name'=>'Tom','value'=>4,'year'=>2019),
                array('ID'=>3,'name'=>'Tom','value'=>6,'year'=>2020),
                array('ID'=>4,'name'=>'Kate','value'=>18,'year'=>2018),
                array('ID'=>5,'name'=>'Kate','value'=>20,'year'=>2019),
            );
            
$out_data = convertInToOut($in_data);

var_dump($out_data);

function convertInToOut($inData){
    $years = array();
    $persons = array('year'=>array('name'));
    //creating meta data 
    foreach($inData as $in){
        $years[$in['year']] = $in['year'];
        $persons[$in['name']] = array($in['name']); 
    }

    sort($years,SORT_NUMERIC);
    //aligning based on year
    $yearIndex = array();
    foreach($years as $key=>$year){
        $yearIndex[$year] = $key;
        $persons['year'][] = $year;
    }
    //final step of handling values for each names
    foreach($inData as $in){
        $persons[$in['name']][$yearIndex[$in['year']]+1] = $in['value'];    
    }
    
    return $persons;
    
}           

?>

var_dump($out_data) 的输出：

    array(3) {
  ["year"]=>
  array(4) {
    [0]=>
    string(4) "name"
    [1]=>
    int(2018)
    [2]=>
    int(2019)
    [3]=>
    int(2020)
  }
  ["Tom"]=>
  array(4) {
    [0]=>
    string(3) "Tom"
    [1]=>
    int(15)
    [2]=>
    int(4)
    [3]=>
    int(6)
  }
  ["Kate"]=>
  array(3) {
    [0]=>
    string(4) "Kate"
    [1]=>
    int(18)
    [2]=>
    int(20)
  }
}
这并没有提供OP所请求的输出。 - mickmackusa
@mickmackusa，生成的数组可以用于在HTML表中生成所需的输出。 - kewlashu
那么你发了一个半回答？如果 Stack Overflow 页面包含半个答案，那么我们将无法使用旧/现有页面来关闭新的重复问题。 - mickmackusa
@mickmackusa，半个答案或完整答案取决于观点，目标应该是帮助 OP 达到解决方案，这正是我所尝试做的。对于 OP 来说，创建 HTML 不是主要问题，而是创建所需数据以用于创建 HTML。 - kewlashu
Stack Overflow的帖子从来不是一对一的互动。每个答案都应该努力教育/授权OP以及成千上万的未来研究人员。每个答案都应该完全回答特定的问题，并包括任何相关的见解，这些见解可能有助于具有类似要求的未来研究人员。您的帖子越全面、深入，就越有可能帮助某个人并赚取更多的声望分数。 - mickmackusa
@mickmackusa，感谢您宝贵的意见和深入见解。 - kewlashu
1

首先，您需要有一个单独的查询，按升序获取所有年份。

'SELECT year FROM table GROUP BY year ORDER BY year ASC

或者在下面的循环中按照注释从结果数组中提取年份，但确保在主查询中按年份升序排序，以避免调用数据库两次。

你应该拥有这样的结果。在foreach循环中操作数组，它会给你一个按用户分类的多维数组。

然后按照以下代码在html中循环它，你就完成了。

   <?php

$mydata = array(
    array(
        'id' => 1,
        'name' => 'Tom',
        'value' => 5,
        'year' => 2019
    ),
    array(
        'id' => 1,
        'name' => 'Tom',
        'value' => 5,
        'year' => 2018
    ),
    array(
        'id' => 1,
        'name' => 'kate',
        'value' => 5,
        'year' => 2017
    ),
    array(
        'id' => 1,
        'name' => 'Tom',
        'value' => 5,
        'year' => 2018
    ),
    array(
        'id' => 1,
        'name' => 'kate',
        'value' => 5,
        'year' => 2018
    ),
    array(
        'id' => 1,
        'name' => 'kate',
        'value' => 5,
        'year' => 2017
    ),
    array(
        'id' => 1,
        'name' => 'joe',
        'value' => 5,
        'year' => 2016
    ),
    array(
        'id' => 1,
        'name' => 'joe',
        'value' => 5,
        'year' => 2017
    )
);

 //this line assumes that you queries the database to fetch the years
$years = ['2019','2018','2017','2016'];

$display = array();

foreach ($mydata as $data) {
   //only add this if you don't want to query the database a second 
    //time to get the years
   if(!in_array($data['year'], $years)){
       $years[] = $data['year'];
    }
    $display[$data['name']]['values'][$data['year']] = $data['value'];
    $display[$data['name']]['name'] = $data['name'];
}


?>

<html>

<body>
    <table>
        <td>year</td>
        <?php foreach ( $years as $year) { ?>
            <td><?php echo $year; ?></td>
        <?php } ?>
        <tbody>
            <?php foreach ($display as $name => $info) { ?>
                    <tr>
                        <td><?php echo $info['name']; ?></td>

                        <?php foreach($years as $year){ ?>
                        <td><?php echo isset($info['values'][$year])? $info['values'][$year] : 'null'; ?></td>
                        <?php } ?>
                    </tr>

            <?php } ?>
        </tbody>
        </th>

    </table>
</body>

</html>
如果你仔细阅读我的回复的第一句话，你会发现我指定了应该从数据库中查询年份，并按年份分组。 - Joe Abdel Sater
1
循环遍历包含数据的数组并提取所有年份进行分组，可以获得多年相同的结果，这对于性能来说更好。 - Joe Abdel Sater
我完全同意你的观点，并已在我的回答中展示了这一点。 - mickmackusa
我已根据您的建议编辑了我的评论，并提供了两个选项。 - Joe Abdel Sater
0

对我来说非常好用，试试吧！

f_pivot($rs,'NombreFila','NombreColumna','Valor', true, 'Nombre del reporte', 'danger');

这里调用了函数：

function f_pivot ($array, $fila, $columna, $valor, $MostrarTotal = true, $Caption=null, $ColorHeader = "info") {
  echo '<div class="table-responsive">
  <table class="table table-hover table-condensed table-striped">';
  if (empty($array)) {
    echo "<caption>No se encuentran datos para mostrar !</caption>";
  }else{
    foreach ($array as $key => $r) {
        $f[$key] = $r[$fila];
        $c[$key] = $r[$columna];
    }
    echo "<caption>".$Caption."</caption>";
    array_multisort($array, SORT_ASC, $f, SORT_ASC, $c);
    $f = array_values(array_unique($f));
    sort($f);
    $c = array_values(array_unique($c));
    sort($c);
    $Constructor = "<thead><tr><th class='$ColorHeader'>$fila</th>";
    foreach ($c as $key => $c_aux){
        $Constructor = $Constructor . "<th class='$ColorHeader'>" . utf8_encode($c_aux) . "</th>";
    }
    if ($MostrarTotal == true){
      $Constructor = $Constructor . "<th class='$ColorHeader'>TOTAL</th></tr></thead>";
    }else{
      $Constructor = $Constructor . "</tr></thead>";
    }
    echo $Constructor;
    echo "<tbody>";
    foreach ($f as $key => $f_aux) {
        echo "<tr>";
        echo '<td>' . utf8_encode($f_aux) . "</td>";
        $array_aux = array_filter($array, function($item) use ($f_aux, $fila){
            if ($item[$fila]==$f_aux) return true; else return false;
        });
        foreach ($c as $key => $c_aux) {
            $array_aux2 = array_filter($array_aux, function($item) use ($c_aux, $columna){
                if ($item[$columna]==$c_aux) return true; else return false;
            });
            $varValor = array_sum(array_column($array_aux2, $valor));
            echo "<td align='right'>"; 
              if ($varValor == 0){
                echo "-";
              }else{
                echo number_format($varValor,0);
              }
              echo "</td>";
        }
        if ($MostrarTotal == true){
          $Tfila = "<td align='right'><b>" . number_format(array_sum(array_column($array_aux, $valor)),0) . "</b></td>";
          echo $Tfila;
        }
        echo "</tr>";
    }
    echo "</tbody>"; 
    $Constructor = "";
    echo "<tfoot>
            <tr>
              <td></td>";
    foreach ($c as $key => $c_aux){
        $Tarray = array_filter($array, function($item) use ($c_aux, $columna){
                if ($item[$columna]==$c_aux) return true; else return false;
            });
        $Constructor =  "<td align='right'><b>" . number_format(array_sum(array_column($Tarray, $valor)),0) . "</b></td>";
        echo $Constructor;
    }
    if ($MostrarTotal == true){
      $Constructor =  "<td align='right'><b>" . number_format(array_sum(array_column($array, $valor)),0) . "</b></td>";
      echo $Constructor;
    }
    echo "</tr>
        </tfoot>";
}
echo '  </table>

';
1
请将您的注释翻译成英文。 - eglease
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接