problem with Yii::app()->db->createCommand() query

Yii 1.1 General Discussion
1

I have a grid that will calculate the balance given from each user to a given charity and group the balance per charity in the grid. This is my dp for that.


<?php 

 $criteria->with='acc';

                $criteria->condition='acc.AccountId='.Yii::app()->user->id.' 

                                            AND t.CharityId='.$model->CharityId; 

                $criteria->select='AccountId,CharityId,sum(Amount) as Amount';

                $criteria->group='t.AccountId,CharityId';

                $dataProvider=new CActiveDataProvider(Trans::model(), array(

                    'criteria'=>$criteria, 

                ));

?>

Now I want to create a query that will give me the same balance for a given charity outside of the grid.


<?php 

	public function getActiveBalance ()

	{ 

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

				->select('AccountId,CharityId,SUM(Amount) as Amount')

				->from('trans')

				->where('AccountId=:id, CharityId=:cha', array(':id'=>Yii::app()->user->id, ':cha'=>$this->CharityId))

				->group('AccountId,CharityId')

				->query();

	

		return $balance;

	}

?>

With this I get this error




CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; 

check the manual that corresponds to your MySQL server version for the right syntax to use near ' CharityId='5'

GROUP BY `AccountId`, `CharityId`' at line 3. The SQL statement executed was: SELECT AccountId,CharityId,SUM(Amount) as Amount

FROM `trans`

WHERE AccountId=:id, CharityId=:cha

GROUP BY `AccountId`, `CharityId`. Bound with :id=1, :cha=5

I am not sure the cause of this error though. Any ideas?

2

Missing AND:





->where('AccountId=:id AND CharityId=:cha',...)
3

Thanks Joblo!

Now if I try to call it in my view with


echo $model->activeBalance;

I get


Object of class CDbDataReader could not be converted to string

Why am I not just getting the direct output of the calculated variable?

4

Use ‘var_dump’ instead of ‘echo’.

Your function getActiveBalance() returns a CDbDataReader not the data.

See: Working with databases

Maybe you want something like this:




  $data = $model->activeBalance->queryAll();

  var_dump($data);
5

Try with below

function xyz($pTypeFlag = false, $pCityID = 0){

	 &#036;ret = false;


	 &#036;conn = &#036;this-&gt;getDbConnection();


		


	&#036;query = 'SELECT DISTINCTROW c.* FROM products as p, geo_cities as c WHERE c.CityId = p.bus_departure_city_id AND p.bus_departure_city_id = ' . &#036;pCityID .' ORDER BY c.City';


	&#036;rows = &#036;conn-&gt;createCommand(&#036;query)-&gt;query();		


	while((&#036;row = &#036;rows-&gt;read()) &#33;== false)


	{		


		&#036;ret[&#036;row['CityId']] = &#036;row['City'];


	}		


	return &#036;ret;


}
6

Ok, this is what I ended up doing


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

				->select('SUM(Amount) as Amount')

				->from('trans')

				->where('AccountId=:id AND CharityId=:cha', array(':id'=>Yii::app()->user->id, ':cha'=>$this->CharityId))

				->group('AccountId,CharityId')

				;


		$data = $balance->query();

		foreach ($data as $out) return implode('<br />', $out);

This will give me the balance as I want and I can call it in a view with $model->function. Not sure if there is a better way to go about it though.