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

[bruce amick]

My problem is that I can only "retrieve" one row via $result->fetch_assoc() and it isn't showing the updated result for the calculated column. The same stored procedure run on the mysql client returns the desired results as a series of 1 row result sets. I'm thinking this is where my php problem lies in that I'm not using the correct mysqli function for this sort of thing.

Here's my code for the php page to return results:

$sql = "call spPaySumRpt($datestart,$dateend)";
   
   if($result = $cxn->query($sql))
   {
      echo '<table border=1px>';
      while($row = $result->fetch_assoc())
      {
         echo '<tr>' .
              '<td>' .$row['shiftTypeCode_val'] . '</td>' .
              '<td>' .$row['productionId_val'] . '</td>' .
              '<td>' .$row['sw_personId_val'] . '</td>' .
              '<td>' .$row['empName_val'] . '</td>' .
              '<td>' .$row['numShifts'] . '</td>' .
              '</tr>';         
      }
      echo '</table>';
   }

Here's the stored procedure "spPaySumRpt()":

CREATE PROCEDURE `spPaySumRpt`(
                     IN in_dateStart DATE,
                     IN in_dateEnd  DATE
                  )
    READS SQL DATA
BEGIN
      DECLARE shiftTypeCode_val CHAR(10);
   DECLARE sw_personId_val INT(11);
   DECLARE productionId_val INT(11);
   DECLARE paychexId_val INT(11);
   DECLARE empName_val VARCHAR(80);
   
      DECLARE numShifts INT(11);
   DECLARE shiftPay DECIMAL(8,2);
   DECLARE shiftComm DECIMAL(8,2);
   
   
      DECLARE no_more_rows BOOLEAN;
   DECLARE loop_count INT DEFAULT 0;
   DECLARE num_rows INT DEFAULT 0;
   
DECLARE paysum_cur CURSOR FOR
      SELECT DISTINCT
            shiftTypeCode,
            shiftworked.personId,
            productionId,
            paychexId,
            CONCAT(lastName, ', ', firstName) as empName
      FROM shiftworked, vwEmployeePerson, performance
      WHERE shiftworked.personId=vwEmployeePerson.personId
      AND shiftworked.performanceId = performance.performanceId 
      ORDER BY productionId, empName;
   
   DECLARE CONTINUE HANDLER FOR NOT FOUND
      SET no_more_rows = TRUE;
      
   
   
      
      OPEN paysum_cur;
   SELECT FOUND_ROWS() into num_rows;
   
   the_loop: LOOP
   
   FETCH paysum_cur
      INTO shiftTypeCode_val,
           sw_personId_val,
           productionId_val,
           paychexId_val,
           empName_val;
      IF no_more_rows THEN
        CLOSE paysum_cur;
        LEAVE the_loop;
    END IF;
    
    CALL spNumShifts(shiftTypeCode_val,
                     sw_personId_val,
                     productionId_val,
                     in_dateStart,
                     in_dateEnd,
                     numShifts
                     );
    SELECT  shiftTypeCode_val,
            productionId_val,
            sw_personId_val,
            empName_val,
            numShifts;
    
    SET loop_count = loop_count + 1;
    
    END LOOP the_loop;
    
    --select num_rows, loop_count;
END

and here's the results I get from the mysql client:

mysql> call spPaySumRpt('2010-10-5','2010-10-10');
+-------------------+------------------+-----------------+-------------+-----------+
| shiftTypeCode_val | productionId_val | sw_personId_val | empName_val | numShifts |
+-------------------+------------------+-----------------+-------------+-----------+
| 3B27              |                2 |               4 | Knox, Jill  |         2 |
+-------------------+------------------+-----------------+-------------+-----------+
1 row in set (0.01 sec)

+-------------------+------------------+-----------------+---------------+-----------+
| shiftTypeCode_val | productionId_val | sw_personId_val | empName_val   | numShifts |
+-------------------+------------------+-----------------+---------------+-----------+
| 3B27              |                2 |               7 | Pulido, Lacie |         1 |
+-------------------+------------------+-----------------+---------------+-----------+
1 row in set (0.02 sec)

+-------------------+------------------+-----------------+---------------+-----------+
| shiftTypeCode_val | productionId_val | sw_personId_val | empName_val   | numShifts |
+-------------------+------------------+-----------------+---------------+-----------+
| 3B27              |                2 |               2 | Ricker, Sarah |         6 |
+-------------------+------------------+-----------------+---------------+-----------+
1 row in set (0.04 sec)

+-------------------+------------------+-----------------+-------------------+-----------+
| shiftTypeCode_val | productionId_val | sw_personId_val | empName_val       | numShifts |
+-------------------+------------------+-----------------+-------------------+-----------+
| 3B27              |                2 |               3 | Schlussel, Farin  |         3 |
+-------------------+------------------+-----------------+-------------------+-----------+
1 row in set (0.05 sec)

+-------------------+------------------+-----------------+------------------+-----------+
| shiftTypeCode_val | productionId_val | sw_personId_val | empName_val      | numShifts |
+-------------------+------------------+-----------------+------------------+-----------+
| 3B27              |                2 |               5 | Simowitz, Rachel |         3 |
+-------------------+------------------+-----------------+------------------+-----------+
1 row in set (0.06 sec)

+-------------------+------------------+-----------------+--------------+-----------+
| shiftTypeCode_val | productionId_val | sw_personId_val | empName_val  | numShifts |
+-------------------+------------------+-----------------+--------------+-----------+
| 3B27              |                2 |               6 | Smith, Darci |         1 |
+-------------------+------------------+-----------------+--------------+-----------+
1 row in set (0.08 sec)

Query OK, 0 rows affected (0.09 sec)

And finally, the result I get from the testsp3.php page:

3B27 2 4 Knox, Jill

(note the absence of the numShifts data).



Thanks in advance for any advice, guidance or sympathy.



Edited 1 time(s). Last edit at 12/06/2010 08:44AM by bruce amick.