Jump to content

Search the Community

Showing results for tags 'stored-procedure'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Welcome to PHP Freaks
    • Announcements
    • Introductions
  • PHP Coding
    • PHP Coding Help
    • Regex Help
    • Third Party Scripts
    • FAQ/Code Snippet Repository
  • SQL / Database
    • MySQL Help
    • PostgreSQL
    • Microsoft SQL - MSSQL
    • Other RDBMS and SQL dialects
  • Client Side
    • HTML Help
    • CSS Help
    • Javascript Help
    • Other
  • Applications and Frameworks
    • Applications
    • Frameworks
    • Other Libraries
  • Web Server Administration
    • PHP Installation and Configuration
    • Linux
    • Apache HTTP Server
    • Microsoft IIS
    • Other Web Server Software
  • Other
    • Application Design
    • Other Programming Languages
    • Editor Help (PhpStorm, VS Code, etc)
    • Website Critique
    • Beta Test Your Stuff!
  • Freelance, Contracts, Employment, etc.
    • Services Offered
    • Job Offerings
  • General Discussion
    • PHPFreaks.com Website Feedback
    • Miscellaneous

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Age


Donation Link

Found 1 result

  1. 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 ) )
×
×
  • 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.