stored procedure with cursor only returning one row in PHP -- works in MySQL client
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.