更新查询未更新事务中的表

Question

我试图同时获取和更新数据，但在获取数据后，事务被回滚，因为表没有更新
控制器-

public function addAmount()
{
$this->form_validation->set_rules('balance_id', 'Balance Id', 'required|trim');
$this->form_validation->set_rules('farmer_id', 'farmer_id', 'required|trim');
$this->form_validation->set_rules('amount', 'amount', 'required|trim');
$this->form_validation->set_rules('amount_discount', 'discount', 'required|trim');
$this->form_validation->set_rules('payment_mode', 'payment mode', 'required|trim');
if ($this->form_validation->run() == false) {
$data = array(
'amount'                 => form_error('amount'),
'balance_id' => form_error('balance_id'),
'farmer_id'  => form_error('farmer_id'),
'amount_discount'        => form_error('amount_discount'),
'payment_mode'           => form_error('payment_mode'),
);
$array = array('status' => 'fail', 'error' => $data);
echo json_encode($array);
} else {
$id =$this->generate_otp();
$data = array(
'farmer_id'       => $this->input->post('farmer_id'),
'balance_id'      => $this->input->post('balance_id'),
'amount_paid'     => $this->input->post('amount'),
'paying_date'     => $this->input->post('date'),
'amount_discount' => $this->input->post('amount_discount'),
'description'     => $this->input->post('description'),
'payment_mode'    => $this->input->post('payment_mode'),
'payment_id'     =>$id,            
);
$inserted_id = $this->advance_model->amount_deposit($data);
echo '<pre>'; print_r($inserted_id); echo ("</pre>"); exit();
$array = array('status' => 'success', 'error' => '');
echo json_encode($array);
}
}

型号：

public function amount_deposit($data)
{
$this->db->trans_start(); // Query will be rolled back
$paid_amount     = $data['amount_paid'];
$this->db->insert('tbl_pay_amount', $data);
$inserted_id = $this->db->insert_id();
if ($data['balance_id'] != null) {
$query = $this->db->select('balance,balance_id,reason')
->from('tbl_balance')
->where('balance_id', $data['balance_id'])
->get();
return $query->row();
if(!empty($query)){
$b =$query['balance'];
$balance =$b - $paid_amount;
}
$this->db->update('tbl_balance', array('balance' => $balance));
}
$this->db->trans_complete(); # Completing transaction
if ($this->db->trans_status() === false) {
$this->db->trans_rollback();
return false;
} else {
$this->db->trans_commit();
return json_encode(array('invoice_id' => $inserted_id, 'sub_invoice_id' => 1));
}
}

当要添加金额时，需要自动更新余额，但现在交易是回滚的，因为从同一张表中获得余额后无法更新余额，提前感谢。

Answer 1

刚刚添加了foreach循环并删除了return语句，现在它可以正常工作了。

public function amount_deposit($data)
{
$this->db->trans_start(); // Query will be rolled back
$paid_amount     = $data['amount_paid'];
$this->db->insert('tbl_pay_amount', $data);
$inserted_id = $this->db->insert_id();
if ($data['balance_id'] != null) {
$query = $this->db->select('balance,balance_id,reason')
->from('tbl_balance')
->where('balance_id', $data['balance_id'])
->get();
$result = $query->result();
}
if (!empty($result) && isset($result)) {
foreach ($result as $key => $balance_value) {
$balance_id    = $balance_value->balance_id;
$balanceAmount = $balance_value->balance;
$balance =      $balanceAmount - $paid_amount;
}

$this->db->where('balance_id', $balance_id);
$this->db->update('tbl_balance', array('balance' => $balance));
}                    
$this->db->trans_complete(); # Completing transaction
if ($this->db->trans_status() === false) {
$this->db->trans_rollback();
return false;
} else {
$this->db->trans_commit();
return json_encode(array('invoice_id' => $inserted_id, 'sub_invoice_id' => 1));
}
}