Yii 1.1: custom sql query results to CDetailView

4 followers

This is a bried guide about displaying of custom sql query results.

The result of the query will display on CDetailView widget

Remember that the query have to returns only one row. In any case the code takes into only the first row-result

  • Prepare and execute the sql query
$connection = Yii::app()->db;
$command = $connection->createCommand('select * from your_table');
$row = $command->queryRow(); //executes the SQL statement and returns the first row of the result.
  • Convert the results to the appropriate for CDetailView format
$res = array();
    foreach ($row as $key=>$val) {
        $res[] = array('label'=>$key,'value'=>$val);
    }
  • Display the data
$this->widget('zii.widgets.CDetailView', array(
'data' => array(), //to avoid error
'attributes' => $res,
));

Notes:

  • If you want to change the name of the displayed columns you could either set alias on sql query
$command = $connection->createCommand('select col_1 as 'my name column',col_2 as 'my name column2' ... from your_table');

or make a match (with mapping array) in foreach loop.

  • If you not sure about the purity of the data you have to use
$res[] = array('label'=>$key,'value'=>CHtml::encode($val));

I show you now how to make the similar task using other Providers

With CSqlDataProvider

$sqlProvider = new CSqlDataProvider('select * from your_table');
$sqlProvider = $sqlProvider->getData();
$sqlData = $sqlProvider[0];
 
$this->widget('zii.widgets.CDetailView', array(
    'data' => $sqlData,
));

With CArrayDataProvider

$connection = Yii::app()->db;
$command = $connection->createCommand('select * from your_table');
$row = $command->queryRow();
 
$sqlData = new CArrayDataProvider(array($row));
$sqlData = $sqlData->getData();
$sqlData = $sqlData[0];
$this->widget('zii.widgets.CDetailView', array(
    'data' => $sqlData,
));

Total 8 comments

#19322 report it
Rohit Suthar at 2015/05/20 03:36am
RE #19321

your welcome @bcs56897 Can your share, how to resolved that issues??

#19321 report it
bcs56897 at 2015/05/20 03:09am
ref: #19318

thanks @Rohit Suthar for your assistance,i fixed that error

#19319 report it
Rohit Suthar at 2015/05/19 08:27am
RE #19318

this may help you - http://yii.at/j6HAZ

#19318 report it
bcs56897 at 2015/05/19 08:17am
i have a problem with a CSqlDataProvider

thanks alot @Rohit Suthar for you concern,i have found out that the problem (500) was not from that source code,i fixed it but still it throws this CException

The attribute must be specified in the format of "Name:Type:Label", where "Type" and "Label" are optional.

#19317 report it
Rohit Suthar at 2015/05/19 08:10am
RE #19315

hi @bcs56897, Please print array result and check its empty or else.

eg. -

print_r($sqlData);
exit;
#19315 report it
bcs56897 at 2015/05/19 07:51am
i have a problem with a CSqlDataProvider

i am trying to customize a sql query results to CDetailView as shown onthis link.and it throws this exception. The attribute must be specified in the format of "Name:Type:Label", where "Type" and "Label" are optional. any assistance to understand this i shall appreciate. my controller codes

public function actionView($id)
        {
                //second option
                $q="Select family.familyID,concat(employee.lastName,' ',employee.firstName,' ',employee.secondName),family.WifeOrHusbandName,family.sonsName
                                ,family.daughtersName,family.familyAddress from family JOIN employee ON family.employeeID=employee.employeeID where family.familyID=".$id;
                $sqlProvider = new CSqlDataProvider($q);
                $sqlProvider = $sqlProvider->getData();
                $sqlData = $sqlProvider[0];
 
                $this->render('view',array(
                                'model'=>$sqlData,
                ));
 
        }

my view codes

$this->widget('zii.widgets.CDetailView', array(
                'data' => $model,
));
#14939 report it
Kostas Apazidis (KonApaz) at 2013/09/23 10:11am
RE: Easier with CSqlDataProvider

Yes it could be bone with CSqlDataProvider or CArrayDataProvider!

I will extend this wiki both of two aproaches. Thanks for your comment robregonm :)

#14938 report it
robregonm at 2013/09/23 09:41am
Easier with CSqlDataProvider?

I think, that could be more easily implemented with CSqlDataProvider. Similar results, more flexibility, more compatibility.

Leave a comment

Please to leave your comment.

Write new article