Pass Variable To Sql Builder Query

Yii 1.1 General Discussion
1

I have some sql query i am running to obtain information from different tables and present it with CDetailView.

I want to pass


$model->id

as part of the where statement…see code


<?php 	$command = Yii::app()->db->createCommand();	

		$where='$model->id';

		

		$sql = $command->select('SROL_CANDIDATE_NAME,SROL_INDEX_NUMBER,SROL_AGE,SROL_GENDER,EC_CODE_TRANSLATION,SROL_PLE_INDEX_NUMBER,CC_NAME,SROL_YEAR_OF_PLE,SI_NAME')

		->from('students_registered_o_level')

		->where('students_registered_o_level.id=$where')

		->leftjoin('citizenship_countries','SROL_CC_ID=citizenship_countries.id')

		->leftjoin('district_information','SROL_DI_ID=district_information.id')

		->leftjoin('entry_codes','SROL_ENTRY_CODE=entry_codes.id')

		->leftjoin('school_information','SROL_SI_ID=school_information.ID')

		->queryRow();	

		

		$res = array();

		foreach($sql as $key =>$val){

			$res[] = array('label'=>$key, 'value'=>$val);

			} 

			

            


<?php $this->widget('zii.widgets.CDetailView', array(

	'data'=>array(),

	'attributes'=>$res,

)); ?>

However, it is giving me an error at the where clause, and i know it is because of the way i passed the variable…

How do i rectify this.

Thanks

2

You need to use bound parameters as follows:




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

                $id=$model->id;

                

                $sql = $command->select('SROL_CANDIDATE_NAME,SROL_INDEX_NUMBER,SROL_AGE,SROL_GENDER,EC_CODE_TRANSLATION,SROL_PLE_INDEX_NUMBER,CC_NAME,SROL_YEAR_OF_PLE,SI_NAME')

                ->from('students_registered_o_level')

                ->where('students_registered_o_level.id=:id')

                ->leftjoin('citizenship_countries','SROL_CC_ID=citizenship_countries.id')

                ->leftjoin('district_information','SROL_DI_ID=district_information.id')

                ->leftjoin('entry_codes','SROL_ENTRY_CODE=entry_codes.id')

                ->leftjoin('school_information','SROL_SI_ID=school_information.ID')

                ->queryRow(true, array(':id'=>$id));   

                

                $res = array();

                foreach($sql as $key =>$val){

                        $res[] = array('label'=>$key, 'value'=>$val);

                        }

Note that I’ve updated the queryRow() call too.

3

Thanks. I have also continued to read up on it here.

Solves the problem.

Thanks again