php - 创建新的 html 表，按年，根据年降序变化

Question

我有从查询返回的数据。它可以是一个连接或只是一个选择所有内容的简单表格。我希望数据按日期降序显示。每年在一个单独的表中。没有必要使用日期函数。只需显示两列，并将年份分组在单独的表格中。

架构

create table myTable123
(   id int auto_increment primary key,
    ayr int not null,
    otherStuff varchar(100) not null
);

insert myTable123 (ayr,otherStuff) values
(2001,'here is stuff for 2001'),
(2001,'here is stuff for 2001'),
(2002,'here is stuff for 2002'),
(2009,'here is stuff for 2009'),
(2005,'here is stuff for 2005'),
(2001,'here is stuff for 2001'),
(2001,'here is stuff for 2001'),
(2002,'here is stuff for 2002'),
(2009,'here is stuff for 2009'),
(2005,'here is stuff for 2005');

说实话，这是对类似 question 的重新发布我正在处理(第 1 天用户)。但我不希望它在该操作发生时就消失。你知道我的意思。我希望。我正要将其发布到那里，但老实说不喜欢用户名。

Answer 1

table_year_test.php

<?php
    // MYSQLI_REPORT_ALL remmed out to avoid
    //Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'No index used in query/prepared statement'
    // which is certainly the case with the demo query
    //mysqli_report(MYSQLI_REPORT_ALL);
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);  // so go with this one
    error_reporting(E_ALL); // report all PHP errors
    ini_set("display_errors", 1); 

    //echo "start<br/>";
    try {
        $mysqli= new mysqli('hostname', 'dbuser', 'password', 'dbname');
        if ($mysqli->connect_error) {
            die('Connect Error (' . $mysqli->connect_errno . ') '
                . $mysqli->connect_error);
        }
        
        // your query can be a join. No difference. Just plug yours in and tweak everywhere as necessary
        $query = "select ayr,otherStuff from myTable123 order by ayr desc";

        // The following variable is used to pick up a "year change" while processing the data to segment tables
        $curYear="^^junk^^"; // so set it to junk first, so first time in is a change
        
        $bOneYet=false; // has there been any output at all yet. I mean anything? So far, no
        if(!$result = $mysqli->query($query)){
            die('There was an error running the query [' . $mysqli->error . ']');
        }
        while ($row = $result->fetch_assoc()) { 
            if ($row['ayr']!=$curYear) {
                // the year has changed (including the first time in this while)
                if (!$bOneYet) {
                    $bOneYet=true;  // will one get in here once
                }
                else {
                    // must end previous table
                    echo "</table><p><p>";                
                }
                // regardless, we need a new table
                echo "<table border=1><tr><th>The Year</th><th>The other thing</th></tr>";
            }
            echo "<tr><td>" . $row['ayr'] . "</td><td>" . $row['otherStuff'] . "</td></tr>";
            $curYear=$row['ayr'];   // kind of important. Facilitates subsequent year table segments
        }
        echo "</table><p>";    // close up the last dangling table
        $result->free(); 

        $mysqli->close();
    } 
    catch (mysqli_sql_exception $e) { 
        throw $e; 
    } 

截图

希望源代码注释足以在线描述按表格划分年份的方式。关注变量$curYear随着数据按年降序处理，它会拾取该变化。

变量$bOneYet在循环中只有一次为假。合理的是，当发生年份变化时，</table>写出来了，除了第一次通过。

不要忘记代码顶部显示的错误报告的重要性。转向mysqli或 pdo .

显示测试和暂存错误，从不显示生产错误。