Codeigniter Join Database Problem

Question

This is my model:

    function getGalleryListing()
{
    $this->db->select('photo.id,photo.src,photo.title,photo.user_id,users.id,users.username');
    $this->db->from('photo');
    $this->db->join('users', 'users.id = photo.user_id');
    $query = $this->db->get();
    return $query->result();
}

And my controller:

    function index()
{       
    $data['gallery_list'] = $this->Gallery_model->getGalleryListing();
    $data['content'] = $this->load->view('gallery/index', $data, true);
    if ($this->dx_auth->is_logged_in()) 
    {

        $this->load->view('template_login', $data);
    }
    else
    {
        $this->load->view('template', $data);
    }

}

The problem is, when I do <?= $gallery->id ?>, it keeps showing me the ID from USERS.ID instead of PHOTO.ID. Did I miss out something? Thanks for any help.

Answer 1

You are selecting two ids (from photo and users) and only the later is shown. You need to use alias with As to assign a different name to one of the ids and this way it will show you required id successfully. For example, your query should look like this:

select (photo.id) as photo_id,photo.src,photo.title,photo.user_id,users.id,users.username from photo inner join users on users.id = photo.user_id

Now <?= $gallery->photo_id ?> will give you the photo id and <?= $gallery->id ?> will give you userid.

Note: You can modify above query in CI's perspective or use as it is if you can. Thanks

Answer 2

I use DataMapper OverZealous Edition
If a user has one to many relationship declared to a photo then should you use the following:

Inside the user model:
$this->photo->get()->all

Otherwise something like:
$user = $this->user->where('id', 1)->first();
$photos = $user->photo->get()->all;

On a side note, are you sure the table isnt called photos, not photo

Answer 3

On the many instances that I've ran into issues with using CodeIgniter's JOIN function I ended up writing my own SQL command if you are familiar with SQL. That gives you better control than the join function itself.