php - 如何在两个表中随机排列帖子？

Question

我正在开发一个帖子系统..
因此，我必须显示用户的 friend 以及用户参与的群组的帖子。
这是我显示帖子的代码..

<?php
  $sql = "SELECT * FROM posts WHERE uploader_id=:friend_id ORDER BY id DESC";
  $query = $db->prepare($sql);
  $query->execute(array(
     ":friend_id" => $friend_id
  ));
  $rows = $query->fetchAll(PDO::FETCH_ASSOC);
  foreach ($rows as $row) {
      $name = $row['name'];
      echo "POST BY $name";
  }
  $sql = "SELECT * FROM group_posts WHERE id=:member_group ORDER BY id DESC";
  $query = $db->prepare($sql);
  $query->execute(array(
     ":member_group" => $group_id
  ));
  $rows = $query->fetchAll(PDO::FETCH_ASSOC);
  foreach ($rows as $row) {
     $name = $row['name'];
     echo "POST BY $name";
  }
?>

现在，我希望以一种方式对所有这些帖子进行排序，使 post 表和 group_posts 表中的所有帖子都按降序显示。

更新

我将代码编辑为这个..
我发现在对我的帖子系统进行编码之前我必须首先对其进行编码..

<?php
  $sql = "SELECT * FROM friends WHERE user_one=:me OR user_two=:me2 UNION SELECT * FROM group_members WHERE member_id=:me3";
  $query = $db->prepare($sql);
  $query->execute(array(
      ":me" => $my_id,
      ":me2" => $my_id,
      ":me3" => $my_id
  ));
  $rows = $query->fetchAll(PDO::FETCH_ASSOC);
  foreach ($rows as $row) {
     $user_one = $row['user_one'];
     $user_two = $row['user_two'];
     $group_id = $row['group_id'];
     if ($user_one == $my_id) {
         $friend_id = $user_two;
     } else {
         $friend_id = $user_one;
     }
     echo $friend_id . "<BR>" . $group_id;
  }
?>

现在，问题来了..这成功打印了 $friend_id，但是在打印 $group_id 时显示了 undefined index “group_id”。
我已检查所有字段是否正确。

Answer 1

尝试仅使用一个带有 UNION 的查询

SELECT *
FROM (
  SELECT name, id FROM posts WHERE uploader_id=:friend_id
  UNION
  SELECT name, id FROM group_posts WHERE id=:member_group
) p
ORDER BY p.id DESC

请注意，您的内部查询必须以相同的顺序返回相同数量的列(我认为也具有相同的名称/别名)。