如何用PHP/SQL实现当月周报并按‘第1周、第2周’分组展示记录

Got it, let's fix this up so you're not just getting counts—you'll have full records grouped by "第1周、第2周……" for the current month. Here's a step-by-step solution:

1. Update Your SQL Query #

Your original query only fetches totals, so we need to adjust it to pull all relevant records along with a proper week number relative to the current month. This ensures we group records by their position within the month (not the calendar year's week number).

MySQL Example: #

SELECT 
    *,
    -- Calculate week number relative to the start of the month (Week 1, Week 2...)
    WEEK(dateposted) - WEEK(DATE_FORMAT(dateposted, '%Y-%m-01')) + 1 AS week_number
FROM 
    tblcomplain
WHERE 
    -- Filter for records in the current month
    dateposted >= DATE_FORMAT(NOW(), '%Y-%m-01') 
    AND dateposted < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
ORDER BY 
    week_number, dateposted; -- Sort first by week, then by date within the week

Quick Notes: #

• If you're using PostgreSQL, replace WEEK() with EXTRACT(WEEK FROM dateposted) and tweak the week calculation to match.
• The WHERE clause ensures we only pull records from the current month—no extra data from other months.

2. PHP Code to Group and Display Records #

Now we'll take the SQL results, group them by week number, and render them in a user-friendly format.

<?php
// Database connection (adjust credentials to match your setup)
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// Execute the SQL query
$sql = "SELECT 
            *,
            WEEK(dateposted) - WEEK(DATE_FORMAT(dateposted, '%Y-%m-01')) + 1 AS week_number
        FROM 
            tblcomplain
        WHERE 
            dateposted >= DATE_FORMAT(NOW(), '%Y-%m-01') 
            AND dateposted < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
        ORDER BY 
            week_number, dateposted";

$result = $conn->query($sql);

// Group records by week number
$weeklyReports = [];
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $weekNum = $row['week_number'];
        // Add the record to the corresponding week group
        if (!isset($weeklyReports[$weekNum])) {
            $weeklyReports[$weekNum] = [];
        }
        $weeklyReports[$weekNum][] = $row;
    }
}

// Close connection
$conn->close();
?>

<!-- HTML to display the reports -->
<div class="weekly-reports">
    <?php if (!empty($weeklyReports)): ?>
        <?php foreach ($weeklyReports as $weekNum => $records): ?>
            <div class="week-section">
                <h3>第<?php echo $weekNum; ?>周</h3>
                <ul>
                    <?php foreach ($records as $record): ?>
                        <li>
                            <!-- Customize these fields to match your tblcomplain columns -->
                            <strong>投诉ID:</strong> <?php echo $record['complain_id']; ?><br>
                            <strong>提交日期:</strong> <?php echo date('Y-m-d H:i:s', strtotime($record['dateposted'])); ?><br>
                            <strong>内容:</strong> <?php echo htmlspecialchars($record['complain_content']); ?>
                        </li>
                    <?php endforeach; ?>
                </ul>
            </div>
        <?php endforeach; ?>
    <?php else: ?>
        <p>当月暂无投诉记录。</p>
    <?php endif; ?>
</div>

3. Optional: Handle Empty Weeks #

If you want to display weeks even when there are no records (e.g., "第3周：无记录"), add this step before rendering the HTML:

// Get the total number of weeks in the current month
$firstDay = new DateTime(date('Y-m-01'));
$lastDay = new DateTime(date('Y-m-t'));
$totalWeeks = $lastDay->diff($firstDay)->days / 7;
$totalWeeks = ceil($totalWeeks);

// Ensure all weeks are present in $weeklyReports
for ($i = 1; $i <= $totalWeeks; $i++) {
    if (!isset($weeklyReports[$i])) {
        $weeklyReports[$i] = [];
    }
}
// Sort the weeks numerically
ksort($weeklyReports);

This way, every week of the month will show up, even if there's no data for it.

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