INSERT and UPDATE using mysqli_multi_query not working

Question

Is it possible to have insert and update in a single mysqli_multi_query. The database updates, but the value is showing 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);
}

How would `mysqli_affected_rows` know which one you are asking about? — IncredibleHat, Apr 13 '18 at 14:42
Yes it is possible: http://php.net/manual/en/mysqli.multi-query.php — Nic3500, Apr 13 '18 at 14:43
`mysqli_multi_query` with variables is potentially opening you to a horrid SQL injection.. If this were PDO I'd do the queries separately with a transaction. — chris85, Apr 13 '18 at 14:45
Possible duplicate of [MySQL get all affected rows for multiple statements in one query](https://stackoverflow.com/questions/4163468/mysql-get-all-affected-rows-for-multiple-statements-in-one-query) — Nic3500, Apr 13 '18 at 14:46
I m not sure if I got the right context here , but maybe you can use: `INSERT ON DUPLICATE KEY UPDATE ` https://mariadb.com/kb/en/library/insert-on-duplicate-key-update/ — Attila Naghi, Apr 13 '18 at 15:02

score -1 · Accepted Answer · answered Apr 14 '18 at 15:53

-1

This was solved using 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";

answered Apr 14 '18 at 15:53

Dexter

7,911
4
41
40

1

Glad it worked , but please use `{}` see: https://stackoverflow.com/a/2596838/3084005 . Good luck – Attila Naghi Apr 16 '18 at 07:19

INSERT and UPDATE using mysqli_multi_query not working

1 Answers1