CODEWITHSUNDEEP
phpmysqlcodeigniterjoin
user2665611
user2665611

Reputation: 9

php codeigniter mysql joins

Ok so im working on my first ever php/mysql project having come from a software position. I am learning codeigniter and i have worked out that this mysql join will get me friends statuses based on an user.id, how do i add in all my posts to that do i have to do an AND query?

select * from friendships
join users on users.`id` = friendships.`friend_id`
join statuses on statuses.`user_id` = users.id
where friendships.`user_id` = 2 
ORDER BY statuses.`id` desc

Any help greatly appreciated

Upvotes: 0

Views: 1765

Answers (4)

Aman Kumar
Aman Kumar

Reputation: 4547

You can also use this query in codeigniter

$query = $this->db->query("select * from friendships
                           join users on users.`id` = friendships.`friend_id`
                           join statuses on statuses.`user_id` = users.id
                           where friendships.`user_id` = 2 
                           ORDER BY statuses.`id` desc");
$result = $query->row_array();

Upvotes: 0

ChrisMJ
ChrisMJ

Reputation: 1620

Why not just add each user as a follower of themselves in your friendships table? So on registration you add them to users table and to the friendships table.

User 1 follows User 1 etc

Upvotes: 0

Girish Kumar Sinha
Girish Kumar Sinha

Reputation: 832

Join query in codeigniter can be written as:

$this->db->select("*");
$this->db->from("friendships");
$this->db->join("users","users.id = friendships.friend_id");
$this->db->join("statuses","statuses.user_id = users.id");
$this->db->where("friendships.user_id",2);
$this->db->order_by("statuses.id","desc");
$result=$this->db->get();

or

$this->db->select("*");
$this->db->join("users","users.id = friendships.friend_id");
$this->db->join("statuses","statuses.user_id = users.id");
$this->db->where("friendships.user_id",2);
$this->db->order_by("statuses.id","desc");
$result=$this->db->get("friendships");

Upvotes: 1

Malcolm Diggs
Malcolm Diggs

Reputation: 455

Well, CodeIgniter abstracts DB interaction through ActiveRecord (so writing full SQL query strings really isn't necessary). They do a better job of explaining it than I could:

http://ellislab.com/codeigniter/user-guide/database/active_record.html

Upvotes: 0

Related Questions