mysql - 加入 2 个 MySQL 表以列出所有帖子并查找特定用户关注的用户(登录的 INN 用户)

Question

我的项目中有两个表。

POSTS 表

p_id     p_user_id      p_title     p_description
.................................................
1        1              Post 1      lorem ipsum * 1
2        1              Post 2      lorem ipsum * 2
3        2              Post 3      lorem ipsum * 3
4        3              Post 4      lorem ipsum * 4

追随者表

f_id     f_following_users_id    f_followed_users_id     f_date
...................................................................
1        2                       1                       2018-01-25
2        2                       3                       2018-01-25
3        3                       2                       2018-01-25

现在我想根据登录用户 ID 获取所有用户的列表。登录用户 ID 取自 $_SESSION['user_id']。

我想要的结果是这样的

第一种情况如果登录用户user_id为1，即$_SESSION['user_id'] = 1，或者第二张表中的following_users_id为1，结果应该是:

Array
(
[0] => Array
    (
        [p_id] => 1
        [p_user_id] => 1
        [p_title] => Post 1
        [p_description] => lorem ipsum * 1
        [f_id] => 
        [f_following_users_id] => 
        [f_followed_users_id] => 
        [f_date] =>
    )

[1] => Array
    (
        [p_id] => 2
        [p_user_id] => 1
        [p_title] => Post 2
        [p_description] => lorem ipsum * 2
        [f_id] => 
        [f_following_users_id] => 
        [f_followed_users_id] => 
        [f_date] => 
    )

[2] => Array
    (
        [p_id] => 3
        [p_user_id] => 2
        [p_title] => Post 3
        [p_description] => lorem ipsum * 3
        [f_id] => 
        [f_following_users_id] => 
        [f_followed_users_id] => 
        [f_date] => 
    )
    
)

数组的最后四个字段应该是空白的，因为 user_id 的用户还没有关注任何人

第二种情况:如果登录用户user_id为2，即$_SESSION['user_id'] = 2，或者第二张表中的following_users_id为2，结果应该是:

Array
(
[0] => Array
    (
        [p_id] => 1
        [p_user_id] => 1
        [p_title] => Post 1
        [p_description] => lorem ipsum * 1
        [f_id] => 1
        [f_following_users_id] => 2 
        [f_followed_users_id] => 1
        [f_date] => 2018-01-25
    )

[1] => Array
    (
        [p_id] => 2
        [p_user_id] => 1
        [p_title] => Post 2
        [p_description] => lorem ipsum * 2
        [f_id] => 1
        [f_following_users_id] => 2 
        [f_followed_users_id] => 1
        [f_date] => 2018-01-25
    )

[2] => Array
    (
        [p_id] => 3
        [p_user_id] => 2
        [p_title] => Post 3
        [p_description] => lorem ipsum * 3
        [f_id] => 
        [f_following_users_id] => 
        [f_followed_users_id] => 
        [f_date] => 
    )

)

前 2 个数组的前四个字段应填充关注者表的第一行，因为用户 ID 已关注用户 ID 1 的用户。第三个帖子的 4 行应为空白，因为用户 ID 2 没有' t 关注了 user_id 为 2 的用户**

第三种情况:如果登录用户user_id为3，即$_SESSION['user_id'] = 3，或者第二张表中的following_users_id为3，结果应该是:

Array
(
[0] => Array
    (
        [p_id] => 1
        [p_user_id] => 1
        [p_title] => Post 1
        [p_description] => lorem ipsum * 1
        [f_id] => 
        [f_following_users_id] => 
        [f_followed_users_id] => 
        [f_date] => 
    )

[1] => Array
    (
        [p_id] => 2
        [p_user_id] => 1
        [p_title] => Post 2
        [p_description] => lorem ipsum * 2
        [f_id] => 
        [f_following_users_id] =>  
        [f_followed_users_id] => 
        [f_date] => 
    )

[2] => Array
    (
        [p_id] => 3
        [p_user_id] => 2
        [p_title] => Post 3
        [p_description] => lorem ipsum * 3
        [f_id] => 3
        [f_following_users_id] => 3
        [f_followed_users_id] => 2
        [f_date] => 2018-01-25
    )

)

First 2 arrays 4 last column should be blank as User_id 3 haven't follow these posts users.但是第三个数组的最后四列应该被填充，因为 user_id 3 已经跟在 user_id 2 之后。

我已经搜索了所有的解决方案，但都是徒劳的。也许即使在 Stack Overflow 上我也找不到正确的帖子如何执行此操作。

我试过的 MySQL 查询是:

SELECT * FROM posts AS p LEFT JOIN followers AS f ON p.p_user_id=f.f_followed_users_id WHERE f.f_followed_users_id = p_user_id AND f.f_following_users_id = 1 ORDER BY p.p_id

但我没有得到我想要的结果。我试过左连接右连接外连接和内连接。但没有成功。我可以像我想要的那样加入这 2 个表吗？

Answer 1

我觉得您可以尝试循环而不是连接表格，因为您的关注者表格只有一行可以满足您的条件。

那么你为什么不这样尝试:

1) 从您的数据库中获取所有帖子。2)对于您检索到的每个帖子，根据您的条件从关注者表中获取数据。3)创建一个新数组。4)如果第2步返回结果，则将followers表数据与post一起合并到新数组中。5) else 合并 post 自身到新数组。

完成每个循环后，尝试打印它并使用检查数据，

echo "<pre>";
print_r($new_array);
exit;

试试这个，如果您需要任何进一步的帮助，请告诉我