我想问是否可以为UPDATE设置Conditional。首先我有两张桌子。
billing_records：
brecord_id (PK)
date_billed
monthly_rent
water
electricity
total_payable
status (paid/unpaid)
payment_records
payment_id (PK)
date
type_payment (cash/cheque/on_bank)
amount_payable
change
balance
cheque_number
bank_number
brecord_id (FK)
我希望在数据库中插入记录时代码如下：
IF balance==0
UPDATE status to 'paid' from billing_records TABLE
else
UPDATE total_payable(of billing_records) = balance(of payment_records)
在提交按钮期间，我有两个操作要做，在payment_records和UPDATE billing_records中插入数据。我在付款记录中插入数据没有问题，仅更新billing_records。
我的控制器上的这条线路有问题
// 4. call model to save input to database table
$this->m_account_statement->insertPayableRecords($data);
$this->m_account_statement->changeStatusToPaid($brecord_id);
这是我的代码：
控制器：
public function managePayment($brecord_id=0){
if($this->_submit_validate_payment($this->input->post('type_payment'))===FALSE){
$row = $this->m_account_statement->payableRecord($brecord_id);
$data['amountPayable'] = $row->result();
$data['brecord_id'] = $brecord_id;
return $this->load->view('admin/vrecord_payment',$data);
} else {
// 2. get the inputs
$data['payment_id'] = $this->input->post('payment_id');
$data['amount_payable'] = $this->input->post('amount_payable');
$data['amount_received'] = $this->input->post('amount_received');
$data['type_payment'] = $this->input->post('type_payment');
$data['cheque_number'] = $this->input->post('cheque_number');
$data['bank_number'] = $this->input->post('bank_number');
$data['balance'] = $this->input->post('balance');
$data['change'] = $this->input->post('change');
$data['brecord_id'] = $this->input->post('brecord_id');
$data['date'] = $this->input->post('date_transaction');
// 4. call model to save input to database table
$this->m_account_statement->insertPayableRecords($data);
$this->m_account_statement->changeStatusToPaid($brecord_id);
// 5. confirmation of registration
$this->session->set_flashdata('message',' Successfully Record Payment');
redirect('caccount_statement/displayTenants');
}
}
型号：
public function changeStatusToPaid($billing_id)
{
$this->db->query("UPDATE billing_record SET status = 'paid' WHERE brecord_id = $billing_id");
}
public function insertPayableRecords ($data){
if($this->db->insert('payment_record', $data)){
return TRUE;
}
return FALSE;
}
可以设置一个可以使用此流的条件。
if balance==0
//then fetch a record for status
//means run a query to get status
//now update
else
// select some different values for update
// now update query with fetched values
public function managePayment($brecord_id=0){
//check if id is present or not like this:
if ($brecord_id > 0) {//Update
// ... put your update code ...
} else { //Add
// ... put your add code ...
}
}