MySQL Forums :: PHP :: stored procedure out parameter problem in php page using mysql

Majid,

To call an sproc under mysql PHP API:

Assume sproc myproc(IN i int, IN j int):

$conn = mysql_connect( "HOST", "USR", "PWD", 0, 65536 );
mysql_select_db( "DBNAME" );
$ivalue=1;
$jvalue=2;
$res = mysql_query( "call myproc($ivalue,$jvalue)" ) or die( mysql_error() );
while( $row = mysql_fetch_row( $res )) {
  foreach( $row as $cell ) echo $cell, " ";
  echo "<br/>";
}
close( $conn );

With the mysql interface, you have to close the connection and open a new one to execute more queries after calling an sproc. Note also that the mysql API cannot fetch OUT parameter values.

With mysqli PHP API:

Assume sproc myproc( IN i int, OUT j int ):

$mysqli = new mysqli(  "HOST", "USR", "PWD", "DBNAME" );
$ivalue=1;
$res = $mysqli->multi_query( "CALL myproc($ivalue,@x);SELECT @x" );
if( $res ) {
  $results = 0;
  do {
    if ($result = $mysqli->store_result()) {
      printf( "<b>Result #%u</b>:<br/>", ++$results );
      while( $row = $result->fetch_row() ) {
        foreach( $row as $cell ) echo $cell, "&nbsp;";
      }
      $result->close();
      if( $mysqli->more_results() ) echo "<br/>";
    }
  } while( $mysqli->next_result() );
}
$mysqli->close();

There is no need to close and re-open the connection after calling an sproc from the mysqli interface. The mysqli interface is recommended for executing stored procedures.

PB
http://www.artfulsoftware.com