Yii Framework Forum: Query Builder And Carraydataprovider - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Query Builder And Carraydataprovider Rate Topic: -----

#1 User is offline   spritespirit 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 24-March 12
  • Location:India

Posted 13 January 2013 - 03:13 PM

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:

Quote

Call to a member function getData() on a non-object in View file


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);

}
?>

0

#2 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 13 January 2013 - 03:51 PM

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
1

#3 User is offline   spritespirit 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 24-March 12
  • Location:India

Posted 14 January 2013 - 02:33 PM

[quote name='seenivasan' timestamp='1358110273' post='188720']
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.
0

#4 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 15 January 2013 - 12:48 AM

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.
0

#5 User is offline   spritespirit 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 24-March 12
  • Location:India

Posted 15 January 2013 - 05:19 AM

View Postseenivasan, on 15 January 2013 - 12:48 AM, said:

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?
0

#6 User is offline   spritespirit 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 24-March 12
  • Location:India

Posted 15 January 2013 - 07:24 AM

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.
0

#7 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 15 January 2013 - 10:24 AM

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))
                

0

#8 User is offline   spritespirit 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 24-March 12
  • Location:India

Posted 29 January 2013 - 07:53 AM

View Postseenivasan, on 15 January 2013 - 10:24 AM, said:

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
0

#9 User is offline   Shahcheraghean 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 478
  • Joined: 12-December 12
  • Location:Mashhad, IRAN

Posted 18 November 2013 - 12:49 PM

View Postseenivasan, on 13 January 2013 - 03:51 PM, said:

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


Thanks so much.
Is it possible to use CArrayDataProvider in CListView?
www.shgn.ir
www.shgn.ir/yii-framework/
yii2.swsco.ir
In a world without any fences or walls,Who needs Gates or Windows!
0

#10 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,222
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 18 November 2013 - 01:21 PM

View PostShahcheraghean, on 18 November 2013 - 12:49 PM, said:

Is it possible to use CArrayDataProvider in CListView?

Yes. Also: Why weren't you using CSqlDataProvider right from the start?
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#11 User is offline   Shahcheraghean 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 478
  • Joined: 12-December 12
  • Location:Mashhad, IRAN

Posted 19 November 2013 - 03:46 AM

Oh, Really Great, I didn't know there is a class named CSqlDataProvider.::)
www.shgn.ir
www.shgn.ir/yii-framework/
yii2.swsco.ir
In a world without any fences or walls,Who needs Gates or Windows!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users