What is the best practice of SUM column values using codeigniter

Question

I am currently using below code to sum up numbers of quantity based on a ID targeting mysql quantity column, just wondering if this is the best practice using codeigniter? or is there a shorter way to execute this.

My Model.php

public function sum_quantity_of_stacks($id){

        $query = $this->db->query("SELECT *, SUM(qty) as total FROM stocks WHERE drug_id = '$id'");
        return $query->row_array();

    }

    public function sum_quantity_of_request($id){

        $query = $this->db->query("SELECT *,SUM(quantity) as total_request FROM med_request WHERE med_given = '$id' AND status = 'Approved'");
        return $query->row_array();

    }

Then my view.php

$id = $medicine['ID'];

        $data['get_count'] = $this->Medicine_model->sum_quantity_of_stacks($id);

        $data['get_request'] = $this->Medicine_model->sum_quantity_of_request($id);

        $total_available = $data['get_count']['total'];
        $total_given = $data['get_request']['total_request'];

        echo $total_available  - $total_given;

Above code gives me the current available stocks of my medicine inventory.

Answer 1

No, there are no such way to do that, similar to what are you are doing now.

SELECT other column with SUM feature not possible

But there are a method name select_sum() available in query builder by which you may get SUM only like

public function sum_quantity_of_stacks($id){
    $this->db->select_sum('qty','total');
    $this->db->where('drug_id',$id);
    $query = $this->db->get('stocks');
    $res = $query->row_array();
    //now SUM is available in $res['total']
    return $res['total'];
   // or return $query->row_array();
}