Display my posts to friends only

[tp45]

Good day
am trying to display users’s posts for their friends only, this functionality works but it doesn’t work properly, here is my code

Here i get friends for a specific user

function getfriends(){
  $db = new Database(true);
  $my_id = $_SESSION['user_id'];
  $return_data = [];
  $db->query('SELECT * FROM friends WHERE (user_one = :my_id OR user_two = :my_id2)');
  $db->bind(':my_id', $my_id);
  $db->bind(':my_id2', $my_id);
  $all_users = $db->resultset();
  foreach($all_users as $row){
      if($row->user_one == $my_id){
          $db->query('SELECT * FROM users WHERE user_id = :user_id');
          $db->bind(':user_id', $row->user_two);
          $results = $db->resultset();
          array_push($return_data, $results);
      }else{
          $db->query("SELECT * FROM users WHERE user_id = :user_id1");
          $db->bind(':user_id1', $row->user_one);
          $results = $db->resultset();
          array_push($return_data, $results);
      }
  }
  //die(print_r($return_data,true));
  return $return_data;
}

Here if a user is in the friendlist i show their posts including the logged in user

function isMyFriend($userToCheck){
  $db = new Database(true);
  $user_id = $_SESSION['user_id'];
  $db->query('SELECT * FROM users WHERE user_id = :user_id');
  $db->bind(':user_id', $user_id);
  $row = $db->single();
  @$userna = $row->username;

  foreach (getfriends() as $key) {
    foreach ($key as $value) {
      $friendArray = $value->username;
    }
  }
  if ((strstr($friendArray,$userToCheck) || $userToCheck == $userna)) {
    return true;
  }else {
    return false;
  }

}

The current user has 3 friends but this shows current user posts and one friends posts as for other 2 it doesn’t show their posts. I tried putting the if statement inside the for-each loop but it still shows one friend post, is there any way i can iterate through the usernames to make their posts show?

I use this function like this:

if(isMyFriend($post->user_id)){
   //here i display posts
}

Please help
Thanks in advance

[anaror]

You can query all the friends of the user first and then query for all the posts of their friends,
or you can use subqueries to determine all of their friends and get the posts all at once.
Here’s a complete example that I probably spent too much time making (assuming the bound parameter equates to user id: 1)

CREATE DATABASE  IF NOT EXISTS `test_frieds_posts_example` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test_frieds_posts_example`;

--
-- Table structure for table `friends`
--

DROP TABLE IF EXISTS `friends`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `friends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_one` varchar(45) DEFAULT NULL,
  `user_two` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `friends`
--

LOCK TABLES `friends` WRITE;
/*!40000 ALTER TABLE `friends` DISABLE KEYS */;
INSERT INTO `friends` VALUES (1,'1','2'),(2,'1','0'),(3,'4','1'),(4,'1','5'),(5,'5','6');
/*!40000 ALTER TABLE `friends` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `posts`
--

DROP TABLE IF EXISTS `posts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `posts` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(45) DEFAULT NULL,
  `content` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `posts`
--

LOCK TABLES `posts` WRITE;
/*!40000 ALTER TABLE `posts` DISABLE KEYS */;
INSERT INTO `posts` VALUES (1,'1','post by 1'),(2,'2','post by 2'),(3,'3','post by 3'),(4,'4','post by 4'),(5,'5','post by 5'),(6,'6','post by 6');
/*!40000 ALTER TABLE `posts` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1),(2),(3),(4),(5);
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;


-- The actual query example:


SELECT 
    *
FROM
    posts
WHERE
    posts.user_id != 1
        AND (posts.user_id IN ((SELECT 
            user_one
        FROM
            friends
        WHERE
            (user_one = 1 OR user_two = 1)
                AND user_one != 1))
        OR posts.user_id IN ((SELECT 
            user_two
        FROM
            friends
        WHERE
            (user_one = 1 OR user_two = 1)
                AND user_two != 1)));

[tp45]

Thank you @anaror for your help.