CodeIgniter Active Record multiple

0
votes

I have the following MY-SQL query in CodeIgniter and I am trying to get the multiplication of [sub_product_price]*[quantity] and display it as a multiply.Any one can help me with it . I went through the CodeIgniter User Guide and I could not find anything to related this.

Thanks!!!!

 function product_profit_chart(){

    $this->db->select(array(
        'tbl_sub_products.sub_product_name',
        'tbl_sub_products.sub_product_id',
        'tbl_sub_products.sub_product_price',
        'SUM(tbl_order_products.quantity) as quantity'
    ));
    $this->db->from('tbl_order_products');
    $this->db->join('tbl_order_product_details', 'tbl_order_product_details.order_id=tbl_order_products.order_id','LEFT');
    $this->db->join('tbl_sub_products', 'tbl_sub_products.sub_product_id=tbl_order_products.product_id','LEFT');
    $this->db->group_by('tbl_sub_products.sub_product_id');
    $this->db->where('tbl_order_product_details.order_status', 'completed');
    $query = $this->db->get();
    return $query->result();

}

return array of the above query.

[1] => stdClass Object
        (
            [sub_product_name] => chocolate cake 500G
            [sub_product_id] => SP001
            [sub_product_price] => 1000.00
            [quantity] => 14
        )

[2] => stdClass Object
        (
            [sub_product_name] => Sausage Bun
            [sub_product_id] => SP002
            [sub_product_price] => 50.00
            [quantity] => 16
        )
3
phpmysqlsqlcodeigniter
You don't have to preface every select key with the table name that what $this->db->from() is for. - Bankzilla
what is solution for that @Bankzilla - Ann Nimbi
I can suggest an edit for your code to make it more readable but I'm not sure what you're actually trying to achieve - Bankzilla
I want to multiply sub_product_price with the quantity - Ann Nimbi

3 Answers

0
votes

Change your function as follows:

function product_profit_chart(){

$this->db->select(array(
    'tbl_sub_products.sub_product_name',
    'tbl_sub_products.sub_product_id',
    'tbl_sub_products.sub_product_price',
    'SUM(tbl_order_products.quantity) as quantity',
    'SUM(tbl_sub_products.sub_product_price * quantity) as amount'
));
$this->db->from('tbl_order_products');
$this->db->join('tbl_order_product_details', 'tbl_order_product_details.order_id=tbl_order_products.order_id','LEFT');
$this->db->join('tbl_sub_products', 'tbl_sub_products.sub_product_id=tbl_order_products.product_id','LEFT');
$this->db->group_by('tbl_sub_products.sub_product_id');
$this->db->where('tbl_order_product_details.order_status', 'completed');
$query = $this->db->get();
return $query->result();

}

Hope this helps you.

0
votes

Just add one more select item where you perform the multiplication:

$this->db->select(array(
    'tbl_sub_products.sub_product_name',
    'tbl_sub_products.sub_product_id',
    'tbl_sub_products.sub_product_price',
    'SUM(tbl_order_products.quantity) as quantity',
    /* add this column: */
    'SUM(tbl_order_products.quantity) * tbl_sub_products.sub_product_price as total'
));

See this demo (Not Codeigniter Active Record, but shows the working query logic)

0
votes

Untested, it's using aliases for the queries and using select_sum() instead of writing it in the select query 

function product_profit_chart(){
    $this->db->select('sub_product_name', 'sub_product_id', 'sub_product_price');
    $this->db->select_sum('sub_prod.sub_product_price * prod.quantity as quantity');
    $this->db->from('tbl_order_products prod');
    $this->db->join('tbl_order_product_details prod_details', 'prod_details.order_id=prod.order_id','LEFT');
    $this->db->join('tbl_sub_products as sub_prod', 'sub_prod.sub_product_id=prod.product_id','LEFT');
    $this->db->group_by('sub_prod.sub_product_id');
    $this->db->where('prod_details.order_status', 'completed');
    $query = $this->db->get();
    return $query->result();
}