php do while won't work with next->rowset

Question

Hi i have my wamp server on my computer PHP 5.4.12 Apache 2.4.4 MYSQL 5.6.12

And my server PHP 5.5.3 Apache 2.4.6 MYSQL 5.5.37

and when i'm doing this function on my server i have this error : SQLSTATE[HY000]: General error but in my localhost i don't have any error

function getinformationpublic($nocate)
{
    try
    {
        $public = array();
        global $Cnn;
        $reponse = $Cnn->prepare("CALL GetInfoPublicCible(:nocategorie)");
        $reponse->bindParam('nocategorie',$nocate,PDO::PARAM_INT);
        $reponse->execute();
        do {
            $rowset = $reponse->fetchAll(PDO::FETCH_ASSOC);
            $public[] = $rowset; 

        } while ($reponse->nextRowset());

        $reponse->closeCursor();
        return $public;
    }
    catch (PDOException $erreur)
    {
        $msg[]=$erreur->getMessage();
        $_SESSION["message"]["d"]=$msg;
    }

}

but when i'm doing this one on my server i don't have error

function getinformationpublic($nocate)
{
    try
    {
        $public = array();
        global $Cnn;
        $reponse = $Cnn->prepare("CALL GetInfoPublicCible(:nocategorie)");
        $reponse->bindParam('nocategorie',$nocate,PDO::PARAM_INT);
        $reponse->execute();
            $rowset = $reponse->fetchAll(PDO::FETCH_ASSOC);
            $public[] = $rowset; 
                        $reponse->nextRowset();
                        $rowset = $reponse->fetchAll(PDO::FETCH_ASSOC);
            $public[] = $rowset; 
                        $reponse->nextRowset();
                        $rowset = $reponse->fetchAll(PDO::FETCH_ASSOC);
            $public[] = $rowset; 
                $reponse->nextRowset();
                         $rowset = $reponse->fetchAll(PDO::FETCH_ASSOC);
            $public[] = $rowset; 
        $reponse->closeCursor();
        return $public;
    }
    catch (PDOException $erreur)
    {
        $msg[]=$erreur->getMessage();
        $_SESSION["message"]["d"]=$msg;
    }

}

add `echo $i++;` before yours `} while (....)` you will see then WHEN exactly it fails. — Seti, May 07 '14 at 16:10
it fails at the last one. When i do echo $i++ on my page i have 123 — Pierre-Luc Bolduc, May 07 '14 at 16:49

score 5 · Accepted Answer · answered Oct 15 '14 at 11:54

I had same problem with PDO::nextRowset(), as it returns true even there is no more rowsets available, therefore when calling fetchAll(), it raises exception HY000. (tested on PHP 5.5.12 windows, Mysql 5.5.17 linux)

A workaround for this problem is to check number of columns with method PDO::columnCount() before fetching rowset. If it is non-zero, you have a valid rowset, and thus you could call PDO::fetchAll().

Even if PDO::nextRowset() reports true, columnCount() will report number of columns before moving to next rowset.

Example:

while ($objQuery->columnCount()) {
    $tab[] = $objQuery->fetchAll(\PDO::FETCH_ASSOC);
    $objQuery->nextRowset();
}

`as it returns true even there is no more rowsets available` that's not true. read [here](http://php.net/manual/en/pdostatement.nextrowset.php). the problem is he's using a do...while. The do part will be executed before verifying if the while is true, hence on the last iteration, even if the while is false, the do part will be executed. — Félix Adriyel Gagnon-Grenier, Jul 22 '15 at 14:39

Félix Adriyel Gagnon-Grenier · Answer 2 · 2015-07-22T14:51:22.697

The problem is you are using the do...while form, which will execute the code in the do part before verifying the while condition. This means on the last iteration, even if nextRowset() just returned false, the do part will be executed one last time.

just remove the do part, and put everything in a while. It is not true that nextRowset returns true even if there is no rowset. have a read on do...while and on nextRowset()

$public[] = $reponse->fetchAll(PDO::FETCH_ASSOC);
// so that the first rowset gets into your array
while ($reponse->nextRowset()) {
    $public[] = $reponse->fetchAll(PDO::FETCH_ASSOC);
}

also you need a double dot with your bindParam, like the param it is binded to

$reponse->bindParam(':nocategorie',$nocate,PDO::PARAM_INT);

Seti · Answer 3 · 2014-05-07T17:01:41.600

0

while($rowset = $reponse->fetchAll(PDO::FETCH_ASSOC))
{
    $public[] = $rowset;
    $reponse->nextRowset();
}

This should work. A bit of change. We check if there is a rowset then goes to next one. And then if it is we do that once more. Try it now.

edited May 07 '14 at 17:01

answered May 07 '14 at 16:51

Seti

2,169
16
26

This only fixed problem when you dont have answer for your query. Now we will fix the last check that fails. – Seti May 07 '14 at 16:55
it works when all my fetchall return value. I saw that on php.net : PDO::nextRowset() does not appear to be currently supported by the Firebird PDO driver. Unfortunate. Maybe its that? – Pierre-Luc Bolduc May 07 '14 at 17:15
Well i know that my stored proc return 4 arrays i will do `for($i =0; $i<4; $i++){ $rowset = $reponse>fetchall(PDO::FETCH_ASSOC); $public[] = $rowset; $reponse->nextRoeset(); }` – Pierre-Luc Bolduc May 07 '14 at 17:19
That may be, but if it returns all the data - just at the end it make some problems (but still - retrieves all data) then you my `try {} catch () {}` it... Just put some HUGE visible `//TODO: comment` so you will remember about that in the future. it may be the firebird - i know it just worked for mysql (tested both codes thats why i posted the one that worked for me). – Seti May 07 '14 at 17:29

php do while won't work with next->rowset

3 Answers3

Linked