php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Doc Bug #47471 MySQLi, SP, loop count and the amount of result sets
Submitted: 2009-02-22 10:14 UTC Modified: 2011-03-21 17:57 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:2 of 2 (100.0%)
Same Version:1 (50.0%)
Same OS:1 (50.0%)
From: pcdinh at gmail dot com Assigned: abedford (profile)
Status: Closed Package: MySQLi related
PHP Version: 5.3.0beta1 OS: Windows XP SP3
Private report: No CVE-ID: None
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
46 + 25 = ?
Subscribe to this entry?

 
 [2009-02-22 10:14 UTC] pcdinh at gmail dot com
Description:
------------
When executing a stored procedure that returns multiple result sets, MySQLi driver always does an additional loop after all the result set are returned.

The last loop always returns a FALSE without any error, which indicates as if there is a query that does not return a result set in the SP. PHP Manual does not mention about the additional special loop.

SP:

CREATE PROCEDURE foo()
BEGIN
  SELECT 'foo' FROM DUAL;
  SELECT 'bar', 'bar2' FROM DUAL;
END $$

Reproduce code:
---------------
<?php

$mysqli = new mysqli("localhost", "root", "123456", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$sql = "
DELIMITER $$
DROP PROCEDURE IF EXISTS foo $$
CREATE PROCEDURE foo()
BEGIN
  SELECT 'foo' FROM DUAL;
  SELECT 'bar', 'bar2' FROM DUAL;
END $$";

$rsCount   = 0;
$loopCount = 0;
$hasNext   = null;

if ($mysqli->real_query("CALL foo()"))
{
    do
    {
        $loopCount++;

        if ($hasNext === null)
        {
            echo 'Loop begins before any check. The first result set is available. '."\n";
        }
        else
        {
            echo "Check if there is any result set: ".(int)$hasNext." at loop ".$loopCount.". \n";
        }

        $result = $mysqli->store_result();

        if ($mysqli->errno > 0)
        {
            echo "Error: ".$mysqli->error." <<<<<<<<";
            continue;
        }

        if (is_object($result))
        {
            $rsCount++;
            // $result->free_result();
            echo 'Result set at loop '.$loopCount.'.'."\n";
        }
        else
        {
            // No result set returns
            echo 'No result set at loop '.$loopCount.'.'."\n";
        }

    } while ($hasNext = $mysqli->next_result()); // mysqli_next_result always returns true if an error occured.
}

echo "Total loop: $loopCount; Total result sets: $rsCount;";

/* close connection */
$mysqli->close();
?>


Expected result:
----------------
I created a stored procedure named foo() that is expected to return 2 result sets

DELIMITER $$
DROP PROCEDURE IF EXISTS foo $$
CREATE PROCEDURE foo()
BEGIN
  SELECT 'foo' FROM DUAL;
  SELECT 'bar', 'bar2' FROM DUAL;
END $$

The PHP code should print out:

Loop begins before any check. The first result set is available. 
Result set at loop 1.
Check if there is any result set: 1 at loop 2. 
Result set at loop 2.
Total loop: 2; Total result sets: 2;



Actual result:
--------------
The PHP code prints out:

Loop begins before any check. The first result set is available. 
Result set at loop 1.
Check if there is any result set: 1 at loop 2. 
Result set at loop 2.
Check if there is any result set: 1 at loop 3. 
No result set at loop 3.
Total loop: 3; Total result sets: 2;

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-02-25 13:31 UTC] johannes@php.net
The interface is a bit weird, yes.

mysqli_next_result() returns false in case the query is wrong. It will always return true in case the query was ok, independent from the number of fetched/available result sets.

To check whether there are more result sets use mysqli_more_results() or check the return value of storing it.
 [2009-02-25 18:52 UTC] pcdinh at gmail dot com
So why did MySQli iterate 3 times meanwhile there were 2 result sets? Why are there always an additional loop when all of the result sets are retrieved? According to your explanation, the additional loop should go first, not last, because the sequence of queries should go as follows:

1 - CALL foo                        : true
2 - SELECT 'foo' FROM DUAL;         : true 
3 - SELECT 'bar', 'bar2' FROM DUAL; : true
 [2010-12-25 13:43 UTC] kalle@php.net
-Type: Bug +Type: Documentation Problem
 [2011-01-06 15:32 UTC] uw@php.net
-Assigned To: +Assigned To: abedford
 [2011-01-06 15:32 UTC] uw@php.net
That's how the MySQL client server protocol works. Nothing can be changed about it. Its a server matter and reflected in the C API which in turn is the basis for mysqli. 

At some point in the manual the loop for fetching results should be shown. Always use that loop.

Tony, can you handle it?
 [2011-03-21 17:57 UTC] abedford@php.net
-Status: To be documented +Status: Closed
 [2011-03-21 17:57 UTC] abedford@php.net
There is already a suitable example showing use of mysqli_more_results() and 
mysqli_next_result(). It is linked to from both these functions, and can be found 
under mysqli_multi_query().
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC