Show SQL generated from CDbCriteria

Yii 1.1 General Discussion
1

Is there a way to display the SQL string that is generated from CDbCriteria?

It will help greatly in trying to locate a problem, especially for complex queries.

2

Step 1: Change your config file to show log messages on web pages

Step 2: set ‘enableParamLogging’ => true for your db component given in config file.

3

Logging is different from displaying. I’ve also searched for a way to display parsed queries but I haven’t found it yet.

4

HI Sadaf thanks for the suggestion. wisp is right. I don’t want logging. I want to see the actual SQL being produced.

5

If you configure a CWebLogRoute, the log will be displayed at the end of the page.

6

It will not show the actual query, the placeholders are still there…

7

There’s no way to see this - the placeholders are replaced by PDO which does not provide the final SQL.

8

Ok maybe I’ll post it as a feature request…

In Zend Framework it’s already possible like this:




$select = $db->select()->from('products');

 

$sql = $select->__toString();

echo $sql;

 

// The output is the string:

//   SELECT * FROM "products"
9

It makes no sense to create a feature request. As i tried to explain above: This is not possible by design, as PDO does not provide this information. Zend may be different as they may not build uppon PDO.

10

That’s odd, during my latest Zend project I used PDO and I was able to use this functionality

11

You can get the SQL - but you can not get the SQL with placeholders replaced, at least not if you use parameter binding.

12

hi miketee and wisp:

try this





  $cmd = Yii::app()->db->createCommand()

    ->select('username, password')

    ->from('tbl_user')

    ->where('id=:id', array(':id'=>1));


        echo $cmd->getText().'<br/>';

        //the  params which will bind to ...

        print_r($cmd->params);

:lol: if you use CActiveRecord there is no such functionality ,the only way to view the code is to use the way Mike give . you can try the extension of this page for logging . for me i use this extension Yii-debug-toolbar you can see the generated sql in the debug panel (just do some config)

13

Some time ago I tried a modified PHPPDO and was able to log the final generated SQL (just for finding out if possible).

See this posting.

From reading today it seems my post is a bit cryptic, though. Compare the modified snippets with the original PHPPDO code and it probably will become understandable.

/Tommy

14

!!!!Solved!!!!, Thanks very much.

I used that extension and i get what i need.

Kind regards.

15

Just found this post.

When I need to check the cdbcriteria generated by YII or when I need to store the criteria in db for some reason, I do it this way (assuming you have a $dataProvider in your view of course).

// To see which columns have beens selected in the request

echo $dataProvider->criteria->select;

// We display the conditions

echo $dataProvider->criteria->condition . ‘<br>’;

// We get all the keys from the dataProvider params

$x = array();

foreach($dataProvider->criteria->params as $key=>$value) {

$x[count($x)] = $key;

}

// We display the keys and their values

$count = 0;

foreach ($dataProvider->criteria->params as $item) {

echo &#036;x[&#036;count] . ' = ' .&#036;item . '&lt;br&gt;';


&#036;count++;

}

This way you see exactly what YII is doing in a search form for instance.

16

Here’s a dirty way to do it:

Intentionally misspell a table or field name so it throws an exception but is syntactically correct. You should see a 500 error in which Yii will include the offending MySQL query along with the bound parameters. If it’s an asynchronous request, you will need to have the console open (ctrl+shift+J in Chrome, and monitor the network tab).

1 Like