CODEWITHSUNDEEP
phpsqlcodeigniterjoinquery-builder
Chaitanya K
Chaitanya K

Reputation: 1852

Translate raw SELECT SQL with two comma-JOINed tables to CodeIgniter's query builder syntax

How to write this query in codeigniter active records

select 
a.parent_cat_id,a.parent_cat_name, b.child_cat_id,b.child_cat_name,
c.sub_child_cat_id,c.sub_child_cat_name
FROM parent_categories a,child_categories b,sub_child_categories c 
WHERE a.parent_cat_id=b.parent_cat_id AND b.child_cat_id=c.child_cat_id

Tried this but it Shows 0 result

$this->db->select('a.parent_cat_id,a.parent_cat_name, b.child_cat_id,b.child_cat_name,c.sub_child_cat_id,c.sub_child_cat_name');
$this->db->from('parent_categories a,child_categories b,sub_child_categories c');
$this->db->where('a.parent_cat_id','b.parent_cat_id'); 
$this->db->where('b.child_cat_id','c.child_cat_id'); 
$result = $this->db->get()->result_array();

when i echo the above ci query i get

SELECT `a`.`parent_cat_id`, `a`.`parent_cat_name`, `b`.`child_cat_id`, `b`.`child_cat_name`, `c`.`sub_child_cat_id`, `c`.`sub_child_cat_name`
FROM `parent_categories` `a`, `child_categories` `b`, `sub_child_categories` `c`
WHERE `a`.`parent_cat_id` = 'b.parent_cat_id'
AND `b`.`child_cat_id` = 'c.child_cat_id'

enter image description here

Upvotes: 0

Views: 73

Answers (2)

Sona
Sona

Reputation: 490

You have to use Join Query For That Here is Code Snippet

    $this->db->select('a.parent_cat_id,a.parent_cat_name, b.child_cat_id,b.child_cat_name,c.sub_child_cat_id,c.sub_child_cat_name'); 
    $this->db->from('parent_categories a');
    $this->db->join('child_categories b', 'b.parent_cat_id = a.parent_cat_id', 'left'); 
    $this->db->join('sub_child_categories c', 'c.child_cat_id = b.child_cat_id', 'left'); 
    $query = $this->db->get();
    $res =  $query->result();

Upvotes: 1

Sanjuktha
Sanjuktha

Reputation: 1085

Try changing $this->db->where in your query as below-

$this->db->select('a.parent_cat_id,a.parent_cat_name, b.child_cat_id,b.child_cat_name,c.sub_child_cat_id,c.sub_child_cat_name');
    $this->db->from('parent_categories a,child_categories b,sub_child_categories c');
    $this->db->where("a.parent_cat_id = b.parent_cat_id"); 
    $this->db->where("b.child_cat_id = c.child_cat_id");
    $result = $this->db->get()->result_array();

Upvotes: 1

Related Questions