Call a stored procedure from PHP

from the Artful MySQL Tips List


The best way to call a MySQL stored procedure in PHP is via the mysqli API, which without any fussy settings supports multiple stored procedure calls, multiple resultsets, and OUT parameters. 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();

      echo "<br/>";

    }

  } while( $mysqli->more_results() && $mysqli->next_result() );

}

$mysqli->close();



You can call a stored procedure via the deprecated mysql PHP API if you set the fifth parameter to mysql_connect() to an internal magic constant CLIENT_MULTI_STATEMENTS, 65536. But you can make only one stored procedure call per connection, and there is no support for OUT parameters. 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 );



For more discussion see "Call Stored Procedure with OUT Parameter".

Return to the Artful MySQL Tips page