How to execute multiple MySql query at same time?

Question

I want to update my data, I use this but it won't update my data, the id still empty. So, how I can execute multiple SQL queries at the same time?

$mysqli = new mysqli("a", "a", "a", "a");

if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

$sql = "UPDATE laporan_gini SET id_provinsi='1' WHERE nama_item_vertical_variabel= 'INDONESIA'";
$sql = "UPDATE laporan_gini SET id_provinsi='61' WHERE nama_item_vertical_variabel= 'KALIMANTAN BARAT'";

if (!$mysqli->multi_query($sql)) {
    echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
    if ($res = $mysqli->store_result()) {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
        $res->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());

Answer 1

The best way is use sql triggers or stored procedure:
https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html

In other way you can run it in code:

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

$sql = "SELECT COUNT(*) AS _num FROM test; ";
$sql.= "INSERT INTO test(id) VALUES (1); ";
$sql.= "SELECT COUNT(*) AS _num FROM test; ";

if (!$mysqli->multi_query($sql)) {
    echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
    if ($res = $mysqli->store_result()) {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
        $res->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());
?>

Answer 2

You should never use mysqli::multi_query() to execute your SQL because this function does not support parameter binding. You must execute both queries separately using prepared statements. If you do not intend to bind any data to your SQL then you can reduce it by one line and use mysqli::query() instead.

If both queries depend on each other then you can wrap them in a transaction (assuming that your table engine supports transactions).

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("a", "a", "a", "a");
$mysqli->set_charset('utf8mb4'); // always set the charset

// start transaction
$mysqli->begin_transaction();

$sql = "UPDATE laporan_gini SET id_provinsi='1' WHERE nama_item_vertical_variabel= 'INDONESIA'";
$stmt = $mysqli->prepare($sql);
$stmt->execute();

$sql = "UPDATE laporan_gini SET id_provinsi='61' WHERE nama_item_vertical_variabel= 'KALIMANTAN BARAT'";
$stmt = $mysqli->prepare($sql);
$stmt->execute();

// commit data and end transaction
$mysqli->commit();