phpmysqlmysqlimysqli-multi-querymulti-query

How to execute multiple MySql query at same time?


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

Solution

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