php - Codeigniter active record - query 3 tables

Question

I need to make a query using 3 tables and i having some trouble with that. I have 3 tables in my project:

projects, projects_categories and categories

projects

• id_project
• title
• date

projects_categories

• id_proj_cat
• id_project
• id_category

categories

• id_category
• name

I already made a join query but the result is a array with the same project_id showing several times. What i need is a more efficient query that can list for each project_id a array inside, with it′s categories and names. Something like that.

I can make a separate query but im trying to achieve that in one single query.

Answer 1

Try this

    $this->db->from("projects p");
    $this->db->select("p.id_project,c.categories,c.name");
    $this->db->join("projects_categories pc","pc.id_project = p.id_project","LEFT");
    $this->db->join("categories c","c.id_category = pc.id_category","LEFT");
    $result=$this->db->get()->result_array();

Now $result is your array.