2015-09-11 14:16
浏览 46

使用php PDO执行mysql pivotable语句

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;

图片转代码服务由CSDN问答提供 功能建议

我在命令行中使用了Mysql prepare语句。 如何让我使用PHP PDO prepare语句?

  set @sql = null; 
SET group_concat_max_len = 15000; 
选择group_concat(distinct concat('sum)  (if(date =''',date_format(date,'%Y-%m-%d'),''',total,null))',date_format(date,'dd%Y%m%d'  )))从sales销售@sql,其中date_format(date,'%m%y')='0615'; 
set @sql = concat('select',@ sql,'来自sales'); 
 \  nPREPARE stmt FROM @sql; 
EXECUTE stmt; 
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doule0941 2015-09-11 14:30

    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);
    解决 无用
    打赏 举报
  • duanfeng7756 2015-09-11 14:23

    You don't need to use prepared statements with PDO in this case.

    The code that you pasted is the only way to get dynamically generated statements working in pure SQL.

    Since PHP is involved, you should generate that SQL in a for loop and execute it with PDO::query method.

    解决 无用
    打赏 举报

相关推荐 更多相似问题