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