Select from MySQL Pivot Stored Procedure using PHP

Question

I am trying to use the MySQL pivot from this site - http://mysql.rjweb.org/doc.php/pivot - to create and select a pivot using MySQL & PHP. So far I have saved the stored procedure to MySQL and that worked fine. When I go into the MySQL console on the server and enter the query below I receive the expected data/result.

CALL Pivot('`rates-short`', '`Hotel_Name`', '`CheckIn_Date`',
           '`Discount_Percentage`', "WHERE `Num_Nights` = 1", '');

However, when I call that same SQL statement in PHP, I don't get any data. Instead I get the text below (from a PHP print_r). How do I get the pivot data?

Array ( [@stmt] => SELECT GROUP_CONCAT(CONCAT(
     'SUM(IF(`CheckIn_Date` = ', '"', val, '"', ',
            `Discount_Percentage`, 0)) AS ', '"', val, '"')
                SEPARATOR ", ") INTO @sums
    FROM ( SELECT DISTINCT `CheckIn_Date` AS val
               FROM `rates-short`
               WHERE `Num_Nights` = 1
               ORDER BY 1
         ) AS top )

I have tried many different PHP MySQL/MySQLi/stored procedure code variations and they all return the same thing. Here's what I have at the moment.

<?php
$dbhost = xxx;
$dbuser = xxx;
$dbpass = xxx;
$dbname = xxx;


try {
    $pdo = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
    $sql = "CALL Pivot('`rates-short`', '`Hotel_Name`', '`CheckIn_Date`',
          '`Discount_Percentage`', 'WHERE `Num_Nights` = 1', '')";
    $q   = $pdo->query($sql);
    $q->setFetchMode(PDO::FETCH_ASSOC);
}
catch (PDOException $e) {
    die("Error occurred:" . $e->getMessage());
}

while ($r = $q->fetch()) {
    echo count($r);
    print_r($r);
}
?>

What version of MySQL? Could you provide `SHOW CREATE TABLE \`rates-short\`;` — Rick James, Dec 17 '19 at 03:48

score 0 · Answer 1 · answered Dec 17 '19 at 04:02

Sounds like the problem is with "retrieving multiple result sets through PDO". (The problem will occur with PHP's mysqli interface, too.) Here are some references:

Select from MySQL Pivot Stored Procedure using PHP

1 Answers1