Yii 1.1: How to display data from two tables in TbGridView using DataProvider?

5 followers

In this wiki I will show how to display data from two tables in Bootstrap TbGridView using dataProvider.

In your controller/action

public function actionIndex()
 {
 
    $rawData=Yii::app()->db->createCommand("SELECT a.id as ID, a.first_name as FName, a.last_name as LName, a.status as Status, b.country_name as Country FROM user a LEFT JOIN country_list b ON a.country_id = b.id")->queryAll();  //Also can use where condition for particular use.
 
    // or using: $rawData=User::model()->findAll();
 
    $dataProvider=new CArrayDataProvider($rawData, array(
        'id'=>'user',
        'sort'=>array(
        'attributes'=>array(
            'ID', 'FName', 'LName', 'Status', 'Country'
            ),
         ),
            'pagination'=>array(
            'pageSize'=>10,         //records display
          ),
        ));
 
        $this->render('index',array(
            'dataProvider'=>$dataProvider,
        ));
    }

In your view file

$this->widget('bootstrap.widgets.TbGridView',array(
    'id'=>'user-info-grid',
    'dataProvider'=>$dataProvider,
        'type'=>'striped bordered condensed',
        'template'=>'{summary}{pager}{items}{pager}',
        'columns'=>array(
        'ID',
        'FName',
        'LName',
        'Country',              
        array(
        'name'=>'Status',
        'type'=>'raw',
        'value' =>'($data[Status]==1)? "<span class=\"label label-success\">Active</span>": "<span class=\"label label-important\">Inactive</span>"',
        ),
    ),
));

Working fine... for more help please leave a comment.

Total 4 comments

#17775 report it
Rohit Suthar at 2014/07/22 02:28am
RE #17774

you can join or add two or more database tables using join operation, that is already mention in this example. see -

$rawData=Yii::app()->db->createCommand("SELECT a.id as ID, a.first_name as FName, a.last_name as LName, a.status as Status, b.country_name as Country FROM user a LEFT JOIN country_list b ON a.country_id = b.id")->queryAll();

As per your query, you need to modify query with adding WHERE clause. Suppose my searching parameters are status and country so will modify my query like this -

$sql = "SELECT a.id as ID, a.first_name as FName, a.last_name as LName, a.status as Status, b.country_name as Country FROM user a LEFT JOIN country_list b ON a.country_id = b.id WHERE 1=1 ";
 
if(isset($_GET['status']))
 $sql .= " AND a.status = ".$_GET['status'];
 
if(isset($_GET['country']))
 $sql .= " AND b.country_name = ".$_GET['country'];
 
 
$rawData = Yii::app()->db->createCommand($sql)->queryAll();
#17774 report it
vijay p s at 2014/07/22 01:29am
@example please

Can You please explain this advanced search modification perform.

using the query I retrieve a grid. now there is no start date in the advance search, because this start date is retrieved from another table.(eg : maturity table) how can i use this start date in the advanced search.

#17753 report it
Rohit Suthar at 2014/07/19 11:28am
RE #17749

Ya you can, just need to modify the query in action controller according to your searching parameters.

#17749 report it
vijay p s at 2014/07/19 12:22am
Doubt!

How can we use Advanced Search in this gridview

Leave a comment

Please to leave your comment.

Write new article