PHP请假管理系统：关联两表计算并更新剩余年假天数

我正在开发一款请假管理系统，管理员创建员工账号时，会在staff表中默认添加年假天数。当员工提交的请假申请获批后，需要将leave表中的leave_days_taken（已请假天数）从staff表的leave_days（年假天数）中扣除，并更新staff表的leave_days字段。目前卡在如何关联两张表、跨表计算数值并最终更新staff表的年假天数上，相关信息如下：

表结构 #

Staff表结构 #

staff_id
firstname
lastname
username
dob
doa
password
leave_days

Leave表结构 #

leaveid
staffid
date_issued
leave_days_taken
status

现有approve.php代码 #

<?php
session_start();

//Getting leaveid from /leave page
$leaveid = $_GET['leaveid'];

//DB Connection
$conn = mysqli_connect('localhost', 'root', '','leavemanagement');
//If approve button was clicked

//Update the status column
$sql = "UPDATE `leave` SET status = 'Approved' WHERE leaveid = '$leaveid'";
$query = mysqli_query($conn, $sql);

if($query == true){?>
    <script>
        alert("Leave request has been approved");
        window.location.href = 'leave.php';
    </script>
<?php
} else{ ?>
    <script>
        alert("Leave request has not been approved");
        window.location.href = 'leave.php';
    </script>
  <?php  } ?>

解决方案 #

要实现审批后扣除年假的功能，需先获取当前请假申请对应的员工ID和请假天数，再关联staff表完成更新，同时修复SQL注入风险：

修改后的approve.php代码 #

<?php
session_start();

// 校验请假申请ID有效性
if (!isset($_GET['leaveid']) || empty($_GET['leaveid'])) {
    echo "<script>alert('无效的请假申请ID'); window.location.href='leave.php';</script>";
    exit;
}

$leaveid = $_GET['leaveid'];
$conn = mysqli_connect('localhost', 'root', '', 'leavemanagement');

// 预处理查询，获取当前请假申请的员工ID和请假天数
$stmt = $conn->prepare("SELECT staffid, leave_days_taken FROM `leave` WHERE leaveid = ?");
$stmt->bind_param("i", $leaveid);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows === 0) {
    echo "<script>alert('未找到对应的请假申请'); window.location.href='leave.php';</script>";
    exit;
}

$leaveData = $result->fetch_assoc();
$staffid = $leaveData['staffid'];
$daysTaken = $leaveData['leave_days_taken'];

// 开启事务，保证两个操作原子性
$conn->begin_transaction();

try {
    // 更新请假申请状态为已批准
    $stmt = $conn->prepare("UPDATE `leave` SET status = 'Approved' WHERE leaveid = ?");
    $stmt->bind_param("i", $leaveid);
    $stmt->execute();

    // 扣除员工对应年假天数
    $stmt = $conn->prepare("UPDATE staff SET leave_days = leave_days - ? WHERE staff_id = ?");
    $stmt->bind_param("di", $daysTaken, $staffid);
    $stmt->execute();

    // 提交事务
    $conn->commit();
    echo "<script>alert('请假申请已批准，年假已更新'); window.location.href='leave.php';</script>";
} catch (Exception $e) {
    // 操作失败回滚事务
    $conn->rollback();
    echo "<script>alert('操作失败：" . $e->getMessage() . "'); window.location.href='leave.php';</script>";
}

$conn->close();
?>

关键说明 #

• 表关联逻辑：通过leave表的leaveid定位到对应申请，获取staffid和leave_days_taken，再用staffid关联staff表执行年假扣除操作。
• 事务保障：使用数据库事务确保“更新请假状态”和“扣除年假”两个操作同时成功或失败，避免数据不一致。
• 安全防护：全程使用mysqli预处理语句，避免直接拼接用户输入导致的SQL注入风险。

内容的提问来源于stack exchange，提问作者Arthur Sven Msiska