Binding Tables with Code Igniter

Question

I have the following problem, I have a table of customers that stores personal data, login, password among others, and another table of invoices, which has all invoices of all customers, but I need to search the customer data with a user ID stored in the invoice table, as I do using Code Igniter?

Here is part of the code that searches the invoices:

   $query = $this->admin->get_all('faturas')->result();
            foreach ($query as $linha) {
                echo '<tr>';
                printf('<td>%s %s</td>', $linha->nome, $linha->sobrenome);
                printf('<td>%s</td>', $linha->numero);
                printf('<td>%s</td>', $linha->valor);
                printf('<td>%s</td>', ($linha->status==0) ? 'Não':'Sim');
                printf('<td class="text-center">%s%s%s</td>', 
                    anchor("admin/cliente/alterar/$linha->id",' ', array('class'=>'table-actions table-edit', 'title'=>'Editar')),
                    anchor("admin/cliente/alterar_senha/$linha->id",' ', array('class'=>'table-actions table-pass', 'title'=>'Alterar Senha')),
                    anchor("admin/cliente/excluir/$linha->id",' ', array('class'=>'table-actions table-delete deletareg', 'title'=>'Excluir'))
                    );
                echo '</tr>';

Solved like this:

$query = $this->admin->get_all('faturas')->result();
            foreach ($query as $linha) {
                $cliente = $this->admin->get_byid('clientes', $linha->id_cliente)->row();
                echo '<tr>';
                printf('<td>%s %s</td>', $cliente->nome,  $cliente->sobrenome);
                printf('<td>%s</td>', $linha->numero);
                printf('<td>%s</td>', $linha->valor);
                printf('<td>%s</td>', ($linha->status==0) ? 'Não':'Sim');
                printf('<td class="text-center">%s%s%s</td>', 
                    anchor("admin/cliente/alterar/$linha->id",' ', array('class'=>'table-actions table-edit', 'title'=>'Editar')),
                    anchor("admin/cliente/alterar_senha/$linha->id",' ', array('class'=>'table-actions table-pass', 'title'=>'Alterar Senha')),
                    anchor("admin/cliente/excluir/$linha->id",' ', array('class'=>'table-actions table-delete deletareg', 'title'=>'Excluir'))
                    );
                echo '</tr>';
            }

Answer 1

It is possible with a INNER JOIN, in the CodeIgniter can be done this way:

<?php 

// application/models/admin.php

public function get_faturas()
{
    $this->db->select('faturas.numero, faturas.valor, faturas.status, cliente.nome, cliente.sobrenome');
    $this->db->from('faturas');
    $this->db->join('cliente', 'faturas.id_cliente = cliente.id');

    $query = $this->db->get();
    $result = $query->result();

    return $result;
}

?>