How to load two tables in a single select (html)?

Question

I have two client charts, one called personal and one called personal I need to load both tables within 1(a select) using php and codeigniter 3.6.

Be able to load the personal table, as follows the code in the controller and in the view below, remembering that the table Attendance has the foreign keys of the personal table (fkcodpf) and the personal table (fkcodpj):

CONTROLER

class Atendimento extends CI_Controller {

public function cadastro() {


    $dados['pessoafisica'] = $this->db->get('pessoafisica')->result();        
    $this->load->view('includes/html_header');
    $this->load->view('includes/menu');
    $this->load->view('cadastrar/cad_atendimento', $dados);
}
}

Customer Registration VIEW (select)

<label>Nome do Cliente</label>
<select id="fkcodpf" name="fkcodpf" class="form-control" >
<option value=""></option>  
<?php foreach ($pessoafisica as $pf) { ?>
<option value="<?= $pf->codpf ?>"> <?= $pf->nomepessoafisica; ?></option>
<?php } ?>
</select> 

But how to click together on this select, the personal table? If possible by sorting by personal name, followed by personal name?

Answer 1

If I understand correctly, you want to merge the data from the table of individuals with the data from the tables of legal persons within a single element select. Nor will I make observations regarding the complication that you will have to differentiate the data in the application (who is PJ and who is PF), because you should already have this in mind at the time of validation. In my opinion you should use another element and Javascript, but that is not the scope of the question.

But it should be noted that this should be heavy to load. If the client’s machine is not there such things, it will not be funny to use. I simulated 4290 records, and it wasn’t very cool the response time.

That said, as far as I could tell, CodeIgniterdoes not have, in the native library, the possibility to execute a UNION. But you have the ability to execute a QUERY in text format (read more here).

Knowing this, it follows the method of model:

function select_duas_tabelas(){
        $sql = "SELECT cpf as id,nome FROM tb_pessoa_fisica ";
        $sql.= "UNION ALL ";
        $sql.= "SELECT cnpj,nome FROM tb_pessoa_juridica ";
        $sql.= "ORDER BY nome ASC";
        $query = $this->db1->query($sql);
        return $query->result_array();
    }

Basically, number and cnpjs will be called 'id' in this query, and will take the place of id in your element’s fkcodpf.

Follows the controller loading the data into the view:

function select_duas_tabelas(){
    $data['title'] = $this->lang->line('pages_select_duas_tabelas_title');
    $data['dados'] = $this->teste->select_duas_tabelas();
    $this->template->load('template/index', 'pages/select_duas_tabelas', $data);
}

Finally, it follows the view showing your element select with all PF and PJ data:

<label>Nome do Cliente</label>
<select id="fkcodpf" name="fkcodpf" class="form-control" >
    <option value=""></option>  
    <?php
    foreach ($dados as $p) { ?>
        <option value="<?=$p['id']?>"><?=$p['nome'];?></option>
    <?php } ?>
</select>

That’s it.