Get the total amount and store it on another table. (Codeigniter)

Question

my target is to total all the (commtype = IN) betAmount only and store it on my "comCurrentBalance" users table. Then, with my SQL update it'll be subtracted the user input's amount to the total value of betAmount from "agent_commmission_history" table. I provided a screenshot below for my problem and my target. Any help will be appreciated.

Controller:

public function comm($userID=0) {
        
        $this->commissions->commOut($userID);

        redirect(base_url() . "commission/commi");
    }

Model:

function commOut($data) {
        
        
        
        $ref= $this->session->userdata('uid') + date ('his');
        $id = $this->session->userdata('uid');
        
        $data = array(
            'userID' => $id,
            'transactionSource' => 'Commi',
            'refNumber' =>  $ref ,
            'amount' =>$this->input->post("amount"),
            "transType" =>"out",
            
        );
       
        $sqlInsertLedger = "INSERT INTO transaction_ledger (transactionSource, transType, refNumber, userID, amount, currentBalance, previousBalance, remarks, createdBy)
                                select '".$data['transactionSource']."', '".$data['transType']."', '".$data['refNumber']."', ".$data['userID'].", ".$data['amount'].", sum(TU.currentPoints + ".$data['amount'].") as totalPoints, TU.currentPoints,
                                'funded by agent', '".$this->session->userdata('uid')."'
                                from users TU where TU.userID=?";
        
        $Q = $this->db->query($sqlInsertLedger, $data['userID']);
        
        //update user table
        
        $sqlUpdate = "update users set  commCurrentBalance = commCurrentBalance - ?, currentPoints = currentPoints + ? where userID = ?"; // ***Current points is already adding and comCurrentBalance is subtracting based on user's input. However, my target is to total the betamount before subtracting happens.*****
        $Q = $this->db->query($sqlUpdate, array($data['amount'], $data['amount'], $data['userID']));
        
   
    }

question from:https://stackoverflow.com/questions/66058320/get-the-total-amount-and-store-it-on-another-table-codeigniter

Answer 1

Waitting for answers