MySQL Forums
Forum List  »  PHP

Re: Stored procedure
Posted by: Peter Brawley
Date: January 08, 2008 10:10AM

In the 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 the 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, which is recommended for executing stored procedures.

PB

Options: ReplyQuote


Subject
Written By
Posted
January 08, 2008 06:15AM
Re: Stored procedure
January 08, 2008 10:10AM
January 08, 2008 11:21PM
January 09, 2008 03:04PM


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.