CODEWITHSUNDEEP
phpmysqlimysqli-multi-query
Robin Narban
Robin Narban

Reputation: 29

How to run multiple SQL queries at the same time?

I have two tables, one comment and the other pendingcomment. When I copy the data of the pendingcomment in the comment table, I want that data to be deleted from the pendingcomment table.

I was thinking of using mysqli_multi_query to do it, but I don't know how to use it. Is there any way to execute multiple queries as one in PHP?

Upvotes: 2

Views: 2501

Answers (1)

Dharman
Dharman

Reputation: 33238

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();

You must use transactions to make the two statements operate as a single DB operation. A 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.

And a very important warning:

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.

Upvotes: 2

Related Questions