Query doesn´t show results if some columns are empty using codeigniter

Question

Hi guys I´m trying to get results from two different tables using mysql and codeigniter, and actually I´m able to do it the problem is that one table (empleados) has data like name, last name, etc. (see the picture) and the other table (cuentas) has empty columns because I´m gonna insert them later based on the result, the problem is that if cuentas doesn´t have any data stored, it doesn´t show the results from the other table (name, last_name etc), so I want to see the results even if the other table doesn´t have stored data.

This is the controller:

function search2()
{
  if ($_POST) {
      $Interno=$_POST['Interno'];
  }else{
    $Interno = '';
  }
  $this->db->select('empleados.Interno, empleados.Curp, empleados.Nombre, empleados.A_Paterno, empleados.A_Materno, cuentas.Clabe, cuentas.Banco, cuentas.Observaciones, cuentas.Status');
$this->db->from('empleados');
$this->db->join('cuentas','cuentas.Interno = empleados.Interno');
$this->db->where('empleados.Interno', $Interno);
$this->db->where('cuentas.Status !=', 'I');
$q = $this->db->get();
$data = array();
$data['records'] = $q->result_array();
$this ->load -> view('main/indice', $data); 


}

This is the view:

 <?php
    foreach ($records as $row) {
            echo"<br></br>";
            echo "<label class='control-label'>Interno</label>";
            echo "<input type='text' id='Interno'  name ='Interno' class='form-control Input col-sm-3' placeholder='Interno' value='".$row['Interno']."'>";
            echo "<label class='control-label'>CURP</label>";
            echo "<input type='text' id='curp' name ='curp' class='form-control Input col-sm-3' placeholder='Curp' value='".$row['Curp']."'>";
            echo "<label class='control-label'>nombre</label>";
            echo "<input type='text' id='nombre' name ='nombre' class='form-control Input col-sm-3' placeholder='Nombre' value='".$row['Nombre']."'>";
            echo "<label class='control-label'>Apellido Paterno</label>";
            echo "<input type='text' id='A_Paterno' name ='A_Paterno' class='form-control Input col-sm-2' placeholder='Apellido Paterno' value='".$row['A_Paterno']."'>";
            echo "<label class='control-label'>Apellido Materno</label>";
            echo "<input type='text' id='A_Materno' name ='A_Materno' class='form-control Input col-sm-1' placeholder='Apellido Materno' value='".$row['A_Materno']."'>"; 

               echo "<label class='control-label'>Clabe</label>";
            echo "<input type='text' id='Clabe' name ='Clabe' class='form-control Input col-sm-3' placeholder='' value='".$row['Clabe']."'>";
            echo "<label class='control-label'>Banco</label>";
            echo "<input type='text' id='Banco' name ='Banco' class='form-control Input col-sm-2' placeholder='' value='".$row['Banco']."'>";
            echo "<label class='control-label'>Observaciones</label>";
            echo "<input type='text' id='Observaciones' name ='Observaciones' class='form-control Input col-sm-1' placeholder='' value='".$row['Observaciones']."'>"; 
    }
  ?>

This is the page when table "cuentas" doesn´t have any info but table "empleados" does.

This is the page when table "cuentas" has data

Again I´d like to see the results of the first table even if the second one has empty fields.

score 1 · Accepted Answer · answered Jun 21 '18 at 18:03

You need a left join instead of inner join, Also move your cuentas.Status filter in join part, Doing this you will get empleados records if there are no associated rows found in cuentas table

$this->db->select('empleados.Interno, empleados.Curp, empleados.Nombre, empleados.A_Paterno, empleados.A_Materno, cuentas.Clabe, cuentas.Banco, cuentas.Observaciones, cuentas.Status');
$this->db->from('empleados');
$this->db->join('cuentas',"cuentas.Interno = empleados.Interno AND cuentas.Status !='I'", 'left');
$this->db->where('empleados.Interno', $Interno);
$q = $this->db->get();

@AdrianJimenez no worries just have a look at [What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) — M Khalid Junaid, Jun 21 '18 at 19:11

Query doesn´t show results if some columns are empty using codeigniter

1 Answers1