We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Events

What is the difference between "db:beforeQuery" and "db:afterQuery" ?

Hi, I want to write sql-logs. In my compay, only DBAs have the privilege of the db servers. And you know, Phalcon uses PDO for db-layer. PDO accepts "?" and ":value" for later-binding. But we want to get the statements after bindParams().

Here I test with db:beforeQuery and db:afterQuery: 

try {
    // event manager                                                                                 
    $evtManager = new \Phalcon\Events\Manager();
    $logger = new \Phalcon\Logger\Adapter\File($configs->database->$nodeName->logger . date("Ymd"));
    $evtManager->attach('db', function($event, $connection) use ($logger) {
        if ($event->getType() == 'afterQuery') {
            $logger->log($connection->getRealSQLStatement(), \Phalcon\Logger::INFO);
        }
    });
    $connection->setEventsManager($evtManager);
} catch(\Exception $e) {
    error_log("Db.php: event attach error");
}

But they are no differences.

beforeQuery:

UPDATE `city` SET `cn` = ?, `cpy` = ?, `prn` = ?, `prpy` = ?, `pren` = ?, `lid` = ?, `n` = ?, `c` = ?, `pn` = ?, `pc` = ?, `rn` = ?, `rc` = ?, `en` = ?, `py` = ?, `py2` = ?, `a` = ?, `apy` = ?, `apy2` = ?, `aen` = ?, `ren` = ?, `rpy` = ?, `rpy2` = ?, `ra` = ?, `rapy` = ?, `raen` = ?, `cen` = ?, `is` = ? WHERE `id` = ?

afterQuery：

UPDATE `city` SET `cn` = ?, `cpy` = ?, `prn` = ?, `prpy` = ?, `pren` = ?, `lid` = ?, `n` = ?, `c` = ?, `pn` = ?, `pc` = ?, `rn` = ?, `rc` = ?, `en` = ?, `py` = ?, `py2` = ?, `a` = ?, `apy` = ?, `apy2` = ?, `aen` = ?, `ren` = ?, `rpy` = ?, `rpy2` = ?, `ra` = ?, `rapy` = ?, `raen` = ?, `cen` = ?, `is` = ? WHERE `id` = ?

What is the problem? Something wrong with me?

2


98.9k
Accepted
answer

Hi, beforeQuery is executed before send the SQL statement to the database server, and afterQuery is executed after the statement is sent to the server. So you can use both events to benchmark if a query is slow or it has an acceptable speed.

afterQuery doesn't transform the query replacing the placeholders.

try {

    // event manager 
    $evtManager = new \Phalcon\Events\Manager();

    $logger = new \Phalcon\Logger\Adapter\File($configs->database->$nodeName->logger . date("Ymd"));

    $evtManager->attach('db', function($event, $connection) use ($logger) {
        if ($event->getType() == 'afterQuery') {
            $variables = $connection->getSqlVariables();
            if (count($variables)) {
                $logger->log($connection->getSQLStatement() . ' ' . join(', ', $variables), \Phalcon\Logger::INFO);
            } else {
                $logger->log($connection->getSQLStatement(), \Phalcon\Logger::INFO);
            }
        }
    });

    $connection->setEventsManager($evtManager);
} catch(\Exception $e) {
    error_log("Db.php: event attach error");
}


9.4k

@Phalcon thank u very much.