Re: stored procedure with cursor only returning one row in PHP -- works in MySQL client

[bruce amick]

Peter: thank you for your reply. I had suspected multi_query might be the clue to the solution but had no experience with that set of functions. After reviewing the tips on your website and also taking some tips from php.net documentation, I've advanced the problem a little further but still not solved. No matter what I do, I can't seem to "bring back" the "out" variable from the inner sproc, even though I run a select statement after the inner sproc:

    
    CALL spNumShifts(shiftTypeCode_val,
                     sw_personId_val,
                     productionId_val,
                     in_dateStart,
                     in_dateEnd,
                     @numShifts
                     );
  
 SELECT @numShifts into numShifts;   
 SELECT  shiftTypeCode_val,
            productionId_val,
            sw_personId_val,
            empName_val,
            numShifts;

The php I use to "extract" the results is :

$sql = "call spPaySumRpt($datestart,$dateend)";  
   echo "<table border='1px' cellpadding='3px'>";
   if($cxn->multi_query($sql))
   {
      do
      {
         if($result = $cxn->use_result())
         {
            while($row = $result->fetch_row())
            {
               echo "<tr>";
               foreach($row as $cell)
               {
                  echo "<td>" . $cell . "</td>";
               }
            }
            $result->close();
         }
         if($cxn->more_results())
         {
            echo "</tr><tr>";
         }
      } while($cxn->next_result());
   }

and the results I get are...

3B27 2 4 Knox, Jill
3B27 2 7 Pulido, Lacie
3B27 2 2 Ricker, Sarah
3B27 2 3 Schlussel, Farin
3B27 2 5 Simowitz, Rachel
3B27 2 6 Smith, Darci


Still missing the numShifts value from each result set!!

I hope you have some more suggestions!

thanks,
Bruce Amick