TPLMedia24 TPLMedia24 - 7 days ago 6
MySQL Question

Sum of multiple row and insert into another column using Codeigniter not working

My transation table as follows

id acount dr cr bal
1 Brack Bank 0.00 5000.00 0.00
2 Uttora Bank 0.00 1000.00 5000.00
3 FC Bank 0.00 3000.00 6000.00
4 purchase 4000.00 0.00
5 sallary 3000.00 0.00


I want to make my transaction table as follows

id acount dr cr bal
1 Brack Bank 0.00 5000.00 5000.00
2 Uttora Bank 0.00 1000.00 6000.00
3 FC Bank 0.00 3000.00 9000.00
4 purchase 4000.00 0.00 5000.00
5 sallary 3000.00 0.00 2000.00


My view

<?php echo form_open('transaction/add',array("class"=>"form-horizontal")); ?>

<?php $p = $sum[0]->cr; ?><br/>
<?php $q = $sum[0]->dr; ?><br/>

<input type="hidden" value="<?php echo $p - $q ?>" name="calculation">

<input type="text" name="account" value="<?php echo $this->input->post('account'); ?>" class="form-control" id="account" />

<button type="submit" class="btn btn-success">Save</button>

<?php echo form_close(); ?>


My Controller

function add()
{
if(isset($_POST) && count($_POST) > 0)
{
$params = array(
'account' => $this->input->post('account'),
'bal' => $this->input->post('calculation'),
);

$accounts_transaction_id = $this->Accounts_transaction_model->update_transactions_balance($params);

redirect('transaction/index');

}else{
$this->load->view('transaction/new_deposit');
}
}


My model

function update_transactions_balance($params)
{

$account = $this->input->post('account');
$bal = $this->input->post('calculation');
$this->db->set('bal', "bal+$bal", FALSE);
$this->db->where('account', "$account");
$this->db->update('accounts_transactions');
}


I want to update each row when I submit new data. The problem is it is not updating my bal column on first insert of data. Also, when I insert data more than once, it is updating it with the wrong value.

Current data

Answer

Your expected query is :

UPDATE accounts_transactions SET bal = bal+$bal WHERE account = '$account';

So,

<?php
function update_transactions_balance($params)
{  
    $account = $this->input->post('account');
    $bal = $this->input->post('calculation');
    $this->db->query("UPDATE accounts_transactions SET bal = bal+$bal WHERE account = '$account'"); 
}
?>
Comments