关于MySQL统计今日、昨日、本月、上月及本年ticket_id数量结果异常的技术问询

Let's break down the issues in your code and get those stats working correctly—there are a few straightforward bugs causing the 0 result for today's tickets, plus we'll cover all the time ranges you need.

1. Critical Bugs in Today's Ticket Count #

First, your SQL and PHP code have two obvious errors:

• SQL Logic Error: Your condition DATE(ticket_created_at) = 'ticket_created_at' is comparing the date-formatted timestamp to the string literal 'ticket_created_at'—this will never match any real dates. You need to compare against today's date using CURDATE().
• Typo in PHP SQL: You wrote SSELECT instead of SELECT—this invalid syntax will cause the query to fail silently (unless you add error handling).

Fixed Today's Count Code #

Add error handling to catch future issues, and correct the SQL:

// Get total tickets today with error handling
$sql_total_tickets_today = mysqli_query($mysqli, "SELECT COUNT(`ticket_id`) AS total_tickets_today FROM `tickets` WHERE DATE(`ticket_created_at`) = CURDATE();");
if (!$sql_total_tickets_today) {
    die("Today's ticket query failed: " . mysqli_error($mysqli));
}
$row = mysqli_fetch_array($sql_total_tickets_today);
$total_tickets_today = $row['total_tickets_today'] ?? 0; // Fallback to 0 if no results

2. Implementing the Other Time Ranges #

Here are the correct SQL queries for each remaining time period, paired with PHP code to fetch the results:

Yesterday's Tickets #

$sql_total_tickets_yesterday = mysqli_query($mysqli, "SELECT COUNT(`ticket_id`) AS total_tickets_yesterday FROM `tickets` WHERE DATE(`ticket_created_at`) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);");
$row_yesterday = mysqli_fetch_array($sql_total_tickets_yesterday);
$total_tickets_yesterday = $row_yesterday['total_tickets_yesterday'] ?? 0;

This Month's Tickets #

Matches all tickets from the current calendar month:

$sql_total_tickets_month = mysqli_query($mysqli, "SELECT COUNT(`ticket_id`) AS total_tickets_month FROM `tickets` WHERE YEAR(`ticket_created_at`) = YEAR(CURDATE()) AND MONTH(`ticket_created_at`) = MONTH(CURDATE());");
$row_month = mysqli_fetch_array($sql_total_tickets_month);
$total_tickets_month = $row_month['total_tickets_month'] ?? 0;

Last Month's Tickets #

Avoids edge cases (like December → January) by comparing year and month together:

$sql_total_tickets_lastmonth = mysqli_query($mysqli, "SELECT COUNT(`ticket_id`) AS total_tickets_lastmonth FROM `tickets` WHERE DATE_FORMAT(`ticket_created_at`, '%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m');");
$row_lastmonth = mysqli_fetch_array($sql_total_tickets_lastmonth);
$total_tickets_lastmonth = $row_lastmonth['total_tickets_lastmonth'] ?? 0;

This Year's Tickets #

Matches all tickets from the current calendar year:

$sql_total_tickets_year = mysqli_query($mysqli, "SELECT COUNT(`ticket_id`) AS total_tickets_year FROM `tickets` WHERE YEAR(`ticket_created_at`) = YEAR(CURDATE());");
$row_year = mysqli_fetch_array($sql_total_tickets_year);
$total_tickets_year = $row_year['total_tickets_year'] ?? 0;

3. Performance & Reliability Tips #

• Add an Index: If your tickets table is large, add an index on ticket_created_at to speed up these date-based queries:

  ALTER TABLE `tickets` ADD INDEX idx_ticket_created_at (`ticket_created_at`);
  

• Consistent Error Handling: Keep the mysqli_error checks in place—they'll save you time debugging if a query fails for any reason.
• Null Coalescing: The ?? 0 ensures you don't get an "undefined index" error if there are no tickets in a given time period.

Your frontend HTML code looks fine—once you fix the PHP/SQL, the numbers should display correctly.

内容的提问来源于stack exchange，提问作者afs