codeigniter inner join not working as expected

Question

I have a inner join query as below,

SELECT * FROM shop_offer so INNER JOIN shop s ON s.shop_id = so.shop_id INNER JOIN city c ON c.city_id = s.city_id INNER JOIN locality l ON l.locality_id = s.locality_id INNER JOIN category ca ON ca.category_id = s.category_id WHERE so.offer_discount >= 10 AND so.publish = 1

While executing the above query in phpmyadmin, the result showing only one row as expected.

But when i tried this query in codeigniter, the result showing 3 rows. My codeigniter code is,

        $this->db->select('*');
        $this->db->from('shop_offer as so');
        $this->db->join('shop as s', 's.shop_id = so.shop_id');
        $this->db->join('city as c', 'c.city_id = s.city_id');
        $this->db->join('locality as l', 'l.locality_id = s.locality_id');
        $this->db->join('category as ca', 'ca.category_id = s.category_id');
        $this->db->where(array('so.offer_discount >=' => 10, 'so.publish' => 1));
        $query = $this->db->get();

What is the problem in codeigniter code. I am new in codeigniter. Is thre any way to solve this error.

Niranjan N Raju Niranjan N Raju · Accepted Answer · 2015-12-10T06:05:31

You can also try running the query like this,

$this->db->query("SELECT * FROM shop_offer so 
    INNER JOIN shop s ON s.shop_id = so.shop_id 
    INNER JOIN city c ON c.city_id = s.city_id 
    INNER JOIN locality l ON l.locality_id = s.locality_id 
    INNER JOIN category ca ON ca.category_id = s.category_id 
    WHERE so.offer_discount >= 10 AND so.publish = 1");

codeigniter inner join not working as expected

1 Answers