使用mysqli\u multi\u查询插入和更新不起作用

jtw3ybtb  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(558)

有没有可能 insert 以及 update 在一个 mysqli_multi_query . 数据库更新了,但值正在显示 0 .

$sql = "SELECT
        e.*,
        c.fee AS coursefee
        FROM enrollment e
            LEFT JOIN courses c ON e.course_id = c.course_id
        WHERE c.course_id = '$courseId'
        ";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_array($result);
$courseFee = $row['coursefee']; // 3500

// fees page
$sql_f = "SELECT * FROM fees WHERE studentid = '$studentId'";
$result_f = mysqli_query($con, $sql_f);

if(mysqli_num_rows($result_f) == 0){
    $query = "INSERT INTO enrollment (student_id, course_id, batch_id, joiningdate) VALUES ('$studentId', '$courseId', '$batchId', '$joiningDate');";
    $query .= "INSERT INTO fees (studentid, coursefee) VALUES ('$studentId','$courseFee')";
}

// The code below is not working.
else{
    $query = "INSERT INTO enrollment (student_id, course_id, batch_id, joiningdate) VALUES ('$studentId', '$courseId', '$batchId', '$joiningDate');";
    $query .= "UPDATE fees SET coursefee='$courseFee' WHERE studentid = '$studentId'";
}

mysqli_multi_query($con, $query);

if(mysqli_affected_rows($con) > 0){
    $_SESSION['success'] = "New Enrollment is Successfully Created!";
    header ("Location: ../../enrollments.php");
}
else {
    echo "Records are NOT Added. Please try again<br />";
    echo mysqli_error ($con);
}
2sbarzqh

2sbarzqh1#

这是通过使用 ON DUPLICATE KEY UPDATE ```
$query =
"INSERT INTO enrollment (student_id, course_id, batch_id, joiningdate) VALUES ('$studentId', '$courseId', '$batchId', '$joiningDate');";
$query .=
"INSERT INTO fees(fee_id, coursefee) VALUES (LAST_INSERT_ID($feeId), $courseFeeFromFeesTable)
ON DUPLICATE KEY UPDATE coursefee = $total";

相关问题