当前位置：首页 > PHP

php mysql 实现排班

2026-01-30 06:04:05PHP

PHP 与 MySQL 实现排班系统

数据库设计

创建排班系统需要设计合理的数据库表结构。通常需要以下表：

员工表 (employees)：存储员工信息
班次表 (shifts)：定义班次类型和时间
排班表 (schedules)：记录具体排班信息

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100),
    department VARCHAR(100)
);

CREATE TABLE shifts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL
);

CREATE TABLE schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    shift_id INT,
    date DATE NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (shift_id) REFERENCES shifts(id)
);

基本功能实现

添加排班记录

function addSchedule($employeeId, $shiftId, $date) {
    $conn = new mysqli("localhost", "username", "password", "scheduling_db");
    $sql = "INSERT INTO schedules (employee_id, shift_id, date) VALUES (?, ?, ?)";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("iis", $employeeId, $shiftId, $date);
    $stmt->execute();
    $stmt->close();
    $conn->close();
}

查询某日排班

function getDailySchedule($date) {
    $conn = new mysqli("localhost", "username", "password", "scheduling_db");
    $sql = "SELECT e.name, s.name as shift_name, sh.start_time, sh.end_time 
            FROM schedules s
            JOIN employees e ON s.employee_id = e.id
            JOIN shifts sh ON s.shift_id = sh.id
            WHERE s.date = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $date);
    $stmt->execute();
    $result = $stmt->get_result();
    $schedule = $result->fetch_all(MYSQLI_ASSOC);
    $stmt->close();
    $conn->close();
    return $schedule;
}

高级功能实现

冲突检测 在添加排班前检查员工是否已有排班：

function hasExistingSchedule($employeeId, $date) {
    $conn = new mysqli("localhost", "username", "password", "scheduling_db");
    $sql = "SELECT COUNT(*) FROM schedules WHERE employee_id = ? AND date = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("is", $employeeId, $date);
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->close();
    $conn->close();
    return $count > 0;
}

批量排班 使用事务处理批量排班操作：

function batchAddSchedules($schedules) {
    $conn = new mysqli("localhost", "username", "password", "scheduling_db");
    $conn->begin_transaction();
    try {
        $sql = "INSERT INTO schedules (employee_id, shift_id, date) VALUES (?, ?, ?)";
        $stmt = $conn->prepare($sql);
        foreach ($schedules as $schedule) {
            $stmt->bind_param("iis", $schedule['employee_id'], $schedule['shift_id'], $schedule['date']);
            $stmt->execute();
        }
        $conn->commit();
    } catch (Exception $e) {
        $conn->rollback();
        throw $e;
    } finally {
        $stmt->close();
        $conn->close();
    }
}