title: How to run RAW SQL query in PhalconPHP
body: <p>I try to get result from this query</p>
  
  <pre><code>$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;
                  "
  </code></pre>
  
  <p>But result was "SQLSTATE[HY000]: General error".</p>
  
  <p>Phalcon version: 1.2.4</p>
  
  <p>Profiler log is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Problem solved: I create stored procedure and insert all query. 
  Next i call stored procedure in php script.</p>
  
  <pre><code>$sql = "CALL GetReportByProjectBetweenDates(1, 5)";
  $result = $this-&gt;db-&gt;query($sql); 
  </code></pre>