phpcodeigniterselectactiverecordquery-builder

Convert raw SQL with LEFT JOIN, WHERE and ORDER BY into a CodeIgniter active record script


I have a query that I use to get my data from two tables. How can I use the CodeIgniter active record methods instead of my query?

public function get_categories($parent_id = 0)
{      
    $query = $this->db->query("
        SELECT
            *
        FROM 
            " . $this->db->dbprefix . "category c
        LEFT JOIN
            " . $this->db->dbprefix . "category_description cd
                ON (c.category_id = cd.category_id)
        WHERE
            c.status=1
            and c.parent_id = '" . (int)$parent_id . "'
        order by
            sort_order
    ");
    return $query->result_array();
}

How can I convert it to a CodeIgniter active record script?


Solution

  • Try this

    $this->db->select('*');
    $this->db->from($this->db->dbprefix.'category c');
    $this->db->join($this->db->dbprefix.'category_description cd', 'c.category_id = cd.category_id');
    $this->db->where('c.status',1);
    $this->db->where('c.parent_id',$parent_id);
    $this->db->order_by("sort_order", "asc");
    $query = $this->db->get();
    return $query->result_array();