The Problem
I am trying to create a website, where users can post posts and then their friends can see these posts. Similar to facebook, twitter etc.
I got to the point where users can be friends and they can post things. However, I am stuck restricting the shown posts to just the user's friends' posts. At the moment every user can see every post.
I am using PDO in a MVC architecture.
The database structure
TABLE `friends` (
`friendship_id` int(11) NOT NULL AUTO_INCREMENT,
`user_one` int(11) NOT NULL,
`user_two` int(11) NOT NULL,
PRIMARY KEY (`friendship_id`),
FOREIGN KEY (user_one) REFERENCES users(user_id),
FOREIGN KEY (user_two) REFERENCES users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
In the table above, depending on who sent the friend request to who, 'user_one' can be either myself or my friend or 'user_two' can be myself or my friend.
TABLE `posts` (
`post_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`post_text` text NOT NULL,
`user_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
The Model
/**
* Get all posts from the suer's friends
* @return array an array with several objects (the posts)
*/
public static function getAllPosts()
{
$my_id = Session::get('user_id');
$database = DatabaseFactory::getFactory()->getConnection();
// get all of the user's friends
$friendsQuery = $database->prepare("
SELECT user_one FROM friends WHERE user_two = '$my_id';
SELECT user_two FROM friends WHERE user_one = '$my_id';
");
$friendsQuery->execute();
$friends = $friendsQuery->fetchAll();
$friendsQuery->closeCursor();
$query = $database->prepare("SELECT * FROM posts WHERE user_id = '$my_id' OR user_id = '$friends'");
$query->execute(array());
return $query->fetchAll();
}
In the above code my problem lies in the $friends variable. If I replace it manually with the user_id of my friend it works as intended.
E.g.:
"SELECT * FROM posts WHERE user_id = '$my_id' OR user_id = '1'";
The Controller
/**
* Gets all posts (of the user and his friends).
*/
public function index()
{
$this->View->render('post/index', array(
'posts' => PostModel::getAllPosts()
));
}
The View
<?php if ($this->posts) { ?>
<?php foreach($this->posts as $key => $value) { ?>
<p><?= htmlentities($value->post_text); ?></p>
<?php } ?>
<?php } ?>
Summary
I am not able to create a sql query, which just selects posts, which were posted by myself or my friends and does not select any other posts.
I would be very thankful, if somebody could help me out with this!
UPDATE
My model now looks like this:
/**
* Get all posts from the suer's friends
* @return array an array with several objects (the posts)
*/
public static function getAllPosts()
{
$my_id = Session::get('user_id');
$database = DatabaseFactory::getFactory()->getConnection();
// get all of the user's friends
$friendsQuery = $database->prepare("
SELECT user_one FROM friends WHERE user_two = '$my_id';
SELECT user_two FROM friends WHERE user_one = '$my_id';
");
$friendsQuery->execute();
$friends = $friendsQuery->fetchAll();
print_r($friends);
$friendsQuery->closeCursor();
foreach($friends as $friend)
{
$friend_ids[$friend->key] = $friend->val;
}
print_r($friend_ids);
$friendsSQL = implode(',',$friend_ids);
$query = $database->prepare("SELECT * FROM posts WHERE user_id = '$my_id' OR user_id IN('$friendsSQL')");
$query->execute();
return $query->fetchAll();
}
This is my output:
Array ( [0] => stdClass Object ( [user_one] => 1 ) )
Notice: Undefined property: stdClass::$key
Notice: Undefined property: stdClass::$val
Array ( [] => )
You can add all friends_id into an array and then use this code.
//$friends is an array.
$friendsSQL = implode(',',$friends);
$query = $database->prepare("SELECT * FROM posts WHERE user_id = '$my_id' OR user_id IN($friendsSQL)");
your $friends variable is being returned by a fetchAll() function so will probably be a list of results. Your then using that list as a string in the query which won't work. You need to extract the ID of the friend from the $friends list.
if you're using PDO then use the variable handling built into PDO ie:
$query = $database->prepare("SELECT * FROM posts WHERE user_id = :variable_ID OR user_id = '1'");
$query->bindValue( ':variable_ID', $my_id, PDO::PARAM_STR ] );
$query->execute();
You should note that $friendsQuery->fetchAll() will return an Array. Also, if PDO::FETCH_ASSOC is supplied as an additional argument [fetchAll(PDO::FETCH_ASSOC)], it would be an associative array.
The structure of the array could look like this :
Array ( [int] datasetNumber =>
Array (
[string] "friendship_id" => [int] 1,
[string] "user_one" => [int] 1,
[string] "user_two" => [int] 1
)
)
I gave some more Information about returning data from PDO Statements in this post.