Accessing different resultsets that were created by a mysql stored procedure

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

Hi everone,
I am just learning how to connect to a mysql server using php. I am
familiar to use mysql with stored procedures and java as frontend and I
want to use the same procedures (the same database) in php, but I can
not find a solution for the following problem.

Mainly all of my stored procedures return three resultsets (RS).

1.) RS1: contains an error-code
2.) RS2: contains an error-message
3.) RS3: contains the result I am interested in

In java it is possible to first take a look at the RS1 and then either
on  RS2 or RS3 depending on the code in RS1 (procedure produced errors
or not...)

Here is an example for the idea of such a stored procedure:


CREATE DEFINER=`root`@`localhost` PROCEDURE `uspBeispiel`()

     DECLARE rResultMsg varchar(255);
     DECLARE rResultInt int;

     SET rResultMsg = 'OK';
     SET rResultInt = 1;

--  Executing some code. If the code produces errors[1] I store
--  an errormessage and an error code in rResultMsg and rResultInt.

--  [1] (Imagine you are working on a a treestructure and you want to
--  ensure that all children that are added by your procedure
--  have unique names regarding to the parentnode)

     IF(rResultInt = 1) THEN

--  IF needed I return the information I am interested in.
--  (for example the complete treestructure. Here very simple:)

         SELECT "Beispiel" AS Test;

     END IF;

     SELECT rResultMsg AS ResultMsg;
     SELECT rResultInt AS ResultInt;

END $$


My class connects to the database using the following lines in the

$this->connection = mysql_connect(MYSQL_SERVER,

if(!$this->connection) {
    throw new Exception(mysql_error());

$this->db =  mysql_select_db(MYSQL_DATABASE, $this->connection);

if(!$this->db) {
    throw new Exception(mysql_error());

and here is the code that I use to call the procedure:

function beispiel() {

    $sql = "CALL uspBeispiel()";
    $result = mysql_query($sql) or die(mysql_error());

//      And at this point I have the problem, that i only can access
//      the first resultset, that was created by my procedure.
//      That would be:
//      SELECT "Beispiel" AS Test
//      if no error occured (I have not changed rResultInt in
//      uspBeispiel())


I hope I described my problem in an understandable way. English is not
my native language.

Best regards,

Re: Accessing different resultsets that were created by a mysql stored procedure

Jan Mielke wrote:
Quoted text here. Click to load it

You apparently need to use the more recent mysqli PHP extension for this
kind of functionality (never tried it myself, but since our school's
hosting server has upgraded recently, it is worth looking into this for me):

Also, make sure that the database client-server protocol version is 4.1
or above.

There is also an extra parameter which can be passed to the
mysql_connect() function in the "client_flags" option. It might work if
you connect like this:

// PHP code:
define("CLIENT_MULTI_RESULTS", 131072);
$res = mysql_connect (

I found this in the user comments at this page on the PHP manual site:

Re: Accessing different resultsets that were created by a mysql stored procedure

Robert Hairgrove schrieb:

Quoted text here. Click to load it

Thanks a lot!

was the thing I was looking for.

Best regards,

Site Timeline