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

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.




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

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


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




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


	public function getActiveBalance ()


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

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


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




		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?

Missing AND:

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

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?

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


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;


Ok, this is what I ended up doing

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

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


				->where('AccountId=:id AND CharityId=:cha', array(':id'=>Yii::app()->user->id, ':cha'=>$this->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.