$list= Yii::app()->db->createCommand('select s.subjectName from teacherteachsub t, subjects s where s.subjectId=t.subjectId and subjectTeacherId="'.$teacher_id.'"')->queryAll();
$rs=array();
foreach($list as $item){
$rs[]=$item['subjectName'];
}
return $rs;
}
This method will provide data to a drop down list in my view, giving all the subject names a teacher teaches
But i need to convert the selected drop down list value teachers selected subject to its respective subject code.
for that i had another function…
public function getSubjectId($subject_name){
$subject_id= Yii::app()->db->createCommand('select subjectId from subjects where subjectName="'.$subject_name.'"')->queryAll();
return $subject_id;
}
I tried to invoke this in the beforeSave() function with the current value of subject as the parameter…
Actually performing a queryAll() on your command, will return a resultset containings rows of data (in your case, 1 row, with only 1 column. You could use queryScalar, to get only that particalar value.
Otherwise you would have to do something like this:
public function getSubjectId($subject_name){
$result = Yii::app()->db
->createCommand('select subjectId from subjects where subjectName="'.$subject_name.'"')
->queryAll();
$subject_id = $result[0]['subjectId']; //gets the column subjectId from the first row of your dataset (assuming there is always first row)
return $subject_id;
}
OR (basically the same, but better readability, and does some of the error handling for you in case of an empty resultset)
public function getSubjectId($subject_name){
$subject_id = Yii::app()->db
->createCommand('select subjectId from subjects where subjectName="'.$subject_name.'"')
->queryScalar();
return $subject_id;
}
Also, try to avoid placing variables directly into queries! When these variables are going to contain user input you’re exposing yourself to SQL Injection vulnerabilty. Using binded parameters is A LOT safer, and also really easy in Yii.
Example:
public function getSubjectId($subject_name){
$subject_id = Yii::app()->db
->createCommand('select subjectId from subjects where subjectName = :subject_name')
->queryScalar(array(':subject_name' => $subject_name));
return $subject_id;
}