Debug database queries with parameters

Sometimes you have to query the database directly in your module. In these situations you can use the placeholder feature of the Zend_Db_Statement component. :placeholder will be replaced by the respective value when calling fetch on a Zend_Db_Adapter_Mysqli instance with an array of replacements for example. If you need to debug the query with all parameters in it, you can use the profiler (Zend_Db_Profiler):

/* @var Varien_Db_Adapter_Mysqli $oRead*/
//$oRead = Varien_Db_Adapter_Mysqli extends Zend_Db_Adapter_Mysqli extend Zend_Db_Adapter_Abstract
$oRead = Mage::getSingleton('core/resource')->getConnection('core_read');
$oRead->getProfiler()->setEnabled(true);

$sSelect = "SELECT
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            catalog_category_entity.path,
            '/',
            2
        ),
        '/',
        -1) = :root_id AS 'result'
    FROM
      catalog_category_entity
    WHERE
      tb_id=:tb_id;";

$sErg = $oRead->fetchOne($sSelect, array(
    'root_id' => $iRootID,
    'tb_id' => $sTBId
));


if ($sErg == '1') {
    $oRead->getProfiler()->setEnabled(true);
    return true;
}
Mage::log("ROOT: $iRootID");
Mage::log("Query: {$oRead->getProfiler()->getLastQueryProfile()->getQuery()}");
Mage::log("QueryParams:");
Mage::log($this->_getWriteConnection()->getProfiler()->getLastQueryProfile()->getQueryParams());
Mage::log("Category $sTBId not exists under $iRootID!");
$oRead->getProfiler()->setEnabled(false);
return false;

To get more information about the last query itself and more stats, have a look at Zend_Db_Profiler and Zend_Db_Profiler_Query.

One thought on “Debug database queries with parameters

Leave a Reply

Your email address will not be published.

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.