Hi,
I'm new to php (Yii Framework) and SQL (phpMyAdmin). I have problem to ask.
Currently the three dropdown menu from different table and each have different id called aircraft_type, aircraft_sn and aircraft_reg.
I want to use only one table called aircraft. In this table have all three details and only have one id. What I want to do is, when user choose value from aircraft type, the aircraft serial number and aircraft registration will also automatically fill. Calling value from table aircraft only.
So far the code I have is:
1)_form.php (project form)
<div class="panel panel-bordered panel-success"> <!-- Section A : DMS Admin --> <div class="panel-heading"> <h3 class="panel-title">Section A : Project Details - Admin</h3> </div> <div class="panel-body"> <?php $form=$this->beginWidget('CActiveForm', array(
'id'=>'project-form',
// Please note: When you enable ajax validation, make sure the corresponding
// controller action is handling ajax validation correctly.
// There is a call to performAjaxValidation() commented in generated controller code.
// See class documentation of CActiveForm for details on this.
'enableAjaxValidation'=>false,
'htmlOptions' => array(
'class'=>'form-horizontal',
'role' => 'form',
'enctype' => 'multipart/form-data',
),
)); ?> <div class="form-group <?php echo (!is_null($model->getError('user_id')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'user_id'); ?> </label> <div class="col-md-7"> <?php if ($model->isNewRecord) { ?> <?php echo CHtml::hiddenField('Project[user_id]',Yii::app()->user->user_id,
array('size'=>10,'maxlength'=>10,'class'=>'form-control input-md', 'readonly'=>true)); ?> <?php echo CHtml::textField('fullname',Yii::app()->user->fullname,
array('size'=>50,'maxlength'=>50,'class'=>'form-control input-md', 'readonly'=>true)); ?> <?php } else { ?> <?php echo $form->hiddenField($model,'user_id',array('size'=>10,'maxlength'=>10,'class'=>'form-control input-md', 'readonly'=>true)); ?> <?php echo CHtml::textField('fullname',(!is_null($model->user_id))? $model->user->firstname.' '.$model->user->lastname:'',
array('size'=>50,'maxlength'=>50,'class'=>'form-control input-md', 'readonly'=>true)); ?> <?php } ?> <?php echo $form->error($model,'user_id'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('department_id')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama">Department </label> <div class="col-md-7"> <?php if ($model->isNewRecord) { ?> <?php echo CHtml::textField('department',Yii::app()->user->department_name,
array('size'=>50,'maxlength'=>50,'class'=>'form-control input-md', 'readonly'=>true)); ?> <?php } else { ?> <?php echo CHtml::textField('department',Yii::app()->user->department_name,
array('size'=>50,'maxlength'=>50,'class'=>'form-control input-md', 'readonly'=>true)); ?> <?php } ?> <?php echo $form->error($model,'department_id'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('project_date')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'project_date'); ?> </label> <div class="col-md-7" id="project_date"> <div class="input-group date"> <!-- <input type="text" class="form-control"> --> <?php if ($model->isNewRecord) { ?> <?php echo CHtml::textField('Project[project_date]', date('d M Y'),
array('size'=>20,'maxlength'=>20,'class'=>'form-control input-md')); ?> <?php } else { ?> <?php echo $form->textField($model,'project_date',
array('size'=>20,'maxlength'=>20,'class'=>'form-control input-md')); ?> <?php } ?> <span class="input-group-addon"><i class="fa fa-calendar fa-lg"></i></span> <?php echo $form->error($model,'project_date'); ?> </div> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('project_priority_id')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'project_priority_id'); ?> </label> <div class="col-md-7"> <?php //echo $form->textField($model,'project_priority_id',array('size'=>20,'maxlength'=>20,'class'=>'form-control input-md')); ?> <?php
$list = CHtml::listData($modelProjectPriority, 'id', 'name');
echo CHtml::dropDownList('project-priority-id-select', '', $list,
array(
'class'=>'form-control m-b',
'empty' => 'Select Priority...',
'options' => array($model->project_priority_id => array('selected' => 'selected')),
'required' => 'required',
//'class' => 'col-md-12',
//'style'=>'width: 290px',
)
);
?> <?php echo $form->error($model,'project_priority_id'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('mod_number')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'mod_number'); ?> </label> <div class="col-md-7"> <?php echo $form->textField($model,'mod_number',array('size'=>60,'maxlength'=>50,'class'=>'form-control input-md')); ?> <?php echo $form->error($model,'mod_number'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('client_id')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'client_id'); ?> </label> <div class="col-md-7"> <?php //echo $form->textField($model,'client_id',array('size'=>20,'maxlength'=>20,'class'=>'form-control input-md')); ?> <?php
$list = CHtml::listData($modelClient, 'id', 'name');
echo CHtml::dropDownList('client-id-select', '', $list,
array(
'class'=>'form-control m-b',
'empty' => 'Select Client...',
'options' => array($model->client_id => array('selected' => 'selected')),
'required' => 'required',
//'class' => 'col-md-12',
//'style'=>'width: 290px',
)
);
?> <?php echo $form->error($model,'client_id'); ?> <a class="btn btn-success pull-right" title="" href="<?php echo Yii::app()->createUrl('client/create'); ?>" data-original-title="" style="margin-top:10px; margin-right:20px"> <i class="fa fa-pencil-square-o"></i> <span class="menu-title">New Client</span> </a> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('aircraft_type_id')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'aircraft_type_id'); ?> </label> <div class="col-md-7"> <?php //echo $form->textField($model,'aircraft_reg_id',array('size'=>20,'maxlength'=>20,'class'=>'form-control input-md')); ?> <?php
$list = CHtml::listData($modelAircraftType, 'id', 'name');
echo CHtml::dropDownList('aircraft-type-id-select', '', $list,
array(
'class'=>'form-control m-b',
'empty' => 'Select Type...',
'options' => array($model->aircraft_type_id => array('selected' => 'selected')),
'required' => 'required',
//'class' => 'col-md-12',
//'style'=>'width: 290px',
)
);
?> <?php echo $form->error($model,'aircraft_type_id'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('aircraft_sn_id')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'aircraft_sn_id'); ?> </label> <div class="col-md-7"> <?php //echo $form->textField($model,'aircraft_sn_id',array('size'=>20,'maxlength'=>20,'class'=>'form-control input-md')); ?> <?php
$list = CHtml::listData($modelAircraftSerielNumber, 'id', 'sn');
echo CHtml::dropDownList('aircraft-sn-id-select', '', $list,
array(
'class'=>'form-control m-b',
'empty' => 'Select Serial Number...',
'options' => array($model->aircraft_sn_id => array('selected' => 'selected')),
'required' => 'required',
//'class' => 'col-md-12',
//'style'=>'width: 290px',
)
);
?> <?php echo $form->error($model,'aircraft_sn_id'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('aircraft_reg_id')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'aircraft_reg_id'); ?> </label> <div class="col-md-7"> <?php //echo $form->textField($model,'aircraft_reg_id',array('size'=>20,'maxlength'=>20,'class'=>'form-control input-md')); ?> <?php
$list = CHtml::listData($modelAircraftRegistration, 'id', 'name');
echo CHtml::dropDownList('aircraft-reg-id-select', '', $list,
array(
'class'=>'form-control m-b',
'empty' => 'Select Registration...',
'options' => array($model->aircraft_reg_id => array('selected' => 'selected')),
'required' => 'required',
//'class' => 'col-md-12',
//'style'=>'width: 290px',
)
);
?> <?php echo $form->error($model,'aircraft_reg_id'); ?> <a class="btn btn-success pull-right" title="" href="<?php echo Yii::app()->createUrl('aircraft/create'); ?>" data-original-title="" style="margin-top:10px; margin-right:20px"> <i class="fa fa-pencil-square-o"></i> <span class="menu-title">New Aircraft</span> </a> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('mod_tittle')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'mod_tittle'); ?> </label> <div class="col-md-7"> <?php echo $form->textArea($model,'mod_tittle',array('rows'=>5,'cols'=>50,'maxlength'=>100, 'class'=>'form-control')); ?> <?php echo $form->error($model,'mod_tittle'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('latest_rev')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'latest_rev'); ?> </label> <div class="col-md-7"> <?php echo $form->textArea($model,'latest_rev',array('rows'=>5,'cols'=>50,'maxlength'=>500, 'class'=>'form-control')); ?> <?php echo $form->error($model,'latest_rev'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('pic')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'pic'); ?> </label> <div class="col-md-7"> <?php echo $form->textField($model,'pic',array('size'=>60,'maxlength'=>100,'class'=>'form-control input-md')); ?> <?php echo $form->error($model,'pic'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('soc_num')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'soc_num'); ?> </label> <div class="col-md-7"> <?php echo $form->textField($model,'soc_num',array('size'=>60,'maxlength'=>50,'class'=>'form-control input-md')); ?> <?php echo $form->error($model,'soc_num'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group <?php echo (!is_null($model->getError('remark')))? 'has-error':''; ?>"> <label class="col-md-3 control-label" for="nama"><?php echo $form->labelEx($model,'remark'); ?> </label> <div class="col-md-7"> <?php echo $form->textArea($model,'remark',array('rows'=>5, 'cols'=>50,'maxlength'=>500, 'class'=>'form-control')); ?> <?php echo $form->error($model,'remark'); ?> </div> <div class="col-md-2"></div> </div> <div class="form-group"> <label class="col-md-3 control-label" for="nama">Document Upload</label> <div class="col-md-7"> <?php echo CHtml::fileField('DocumentUpload[mod_file1]', '', array('class'=>'form-control input-md')); ?><br> <?php echo CHtml::fileField('DocumentUpload[mod_file2]', '', array('class'=>'form-control input-md')); ?><br> <?php echo CHtml::fileField('DocumentUpload[mod_file3]', '', array('class'=>'form-control input-md')); ?><br> <?php
/*}*/
if (!$model->isNewRecord) {
foreach ($modelProjectMod as $projectMod) : ?> <div class="input-group mar-btm" id="doc-id-<?php echo $projectMod->id; ?>"> <input type="text" value="<?php echo $projectMod->filename; ?>" class="form-control"> <span class="input-group-btn"> <button class="btn btn-danger btn-labeled fa fa-trash" type="button" onclick="confirmDeleteDocument('<?php echo $projectMod->id; ?>')">Delete</button> </span> </div> <?php
endforeach;
}
?> <!-- <span class="pull-left btn btn-default btn-file">Browse...
</span> --> </div> <div class="col-md-2"></div> </div> <div class="form-group" style="text-align:right;"> <div class="col-md-12 form-controller"> <?php if ($model->isNewRecord) { ?> <button type="button" class="btn btn-warning btn-labeled fa fa-remove fa-lg" title="Cancel" onclick="window.location.href = '<?php echo Yii::app()->createUrl('project/index');?>'"> <span class="menu-title">Cancel</span> </button> <?php } else { ?> <button align="left" type="button" class="btn btn-danger btn-labeled fa fa-trash fa-lg pull-left" title="Delete" onclick="confirmDelete()"> <span class="menu-title">Delete</span> </button> <button type="button" class="btn btn-warning btn-labeled fa fa-remove fa-lg" title="Cancel" onclick="window.location.href = '<?php echo Yii::app()->createUrl('project/view', array('id'=>$model->id));?>'"> <span class="menu-title">Cancel</span> </button> <?php } ?> <button type="submit" class="btn btn-primary btn-labeled fa fa-save fa-lg" name="BtnUpdate" title="Save">Save
</button> </div> </div> <?php $this->endWidget(); ?> </div> </div>2) aircraft.php (table aircraft)
class Aircraft extends CActiveRecord
{
/**
* @return string the associated database table name
*/
public function tableName()
{
return 'aircraft';
}
/**
* @return array validation rules for model attributes.
*/
public function rules()
{
// NOTE: you should only define rules for those attributes that
// will receive user inputs.
return array(
array('id, company_id, aircraft_status_id', 'required'),
array('id, company_id, updated_user_id, aircraft_status_id', 'numerical', 'integerOnly'=>true),
array('aircraft_reg, aircraft_sn, aircraft_type', 'length', 'max'=>50),
array('remarks', 'length', 'max'=>500),
array('created_time, updated_time', 'safe'),
// The following rule is used by search().
// @todo Please remove those attributes that should not be searched.
array('id, aircraft_reg, aircraft_sn, aircraft_type, created_time, updated_user_id, updated_time', 'safe', 'on'=>'search'),
);
}
/**
* @return array relational rules.
*/
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'company' => array(self::BELONGS_TO, 'Company', 'company_id'),
'aircraft_status' => array(self::BELONGS_TO, 'AircraftStatus', 'aircraft_status_id'),
'updated_user' => array(self::BELONGS_TO, 'User', 'updated_user_id'),
);
}
/**
* @return array customized attribute labels (name=>label)
*/
public function attributeLabels()
{
return array(
'id' => 'ID',
'company id' => 'Company',
'aircraft_reg' => 'Aircraft Registration',
'aircraft_sn' => 'Aircraft Serial Number',
'aircraft_type' => 'Aircraft Type',
'remarks' => 'Remarks',
'aircraft_status_id' => 'Aircraft Status',
'created_time' => 'Created Time',
'updated_user_id' => 'Updated User',
'updated_time' => 'Updated Time',
);
}
/**
* Retrieves a list of models based on the current search/filter conditions.
*
* Typical usecase:
* - Initialize the model fields with values from filter form.
* - Execute this method to get CActiveDataProvider instance which will filter
* models according to data in model fields.
* - Pass data provider to CGridView, CListView or any similar widget.
*
* @return CActiveDataProvider the data provider that can return the models
* based on the search/filter conditions.
*/
public function search()
{
// @todo Please modify the following code to remove attributes that should not be searched.
$criteria=new CDbCriteria;
$criteria->compare('id',$this->id);
$criteria->compare('aircraft_reg',$this->aircraft_reg,true);
$criteria->compare('aircraft_sn',$this->aircraft_sn,true);
$criteria->compare('aircraft_type',$this->aircraft_type,true);
$criteria->compare('created_time',$this->created_time,true);
$criteria->compare('updated_user_id',$this->updated_user_id);
$criteria->compare('updated_time',$this->updated_time,true);
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
));
}
/**
* Returns the static model of the specified AR class.
* Please note that you should have this exact method in all your CActiveRecord descendants!
* @param string $className active record class name.
* @return MocStatusLog the static model class
*/
public static function model($className=__CLASS__)
{
return parent::model($className);
}
public $maxId = 0;
public function getMax($filter = null)
{
/* Get max in column1 WHERE column2 = $filter */
$criteria = new CDbCriteria;
$criteria->select = 'MAX(t.id) AS maxId';
$tempmodel = $this->find($criteria);
if (is_null($tempmodel['maxId'])) {
return 0;
}
else {
return $tempmodel['maxId'];
}
}
public function getLabel($id)
{
switch ($id) {
case 1: // Pending
$label = 'warning';
break;
case 2: // Active
$label = 'primary';
break;
case 3: // Expired
$label = 'dark';
break;
case 4: // Deleted
$label = 'danger';
break;
}
return $label;
}
public function getAircraftAndCompany()
{
return $this->name.' <'.$this->company->shortname.'>';
}
}3) ProjectController (from to create new project)
public function actionCreate()
{
if (isset(Yii::app()->session['company_id']) && !empty(Yii::app()->session['company_id']))
$activeCompanyId = Yii::app()->session['company_id'];
else
$activeCompanyId = Yii::app()->user->company_id;
$model = new Project;
$modelClient=Client::model()->findAll();
$modelProjectPriority=ProjectPriority::model()->findAll();
$modelAircraftRegistration=AircraftRegistration::model()->findAll();
$modelAircraftType=AircraftType::model()->findAll();
$modelAircraftSerielNumber=AircraftSerielNumber::model()->findAll();
$modelProjectApproval=ProjectApproval::model()->findAll();
$model->project_date=date('j M Y');
if(isset($_POST['Project']))
{
$model->attributes=$_POST['Project'];
$model->id = Project::model()->getMax()+1;
$model->user_id=Yii::app()->user->account_id;
$tempDate = $model->project_date;
$model->project_date = date('Y-m-d', strtotime($tempDate));
$model->client_id = $_POST['client-id-select'];
$model->project_priority_id = $_POST['project-priority-id-select'];
$model->aircraft_type_id = $_POST['aircraft-type-id-select'];
$model->aircraft_reg_id = $_POST['aircraft-reg-id-select'];
$model->aircraft_sn_id = $_POST['aircraft-sn-id-select'];
$model->project_status_id = 1;
//Moc No. (format:company-dept/MOC/year/running #)
$runningNo=Project::model()->count(array(
'condition'=>'YEAR(created_time)=YEAR(CURRENT_DATE)',
));
if (strlen(Yii::app()->session['company_shortname']) <= 2)
$ref_no = Yii::app()->session['company_shortname'];
else
$ref_no = substr(Yii::app()->session['company_shortname'],0,-2);
$ref_no .= '/PRO';
$ref_no .= '/'.date('y');
$ref_no .= '/'.str_pad($runningNo+1, 4, '0', STR_PAD_LEFT);
$model->project_no = $ref_no;
date_default_timezone_set("Asia/Kuala_Lumpur");
$time = date_default_timezone_get() ;
$time = date('Y-m-d H:i:s');
$model->created_time = $time;
$model->updated_time = $time;
$model->updated_user_id = Yii::app()->user->user_id;
if($model->save()) {
if (isset($_FILES['DocumentUpload'])) {
$files = $_FILES['DocumentUpload'];
$documentUploadPath = Yii::getPathOfAlias('webroot.uploaded.project').'/';
for ($i=1;$i<=3;$i++){
$mod_file = 'mod_file'.$i;
// file upload - Requestor document
if(!is_null($files['name'][$mod_file]) && !empty($files['name'][$mod_file])) {
list($fn, $ext) = explode(".", basename($files['name'][$mod_file]));
$filename = substr(str_replace(" ", "_", $fn),0,5).'_'.date('Ymd_His').".".$ext;
$file = $documentUploadPath.$filename;
if (file_exists($file)) {
unlink($file);
}
move_uploaded_file($files['tmp_name'][$mod_file], $file);
$modelProjectMod = new ProjectDocumentUpload;
$modelProjectMod->id = ProjectDocumentUpload::model()->getMax()+1;
$modelProjectMod->project_id = $model->id;
$modelProjectMod->media_code_id = 1; // project admin
$modelProjectMod->reference_no = 'Project Mod';
$modelProjectMod->filename = $filename;
$modelProjectMod->date = $time;
if ($modelProjectMod->save()) {
// add into audit trails
$modelAuditTrail=new AuditTrail;
$modelAuditTrail->id = AuditTrail::model()->getMax()+1;
$modelAuditTrail->controller = Yii::app()->controller->id;
$modelAuditTrail->action = Yii::app()->controller->action->id;
$modelAuditTrail->account_id = Yii::app()->user->account_id;
$modelAuditTrail->company_id = $activeCompanyId;
$modelAuditTrail->department_id = Yii::app()->user->department_id;
$modelAuditTrail->user_role_id = Yii::app()->user->user_role_id;
$modelAuditTrail->level = 1;
$modelAuditTrail->description = 'Add Request in Project No. '.$model->project_no;
$modelAuditTrail->created_time = $time;
$modelAuditTrail->updated_user_id = Yii::app()->user->user_id;
$modelAuditTrail->updated_time = $time;
$modelAuditTrail->save();
}
}
}
}
}//tutup if $model->save
// //add into timeline
$modelProjectStatusLog=new ProjectStatusLog;
$modelProjectStatusLog->id = ProjectStatusLog::model()->getMax()+1;
$modelProjectStatusLog->project_id = $model->id;
$modelProjectStatusLog->project_status_id = $model->project_status_id;
$modelProjectStatusLog->updated_user_id = Yii::app()->user->user_id;
$modelProjectStatusLog->updated_time = $time;
$modelProjectStatusLog->save();
$this->redirect(array('view','id'=>$model->id));
// }
}
$this->render('create',array(
//'section'=>1,
'model'=>$model,
'modelProjectPriority'=>$modelProjectPriority,
'modelClient'=>$modelClient,
'modelAircraftRegistration'=>$modelAircraftRegistration,
'modelAircraftType'=>$modelAircraftType,
'modelAircraftSerielNumber'=>$modelAircraftSerielNumber,
'modelProjectApproval'=>$modelProjectApproval,
));
}Hope you can help me with this. Thank you in advanced.