Multiple delimiters in PHP gives error, Phpmyadmin does not

Question

I created a QUERY with multiple delimiters to run only once and perform UPDATE in different tables, but gives syntax error when run in PHP, but in Phpmyadmin works normally.

I already checked the permissions of users and you have the proper ones.

UPDATE 
  `fin_dry1`
SET
  `dr_status` = 'P' 
WHERE 
  `dr_token` = '894613';
UPDATE 
  `fin_dry2`
SET
  `dr_status` = 'P' 
WHERE 
  `dr_token` = '894613';
UPDATE 
  `fin_dry3`
SET
  `dr_status` = 'P' 
WHERE 
  `dr_token` = '894613';

You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'UPDATE fin_dry2 SET `dr_sta.

I discovered the use of multi_query, so I changed the mysqli_query() for mysqli_multi_query() and another error appears:

Commands out of Sync; you can’t run this command now.

About the Mysql server version

Server: Localhost via UNIX socket.

Server type: Percona Server.

Server connection: SSL is not being used Documentation.

Server version: 5.6.41-84.1 - Percona Server (GPL), Release 84.1, Revision b308619.

Version of the protocol: 10.

Answer 1

Using Mysqli, you can use the command mysqli_multi_query(). You must pass queries separated by semicolons. Here is an example:

<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql = "SELECT Lastname FROM Persons ORDER BY LastName;";
$sql .= "SELECT Country FROM Customers";

// Execute multi query
if (mysqli_multi_query($con,$sql))
{
  do
    {
    // Store first result set
    if ($result=mysqli_store_result($con)) {
      // Fetch one and one row
      while ($row=mysqli_fetch_row($result))
        {
        printf("%s\n",$row[0]);
        }
      // Free result set
      mysqli_free_result($result);
      }
    }
  while (mysqli_next_result($con));
}

mysqli_close($con);
?>

Here is more information:

https://www.php.net/manual/en/mysqli.multi-query.php

Answer 2

I solved the problem with the help of the top response made by @Bins, with some changes due to error messages with "Strict Standards" that the partner’s solution presented, follows the solution in procedural.

I changed the code I used mysqli_query() for mysqli_multi_query().

$sql = "UPDATE 
  `fin_dry1`
SET
  `dr_status` = 'P' 
WHERE 
  `dr_token` = '894613';
UPDATE 
  `fin_dry2`
SET
  `dr_status` = 'P' 
WHERE 
  `dr_token` = '894613';
UPDATE 
  `fin_dry3`
SET
  `dr_status` = 'P' 
WHERE 
  `dr_token` = '894613';"


if(mysqli_multi_query($dbconn,$sql)){
    do{
        $cumulative_rows+=mysqli_affected_rows($dbconn);
    } while(mysqli_more_results($dbconn) && mysqli_next_result($dbconn));
}
if (mysqli_error($dbconn)) {
    printf("Erro! Houve uma exceção.");
    exit();
}