Multi Query for inserting then deleting data

Question

I am struggling to understand how multi queries work.

I want to insert data from one table into another, then delete the data from the original table. How can I do this? Is the method below viable?

<?php

$con =  mysqli_connect('localhost:3308','root','');

if(!$con)
{
    echo 'Not Connected To Server';
}

if(!mysqli_select_db($con, 'Database'))
{
    echo 'Database Not Selected';
}

$jobNumber = $_POST['jobNumberInsert'];
$siteName = $_POST['siteNameInsert'];
$phoneNumber = $_POST['phoneNoInsert'];
$firstName = $_POST['firstNameInsert'];
$lastName = $_POST['lastNameInsert'];
$streetAddress = $_POST['streetAddressInsert'];
$linetwoAddress = $_POST['linetwoAddressInsert'];
$city = $_POST['cityInsert'];
$county = $_POST['countyInsert'];
$postcode = $_POST['postcodeInsert'];
$serviceInfo = $_POST['serviceInfoInsert'];
$jobDate = $_POST['jobDateInsert'];
$priority_value = $_POST['priorityInsert'];

$sql = "INSERT INTO table2(jobNumber,siteName,phoneNumber,firstName,lastName,streetAddress,linetwoAddress,city,county,postcode,serviceInfo,jobDate,priority) 
        VALUES ('$jobNumber','$siteName','$phoneNumber','$firstName','$lastName','$streetAddress','$linetwoAddress','$city','$county','$postcode','$serviceInfo','$jobDate','$priority_value')";

$sql .= "DELETE FROM table1 WHERE jobNumber= $jobNumber";

if(!mysqli_multi_query($con,$sql))
{
    echo 'Not Inserted or Deleted';
}
else
{
    echo 'Inserted and Deleted';
}

header("refresh:2 url=index.php");
?>

Currently upon executing the code nothing happens. When the statements are executed individually, then they work.

It may be better to execute them as separate statements (using prepared statements) and put them in a transaction. — Nigel Ren, Feb 19 '20 at 16:32

score 3 · Answer 1 · answered Feb 19 '20 at 22:11

Warning: You are wide open to SQL Injections and should use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input! Even when your queries are executed only by trusted users, you are still in risk of corrupting your data. Escaping is not enough!

Do not use mysqli_multi_query()!

You only have 2 queries, which are separate anyway. There's no need to send them together to MySQL.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$con = new mysqli('localhost:3308','root','', 'Database');
$con->set_charset('utf8mb4');

$stmt = $con->prepare('INSERT INTO table2(jobNumber,siteName,phoneNumber,firstName,lastName,streetAddress,linetwoAddress,city,county,postcode,serviceInfo,jobDate,priority) 
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)');
$stmt->bind_param('sssssssssssss', $_POST['jobNumberInsert'], 
    $_POST['siteNameInsert'],
    $_POST['phoneNoInsert'],
    $_POST['firstNameInsert']
    ...
);
$stmt->execute();

$stmtDel = $con->prepare('DELETE FROM table1 WHERE jobNumber=?');
$stmt->bind_param('s', $_POST['jobNumberInsert']);
$stmt->execute();

    header("refresh:2 url=index.php");
?>

I also feel like you should wrap this in a transaction. If the delete fails, then it's likely you would like the insert to be rolled back too.

score 0 · Accepted Answer · answered Feb 19 '20 at 16:38

There is no ; (semicolon) after first statement.

$sql = "INSERT INTO table2(jobNumber,siteName,phoneNumber,firstName,lastName,streetAddress,linetwoAddress,city,county,postcode,serviceInfo,jobDate,priority) VALUES ('$jobNumber','$siteName','$phoneNumber','$firstName','$lastName','$streetAddress','$linetwoAddress','$city','$county','$postcode','$serviceInfo','$jobDate','$priority_value');";

So, it's trying to execute as single statement instead of separate statement.

score 0 · Answer 3 · answered Nov 20 '22 at 04:49

While this method is viable in that it is possible that it worked (at least once you put in the missing semi-colon), you aren't checking if it works.

if(!mysqli_multi_query($con,$sql))
{
    echo 'Not Inserted or Deleted';
}
else
{
    echo 'Inserted and Deleted';
}

This is incorrect. That's not what that result is telling you.

if (mysqli_multi_query($con, $sql)) {
    echo 'Inserted.  May or may not delete -- too late to stop it now.';
} else {
    echo 'Insert failed.';
}

This better describes what your code is doing. Because mysqli_multi_query returns after the first statement is run and true or false only reflects that statement. By the time the echo is performed, it may or may not have run the delete. And if it ran, it may or may not have deleted. You don't know. In order to know that it completed, you have to check each result.

Note: the current mysqli_multi_query documentation claims that MySQL only runs the subsequent queries if next_result is called. However, that's not the behavior that I see. Instead, it runs all the queries and stores the results to pass back when next_result is called. Regardless though, the initial return only gives a result from the first query. To get subsequent results, you must call next_result.

Also, if you want to run any more queries on this connection, you must call next_result until all the existing results have been fetched. So you aren't saving any processing by doing both queries in one multi_query. These problems are part of why people are telling you not to use mysqli_multi_query. Further, it doesn't allow parameter binding.

If you want to check if the insert succeeded before doing the delete, you would be better off running two queries and only running the second if the first succeeds. E.g. with prepared queries as in this answer:

if ($stmt->execute() === false) {
    echo 'Insert failed, not deleting.';
} else {
    // do the insert here
}

If you want to do the two atomically, you would use a transaction:

$conn->autocommit(false);
$conn->begin_transaction();

// now prepare and bind the insert

if ($stmt->execute() === false) {
    echo 'Insert failed, not deleting.';
    $conn->rollback();
} else {
    // now prepare and bind the delete

    if ($delete->execute() === false) {
        echo 'Delete failed.  Rolling back insert.';
        $conn->rollback();
    } else {
        // both succeeded
        $conn->commit();
    }
}

Then either both succeed or both fail. Note that this only works with engines that support transactions, like InnoDB. I.e. not with MyISAM.

Multi Query for inserting then deleting data

3 Answers3