如何在codeigniter中使用bd的多维结果

Question

I have a big problem with arrays, I have little experience with them.

I have 3 tables (materials, suppliers and a third table to relate both with id)

what I need is to take the data for each material and a list of suppliers

$this->db->select('m.id as id_material,m.nombre as detalle');
    $this->db->from('materiales as m');
    $res = $this->db->get();
    $result =array();
    $prov=array();

    foreach($res->result_array() as $key){
        $result[$key["detalle"]]=$key["detalle"];

        $this->db->select("m.id as idm,m.nombre as nom,p.nombre_comercial as prov_nom");
        $this->db->from('materiales as m');
        $this->db->join('materiales_proveedores as mp', 'm.id = mp.id_material', 'inner');
        $this->db->join('proveedores as p', 'p.id = mp.id_proveedor', 'inner');
        $res2 = $this->db->get();

            foreach($res2->result_array() as $key2){
                $result["proveedores"][$key2["prov_nom"]]=$key2["prov_nom"];

            }

    }

    return $result;

With this query I get this : enter image description here

And this is wrong, because it shows me all suppliers, not taking one corresponding to each material, any help?

Answer 1

        $this->db->select("m.id as idm,m.nombre as nom,**distinct** p.nombre_comercial as prov_nom");
    $this->db->from('materiales as m');
    $this->db->join('materiales_proveedores as mp', 'm.id = mp.id_material', 'inner');
    $this->db->join('proveedores as p', 'p.id = mp.id_proveedor', 'inner');
    $res2 = $this->db->get();

        foreach($res2->result_array() as $key2){
            $result["proveedores"][$key2["prov_nom"]]=$key2["prov_nom"];

        }