MySQL Forums :: Stored Procedures :: stored procedure with cursor only returning one row in PHP -- works in MySQL client


Advanced Search

stored procedure with cursor only returning one row in PHP -- works in MySQL client
Posted by: bruce amick ()
Date: December 06, 2010 08:17AM

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.

Options: ReplyQuote


Subject Views Written By Posted
stored procedure with cursor only returning one row in PHP -- works in MySQL client 7274 bruce amick 12/06/2010 08:17AM
Re: stored procedure with cursor only returning one row in PHP -- works in MySQL client 1622 Peter Brawley 12/07/2010 10:47AM
Re: stored procedure with cursor only returning one row in PHP -- works in MySQL client 1417 bruce amick 12/09/2010 06:20PM
Re: stored procedure with cursor only returning one row in PHP -- works in MySQL client 1047 Peter Brawley 12/09/2010 10:47PM
Re: stored procedure with cursor only returning one row in PHP -- works in MySQL client 1872 bruce amick 12/10/2010 10:08AM
Re: stored procedure with cursor only returning one row in PHP -- works in MySQL client 991 Peter Brawley 12/10/2010 10:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.