MySQL Forums
Forum List  »  Stored Procedures

Re: Can't run a Stored Procedure with multiple SELECT queries
Posted by: Peter Brawley
Date: January 23, 2020 12:44AM

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
}

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Can't run a Stored Procedure with multiple SELECT queries
277
January 23, 2020 12:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.