Jump to content

Recommended Posts

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 ) )

 

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.