Query Builder And Carraydataprovider

I am trying to retrieve data from database using query builder, store the result in CArrayDataProvider and display it in view file. but i am getting this error:

please help me spot the error and fix it.

in controller:

public function actionIndex()

  

  {

  $model=new Search;

  

  if(isset($_POST['Search']))

  {

	$searchresult = Yii::app()->db->createCommand()

		->select('p.product_id, p.product_name, r.price, p.master_key')

		->from('tbl_product_index p')

		->join('tbl_pricing r', 'p.product_id=r.product_id')

		->where('master_key=:master_key', array(':master_key'=>$_POST['Search']['brand']['1']))

		->queryRow();

	

	$dataProvider=new CArrayDataProvider($searchresult);


	$this->render('view',

		array(

			'dataProvider'=>$searchresult,

			)

	);

  }

  $this->render('index',array('model'=>$model,));

}




in view:

<?php

 $dataArray = $dataProvider->getData();

foreach ($dataArray as $data){

echo CHtml::encode($data->product_id);


}

?>

Dear Friend

The method queryRow brings the first row only.

The method queryAll brings all the rows.

More over they are fetched as an array.

Kindly check whether the following is helpful in our case.

CONTROLLER

public function actionIndex()

  

  {

  $model=new Search;

  

  if(isset($_POST['Search']))

  {

        $searchresult = Yii::app()->db->createCommand()

                ->select('p.product_id, p.product_name, r.price, p.master_key')

                ->from('tbl_product_index p')

                ->join('tbl_pricing r', 'p.product_id=r.product_id')

                ->where('master_key=:master_key', array(':master_key'=>$_POST['Search']['brand']['1']))

                ->queryAll();//fetching all rows...

        

        $dataProvider=new CArrayDataProvider($searchresult);


        $this->render('view',

                array(

                        'dataProvider'=>$dataProvider,//pass $dataProvider not searchresult.

                        )

        );

  }

  $this->render('index',array('model'=>$model,));

}

VIEW

$dataArray = $dataProvider->getData();

foreach ($dataArray as $data){

echo CHtml::encode($data["product_id"]);//Here $data represents associative array with column names as index representing a row


}

Regards

[quote=“seenivasan, post:2, topic:57526”]

Dear Friend

The method queryRow brings the first row only.

The method queryAll brings all the rows.

More over they are fetched as an array.

Kindly check whether the following is helpful in our case.

CONTROLLER

public function actionIndex()

  

  {

  $model=new Search;

  

  if(isset($_POST['Search']))

  {

        $searchresult = Yii::app()->db->createCommand()

                ->select('p.product_id, p.product_name, r.price, p.master_key')

                ->from('tbl_product_index p')

                ->join('tbl_pricing r', 'p.product_id=r.product_id')

                ->where('master_key=:master_key', array(':master_key'=>$_POST['Search']['brand']['1']))

                ->queryAll();//fetching all rows...

        

        $dataProvider=new CArrayDataProvider($searchresult);


        $this->render('view',

                array(

                        'dataProvider'=>$dataProvider,//pass $dataProvider not searchresult.

                        )

        );

  }

  $this->render('index',array('model'=>$model,));

}

Thanks Seenivasan,this was helpful.

however now i am facing issue in creating command. the query provides an output if i remove the part which involves related tables (like r. price and Join sentence) but it gives empty result if using the below code.

if(isset($_POST['ProductIndex']))

  {

	$searchresult = Yii::app()->db->createCommand()

		->select('p.product_id, p.product_name, r.price, p.master_key') //works fine without r.price

		->from('tbl_product_index p') 

		->join('tbl_pricing r', 'r.product_id=p.product_id') //works fine without this sentence

		->where('master_key=:master_key', array(':master_key'=>$_POST['ProductIndex']['brand']['1']))

		->queryAll();

	

	$dataProvider=new CArrayDataProvider($searchresult);

thanks for the help.

Dear Friend

My little suggestion is that just put the query with some masterkey values in phpMyAdmin.

After ensuring that everthing fine, then try the commandBuilder.

regards.

Thanks friend for the suggestion. I had left related table empty.

Now I am facing challenge in writing where condition as input for search condition is an array.

The input for search is being taken in the form of check box.

so the number of checkboxes selected will decide the number of condition to be put for masterkey.

can you suggest the best way to do it?

i am following the following procedure to search results based on the input taken from checkbox:

1. create a method that fetches user input.

2. retrieves data stored in an array one by one and concatenates the input

3. return the concatenated in the method

4. use the above described function within the CDbCommand query builder

public function forBrand()

        {

            $brand = null;

        foreach ($_POST['ProductIndex']['brand'] as $v)

            {

            $brandnew = $v;

            $brandnew = $brand.','.$brandnew;

            }

            return $brand;

        }

$searchresult = Yii::app()->db->createCommand()

		->SELECT ('p.product_id, product_name, p.master_key, r.price')

		->FROM ('tbl_product_index p')

		->JOIN ('tbl_pricing r', 'r.product_id = p.product_id')

                ->Where (array('in', 'master_key', array($this->forBrand('$_POST['ProductIndex']['brand']'))))

		->queryAll();

any help in achieving the goal would be highly appreciated.

Dear Friend

I guess you are using checkBoxList.

Then in that situation, then we have to rewrite the where method in the following way.

$checked=$_POST['ProductIndex']['brand']; //it will give an array like array(3,5,9);


->Where(array('in', 'master_key', $checked))

                

Thanks a lot. that is exactly what i wanted. Feature is working correctly.

I am now curious to know how you came to know about it. i was not able to find out even after searching quite a bit.

Thanks

Thanks so much.

Is it possible to use CArrayDataProvider in CListView?

Yes. Also: Why weren’t you using CSqlDataProvider right from the start?

Oh, Really Great, I didn’t know there is a class named CSqlDataProvider.