如何在PhalconPHP中运行RAW SQL查询 [英] How to run RAW SQL query in PhalconPHP

问题描述

我尝试从此查询中获取结果

I try to get result from this query

$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 ''',
                      colname,''''
                    )
                  ) 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]:一般错误".

But result was "SQLSTATE[HY000]: General error".

Phalcon版本:1.2.4

Phalcon version: 1.2.4

Profiler日志为:

Profiler log is:

SQL Statement: SET @col = NULL; SET @sql = NULL; Select Group_Concat(Distinct Concat( 'SUM(CASE WHEN tbl.sdate = ''', colname, ''' THEN tbl.result ELSE NULL END) AS ''', colname,'''' ) ) 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; 
Start Time: 1388927869.2788
Final Time: 1388927869.2808
Total Elapsed Time: 0.0019619464874268

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

Problem solved: I create stored procedure and insert all query. Next i call stored procedure in php script.

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

推荐答案

我只看到您的SQL，您是否想展示如何尝试在phalcon中运行它?

All I see is your SQL, do you want to show how you are trying to run it in phalcon?

无需使用模型或元数据即可运行原始SQL的简单方法之一是使用基本适配器，在本示例中，我将使用PDO:

One of the easy ways that you can run raw SQL without using models or metadata is to use a base adapter, in this example I will use PDO:

        $connection = new Phalcon\Db\Adapter\Pdo\Mysql(array(
              'host' => 'localhost',
              'username' => 'user',
              'password' => 'password',
              'dbname' => 'optional'
        ));

        $connection->connect();

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

        $result_set = $connection->query($sql);
        $result_set->setFetchMode(Phalcon\Db::FETCH_ASSOC);
        $result_set = $result_set->fetchAll($result_set);

这篇关于如何在PhalconPHP中运行RAW SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！