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:

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

Solution 1

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

Solution 2

To run a raw sql on a controller:

$this->db->query("SELECT * FROM robots WHERE id > 0");

To run a raw sql on a model:

$this->getDi()->getShared('db')->query("SELECT * FROM robots WHERE id > 0");

Solution 3

From the documentation:

If Raw SQL queries are common in your application a generic method could be added to your model:

<?php

use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

class Robots extends Phalcon\Mvc\Model
{
    public static function findByRawSql($conditions, $params=null)
    {
        // A raw SQL statement
        $sql = "SELECT * FROM robots WHERE $conditions";

        // Base model
        $robot = new Robots();

        // Execute the query
        return new Resultset(null, $robot, $robot->getReadConnection()->query($sql, $params));
    }
}  

Solution 4

basend on the answer of david.duncan, working edition :P (missing commas added + array print)

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

    $con->connect();
    $sql = 'select * from aDatabase.aTable';
    $result = $con->query($sql);
    $result->setFetchMode(Phalcon\Db::FETCH_ASSOC);
    $result = $result->fetchAll($result);

    foreach($result as $one)
    {
        echo print_r($one) . '<br>';
    }

View more solutions