How to sort and filter queries between two tables?

Question

I have two tables, one of posts (posts) and another of friends(Friends). In my script there is an area of feed where I would like to present the latest posts from the logged user’s friends.

But instead of bringing the latest posts in order, the result orders the list of friends and the limit of posts imposed in the query of the table "posts" bringing this amount to each user (friend).

Explaining: The result brings for example the 3 last posts of each friend, but if I have in the system 4 friends it presents the three last of the first (friend), after the second and so on instead of three of each but ordered by the last.

I saw examples on the internet but could not understand the logic of how to make the filtering by the table "Friends" does not interfere in the sorting of the table display "posts".

Could someone help me understand that? Thank you.

<?php
header ('Content-type: text/html; charset=UTF-8');
require "connect_db.php";

   $userID = $_SESSION['user_released'];

   $BuscaFriends = $pdo->query("SELECT * FROM friends WHERE userID LIKE '%$userID%'");
   $BuscaFriends->execute();
   if($BuscaFriends->rowCount() == ""){
?>

  <!-- Trecho HTML Caso não tenha amigos na tabela "friends" !!! -->

<?php
   }else{

   while($fetch = $BuscaFriends->fetch(PDO::FETCH_ASSOC)){
      $idFriend = $fetch['friendID'];
      $statusFriend = $fetch['status'];

      $BuscaPost = $pdo->query("SELECT * FROM posts WHERE autor LIKE '%$idFriend%' ORDER By id DESC LIMIT 3");
      $BuscaPost->execute();

      while($fetch = $BuscaPost->fetch(PDO::FETCH_ASSOC)){
          $autor =  $fetch['autor'];
          $data =  $fetch['data'];
          $ava = $fetch['autor_avatar'];
          $msgID = $fetch['id'];
          $msg = $fetch['content'];

          $Verific = $pdo->prepare("SELECT * FROM users WHERE `user` = :user");
          $Verific->bindParam(':user', $autor, PDO::PARAM_STR);
          $Verific->execute();

          while($fatch = $Verific->fetch(PDO::FETCH_ASSOC)){
              $user = $fatch['user'];
              $autorAvatar = $fatch['avatar'];
              $autorCover = $fatch['cover'];
          }
?>

   <!-- Trecho HTML que retorna resultados !!! -->

<?php
      }//Fecha "while" BuscaPost
   }//Fecha "while" BuscaFriends

   }//Fecho "else" amigos
?>

Answer 1

The problem is you’re making one select to find friends and then making another select individual per friend. The results come sorted by date, but as a query is made for each friend, each set comes sorted according to the posts of that friend.

You can solve all this with a simple join:

SELECT p.* FROM posts p
  JOIN friends f ON f.friendID = p.author
WHERE f.userID = 1
ORDER BY p.date DESC;

See in SQL Fiddle

This query will return all posts from id user friends 1.

If you also want to return friend information in the same query, just add a join the table user:

SELECT p.*, u.name as author_name FROM posts p
  JOIN friends f ON f.friendID = p.author
  JOIN users u ON u.id = f.friendID
WHERE f.userID = 1
ORDER BY p.date DESC;

See in SQL Fiddle

_{Since I don’t know the structure of your bank I did something basic just to give you an idea.}