How to display/use a Stored Procedure on my cfm page?
I created the following stored procedure to summarize counts by week:
CREATE DEFINER=`user`@`%` PROCEDURE `TT_by_Week`()
BEGIN
SET SESSION group_concat_max_len = 1000000;
DROP TABLE IF EXISTS tmp_results;
CREATE TEMPORARY TABLE tmp_results AS
SELECT td_type, FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7)) AS ttDate, ttCategory, ttNum, Count(ttNum) tickets
FROM v_closed_tickets
WHERE extract(year from ttDate) = '2018' AND ttCategory IN ('TT')
GROUP BY td_type, ttDate
ORDER BY td_type, ttDate ASC;
SELECT CONCAT('
SELECT td_type, ',tickets_by_dates,'
FROM tmp_results
GROUP BY td_type WITH ROLLUP
'
)
INTO @query
FROM
(
SELECT GROUP_CONCAT(CONCAT('IFNULL(COUNT(CASE WHEN ttDate=''',actual_date,''' THEN tickets END), ''-'') AS "',col_name,'"')) tickets_by_dates
FROM (
SELECT actual_date, DATE_FORMAT(actual_date,'%m/%d') AS col_name
FROM (SELECT DISTINCT ttDate AS actual_date FROM tmp_results) AS dates
) dates_with_col_names
) result;
PREPARE statement FROM @query;
EXECUTE statement;
DEALLOCATE PREPARE statement;
END
When I call that Stored Procedure using cfdump on my cfm page, I get this:
Questions:
- In general, how do you use a "stored procedure" in ColdFusion? (or reference the data?)
- Why are the values showing "Binary" and not my actual quantities?
- How do I use the correct values to create a regular html table or cfreport?
Here is my cfm page so far:
I need my cfm page to display results in a table like this:
AdChoices