How to sort and filter queries between two tables?

Question

How to sort and filter queries between two tables?

Navigation

#1 by (2 votes)

3

I have two tables, one of posts and one of friends. In my script there is a feed area where I would like to display the latest posts from the logged-in user's friends.

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

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

I have seen examples on the internet but I could not understand the logic of how to make filtering by the "friends" table not interfere with the ordering of the table view "posts".

Could anyone help me understand this? Thankful.

<?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
?>

php mysql sql pdo

asked by anonymous 13.02.2015 / 05:56

1 answer

How to load DataTable into a DataGrid? How to set the background color in multiple lines of a JTable?

score 2 · Accepted Answer

The problem is that you are making select to find friends and then making another select per friend. The results come sorted by date, but as a query is made for each friend, each set is sorted according to that friend's posts.

You can solve this all 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 user's posts of id 1 .

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

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

As I do not know the structure of your bank I did something basic just so you have an idea.