Details

Published: Friday, 17 April 2015 13:15

Hits: 2674

You can return a single resultset from a MySQL 5 Stored Procedure, but it requires that you use the MYSQLI Object-Oriented Interface.  Using the mysql_connect and mysql_query functions do not allow you to return a resultset.  Here is a simple MySQL 5 Stored Procedure...

delimiter //
CREATE PROCEDURE MyProc()
BEGIN
  SELECT * FROM MYTABLE;
END;
//
delimiter ;

Within PHP5, you can gather the results like this...

<?

 //*-----------------------------------------------------------------*
 //* Connect to the MySQL database.                                  *
 //*-----------------------------------------------------------------*
 $oConn = new mysqli('DB_HOST','DB_USER','DB_PASS','DB_NAME');

 //*-----------------------------------------------------------------*
 //* Warn if error on connecting.                                    *
 //*-----------------------------------------------------------------*
 if (mysqli_connect_error()) {
  echo "ERROR: Problem connecting to the database via mysqli.\n";
  exit();
 }

 //*-----------------------------------------------------------------*
 //* Create a string to hold the call to the procedure MyProc.       *
 //*-----------------------------------------------------------------*
 $sQuery = "call MyProc()";

 //*-----------------------------------------------------------------*
 //* Query the database using the multi_query method which allows for*
 //* more than one resultset.  Here, MyProc only returns one.        *
 //*-----------------------------------------------------------------*
 $bResult = $oConn->multi_query($sQuery) or die(mysql_error());

 //*-----------------------------------------------------------------*
 //* If the multi_query method returns true, process the resultset.  *
 //*-----------------------------------------------------------------*
 if ($bResult) {
 
  //*---------------------------------------------------------------*
  //* Store the results in a resultset.                             *
  //*---------------------------------------------------------------*
  $oResult = $oConn->store_result();

  //*---------------------------------------------------------------*
  //* Process each row in turn.                                     *
  //*---------------------------------------------------------------*
  while ($aRow = $oResult->fetch_array(MYSQLI_ASSOC)) {
   //...process the data in $aRow...
  }

 }

 //*-----------------------------------------------------------------*
 //* Close the connection to the database.                           *
 //*-----------------------------------------------------------------*
 $oConn->close();

?>

Note that the example above does not show you how to process any additional resultsets, but you can find out more on the PHP website at http://www.php.net.  Also, I am using the fetch_array method to pull data into an associative array, but that may not be exactly what you want to do.