从已删除的用户SQL函数中获取帖子(Get posts from deleted users SQL function)

从已删除的用户SQL函数中获取帖子(Get posts from deleted users SQL function)

您好我是Sql的新手，我正在重新构建一个简单的PHP仪表板。 我有mysql数据库，我有这个查询的问题。

public function get_posts($thread_id, $start, $limit) { $sql = "SELECT a.*, b.username, b.id as user_id FROM ".TBL_POSTS." a, ".TBL_USERS." b WHERE a.thread_id = ".$thread_id." AND a.author_id = b.id ORDER BY a.date_add ASC LIMIT ".$start.", ".$limit; return $this->db->query($sql)->result();

可能发生的情况是a.author id不等于b.id，因为用户被删除了。 我需要改进我的sql以显示已删除用户的帖子，并显示由删除用户发布的一些信息。

谢谢

Hello I am quite new in Sql and I am remaking one simple PHP dashboard. I have mysql database and I have problem with this query.

public function get_posts($thread_id, $start, $limit) { $sql = "SELECT a.*, b.username, b.id as user_id FROM ".TBL_POSTS." a, ".TBL_USERS." b WHERE a.thread_id = ".$thread_id." AND a.author_id = b.id ORDER BY a.date_add ASC LIMIT ".$start.", ".$limit; return $this->db->query($sql)->result();

The situation which can happen is that a.author id is not equal to b.id because user was deleted. I need to improve my sql to show posts of deleted users and show some information that post is by deleted user.

Thank you

最满意答案

请尝试以下代码。 它选择表b中的所有行并查找表a中的所有匹配项，或者如果a中没有对应的行，则在a的所有字段中放置NULL。

SELECT a.*, b.username, b.id as user_id FROM ".TBL_POSTS." a LEFT JOIN ".TBL_USERS." b ON a.author_id = b.id WHERE a.thread_id = ".$thread_id

Try the code below. It chooses all the rows in table b and finds all the matches in table a or puts NULL in all fields of a if there is no corresponding row in a.

SELECT a.*, b.username, b.id as user_id FROM ".TBL_POSTS." a LEFT JOIN ".TBL_USERS." b ON a.author_id = b.id WHERE a.thread_id = ".$thread_id