php - 从多个用户获取行并按日期排序显示它们

Question

我正在构建一个函数，该函数将从订阅表中获取用户。然后，这些用户用于获取他们的个人文章并以有序的方式显示它们。

我已经成功一半了。它将获取用户，然后输出用户的文章。但一次需要一个用户。我正在寻找一种方法来选择订阅表中的所有用户并获取所有用户，以便我可以同时显示它们，但按日期排序。

表格:

<小时/>

**Subscribe :** 
Lastviewed ( datetime ) 
Uniqueuser ( userid )
Subscription ( subscribed users id)

<小时/>

**Article:**
date_posted ( datetime )
uniqueuser ( users id)

<小时/>

这就是我得到的:

$sesname = $_SESSION['full_name'];
$sesid = $_SESSION['user_id'];

connectDB();
$sql ="select * from subscribe where uniqueuser = '$sesid'";
$rsd = mysql_query($sql);

while ($row = mysql_fetch_array($rsd))
{
    $subscription = $row['subscription'];
    $lastviewed = $row['lastviewed'];
    $substart = $row['substart'];
    $sql3 = "select * from article 
        where uniqueuser = '$subscription' 
        and date_posted > '$lastviewed' 
        order by date_posted 
        desc limit 0, 7";

    $rsd3 = mysql_query($sql3);
    $newarticles = mysql_num_rows($rsd3);

    while ($row3 = mysql_fetch_assoc($rsd3)) {
        $id  =$row3['id'];
        $title = $row3['title'];
        $uid = $row3['uniqueuser'];
        $sql2 = "SELECT * FROM users WHERE id = '$uid'";  
        $result2 = mysql_query($sql2);

        while ($row2 = mysql_fetch_assoc($result2)) {
            $name = $row2['user_name'];
            echo "<b><a>{$name}</a></b> wrote an <a>article</a><br>";
        }
    }
} //END SUBSCRIBE

输出:

User A wrote an article (1th july)
User A wrote an article (2th july)
User A wrote an article (3th july)
User A wrote an article (4th july)
User B wrote an article (1th july)
User B wrote an article (2th july)
User B wrote an article (3th july)
User B wrote an article (4th july)

它会一个接一个地对用户进行排序，即使用户 B 的文章已发布在用户 A 的帖子之间。

想要的输出:

User A wrote an article (1th july)
User B wrote an article (1th july)
User A wrote an article (2th july)
User B wrote an article (2th july)
User A wrote an article (3th july)
User B wrote an article (3th july)
User A wrote an article (4th july)
User B wrote an article (4th july)

那么，无论哪个用户编写了它，我如何将其按日期顺序排列并读取它？我认为我的问题是因为订阅查询上的 while 循环，但我不知道有任何其他方法可以做到这一点。任何帮助是极大的赞赏! =)

Answer 1

或多或少只需连接涉及的 3 个表并按 a.date_posted 排序

$sql2 = "select a.* , u.user_name, a.id as article_id, u.id as user_id 
    from subscribe s
    join article a on s.subscription = a.uniqueuser AND s.lastviewed < a.date_posted
    join user u on a.uniqueuser = u.id
    where s.uniqueuser = '$sesid'
    order by a.date_posted 
    desc limit 0, 7";

$result2 = mysql_query($sql2);

while ($row2 = mysql_fetch_assoc($result2)) {
    $name = $row2['user_name'];
    $date = $row2['date_posted'];
// fetch the columns having the same name using aliases
$a_id = $row2['article_id'];
$u_id = $row2['user_id'];

    echo "<b><a>{$name}</a></b> wrote an <a>article</a>({$date})<br>";
}

编辑

修复条件 s.lastviewed < a.date_posted

添加关于加入

两个或多个表之间的连接是具有有效/真实条件(ON 和 WHERE)的表中所有元素的所有组合的集合