如何在PHP中按日期分组展示页面数据？现有方案失效求助

Hey there! Let's get that date grouping working properly step by step. I can see where the issues are in your current code and SQL, so let's break this down.

1. Fix Your SQL Query First #

Your current SQL uses GROUP BY date DESC, but that's not the right tool here. GROUP BY is for aggregating data (like counting or summing values for a group), not for sorting records so you can display them in groups.

Instead, you need to sort your data by date so all records from the same day are grouped together in the result set. Use this query instead:

SELECT * FROM `users` ORDER BY `date` DESC

This will pull all your user records and order them from newest to oldest, with all same-date records clustered together—perfect for our PHP grouping logic.

2. Correct the PHP Grouping Logic #

The main issue in your PHP code is that you're resetting $group_date to null inside the while loop. That means every iteration checks against a fresh null value, so it will print the date for every single row. Let's fix that:

<?php
// Initialize grouping date OUTSIDE the loop so it persists between iterations
$group_date = null;
while($r = mysqli_fetch_assoc($res)){
    // Extract just the YYYY-MM-DD part of the date
    $current_date = substr($r["date"], 0, 10);
    
    // Only print the date header if we've moved to a new date group
    if ($group_date !== $current_date) {
        $group_date = $current_date;
?>
    <tr>
        <!-- Use colspan to make the date span all 3 columns for cleaner layout -->
        <td colspan="3"><strong><?= $group_date ?></strong></td>
    </tr>
<?php
    }
?>
    <tr>
        <td><?php echo $r['role'];?></td>
        <td><?php echo $r['name']; ?></td>
        <td><?php echo $r['date']; ?></td>
    </tr>
<?php } ?>
</tbody>
</table>

Key Changes Explained: #

• Moved $group_date = null outside the loop so it keeps track of the last date group we displayed
• Added $current_date to store just the date portion (without time) of the current record
• Only prints the date header when we hit a new date value
• Used colspan="3" to make the date header span all three columns (matches your role/name/date columns)
• Added <strong> to make the date header stand out, like your example shows

3. How It All Works Together #

1. The SQL query sorts all records by date, so same-day entries are grouped together in the result
2. The PHP loop checks each record's date:
   • If it's a new date, it prints the date header
   • Then it always prints the record's role/name/full date
3. This gives you the exact grouped display you're looking for—one date header per unique day, followed by all records from that day.

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