Help with join query

I'm having problems with a join query.

I want to have a set of results if a user is logged in and another set of results consisting of two tables if they are not logged in.

I have this query

Code:

$this->db->from('projects');

      // If specific user projects
      if($this->session->userdata('user_id')) :

          $this->db->where('user_id', $this->session->userdata('user_id'));

      // Otherwise, public projects with user data
      else :

           $this->db->join('users', 'users.id = projects.user_id');

      endif;      

      $this->db->where('status', $status);

      $this->db->limit($limit, $offset);

      $this->db->order_by('id', 'desc');

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

I'm getting a MySQL ambiguous error and don't know how to fix this.

Help appreciated, agrc

I'm going to guess that you have an 'id' field in both tables. The ambiguous error means that you've used a column name that appears in more than one table and it doesn't know where to look, so make sure to call out the table with the ambiguous column and you'll be fine.

Code:

$this->db->order_by('projects.id', 'desc');

Support Development •  CodeIgniter 4 Foundations • Practical CodeIgniter 3  • CodeIgniter Tutorials

I'm going to guess that you have an 'id' field in both tables. The ambiguous error means that you've used a column name that appears in more than one table and it doesn't know where to look, so make sure to call out the table with the ambiguous column and you'll be fine.

Code:

$this->db->order_by('projects.id', 'desc');

Thanks for the help, that did the trick. I'll remember this now.

Glad it worked.

Support Development •  CodeIgniter 4 Foundations • Practical CodeIgniter 3  • CodeIgniter Tutorials