MySQL Stored Procedure Empty in PHP works in PHPMyAdmin

Question

I have the following stored procedure. It works fine in phpMyAdmin, but when I try to run it using PHP PDO library I get no errors, just an empty array.

CREATE DEFINER=`root`@`localhost` 
PROCEDURE `get_gauge_values`(IN `maxdate`      TIMESTAMP,
                                    IN `dashboard_id` INT)
begin
  DECLARE finished BOOLEAN;

  DECLARE line_timestamp TIMESTAMP;

  DECLARE line_tagid INT;

  DECLARE line_name VARCHAR(50);

  DECLARE line_value VARCHAR(50);

  DECLARE cid CURSOR FOR
    SELECT Max(hd.timestamp),
           hd.tag_id
    FROM   dashboard_to_dashboard_lines ddl
           JOIN dashboard_lines dl
             ON dl.line_id = ddl.dashboard_line_id
           JOIN historical_data hd
             ON hd.tag_id = dl.gauge_tag_id
    WHERE  hd.timestamp <= maxdate
           AND ( dashboard_id = 0
                  OR ddl.dashboard_id = dashboard_id )
    GROUP  BY 2;

  DECLARE CONTINUE handler
  FOR NOT found
    SET finished=TRUE;

  SET finished=FALSE;

  DROP TABLE IF EXISTS gauge_values_temp;

  CREATE TABLE gauge_values_temp
    (
       name  VARCHAR(255),
       value VARCHAR(50)
    );

  open cid;

  START_LOOP:
LOOP
    FETCH cid INTO line_timestamp, line_tagid;
    IF finished <> false THEN
      LEAVE start_loop;
    ELSE
      INSERT INTO gauge_values_temp
                  (name,
                   value)
      SELECT ol.name,
             hd.value
      FROM   dashboard_lines dl
             JOIN operation_lines ol
               ON ol.line_id = dl.line_id
             JOIN historical_data hd
               ON hd.tag_id = dl.gauge_tag_id
      WHERE  dl.gauge_tag_id = line_tagid
             AND hd.timestamp = line_timestamp;
    end IF;
  end LOOP;

  close cid;

  SELECT *
  FROM   gauge_values_temp;
end

The code that I use in PHP for trying to pull data back is the following:

try {
    $sql = "CALL get_gauge_values('2015-12-28 09:00:00','1')";
    $q = $link->query($sql);
    $q->setFetchMode(PDO::FETCH_ASSOC);
} catch (PDOException $pe) {
    die("Error occurred:" . $pe->getMessage());
}    
while ($r = $q->fetch()) {
    echo 'Row returned<br />';
}
exit;

I'm confused as to why it returns the result set in phpMyAdmin but running it with the PHP code below in a browser returns 0 rows. All I get when I print_r($q) is Array( )...

It looks to me like you need to be using `nextRowset()` since you have multiple `SELECT` statements in the SP. http://php.net/manual/en/pdostatement.nextrowset.php, though to debug, you should use `print_r($q->fetch())` or `print_r($r)`, not use `print_r($q)`. Doing that may reveal a result from the first SELECT statement in your SP, from which you may then build a loop to read all of them. — Michael Berkowski, Dec 31 '15 at 21:22
Possible duplicate of [Looping Over Result Sets in MySQL](http://stackoverflow.com/questions/1745165/looping-over-result-sets-in-mysql) — Norbert, Dec 31 '15 at 21:32

MySQL Stored Procedure Empty in PHP works in PHPMyAdmin

0 Answers0