如何在PhalconPHP中运行RAW SQL查询

$sql = "
                SET @col = NULL;
                SET @sql = NULL;

                Select
                  Group_Concat(Distinct
                    Concat(
                      'SUM(CASE WHEN tbl.sdate = ''',colname,''' THEN tbl.result ELSE NULL END) AS ''',''''
                    )
                  ) Into @col
                From (
                    select concat(month(i.invdate),'.',year(i.invdate)) as colname
                    from invoices as i
                    where i.invtype = 1 and i.pid = 5
                    order by i.invdate  
                ) As collst;

                SET @sql = CONCAT('SELECT tbl.wrkname,',@col,' 
                                        FROM (
                                            Select wl.wgname As wrkname,Concat(Month(i.invdate),''.'',Year(i.invdate)) as sdate,Sum(id.qty * id.price) As result
                                            From invoices As i
                                                Join invoicedetails As id
                                                    On i.pchid = id.pchid
                                                Join workgroups As w
                                                    On i.wid = w.wid
                                                Join workgrouplist As wl
                                                    On w.wglid = wl.wglid
                                            Where i.invtype = ',1,' And i.pid =',5,' Group By i.pid,sdate
                                            Order By i.invdate,wrkname
                                        ) AS tbl
                                        GROUP BY tbl.wrkname');

                PREPARE stmt FROM @sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
                "

但结果是“SQLSTATE [HY000]：一般错误”.

Phalcon版本：1.2.4

Profiler日志是：

SQL Statement: SET @col = NULL; SET @sql = NULL; Select Group_Concat(Distinct Concat( 'SUM(CASE WHEN tbl.sdate = ''','''' ) ) Into @col From ( select concat(month(i.invdate),year(i.invdate)) as colname from invoices as i where i.invtype = 1 and i.pid = 5 order by i.invdate   ) As collst; SET @sql = CONCAT('SELECT tbl.wrkname,' FROM ( Select wl.wgname As wrkname,Sum(id.qty * id.price) As result From invoices As i Join invoicedetails As id On i.pchid = id.pchid Join workgroups As w On i.wid = w.wid Join workgrouplist As wl On w.wglid = wl.wglid Where i.invtype = ',sdate Order By i.invdate,wrkname ) AS tbl GROUP BY tbl.wrkname'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 
Start Time: 1388927869.2788
Final Time: 1388927869.2808
Total Elapsed Time: 0.0019619464874268

问题解决了：我创建了存储过程并插入了所有查询.
接下来我在php脚本中调用存储过程.

$sql = "CALL GetReportByProjectBetweenDates(1,5)";
$result = $this->db->query($sql);

解决方法

在不使用模型或元数据的情况下运行原始SQL的一种简单方法是使用基本适配器,在本例中我将使用PDO：

$connection = new PhalconDbAdapterPdoMysql(array(
              'host' => 'localhost','username' => 'user','password' => 'password','dbname' => 'optional'
        ));

        $connection->connect();

        $sql = 'select * from database.table';

        $result_set = $connection->query($sql);
        $result_set->setFetchMode(PhalconDb::FETCH_ASSOC);
        $result_set = $result_set->fetchAll($result_set);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!