How to JOIN 3 tables with CodeIgniter [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed last month.

Improve this question

I started a new project with CodeIgniter and I create three tables for users and posts and likes like this

user table has these records

User_ID
FullName
UserName

post table has these records

Post_ID
Title
User_ID

that user_id field save creator user_id

and like table

Like_ID
User_ID
Post_ID

I want to join this three tables and I can do it but I need join two result like this and I don't know what can I do

  $this->db->select('*');
  $this->db->from('Post');
  $this->db->join('User', 'User.User_ID = Post.User_ID', 'left');
  $query = $this->db->get();
  return $query->result();

and this

  $this->db->select('*');
  $this->db->from('Like');
  $this->db->where('User_ID' = $id);
  $query = $this->db->get();
  return $query->result();

and then join these results with together.

What do you want exactly ? can you give dummy data to explain what you want to obtain please ? You can do it with a single SQL query no ? — Didi
– Didi, Commented Jun 22, 2016 at 14:34
What data exactly you want ? and what are the fields to compare ? — Anand Jain
– Anand Jain, Commented Jun 22, 2016 at 14:35
I need join the first result with second result or I need select all post and I understand current user like which one post or not — Amir Nahravan
– Amir Nahravan, Commented Jun 22, 2016 at 14:47
@AmirNahravan can you elobarate with what you expect as answer. Example is better. — Abdulla Nilam
– Abdulla Nilam, Commented Jun 22, 2016 at 14:57
Should we presume that you want to join the post, user, and like tables and then merely count the number of related records in the like table? See also: CodeIgniter query builder to JOIN, GROUP BY, and COUNT() rows from one table and codeigniter join count another table and Codeigniter count associated records between 2 tables — mickmackusa
– mickmackusa ♦, Commented Oct 23 at 3:59

Abhishek Sharma · Accepted Answer · 2016-06-22 15:28:09Z

5

You can join multiple tables in single codeigniter query

$this->db->select('*');
$this->db->from('Post');
$this->db->join('User', 'User.User_ID = Post.User_ID', 'left');
$this->db->join('Like','Like.Post_ID = Post.Post_ID AND User.User_ID = $id','left');
$query = $this->db->get();
return $query->result();

answered Jun 22, 2016 at 15:28

Abhishek Sharma

3,3501 gold badge23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shadow · Accepted Answer · 2016-06-22 15:16:30Z

1

If I understand your question correctly, you would like to determine which posts the current user likes. The sql statement would look like sg like the following:

select p.*, u.*, l.user_id as like_user_id
from post p
inner join user u on u.user_id=p.user_id    -- I do not think you need a left join here
left join `like` l on p.post_id=l.post_id and l.user_id=$id

I the like table is left joined to the post table using the common post_id fields. In case l.user_id is null, then the current user does not like the post. Based on the above query and your 1st codeigniter code, you should be able to create the codeigniter version of this query.

answered Jun 22, 2016 at 15:16

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Collectives™ on Stack Overflow

How to JOIN 3 tables with CodeIgniter [closed]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related