Using a CDbDataReader (sql query result) in a zii widget (such as CGridView or CListView) | Wiki

Using a CDbDataReader (sql query result) in a zii widget (such as CGridView or CListView) Follow @yiiframework

If in your application you have to proceed to a complex SQL query that is not associated to a model, you will use CDbDataReader like it is described in the Yii documentation. An example could be

$oDbConnection = Yii::app()->db; // Getting database connection (config/main.php has to set up database
// Here you will use your complex sql query using a string or other yii ways to create your query
$oCommand = $oDbConnection->createCommand('SELECT * FROM my_table WHERE myAttr = :myValue');
// Bind the parameter
$oCommand->bindParam(':myValue', $myValueThatCameFromPostOrAnywereElse, PDO::PARAM_STR);
 
$oCDbDataReader = $oCommand->queryAll(); // Run query and get all results in a CDbDataReader

Then if you use $oCDbDataReader in your view as a dataprovider, it won't work (because this object is not a dataprovider) :

// Will not work because $oCDbDataReader is not a valid data provider
$this->widget('zii.widgets.CListView', array(
  'dataProvider'=>$oCDbDataReader,
  'itemView'=>'_view',
));

To use zii widgets, you will have to convert your CDbDataReader into a valid data provider. You could do so using a CArrayDataProvider :

$dataProvider=new CArrayDataProvider($oSearchResultsArray, array(
  'id'=>'myDataProvider',
  'sort'=>array(
     'attributes'=>array(
       'id', 'title' // Attributes has to be row name of my sql query result
        ),
  ),
  'pagination'=>array(
         'pageSize'=>50,
          ),
));

Finally in your view you will only have to use $dataprovider instead of $oCDbDataReader.

Last but not least, in your item view, CArrayDataProvider doesn't work like yii model data provider. You won't be able to use

$data->myAttribute.

You will have to use

$data['myAttribute']

I hope it could help.

Total 1 comment

#4803

speedyfw at 2011/08/18 02:47am

CSqlDataProvider is the better way!

You can use the DataProvider in any Zii-Widget.

$count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM tbl_user')->queryScalar();
$sql='SELECT * FROM tbl_user';
$dataProvider=new CSqlDataProvider($sql, array(
    'totalItemCount'=>$count,
    'sort'=>array(
        'attributes'=>array(
             'id', 'username', 'email',
        ),
    ),
    'pagination'=>array(
        'pageSize'=>10,
    ),
));

http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider

Please login to leave your comment.

Using a CDbDataReader (sql query result) in a zii widget (such as CGridView or CListView) Follow @yiiframework

Total 1 comment

Leave a comment

Yii Supporters