MySQL Forums
Forum List  »  PHP

Re: stored procedure out parameter problem in php page using mysql
Posted by: Peter Brawley
Date: February 29, 2008 01:05PM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: stored procedure out parameter problem in php page using mysql
February 29, 2008 01:05PM


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.