PHP实现按当月日期表头从MySQL获取并排序数据

Hey there! Let's get your data aligned with that monthly date header you've got. Here's how to pull and sort the MySQL data to match perfectly:

Step 1: Reuse your date range for the query #

First, since your header uses the current month's dates (from the first day of the month up to, but not including, the first day of next month), we'll pass that exact range to MySQL so we only fetch relevant data:

$start = new DateTime('first day of this month'); 
$end = new DateTime('first day of this month + 1 month'); 
// Format dates to match MySQL's DATE format
$startDate = $start->format('Y-m-d');
$endDate = $end->format('Y-m-d');

Step 2: Update your MySQL query to sort and group correctly #

Your current query gets the minimum check-in time, but we need to tie it to specific dates, filter to the month range, and sort to match your header order. Also, a quick heads-up: the mysql_* functions are deprecated—you should switch to mysqli or PDO for security and compatibility, but I'll include both the old syntax and a modern alternative.

Legacy mysql_* version (not recommended):

$query = "SELECT DATE(CHECKTIME) AS check_date, MIN(TIME(CHECKTIME)) AS first_checkin
          FROM your_table_name  // Replace with your actual table name
          WHERE DATE(CHECKTIME) >= '$startDate' AND DATE(CHECKTIME) < '$endDate'
          GROUP BY DATE(CHECKTIME)
          ORDER BY check_date ASC";

$result = mysql_query($query);

Modern mysqli prepared statement (safe and recommended):

// Set up your mysqli connection first
$mysqli = new mysqli('your_host', 'your_user', 'your_password', 'your_database');

// Prepare the query to avoid SQL injection
$stmt = $mysqli->prepare("SELECT DATE(CHECKTIME) AS check_date, MIN(TIME(CHECKTIME)) AS first_checkin
                          FROM your_table_name
                          WHERE DATE(CHECKTIME) >= ? AND DATE(CHECKTIME) < ?
                          GROUP BY DATE(CHECKTIME)
                          ORDER BY check_date ASC");

// Bind the date range parameters
$stmt->bind_param('ss', $startDate, $endDate);
$stmt->execute();
$result = $stmt->get_result();

Step 3: Map the results to your header dates #

Store the query results in an associative array where the key is the date string—this makes it easy to pull the right data when rendering your table:

$checkinData = [];
// For mysqli result:
while ($row = $result->fetch_assoc()) {
    $checkinData[$row['check_date']] = $row['first_checkin'];
}

// For legacy mysql_* result:
// while ($row = mysql_fetch_assoc($result)) {
//     $checkinData[$row['check_date']] = $row['first_checkin'];
// }

// Now render your header and matching data
foreach($period as $day){ 
    $dateStr = $day->format('Y-m-d');
    // Use 'N/A' or a default if there's no data for that day
    $firstCheckin = isset($checkinData[$dateStr]) ? $checkinData[$dateStr] : 'N/A';
?>
    <th><?php echo $day->format('Y-M-d')."\n"; ?></th>
    <!-- Example table cell for the data -->
    <td><?php echo $firstCheckin; ?></td>
<?php } ?>

Key notes: #

• The ORDER BY check_date ASC ensures your results are in the same order as your header (from the first to last day of the month).
• Using DATE(CHECKTIME) < '$endDate' instead of <= matches your DatePeriod logic, which excludes the first day of next month.
• Always use prepared statements (like the mysqli example) to prevent SQL injection attacks—never directly insert variables into your query string!

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