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.

Multi Query for inserting then deleting data

2 Answers2