1

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

Dharman
  • 30,962
  • 25
  • 85
  • 135
Robin Narban
  • 11
  • 5
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). 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](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Apr 25 '21 at 19:55

1 Answers1

2

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135