CODEWITHSUNDEEP
phpmysqlcodeigniterjoin
Harun Anwar
Harun Anwar

Reputation: 113

Unable to get records by join with where clause

I have 2 tables. first users second post. without where clause I got all user post but I want to display only logged in user data. here is table structure for my tables. both tables has common value by user.id = post.user_id

First Table User            Second Table Post
ID                           id
NAME                         user_id
Username                     category_id
Password                     user_id
                             title
                             body

My Query to get all records but doesn't logged in user records

public function get_posts(){
  $this->db->order_by('posts.id', 'DESC');
  $this->db->join('categories', 'categories.id = posts.category_id');
  $query = $this->db->get('posts');
  return $query->result_array();
}

Upvotes: 0

Views: 63

Answers (3)

Girish Ninama
Girish Ninama

Reputation: 593

you can use it like this , just replace $user_id variable with your dynamic post user id value 

$this->db->select('a.*');
$this->db->from('user a');
$this->db->join('post b', 'b.user_id = a.id', 'left');
$this->db->where('a.id', $user_id);
$data_array = $this->db->get()->result_array();
return $data_array ;

Hope this will help you ....

Upvotes: 0

Anfath Hifans
Anfath Hifans

Reputation: 1598

try this,

public function get_posts()
{
    $this->db->select('*');
    $this->db->from('posts');
    $this->db->join('users', 'users.id = posts.user_id');
    $this->db->join('categories', 'categories.id = posts.category_id');
    $this->db->order_by('posts.id', 'DESC');
    $query = $this->db->get();
    return $query->result_array();
}

Upvotes: 0

Pradeep
Pradeep

Reputation: 9707

Hope this will help you :

Here $user_id is id of logged in user should be passed to the model from controller

public function get_posts($user_id)
{
   $this->db->select('*');
   $this->db->from('posts');
   $this->db->order_by('posts.id', 'DESC');
   $this->db->join('users', 'users.id= posts.user_id');
   $this->db->where('users.id',$user_id);
   $query = $this->db->get();
   return $query->result_array();
}

Upvotes: 3

Related Questions