How to run RAW SQL query in PhalconPHP

Question

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; " ``` But result was "SQLSTATE[HY000]: General error". Phalcon version: 1.2.4 Profiler log is:…

Accepted Answer

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

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);
```

How to run RAW SQL query in PhalconPHP

Solution

Problem