I know reading and self study is not enough, It’s just right now I don’t have time to do it properly as I’m working offshore and needed badly to create web based database apps for personal use on my job.
I will take onboard everything you said and I’m starting to like Yii, PHP and JQuery.
New error after the changes.
GET http://localhost/scaffreg/index.php/scaffold/newReferenceId?departmentId=1&facilityId=1 403 (Forbidden)
I guess I need to move along as I’m taking too much time on this one.
Thanks for your help, specially on your patience and time spent.
I hope you don’t mind if I keep this topic open until fully resolved.
So you did have accessRules set up in your controller because this is a different error. I can’t tell from here what’s causing the 500 error, I can’t go through the whole code again and I’m not going to guess the cause of the error. There could be an error in the newReferenceId action or it could be something else. All I can tell is that it’s a PHP error. Check your error logs and see if it gives you more insight into the error.
In the link I provided for the accessRules error, the asker of the question also got the 500 error after the 403 error was fixed. Did you try the solution provided there? Since that is a common error, it’s likely that are possible solutions on this site. A quick search threw up this thread and this thread.
If you can’t fix the error and can’t find a solution after searching this site, please consider posting any further questions as a new topic since this thread has already strayed far from its original topic.
After playing several days with Chrome Developer tools, I came across this CDbException from the 500 error;
CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens. The SQL statement executed was: SELECT 1 FROM `tbl_department` `t` WHERE id=:id LIMIT 1
Stack Trace
#2
– C:\Program Files\Apache Software Foundation\Apache2.4\htdocs\scaffreg\protected\models\Scaffold.php(323): CActiveRecord->exists("id=:id", array(":id", "1"))
318 * returns generated reference if reference generated successfully, if not it returns 0
319 */
320 public static function generateRef($department_id,$facility_id){
321 $newReference = 0;
322 if($department_id && $facility_id){
323 $deptExists=Department::model()->exists('id=:id',array(':id',$department_id));
324 $facExists =Facility::model()->exists('id=:id',array(':id',$facility_id));
325 if($deptExists && $facExists){
326 $sql='SELECT max(id) as lastRef from tbl_scaffold where department_id=:dept_id and facility_id=:fac_id';
327 $command=Yii::app()->db->command($sql);
328 $command->bindParam(':dept_id',$department_id,PDO::PARAM_INT);
#3
– C:\Program Files\Apache Software Foundation\Apache2.4\htdocs\scaffreg\protected\controllers\ScaffoldController.php(197): Scaffold::generateRef("1", "1")
192 $departmentId = $_GET['departmentId'];
193 if(isset($_GET['facilityId']))
194 $facilityId = $_GET['facilityId'];
195
196 //generateRef is the function in $model that calculates the new reference based on the department and facility
197 $ref = Scaffold::generateRef($departmentId,$facilityId);
198
199 if($ref>0)
200 echo CJSON::encode(
201 array(
202 'status'=>'success',
Final working codes. A BIG thanks to ‘BlkRaven’ and ‘dito’ who helped me to solve this topic.
Controller:
public function actionNewReferenceId()
{
$departmentId=null;
$facilityId=null;
if(isset($_GET['departmentId']))
$departmentId = $_GET['departmentId'];
if(isset($_GET['facilityId']))
$facilityId = $_GET['facilityId'];
//generateRef is the function in $model that calculates the new reference based on the department and facility
$ref = Scaffold::generateRef($departmentId,$facilityId);
if($ref>0)
echo CJSON::encode(
array(
'status'=>'success',
'newReferenceID'=>$ref,
));
else
echo CJSON::encode(
array(
'status'=>'error',
'message'=>'Invalid reference generated',
'newReferenceID'=>$ref,
));
}
Model:
/*
* returns generated reference if reference generated successfully, if not it returns 0
*/
public static function generateRef($department_id,$facility_id){
$newReference = 0;
if($department_id && $facility_id){
$deptExists = Department::model()->exists('id=:id',array(':id'=>$department_id));
$facExists = Facility::model()->exists('id=:id',array(':id'=>$facility_id));
if($deptExists && $facExists){
$sql='SELECT max(id) as lastRef from tbl_scaffold where department_id=:dept_id and facility_id=:fac_id';
$command=Yii::app()->db->createCommand($sql);
$command->bindParam(':dept_id',$department_id,PDO::PARAM_INT);
$command->bindParam(':fac_id',$facility_id,PDO::PARAM_INT);
$result=$command->queryRow();
if($result['lastRef']>0)
$newReference = $result['lastRef']+1;
else{
switch($department_id){
case 1: //Ops & Maint
if($facility_id==1) //CUQ
$newReference=10001;
else if($facility_id==2) //DPP
$newReference=30001;
break;
case 2: //AIMS
if($facility_id==1) //CUQ
$newReference=50001;
else if($facility_id==2) //DPP
$newReference=70001;
break;
case 3: //Shutdown
if($facility_id==1) //CUQ
$newReference=80001;
else if($facility_id==2) //DPP
$newReference=90001;
break;
}
}
}
}
return $newReference;
}
_form/view
<?php
Yii::app()->clientScript->registerScript('newReference',"
$('#Scaffold_department_id, #Scaffold_facility_id').change(function(e){
var departmentId = $('#Scaffold_department_id').val();
var facilityId= $('#Scaffold_facility_id').val();
//trigger ajax request only if both dropdownlists have selected values.
if(departmentId && facilityId){
var info = { departmentId:departmentId,
facilityId:facilityId
};
$.ajax({
url: '" . Yii::app()->createUrl("/scaffold/newReferenceId") . "',
data: info,
type: 'GET',
dataType: 'json',
success:function(data){
if(data.status=='success')
//if newReference was generated then id field gets populated.
$('#Scaffold_id').val(data.newReferenceID);
},
});
}
});
");
?>
