从HTML表中的存储过程MySql PHP获取结果

Question

I have stored procedure in database:

DELIMITER $$

USE `billing`$$

DROP PROCEDURE IF EXISTS `Pivot`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Pivot`(
    IN tbl_name VARCHAR(99),       -- table name (or db.tbl)
    IN base_cols VARCHAR(99),      -- column(s) on the left, separated by commas
    IN pivot_col VARCHAR(64),      -- name of column to put across the top
    IN tally_col VARCHAR(64),      -- name of column to SUM up
    IN where_clause VARCHAR(99),   -- empty string or "WHERE ..."
    IN order_by VARCHAR(99)        -- empty string or "ORDER BY ..."; usually the base_cols
    )
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    -- Find the distinct values
    -- Build the SUM()s
    SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ',
                    ' FROM ', tbl_name, ' ', where_clause, ' ORDER BY 1');
    -- select @subq;
    SET @cc1 = "CONCAT('SUM(IF(&p = ', &v, ', &t, 0)) AS ', &v)";
    SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
    SET @cc3 = REPLACE(@cc2, '&t', tally_col);
    -- select @cc2, @cc3;
    SET @qval = CONCAT("'\"', val, '\"'");
    -- select @qval;
    SET @cc4 = REPLACE(@cc3, '&v', @qval);
    -- select @cc4;
    SET SESSION group_concat_max_len = 10000;   -- just in case
    SET @stmt = CONCAT(
            'SELECT  GROUP_CONCAT(', @cc4, ' SEPARATOR ",
")  INTO @sums',
            ' FROM ( ', @subq, ' ) AS top');
     SELECT @stmt;
    PREPARE _sql FROM @stmt;
    EXECUTE _sql;                      -- Intermediate step: build SQL for columns
    DEALLOCATE PREPARE _sql;
    -- Construct the query and perform it
    SET @stmt2 = CONCAT(
            'SELECT ',
                base_cols, ',
',
                @sums,
                ',
 SUM(', tally_col, ') AS Total'
            '
 FROM ', tbl_name, ' ',
            where_clause,
            ' GROUP BY ', base_cols,
            '
 WITH ROLLUP',
            '
', order_by
        );
    SELECT @stmt2;                    -- The statement that generates the result
    PREPARE _sql FROM @stmt2;
    EXECUTE _sql;                     -- The resulting pivot table ouput
    DEALLOCATE PREPARE _sql;
    -- For debugging / tweaking, SELECT the various @variables after CALLing.
END$$

DELIMITER ; 

When i pass a SQL statement i want to capture the final result in rows with column headers and pass it to the HTML Table output on PHP.

CALL Pivot('production', 'product_name', 'market', 'forcast_qty', \"WHERE production_date = '2015-12-31'\", '');

So far when i code the following procedure to get the output i am getting a array but not the end result of the stored procedure.

if(isset($_POST['planned_forecast']))
{
if(isset($_POST['production_date'])){ $date_prod = $_POST['production_date']; } 
$stmt = $DB_con->prepare("CALL Pivot('production', 'product_name', 'market', 'forcast_qty', \"WHERE production_date = '2015-12-31'\", '');");
$stmt->execute();
while ($row  = $stmt->fetchAll(PDO::FETCH_ASSOC))
{
print_r($row);
}
echo '
<thead>';
   echo '
</thead>
';
echo '
<tbody>
   ';
   echo '
   <tr>
      ';
      echo '
      <td></td>
      ';
      echo '
   </tr>
   ';
   echo '
</tbody>
';
echo '</table>';
echo '<input hidden="true" name="date_prod" type="text" value="'.$date_prod.'"/>';
echo '<button type="submit" class="btn btn-default waves-effect waves-light" name="btn-editforcast" id="btn-editforcast">Update</button>';
}

Result:

Array ( [0] => Array ( [@stmt] => SELECT GROUP_CONCAT(CONCAT('SUM(IF(market = ', '"', val, '"', ', forcast_qty, 0)) AS ', '"', val, '"') SEPARATOR ", ") INTO @sums FROM ( SELECT DISTINCT market AS val FROM production WHERE production_date = '2015-12-31' ORDER BY 1 ) AS top ) )

Answer 1

I think you can simplify the stored procedure and use something like this:

DELIMITER $$

USE `billing`$$

DROP PROCEDURE IF EXISTS `Pivot`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Pivot`(
    IN tbl_name VARCHAR(99),       -- table name (or db.tbl)
    IN base_cols VARCHAR(99),      -- column(s) on the left, separated by commas
    IN pivot_col VARCHAR(64),      -- name of column to put across the top
    IN tally_col VARCHAR(64),      -- name of column to SUM up
    IN where_clause VARCHAR(99),   -- empty string or "WHERE ..."
    IN order_by VARCHAR(99)        -- empty string or "ORDER BY ..."; usually the base_cols
    )
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN

    -- Prepare the "pivot" part of the query
    SET @stmt = CONCAT(

        "SELECT GROUP_CONCAT(\" SUM(\" ",
        "                    \"   CASE WHEN ", pivot_col, "='\", ", pivot_col, ", \"'\"  ",
        "                    \"         THEN ", tally_col, "\"  ",
        "                    \"         ELSE 0\"  ",
        "                    \"    END\"  ",
        "                    \") AS '\", ", pivot_col, ", \"'
\") as stmt FROM (select distinct ", pivot_col, " FROM ", tbl_name, ") A INTO @sum_clause"    
    );


    PREPARE _sql FROM @stmt;
    EXECUTE _sql;                     
    DEALLOCATE PREPARE _sql;

        -- Prepare and execute the query itself
    SET @stmt = CONCAT(
        "SELECT ", base_cols, ", ", 
        @sum_clause,
        ", SUM(", tally_col, ") as 'Total qty'"
        " FROM ", tbl_name, " 
" ,
        where_clause, "
",
        " GROUP BY ", base_cols
        );

    PREPARE _sql FROM @stmt;
    EXECUTE _sql;                     
    DEALLOCATE PREPARE _sql;

END$$

DELIMITER ;  

I can't guarantee that it works for all cases, but it works for the parameters that you are using in your example, and produces the same results as the ones in the original question.