Debugging Database Operations in Yii

Whenever you begin working with a database, you introduce more possible causes of errors. Thus, you must learn additional debugging strategies. When using PHP to run queries on the database, the problems you might encounter include:

• An inability to connect to the database
• A database error thrown because of a query
• The query not returning the results or having the effect that you expect
• None of the above, and yet, the output is still incorrect

On a non-framework site, you just need to watch for database errors to catch the first two types of problems. There’s a simple and standard approach for debugging the last two types:

1. Use PHP to print out the query being run.
2. Run the same query using another interface to confirm the results.
3. Debug the query until you get the results you want.

When using a framework, these same debugging techniques are a little less obvious, in part because you may not be directly touching the underlying SQL commands. Thankfully, Yii will still be quite helpful, if you know what switches to flip.

First of all, while developing your site, enable CWebLogRoute:

# protected/config/main.php "components" section
'log'=>array(
    'class'=>'CLogRouter',
    'routes'=>array(
        array(
            'class'=>'CFileLogRoute',
            'levels'=>'error, warning',
        ),
        array(
            'class'=>'CWebLogRoute',
        ),
    ),
),

This will show, in your browser, everything being done by the framework including what queries are being run.

Here, the Web log router is showing one of the queries required to fetch a page record.

But there’s one more thing you should do to make debugging SQL problems easier…

Many queries will use parameters, separating the core of the query from the specific (and often user-provided) values used by it. To see the entire query, with the parameter values in place, you must also set the CDbConnection class’s enableParamLogging attribute to true:

# protected/config/main.php "components" section
'db'=>array(
    'connectionString' =>
        'mysql:host=localhost;dbname=test',
    'emulatePrepare' => true,
    'enableParamLogging' => true,
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
),

Now you’ll be able to see the entire query in your output, including the query’s parameter values.

{WARNING} Public display of errors and detailed logging are terrible for a site’s security and performance. Both should only be used during the development of a site.