php - 按日期时间组织 MySQL 结果

Question

使用 PHP，我试图找到一种方法来组合多个 MySQL 查询的结果，并将它们放入一个数组中，该数组按它们的 DateTime 时间戳排序，该时间戳在数据库中保存在“timestamp”下。

我的代码:

        $reactions = DB::query('SELECT * FROM reactions WHERE poster_id=:userid', array(':userid' => $userid));
        $comments = DB::query('SELECT * FROM comments WHERE poster_id=:userid', array(':userid' => $userid));
        $mentions = DB::query('SELECT * FROM comments WHERE FIND_IN_SET(:users , users)', array(':users' => $userid));
        $postMentions = DB::query('SELECT * FROM posts WHERE FIND_IN_SET(:users , users)', array(':users' => $userid));

编辑:更新代码

$reactions = "'SELECT timestamp as date FROM reactions WHERE poster_id=:userid', array(':userid' => $userid)";
        $comments = "'SELECT timestamp as date FROM comments WHERE poster_id=:userid', array(':userid' => $userid)";
        $mentions = "'SELECT timestamp as date FROM comments WHERE FIND_IN_SET(:users , users)', array(':users' => $userid)";
        $postMentions = "'SELECT timestamp as date FROM posts WHERE FIND_IN_SET(:users , users)', array(':users' => $userid)";

        $sql = 'SELECT timestamp FROM ('.$reactions.' UNION ALL '.$comments.' UNION ALL '.$mentions.' UNION ALL '.$postMentions.') as combined order by timestamp desc';

        $result = DB::query($sql, array());

        print_r($result);

Answer 1

您可以通过 UNION 实现相同的目的。试试下面的代码片段...

$reactions = 'SELECT reactions.date as date FROM reactions WHERE poster_id=:userid', array(':userid' => $userid);
$comments = 'SELECT comments.date as date FROM comments WHERE poster_id=:userid', array(':userid' => $userid);
$mentions = 'SELECT comments.date as date FROM comments WHERE FIND_IN_SET(:users , users)', array(':users' => $userid);
$postMentions = 'SELECT posts.date as date FROM posts WHERE FIND_IN_SET(:users , users)', array(':users' => $userid);

$sql = SELECT combined.date from (".$reactions." UNION ALL ".$comments." UNION ALL ".$mentions." UNION ALL ".$postMentions.") as combined order by combined.date desc;