Multi-queries

from the Artful MySQL Tips List


Executing multiple queries with the mysqli interface requires a call to mysqli_multi_query(), then successive calls to
  • mysqli_store_result() to capture the next result
  • mysqli_field_count() to find if the query was written to return data columns
  • enough calls to mysqli_fetch_row() to fetch the resultset if there is one
  • mysqli_free_result() to free the resultset buffer
  • mysqli_more_results to see if there are more queries
  • mysqli_next_result() to prepare the next result.
until there are no more queries to process. Here is a simple example with redundant calls to show what is happening at each step:

$conn = mysqli_connect( "localhost", "USR", "PWD", "test" );

$qry = 
"drop table if exists a,b;
create table a(i int);
insert into a values(1),(2);
create table b select * from a where i=1;
select a.i,b.i from a natural left join b;
drop tables a,b;";

$qries = explode( ";", $qry );
for( $i=0; $i<count($qries); $i++ ) {
  $qries[$i] = trim($qries[$i]);
}
echo "<b>Processing multi-query...</b><br/>";
$result = mysqli_multi_query( $conn, $qry );
echo "<b>mysqli_multi_query</b> returned ", ($result ? "True" : "False")."<br/><hr>";

  foreach( $qries as $q ) {
    echo "<b>Query:</b> $q<br/>";

    echo "<b>mysqli_store_result returned</b> ";
    $result = mysqli_store_result( $conn );
    $cols = mysqli_field_count( $conn );
    if( is_bool( $result )) {
      $e = mysqli_error( $conn );
      echo ( empty( $e ) ? ( $result ? "True" : "False" ) : $e );
      if( $cols ) echo ". Query failed.";
      echo "<br/>";
    }
    else {
      echo "data<br/>";
      echo "<table border=1>";
      while( $row = mysqli_fetch_row( $result )) {
        echo "<tr>"; foreach( $row as $c ) echo "<td>$c</td>"; echo "</tr>";
      }
      echo "</table>";        
      mysqli_free_result( $result );
    }

    echo "<b>mysqli_more_results</b> returned ";
    if( mysqli_more_results( $conn )) echo "true<br/>";
    else echo "false<br/>";

    echo "<b>mysqli_next_result</b> says ";
    if( mysqli_next_result( $conn )) 
      echo "True<br/>";
    else
      echo (( $e = mysqli_error( $conn )) ? $e : "False" ) . "<br/>";

    echo "<hr>";
  }
}
mysqli_close( $conn );

It produces this output:

Processing multi-query...
mysqli_multi_query returned True

Query: drop table if exists a,b
mysqli_store_result returned False
mysqli_more_results returned true
mysqli_next_result returned True

Query: create table a(i int)
mysqli_store_result returned False
mysqli_more_results returned true
mysqli_next_result returned True

Query: insert into a values(1),(2)
mysqli_store_result returned False
mysqli_more_results returned true
mysqli_next_result returned True

Query: create table b select * from a where i=1
mysqli_store_result returned False
mysqli_more_results returned true
mysqli_next_result returned True

Query: select a.i,b.i from a natural left join b
mysqli_store_result returned data
1 1 2
mysqli_more_results returned true
mysqli_next_result returned True

Query: drop tables a,b
mysqli_store_result returned False
mysqli_more_results returned false
mysqli_next_result returned False

Query:
mysqli_store_result returned False
mysqli_more_results returned false
mysqli_next_result returned False


For a tutorial on multi-queries see http://etutorials.org/Server+Administration/upgrading+php+5/Chapter+3.+MySQL/3.8+Before+and+After+Making+Multiple+Queries/.

Return to the Artful MySQL Tips page