Show Data In Cgridview Using Custom Sql Query

Archives Agile Web Application Development with Yii1.1 and
1

hi,

I want to display data from database in CGridView using custom MySql query.

my code is below-

Model:


public function get_expense_details($id)

	{		

		$count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM meal_expenses_details WHERE meal_expenses_id='.$id)->queryScalar();

		$sql='SELECT * FROM meal_expenses_details WHERE meal_expenses_id='.$id;

		$dataProvider=new CSqlDataProvider($sql, array(

				'totalItemCount'=>$count,

				'sort'=>array(

						'attributes'=>array(

								'product_title', 'qty', 'unit', 'price',

						),

				),

				'pagination'=>array(

						'pageSize'=>10,

				),

		));

		

		return $dataProvider->getData(); /*will return a list of arrays.*/

	}

Controller:


public function actionView($id)

	{

		$mealExpenses = $this->loadModel($id);

		

		$objMealExpensesDetails = new MealExpensesDetails;

		$mealExpenseDetails = $objMealExpensesDetails->get_expense_details($id);

		

		$this->render('view',array(

			'meal_expenses'=>$mealExpenses,

			'meal_expense_details'=>$mealExpenseDetails,

		));

	}

View:




	$this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'meal-expenses-details-grid',

	'dataProvider'=>$meal_expense_details,

	'columns'=>array(

		'product_title',

		'qty',

		'unit',

		'price',

	),

));

But it throw me below error

Fatal error: Call to a member function getData() on a non-object in C:\xampp\htdocs\project_name\framework\zii\widgets\CBaseListView.php on line 111

please guys help me i just tired… :(

2

Hi,I think u r not getting any values in the list. that is the reason for exception

Print_r($mealExpenseDetails[size=2]);[/size]

[size=2]

[/size]

[size=2]in your controller[/size]

just check above

3

It has to be "return $dataProvider;" instead of "return $dataProvider->getData();" in get_expense_details() method

4

change this function to be like this:




public function get_expense_details($id)

        {               

                $count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM meal_expenses_details WHERE meal_expenses_id='.$id)->queryScalar();

                $sql='SELECT * FROM meal_expenses_details WHERE meal_expenses_id='.$id;

                $dataProvider=new CSqlDataProvider($sql, array(

                                'totalItemCount'=>$count,

                                'sort'=>array(

                                                'attributes'=>array(

                                                                'product_title', 'qty', 'unit', 'price',

                                                ),

                                ),

                                'pagination'=>array(

                                                'pageSize'=>10,

                                ),

                ));

                

                return $dataProvider;

        }

instead returning list data, it should returning CDataProvider