Createcommand Issue

Yii 1.1 General Discussion
1

Hi there,

please don’t mind if this sounds as a basic question, since I’m new here :).

My problem:

For my model Concert and related bookings I want to get data about each category by this createCommand query:


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

                    ->select('b.category, (b.nroftickets) AS count')

                    ->from('tbl_booking b')

                    ->where('b.concert_id=:concertId')

                    ->group('b.category')

                    ->order('b.category asc')

                    ->bindParam('concertId', $concertId, PDO::PARAM_INT)

                    ->queryAll();

I’ve defined the extra properties in my Concert model class (it is an AR model):




         public $totalBookingsInA = 0;

         public $totalBookingsInB = 0;

         public $totalBookingsInC = 0;

When i view a concert (and it’s bookings) for the first time I get exact data, but in a mean time when I do another booking for that concert, I still do get the same data.

Here my ConcertController method:


public function loadModelWithBookingDetails($id){

                

                $model= Concert::model()->with('bookings')->findByPk($id);

		

                if($model===null)

			throw new CHttpException(404,'The requested page does not exist.');

                

                 //Yii::log(print_r($model, true), 'info');

                        

                //get nr of bookings 

                $raw = Booking::model()->getTotalbookingsPerCategory($id);

                

                foreach ($raw as $actualrow)

                {

                    if($actualrow['category'] =='A')

                    {

                        $model->totalBookingsInA=$actualrow['count'];

                    }else if($actualrow['category'] =='B')

                    {

                        $model->totalBookingsInB=$actualrow['count'];

                    }else if($actualrow['category'] =='C')

                        {

                    }

                        $model->totalBookingsInC=$actualrow['count'];

                }

                //Yii::log(print_r($model, true), 'info');

                return $model;

        }

When I execute the sql query directly into my MySql database I get the actual data:




SELECT b.category, sum(b.nrOfTickets) AS count

FROM tbl_booking b

where b.concert_id=_some_id_

group by b.category

order by b.category asc;

Could anyone help me here please? I’m lost :/.

Thanks in advance!

KR, AK

2

Hmm strange :/.

I’ve use this:




$sql = 'SELECT b.category, sum(b.nrOfTickets) AS count FROM tbl_booking b WHERE b.concert_id=:concertId GROUP BY b.category ORDER BY b.category asc';

                $cmd = Yii::app()->db->createCommand($sql);

                $cmd->bindValue(':concertId', (int)$concertId, PDO::PARAM_INT);

                $result = $cmd->queryAll($sql);

and now it’s working. I thought it would work as the code above.

3

Hi,

the bindParam and the bindValue are not same:

So maybe the correct code is:




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

                    ->select('b.category, (b.nroftickets) AS count')

                    ->from('tbl_booking b')

                    ->where('b.concert_id=:concertId')

                    ->group('b.category')

                    ->order('b.category asc')

                    ->bindValue(':concertId', $concertId, PDO::PARAM_INT) // mod: bindParam -> bindValue and colon added the param name 

                    ->queryAll();

+1: here the code is wrong:




else if($actualrow['category'] =='C')

                        {

                    }

                        $model->totalBookingsInC=$actualrow['count'];

correct:




else if($actualrow['category'] =='C')

                        {

                        $model->totalBookingsInC=$actualrow['count'];

                    }
4

Hi,

thnx for ur response.

The second "error" was a typo from my side.

I’ve tried with bindValue but still didn’t work for me. The second post of fine, that is working, at least in my case. I’m really confused, since I’dont really know the real cause of that behavier of createCommand.