如何生成未来30天日期数组并复用排班调度代码？

Got it, let's adjust your code to loop through the next 30 days and run the scheduling logic for each day. Here's how to do it, with the two variables you requested ($date for the actual date string, $day_of_week for the numeric day of week):

<?php
// Include database connection
include 'connection.php';
date_default_timezone_set("America/New_York");

// Number of future days to schedule
$daysToSchedule = 30;
// Start from today (use 'tomorrow' if you want to skip today)
$startTimestamp = strtotime('today');

// Loop through each of the next 30 days
for ($i = 0; $i < $daysToSchedule; $i++) {
    // Calculate timestamp for the current day in the loop
    $currentTimestamp = strtotime("+$i days", $startTimestamp);
    
    // Define your required variables
    $date = date('Y-m-d', $currentTimestamp); // Stores date in Y-m-d format
    $day_of_week = date('N', $currentTimestamp); // Stores numeric day (1=Monday, 7=Sunday)

    echo "<h3>Scheduling for $date (Day $day_of_week)</h3>";

    // Get all employees who work this day, grouped by unit
    $units = "SELECT e.user_id, e.station, e.full_name, MAX(e.level) level, es.unit, es.days, es.start_time, es.end_time 
              FROM employees e 
              LEFT JOIN employee_schedule es ON es.pid = e.user_id 
              WHERE es.days LIKE '%$day_of_week%' AND e.status = 1 
              GROUP BY es.unit";
    $units_result = $conn->query($units);

    // Roll through each unit and create schedules
    while ($row_unit = $units_result->fetch_assoc()) {
        // Map employee level to service level
        switch ($row_unit['level']) {
            case 3: $level = 1; break;
            case 4: $level = 2; break;
            case 5: $level = 3; break;
            case 8: $level = 4; break;
            default: $level = 0; // Fallback if level doesn't match
        }

        $unitid = $row_unit['unit'];
        $intime = "$date " . $row_unit['start_time']; // Combine date and start time
        $intimes = strtotime($intime);
        $endtime = strtotime('+23 hours', $intimes);
        $endtime_formatted = date('Y-m-d H:i:s', $endtime);
        $station = $row_unit['station'];
        $timenow = date('Y-m-d H:i:s');

        echo "<p>Scheduling unit $unitid from $intime to $endtime_formatted</p>";

        // Insert unit schedule
        $unitinsert = "INSERT INTO schedules (date_time, unit, level_of_service, start_time, end_time, station) 
                       VALUES ('$timenow', $unitid, $level, '$intime', '$endtime_formatted', $station)";
        
        if (mysqli_query($conn, $unitinsert)) {
            echo "Unit schedule inserted successfully. ID: " . $conn->insert_id . "<br>";
            $unitinid = $conn->insert_id;

            // Get employees for this unit and day
            $employee = "SELECT e.*, es.unit, es.days, pc.email as pemail 
                         FROM employees e 
                         LEFT JOIN employee_schedule es ON es.pid = e.user_id 
                         LEFT JOIN phone_carriers pc ON pc.id = e.phone_carrier 
                         WHERE es.days LIKE '%$day_of_week%' AND es.unit = $unitid AND e.status = 1";
            $employee_result = $conn->query($employee);

            if (mysqli_num_rows($employee_result) > 0) {
                while ($row_employee = $employee_result->fetch_assoc()) {
                    $pid = $unitinid;
                    $eid = $row_employee['user_id'];
                    $ephone = $row_employee['mobile_number'];
                    $emailphone = $row_employee['mobile_number'] . $row_employee['pemail'];

                    // Insert crew assignment
                    $unitcrewinsert = "INSERT INTO crew_assignment (date_time, pid, crew_member, phone_number, message_number, confirmed) 
                                       VALUES ('$timenow', $pid, $eid, '$ephone', '$emailphone', 0)";
                    
                    if (mysqli_query($conn, $unitcrewinsert)) {
                        echo "Crew member $eid assigned successfully<br>";
                    } else {
                        echo "ERROR: Could not assign crew member: " . mysqli_error($conn) . "<br>";
                    }
                }
            } else {
                echo "No employees found for unit $unitid on $date<br>";
            }
        } else {
            echo "ERROR: Could not insert unit schedule: " . mysqli_error($conn) . "<br>";
        }
    }
}
?>

Key Changes Made: #

• 30-Day Loop: Added a for loop that runs 30 times, incrementing the date by one day each iteration. You can adjust the start point (use strtotime('tomorrow') instead of today if you want to skip the current day).
• Required Variables: Explicitly defined $date (formatted as Y-m-d) and $day_of_week (numeric 1-7 for Monday to Sunday) for each day in the loop.
• Updated Queries: Replaced references to the old $date (which was the day number) with $day_of_week in the WHERE clauses to correctly filter employees scheduled for that day.
• Intime Calculation: Used the $date variable directly to build the start time string, eliminating reliance on the hardcoded tomorrow value.
• Cleaner Level Mapping: Switched from multiple elseif statements to a switch for more readable level-to-service mapping.

Important Security Note: #

Your original code uses unsanitized variables directly in SQL queries, which is a critical SQL injection vulnerability. For production use, you should switch to prepared statements with parameter binding. Here's a quick example of how to modify one of your queries to be safe:

// Instead of this unsafe query:
$units = "SELECT ... WHERE es.days LIKE '%$day_of_week%' ...";
$units_result = $conn->query($units);

// Use prepared statements:
$units = "SELECT ... WHERE es.days LIKE ? ...";
$stmt = $conn->prepare($units);
$likeDay = "%$day_of_week%";
$stmt->bind_param("s", $likeDay);
$stmt->execute();
$units_result = $stmt->get_result();

This will protect your database from malicious input attempts.

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