Re: stored procedure out parameter problem in php page using mysql

[Erik Aarts]

This was really helpful! My procedure, however, uses a char IN parameter.

$ivalue='mytext';
$res = $mysqli->multi_query( "CALL myproc('$ivalue',@x);SELECT @x" );

It took me quite some time to discover that you have to quote twice :-(
myproc('$ivalue' instead of myproc($ivalue

Furthermore I discovered that sometimes resultsets are "skipped". This becomes clear if you change the ++$results, and add 1 to $results in every loop.

<?php
$link = new mysqli('localhost', 'root', '','test') or die ('Error connecting to mysql: ' . mysqli_error($link));

$proc = $_GET["proc"];
$arg1 = $_GET["arg1"];
$res = $link->multi_query("call ".$proc."('$arg1',@retval,@error);SELECT @retval,@error");

if( $res ) {
  $results = 0;
  do {
    $results = $results + 1;
    if ($result = $link->store_result()) {
      printf( "<b>Result #%u</b>:<br/>", $results );
      while( $row = $result->fetch_row() ) {
        foreach( $row as $cell ) echo $cell, "&nbsp;";
        echo "<br/>";
      }
      $result->close();
      if( $link->more_results() ) echo "<br/>";
    }
  } while( $link->next_result() );
}

$link->close();
?>

Edited 6 time(s). Last edit at 07/23/2008 01:18PM by Erik Aarts.