Re: Can't run a Stored Procedure with multiple SELECT queries

[Peter Brawley]

I don't know how, or how well, HeidiSQL and phpMyAdmin handle sprocs calling multiple queries. I do know that in PHP it's tricky. Here is a func that does it reliably---it assumes a live connection object $conn and you'll need to to add code to deal with data returned by the query ...

function docall( $qry, $debug=FALSE, &$errs ) {
  GLOBAL $conn;
  $res = mysqli_real_query( $conn, $qry );
  $e = mysqli_error( $conn );
  if( !empty( $e )) {
    $errs++;
    if( $debug ) echo "<b>Real_query error: $e</b> <br />";
  }
  do {
    $result = mysqli_store_result( $conn );
    $e = mysqli_error( $conn );
    if( !empty( $e )) {
      $errs++;
      if( $debug ) echo "<b>Store_result error: $e</b> <br />";
    }
    $cols = mysqli_field_count( $conn );
    if( is_bool( $result )) {
      $e = mysqli_error( $conn );
      echo ( empty( $e ) ? ( $cols ? "Query in stored procedure returned no data" : "OK" ) : $e ), "<br/>";
    }
    else {
      $flags = array( TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, "", FALSE );
      $rows = mysqli_num_rows( $result );
      echo "Retrieved $rows row", ( $rows==1 ? "" : "s" ), "...<br/>";
      // ADD CODE HERE TO DEAL WITH QUERY RESULT
      mysqli_free_result( $result );
      $e = mysqli_error( $conn );
      if( !empty( $e )) {
        $errs++;
        if( $debug ) echo "<b>Free_result error: $e</b> <br />";
      }
    }
    if( mysqli_more_results( $conn ) ) echo "<br/>";
  } while( mysqli_more_results($conn) && mysqli_next_result($conn) ); // CALL MORE BEFORE NEXT
}