Chesney Chesney - 2 months ago 11
PHP Question

How do I view a SQL Statement in Phalconphp?

I want to view the SQL statement that is about to be executed below :

<?php

//Deleting existing robot
$success = $connection->delete(
"robots",
"id = 101"
);

//Next SQL sentence is generated
DELETE FROM `robots` WHERE `id` = 101


How can I add some kind of listener or just plain var_dump the select query that is about to generated by the $connection->delete

Thanks

Answer

The way I settled on is to use a logger class and the event system: Phalcon Events Manager

You create a class that extends the logger adapter

<?php

namespace PhalconX\Logger\Adapter;

/**
 * Basic Array based Logging for debugging Phalcon Operations
 * @package PhalconX\Logger\Adapter
 */
class Basic extends \Phalcon\Logger\Adapter
{
    private $data = array();

    /**
     * Add a statement to the log
     * @param string $statement
     * @param null $type
     * @param array $params
     * @return $this|\Phalcon\Logger\Adapter
     */
    public function log($statement, $type=null, array $params=null)
    {
        $this->data[] = array('sql'=>$statement, 'type'=>$type, 'params'=>$params); // array('sql'=>$statement, 'type'=>$type);
        return $this;
    }

    /**
     * return the log
     * @return array
     */
    public function getLog(){
        return $this->data;
    }

    /**
     * Required function for the interface, unused
     * @param $message
     * @param $type
     * @param $time
     * @param $context
     */
    public function logInternal($message, $type, $time, $context){

    }

    /**
     * Required function for the interface, unused
     */
    public function getFormatter(){

    }

    /**
     * Required function for the interface, unused
     */
    public function close(){

    }
}

and then attach it to your database, and plumb in the events by type

$eventsManager = new \Phalcon\Events\Manager();
$logger = new \PhalconX\Logger\Adapter\Basic();
$profiler = $phalconDi->getProfiler();
//Listen all the database events

/** @var $event \Phalcon\Events\Event */
/** @var $phalconConnection \Phalcon\Db\Adapter\Pdo\Mysql */

$eventsManager->attach('db', function($event, $phalconConnection) use ($logger, $profiler) {
    if ($event->getType() == 'beforeQuery') {
        $profiler->startProfile($phalconConnection->getSQLStatement());
        $logger->log($phalconConnection->getSQLStatement(), \Phalcon\Logger::INFO, $phalconConnection->getSQLVariables());
    }
    if ($event->getType() == 'afterQuery') {
        $profiler->stopProfile();
    }
});

This presumes you have a 'db' key in your dependency injector.

My logger just stores the queries in an array so I can output them at the bottom of my page.