Get KoolPHP UI with 30% OFF!

[SOLVED] dynamic MYSQL query - building a pivot table

Igor
Hi,
for now i have made static pivot table.
table: 'packages'
columns: country, package, price (Sales_2016/Y_2016)
$dsMax->SelectCommand = "
	SELECT country,
	min(IF(package = 'TAB 200MG 24', round(Sales_2016/Y_2016,2), NULL)) AS TAB_200MG_24,
	max(IF(package = 'TAB 100MG 20', round(Sales_2016/Y_2016,2), NULL)) AS TAB_100MG_20,
	max(IF(package = 'TAB 100MG 20', round(Sales_2016/Y_2016,2), NULL)) AS TAB_100MG_40
         ... 
	sum(`Sales_2016`) as Sales2016,
	count(distinct(`manufacturer`)) as competitors
							FROM `molecular`
							group by country";


The number of distinct values in column "package" is >120 so static method is not working for me.
working dynamic mysql query;
phpmyadmin
DELIMITER ;;
DROP PROCEDURE IF EXISTS pivot_question;;
CREATE PROCEDURE pivot_question()
BEGIN
SELECT
  GROUP_CONCAT(
CONCAT("SUM(IF(`package`='", `package`, "',`price2016` ,0)) AS '", `package`, "'"), "\n"
  ) INTO @PivotQuery
FROM (
  SELECT DISTINCT `package` FROM `packages`
) A;
SET @query := 
  CONCAT(
    'SELECT `country`,', @PivotQuery, ' FROM `packages` GROUP BY `country`'
  );
PREPARE statement FROM @query;
EXECUTE statement;
DEALLOCATE PREPARE stmt;
END;;
DELIMITER ;
call pivot_question;;

Im strugling with how to get thiscode working in php file: .....$dsMax->SelectCommand = "???";....
Can someone help me with that?
Thanx,
Igor
Posted Jul 9, 2017 , edited Jul 22, 2017 Kool
Igor
so, the question is how to execute stored procedure i php file?
i tried with:
$dsMax = 'CALL pivot_question()';
error:
Fatal error: Call to a member function setcharset() on string in C:\xampp\htdocs\molecular\KoolPHPSuite\KoolControls\KoolGrid\koolgrid.php on line 1
update:
i change query a little bit
 SELECT DISTINCT `package` FROM `packages` where `molecule` = 'PITOFENONE'
and now i am getting only columns without data:
Posted Jul 10, 2017 , edited Jul 10, 2017 Kool
Igor
I solved this with pivottable.js library: nicolaskruchten/pivottable , and there is also: koolphp pivot :)
Posted Jul 22, 2017 , edited Jul 23, 2017 Kool