Search the Community

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 ",\n") 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, ',\n', @sums, ',\n SUM(', tally_col, ') AS Total' '\n FROM ', tbl_name, ' ', where_clause, ' GROUP BY ', base_cols, '\n WITH ROLLUP', '\n', 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 ) )