How to run multiple SQL queries at the same time

Question

I have two tables, one 'comment' and the other 'pendingcomment'. I want when I copy the data of the 'pendingcomment' in the 'comment' table then that data should be deleted from the 'pendingcomment' table.

This is my code.

 <?php
include '../conn.php';

$id = $_GET['id'];

    
// sql to Insert and  delete a record
$sql = "INSERT INTO comment (blogid, name, email, subject, message, date) SELECT blogid, name, email, subject, message, date FROM pendingcomment WHERE id= $id"; 
$sql .= "DELETE FROM pendingcomment WHERE id=$id";

if (mysqli_multi_query($conn, $sql)) {
    // mysqli_close($conn);
    header('Location: ../pendingcomments.php'); //redirect to the pending page
    exit;
}
 else {
    echo "Error deleting record ";
}


?>

Result: Error deleting record

Answer 1

NEVER USE mysqli_multi_query()!!!

This function is extremely unsafe and causes a lot more problems than it solves. In fact, it doesn't solve any problems, it just creates more. You can't run queries at the same time from PHP! It is impossible to do so without threading or parallelization. If you need something like this then you can check out Swoole or ReactPHP but it's probably not needed in your case.

When executing queries you need to execute them one after another using prepared statements. This is how it should be done properly:

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli('localhost', 'user', 'password', 'test');
$conn->set_charset('utf8mb4'); // always set the charset

$id = $_GET['id'];

// begin atomic transaction
$stmt = $conn->begin_transaction();

// prepare statement for insert
$stmt = $conn->prepare('INSERT INTO comment (blogid, name, email, subject, message, date) SELECT blogid, name, email, subject, message, date FROM pendingcomment WHERE id= ?');
$stmt->bind_param('s', $id);
$stmt->execute();

// prepare statement for delete
$stmt = $conn->prepare('DELETE FROM pendingcomment WHERE id=?');
$stmt->bind_param('s', $id);
$stmt->execute();

// commit transaction
$conn->commit();

header('Location: ../pendingcomments.php'); //redirect to the pending page
exit;

The transaction ensures atomicity of the operations as long as your DB engine is InnoDB or a similar transaction engine. MyISAM is not. Remember to enable mysqli error reporting or it won't work.

Answer 2

You are executing two queries, which means you need to denote the end of the first, add a semi-colon ; to the end of your first query to achieve this. Try below, I've added it to both:

$sql = "INSERT INTO comment (blogid, name, email, subject, message, date) SELECT blogid, name, email, subject, message, date FROM pendingcomment WHERE id= $id;"; 
$sql .= "DELETE FROM pendingcomment WHERE id=$id;";

This question is worth the read, Is it bad to omit semicolon in MySQL queries? which explains the semi-colon usage in more depth

I would also suggest that running these two queries together might cause you some issues, since in the case that your insert fails, you would have still deleted the pendingcomment table record.