Yii 1.1: Using a CDbDataReader (sql query result) in a zii widget (such as CGridView or CListView)

8 followers

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 2 comments

#19323 report it
bcs56897 at 2015/05/20 06:45am
ref: #4803

i tried to follow the instructed but i get this error Error 500 Undefined index: id

Any assistance i'll appreciate

controller codes

public function actionIndex()
    {
        $q="Select family.familyID,employee.employeeID,concat(employee.lastName,' ',employee.firstName,' ',employee.secondName) as employeeNames,family.WifeOrHusbandName,family.sonsName as sonsNames
                ,family.daughtersName as daughterNames,family.familyAddress from family JOIN employee ON family.employeeID=employee.employeeID";
 
        $count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM family')->queryScalar();
        $dataProvider=new CSqlDataProvider($q, array(
                'totalItemCount'=>$count,
                'sort'=>array(
                        'attributes'=>array(
                                'familyID','employeeID','employeeNames','WifeOrHusbandName','sonsName','daughterNames','familyAddress',
                        ),
                ),
                'pagination'=>array(
                        'pageSize'=>10,
                ),
        ));
        $this->render('index',array(
            'dataProvider'=>$dataProvider,
        ));
    }

view codes

<?php
$this->breadcrumbs=array(
    'Families',
);
 
$this->menu=array(
    array('label'=>'Manage Family', 'url'=>array('admin')),
);
?>
 
<h1>Families</h1>
 
<?php 
 
$this->widget('zii.widgets.CListView', array(
    'dataProvider'=>$dataProvider,
    'itemView'=>'_view',
)); ?>

the _view codes

<div class="view">
 
    <b><?php echo 'familyID'; ?>:</b>
    <?php echo CHtml::link(CHtml::encode($data['familyID']), array('view', 'id'=>$data['familyID'])); ?>
    <br />
 
    <b><?php echo 'employeeID'; ?>:</b>
    <?php echo CHtml::encode($data['employeeID']); ?>
    <br />
 
    <b><?php echo 'WifeOrHusbandName'; ?>:</b>
    <?php echo CHtml::encode($data['WifeOrHusbandName']); ?>
    <br />
 
    <b><?php echo 'sonsNames'; ?>:</b>
    <?php echo CHtml::encode($data['sonsNames']); ?>
    <br />
 
    <b><?php echo 'daughterNames'; ?>:</b>
    <?php echo CHtml::encode($data['daughterNames']); ?>
    <br />
 
    <b><?php 'familyAddress'; ?>:</b>
    <?php echo CHtml::encode($data['familyAddress']); ?>
    <br />
 
 
</div>
#4803 report it
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

Leave a comment

Please to leave your comment.

Write new article