Skip navigation links

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


Advanced Search

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
stored procedure out parameter problem in php page using mysql Majid Khan 02/28/2008 11:32PM
Re: stored procedure out parameter problem in php page using mysql Haroon Shah 02/29/2008 12:25AM
Re: stored procedure out parameter problem in php page using mysql Peter Brawley 02/29/2008 01:05PM
Re: stored procedure out parameter problem in php page using mysql Erik Aarts 07/20/2008 07:53AM


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.