好的,所以我正在从事我的第一个php/mysql项目,我是从一个软件职位来的。我正在学习codeigniter,我已经计算出这个mysql加入将基于user.id获得我的朋友状态,我如何将我的所有帖子添加到其中?我必须进行and查询吗?
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
任何帮助都非常感谢
codeigniter中的Join查询可以写成:
$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();
或
$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");
好吧,CodeIgniter通过ActiveRecord抽象了DB交互(所以编写完整的SQL查询字符串实际上是不必要的)。他们比我解释得更好:
http://ellislab.com/codeigniter/user-guide/database/active_record.html
为什么不在友谊表中添加每个用户作为自己的追随者呢?所以在注册时,您可以将它们添加到用户表和友谊表中。
用户1跟随用户1等
您也可以在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();