根据年份(以降序排列)为基础，按年份创建新的html表 [英] Create new html tables, by year, based on years changing in descending order

问题描述

我有从查询返回的数据.它可以是一个联接，也可以只是一个简单的表，用于选择所有内容.我希望数据按日期的降序显示.并且每年在单独的表中.不必使用日期函数.只需显示两列，将年分组在单独的表中即可.

I have data coming back from a query. It could be a join or just a simple table selecting everything. I would like the data to be presented in descending order of date. And each year in a separate table. It is not necessary to use date functions. Simply show the two columns, with years grouped in separate tables.

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');

应该说的是，这是我刚刚正在研究的(第1天用户)类似的问题的转贴.但是我不希望这种操作消失.你知道我的意思.我希望.我本打算将其发布在这里，但老实说不喜欢用户名.

Truth be told, this is a re-post of a similar question I was just working on (a Day1 user). But I don't want it to just vanish if that op does. You know what I mean. I hope. I was about to post it there, but honestly didn't like the username.

推荐答案

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，该变量在处理数据时会逐年减少该变化.

Hopefully the source code comments are sufficient in-line to describe the way the years are segmented by tables. Focus on variable $curYear which picks up that change as the data is processed, descending, by year.

变量$bOneYet在循环中只有一次为false.合理的是，当发生年度更改时，第一次会写出</table>，除了.

The variable $bOneYet is false only one time through the loop. The rational is that when a year change occurs, </table> is written out, except for the first time through.

别忘了在代码顶部看到错误报告的重要性.然后转向mysqli或pdo.

Don't forget the importance of error reporting as seen at the top of the code. And steer toward mysqli or pdo.

显示测试和登台错误，从不生产.

Display errors for test and staging, never production.

这篇关于根据年份(以降序排列)为基础，按年份创建新的html表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！