使用php PDO执行mysql pivotable语句

Question

I have Mysql prepare statement work in command line. how get I get it to work with PHP PDO prepare statement?

set @sql = null;
SET group_concat_max_len=15000;

select group_concat(distinct concat('sum(if(date=''',date_format(date,'%Y-%m-%d'),''',total,null)) as ', date_format(date,'dd%Y%m%d'))) into @sql from sales where date_format(date,'%m%y')='0615';

set @sql = concat('select ',@sql,'  from sales');

PREPARE stmt FROM @sql;
EXECUTE stmt;

Answer 1

You can do the group concatenation in PHP instead of SQL.

$pdo->query("SET group_concat_max_len = 15000");
$stmt1 = $pdo->prepare("select distinct concat('sum(if(date=''',date_format(date,'%Y-%m-%d'),''',total,null)) as ', date_format(date,'dd%Y%m%d')) as col from sales where date_format(date,'%m%y')= :mmyy";
$stmt1->execute(":mmyy" => '0615');
$cols = $stmt1->fetchAll(PDO::FETCH_COLUMN, 0);
$cols_string = implode(',' $cols);
$sql = "SELECT $col_string FROM sales";
$stmt2 = $pdo->prepare($sql);
$stmt2->execute();