Codeigniter how to echo multiple INNER JOIN from same table

Question

I've got two tables movie and director, in movie's table I've got two fields: 'director1' and 'director2'.

With inner join I've match the two table but when I do a foreach of the field 'name' and 'surname' of 'director' I've got only one result, the last one.

This is my model:

function getLastMoviesId($id)
    {
        $this->db->select('*');
        $this->db->from('movie as m');
        $this->db->join('director as d1', 'd1.id = m.director1');
        $this->db->join('director as d2', 'd2.id = m.director2');
        $this->db->where('m.id', $id);
        $query = $this->db->get();
        if($query->num_rows() > 0){
          return $query->result();
        } else {
          return 0;
        }   
    }

This is my view:

<?php foreach ($archivepage as $m): ?>
  <?= html_entity_decode($m->name) ?>
  <?= html_entity_decode($m->surname) ?>
<?php endforeach ?>

Answer 1

Your fields values are being over-written as it contains the same names, use something like this instead.

$this->db->select('m.*, d1.name d1name, d1.surname d1surname, d2.name d2name, d2.surname d2surname')
$this->db->from('movie as m');
$this->db->join('director as d1', 'd1.id = m.director1', 'left');
$this->db->join('director as d2', 'd2.id = m.director2', 'left');
$this->db->where('m.id', $id);

in your view now you can access the values like this

for first one

->d1name ->d1surname 

for second one

->d2name ->d2surname