Convert mysql query into codeigniter active record

Question

I am new to codeigniter, I would like to ask some help. How can I convert this mysql query into a codeigniter active record. This is my query

 SELECT p.*, s.product_id,sum(s.quantity) as st,  sumq,
sum(s.quantity) - sumq as a, concat (prod_desc,' ',brand_name) as names
from stocks s join ( select product_id, sum(quantity) sumq 
from order_details group by product_id) o on s.product_id = o.product_id join (select * from products) p on p.prod_id = s.product_id 

join (select * from brand) b on b.brand_id = p.brand_id

  group by product_id having a >= 1

Calvin · Answer 1 · 2018-02-03T02:52:33.967

0

        $this->db->select('product_id, sum(quantity) sumq')
            ->from('order_details')
            ->group_by('product_id');
        $query_2 = $this->db->get_compiled_select();
        $this->db->from('products');
        $query_3 = $this->db->get_compiled_select();
        $this->db->from('brand');
        $query_4 = $this->db->get_compiled_select();

        $final_query = "SELECT p.*, s.product_id,sum(s.quantity) as st,  sumq,
sum(s.quantity) - sumq as a, concat (prod_desc,' ',brand_name) as names from stock s JOIN ($query_2) o on s.product_id = o.product_id JOIN ($query_3)p on p.prod_id = s.product_id JOIN ($query_4)b on b.brand_id = p.brand_id
GROUP BY product_id having a >= ?";
        $a = 1;
        $this->db->query($final_query,array($a));
        echo $this->db->last_query();

Codeigniter cant use subquery in its active record, So i generate the compiled select statement from active record by using $this->db->get_compiled_select(); after that I write a new query in $final_query and run it with prepared statement. Does codeigniter $this->db->query() or $this->db->escape() prevent SQL Injection?

edited Feb 03 '18 at 02:52

answered Feb 01 '18 at 09:03

Calvin

605
10
26

I'm getting a database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SELECT `p`.*, `s`.`product_id`, sum(s.quantity) as st, `sumq`, sum(s.quantity) ' at line 1 – maine shar Feb 01 '18 at 15:45
Error Number: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SELECT p.*, s.product_id,sum(s.quantity) as st, sumq,\nsum(s.quantity) - sumq ' at line – maine shar Feb 02 '18 at 09:53
@maineshar this should be working now xD, previous mistake was I use $this->db->escape() the entire query so this char of ' gets escaped into \' and caused error on the query (I Misused the $this->db->escape()) – Calvin Feb 03 '18 at 02:53
is it like the query you wanted?, if yes then there is a problem with the query. $this->db->query($final_query)->result_array() to get the query returned in an array format – Calvin Feb 05 '18 at 08:03

score 0 · Accepted Answer · answered Feb 08 '18 at 16:06

I got the answer. I just input the select statement in join.

$this->db->select("p.*,c.category_name,s.size,b.brand_name,sum(t.quantity) as total_q,sumq,sum(t.quantity)-sumq as aq, CONCAT(b.brand_name,' ',p.prod_desc,' ',s.size) AS name", FALSE)

                ->from('stocks t');

                  $this->db->join('products p','p.prod_id = t.product_id','full')
                        ->join('category c','p.category_id = c.category_id','full');
                   $this->db->join('sizes s','p.size_id = s.size_id','full');
                    $this->db->join('brand b','p.brand_id = b.brand_id','full');
                    $this->db->join('(select product_id, sum(order_quantity) sumq from order_details group by product_id) as o','t.product_id = o.product_id','full');
                     $this->db->group_by('prod_id')

                             ->order_by('name');

                $result=$this->db->get();

         if($result->num_rows()>0){
            foreach ($result->result() as $data) {
                $hasil[] = $data;
            }
            return $hasil;
             }

Convert mysql query into codeigniter active record

2 Answers2