php - SQL : Select all Posts from Followers

Question

我试图找到一种有效的方式来显示登录帐户持有人关注的人的所有帖子。

有两个关键表:

1- 帖子

表名:posts

id, account_name, published, body

2- 关注

表名:如下

id, account_name, followed_name

我正在尝试找到一种方法来显示所有被关注帐户的所有帖子。帖子和关注之间的联系是 Account_name .

我知道它可能是一个连接，但这是我构造 WHERE 子句的方式。到目前为止，我有以下内容(帐户名称是通过 $_SESSION['account_name'] 设置的):

$sql = "SELECT * FROM posts LEFT JOIN follows ON posts.account_name = follows.account_name WHERE  --- How would I only get the posts from the accounts being followed ?---"

我敢肯定这是一件很简单的事情，我的大脑感到筋疲力尽，似乎无法解决。

更新尝试在 PDO 中

此时返回 NULL，

$sql = "SELECT * FROM share_posts WHERE account_name IN (SELECT followed_name FROM $this->account_follows WHERE account_name = :account_name)";
    return $this->AC->Database->select($sql, array('account_name' => $account_name)); 

转到我的数据库类:

    public function select($sql, $array = array(), $fetch_mode = PDO::FETCH_ASSOC)
    {

        $stmt = $this->AC->PDO->prepare($sql);

        foreach ($array as $key => $value) 
        {
            $stmt->bindValue("$key", $value);
        }

        $stmt->execute();

        return $stmt->fetchALL($fetch_mode);

    }

即使登录的账号关注了其他账号，此时返回的数据也是NULL。

Answer 1

$account = $_SESSION['account_name'];

//do some sql injection checking on $account here

$sql = "SELECT * FROM posts WHERE account_name IN (SELECT followed_name FROM follows WHERE account_name='".$account."')";

这将获取帐户名称与您关注的人匹配的所有帖子。我不确定谁在关注谁，但在这种情况下，followed_name 是 account_name 关注的人。如果情况相反，请切换值

$sql = "SELECT * FROM posts WHERE account_name IN (SELECT account_name FROM follows WHERE followed_name='".$account."')";