Get data from multiple codeigniter tables (Multidimensional Array)?

Question

How to get data from multiple tables at once?

I need to recover the data of the following object (debtor).

There are several tables where the main table is tbl_devedor. After that, there are others, which are related to tbl_devedor.

Example:

• tbl_devedor
• tbl_email
• id_contrato
• id_telefone
• id_endereco

Follows below the array

$dados = array(
    'contratos' => [],
    'id' => '',
    'id_operador' => 0,
    'pessoa_fisica' => array(
          'id' => 0,
          'rg' => '',
          'dt_nascimento' => '',
          'profissao' => '',
          'salario' => '',
          'genero' => '',
        ),
    'pessoa_juridica' => array (
         'id' => 0,
         'nome_fantasia' => '',
         'inscricao_estadual' => ''
    ),
    'nome' => '',
    'cpf_cnpj' => '',
    'emails' => [],
    'enderecos' => [],
    'telefones' => [],
    'crud' => null
);

echo json_encode($dados);

Remembering that contratos, pessoa_fisicao, pessoa_juridica, emails, enderecos, telefones, comes from different tables, so far what I have is this:

Controller

$devedor = $this->devedor->obter_por_id($id);

Model

public function obter_por_id($id)
{
    $this->db->from($this->tbl_devedor);
    $this->db->where('id',$id);
    $query = $this->db->get();
    return $query->row();
}

I know how to do the Join and get the data from the other tables, but I don’t know how the structure of array.

Answer 1

Even the information being related, I’d make a class Model for each table and then a class Model to manage the others as follows:

Minimal Example:

_{Observing: in this simple example were placed two of these tables the others follow the same layout programming and the proposed idea.}

class Devedor_model

<?php

    class Devedor_model extends CI_Model 
    {

        public function obter_por_id($id)
        {
            $this->db->from('tbl_devedor');
            $this->db->where('id',$id);
            $query = $this->db->get();
            return $query->row_array();
        }

    }

---

class Devedoremail_model

<?php

    class Devedoremail_model extends CI_Model 
    {   

        public function obter_por_id_email($id)
        {
            $this->db->from('tbl_devedor_email');
            $this->db->where('devedor_id',$id);
            $query = $this->db->get();
            return $query->result_array();
        }

    }

these two classes Model would now be used in another class Model that would manage and manipulate this data at once and will generate the array expected:

<?php

    class Devedordados_model extends CI_Model 
    {
        private $CI;

        public function __construct()
        {
            parent::__construct();
            $this->CI =& get_instance();
            // subindo as classe model coloque todas
            $this->CI->load->model("devedor_model");
            $this->CI->load->model("devedoremail_model");
        }

        public function obter_todos_dados_por_id($id)
        {
            $result = array();
            $result = $this->devedor_model->obter_por_id($id);
            $result['email'] = $this->devedoremail_model->obter_por_id_email($id);
            // insere aqui os outros models 
            //$result['enderecos'] = 
            //$result['telefones'] = 
            return $result;
        }

    }

and finally in the controller is called the class that manages all the Model, Devedordados_model:

public function de()
{
    $id = 1;
    $this->load->model('Devedordados_model');

    echo json_encode($this->Devedordados_model->obter_todos_dados_por_id($id),
                     JSON_PRETTY_PRINT);

}

the result obtained by that method of Controller:

{
    "id": "1",
    "nome": "Devedor 1",
    "email": [
        {
            "id": "1",
            "email": "[email protected]",
            "devedor_id": "1"
        },
        {
            "id": "2",
            "email": "[email protected]",
            "devedor_id": "1"
        }
    ]
}

and that would be a proposal, upon your doubt

_References:

• Models
• Auto-loading Models