php - 获取用户 friend 的帖子并按列的日期时间排序

Question

我有两个表，第一个表称为“帖子”，第二个表称为“ friend ”我想要获取“ friend ”表中存在“发布者”列的帖子(这意味着获取 friend 的帖子)，而不是我想按“帖子”表中找到的日期时间列对其进行排序。

我尝试过的是:

$fetch_fr_result = $conn->query("SELECT frid FROM friends WHERE username='$userusername'");
while ($fetch_fr_send = $fetch_fr_result->fetch(PDO::FETCH_OBJ)){
$fetch_fr_id = $fetch_fr_send->frid;

$fetch_fr_data_result = $conn->query("SELECT username FROM users WHERE id='$fetch_fr_id'");
$fetch_fr_data_send = $fetch_fr_data_result->fetch(PDO::FETCH_OBJ);
$fetch_fr_data_username = $fetch_fr_data_send->username;

$fetch_posts_result = $conn->query("SELECT * FROM posts WHERE publisher='$fetch_fr_data_username' ORDER BY orderdate DESC LIMIT 5");

while ($fetch_posts_send = $fetch_posts_result->fetch(PDO::FETCH_OBJ)){
$fetch_posts_msg = $fetch_posts_send->thetext;
$fetch_posts_time = $fetch_posts_send->time;
if (!empty($fetch_posts_msg)){
echo '<div class="posttext">' . $fetch_posts_msg . '</div> - ' . $fetch_posts_time;
}
}
}

这段代码的问题是:获取每个 friend ，然后获取 friend 的帖子，然后按时间排序，但我想按时间排序所有 friend 的帖子!

请告诉我该怎么做？

Answer 1

也许我误解了你的问题，但这可能会给你你想要的。如果没有，请告诉我出了什么问题，我会尽力解决它。

$fetch_result = $conn->query('
    SELECT 
        `p`.thetext,
        `p`.time
    FROM 
        `friends` as f,
        `users` as u,
        `posts` as p
    WHERE 
        `f`.frid = `u`.id /* join friends id with users table */
    AND 
        `p`.publisher = `u`.username /* join friends username with posts table */
    AND 
        `f`.username = "' . $userusername . '" 
    ORDER BY 
        `p`.orderdate DESC
');

我建议您使用 userid 作为其他表中的外键，而不是用户名。您应该更改数据类型以使用更适合的数据类型。您的表中有太多 varchar(1000)。