If you use mysqli's query method to get the result, you will get an error: Commands out of sync; you can't run this command now sss

Stored procedure:

CREATE PROCEDURE test1()
begin
    drop table if exists tb1;
    create table tb1
    (
        val int not null
    )engine = innoDB;
    insert into tb1(val) values(1),(2),(3);
    select * from tb1;
end

<?php
$mysqli = new mysqli("localhost", "root", "sbqcel", "test");

if (mysqli_connect_errno()) 
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$result = null;
$mysqli->autocommit(FALSE);
if(!($result = $mysqli->query( "call test1();")))
{
    echo mysqli_error($link);
    $mysqli->rollback();
}
$mysqli->commit();

print 'Result1:';

while ($row = $result->fetch_row()) 
{
        printf ("%s <br />", $row[0]);
}
$result->close();
mysqli_free_result($result);

echo 'result2:<br />';
if ($result2 = $mysqli->query("select val from tb1;")) 
{
    while ($row = $result2->fetch_row()) 
    {
        printf ("%s <br />", $row[0]);
    }
    $result2->close();
}
else
{
    echo $mysqli->error;
}
mysqli_free_result($result2);

mysqli_close($link);
?> 

After executing the above code, the above error will appear. The message indicates that MYSQL database considers this wrong command execution order. The reason is that after the execution of MYSQL's stored procedure, in addition to returning the actual result set, it also returns the transition state of the stored procedure execution. The above code only processes the first result set, and the second result set is not released.
When a stored procedure returns a resultset, MySQL returns at least two resultsets: first for the SELECT CALL inside the stored procedure. 2ndfor the call of the stored procedure itself (2nd usually is only an OK or ERR packet).

To solve this problem, you need to use mysqli's multi query method to traverse all result sets and release them. The code is as follows:

<?php
$mysqli = new mysqli("localhost", "root", "sbqcel", "test");

if (mysqli_connect_errno()) 
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
echo 'result1:<br />';
$mysqli->autocommit(FALSE);
/* ---------- Here's the point-------- */
if ($mysqli->multi_query("call test1();")) 
{
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->close();
        }
    	if(!$this->conn->more_results()){
	    	break;
    	}
    } while ($mysqli->next_result());
}
/* ---------- Here's the point-------- */
$mysqli->commit();
echo "<br />";
echo "result2:<br />";
if ($result2 = $mysqli->query("select val from tb1;")) 
{
    while ($row = $result2->fetch_row()) {
        printf ("%s <br />", $row[0]);
    }
    $result2->close();
}
else
{
    echo $mysqli->error;
}
$mysqli->close();
?> 

Extract the key points into a method, as follows:

function multi_query($sql){
	$data = Array();
	$this->conn->autocommit(FALSE);
	if ($this->conn->multi_query($sql)) 
	{
		do {
			if ($result = $this->conn->store_result()) {
				while ($row = $result->fetch_assoc()) {
					$o = Array();
					foreach($row as $k => $v){
						if(is_string($k)) $o[$k]=$v;
					}
					$data[] = $o;
				}
				$result->free();
			}
			if(!$this->conn->more_results()){
				break;
			}
		} while ($this->conn->next_result());
	}
	$this->conn->commit();
	//$this->conn->close();
	return $data;
}

In this way, you can call this method when you want to use the stored procedure

It can be seen from another article of the author that there will be a relatively complete process:

MySql error1337 Variable or condition declaration after cursor or handler declaration

In this way, PHP and MySql are available

Reference resources: Commands out of sync; you can't run this command now