Getting News Feed (Social Media)

Question

I am currently developing a social media app which includes users (you can add them as friend) and posts (text, image etc...)

I want to show to user 5 newest posts compared to user's posts and user's friends' posts when user enters to the app.

I know this is not the wisest method to achieve this but so far i did this. Query returned only one post while there are more and its not even newest nor oldest. I think the problem is in "OR" clause.

I get all sno's of friends of user and put in WHERE clause one by one with "OR" clause.

        //CURRENT FRIENDS
        $sql = 'SELECT current AS currents FROM friends WHERE sno = :sno';
        $query = $this -> conn -> prepare($sql);
        $query -> execute(array(':sno' => $usersSno));

        $friends = $query -> fetchObject() -> currents;

        //SELECT 5 NEWEST POST
        $sql = 'SELECT operationId FROM posts WHERE operationId = :operationId';
        $array = array(':operationId' => $usersSno);

        if(isset($friends)) {
            $ids = explode(",", $friends);
            for($i = 0; $i < count($ids); $i++) {
                $sql = $sql.' OR operationId = :operationId'.$i;
                $array[':operationId'.$i] = $ids[$i];
            }
        }

        if($from == null)
            $sql = $sql.' ORDER BY operationId DESC LIMIT 5';
        else {
            $sql = $sql.' AND operationId < :from ORDER BY operationId DESC LIMIT 5';
            $array[':from'] = $from;
        }

        $query = $this -> conn -> prepare($sql);
        $query -> execute($array);
        $result = $query -> fetchAll(PDO::FETCH_ASSOC);

User's sno is 1 and user has two friends which their sno's are 2 and 75.

echo $sql;

SELECT operationId FROM posts WHERE operationId = :operationId OR operationId = :operationId0 OR operationId = :operationId1 ORDER BY operationId DESC LIMIT 5

print_r($array);

Array
(
    [:operationId] => 1
    [:operationId0] => 2
    [:operationId1] => 75
)

Answer 1

I feel like a stupid for realizing my mistake right after starting a bounty for it. I should've use WHERE sno (since i've been getting posts from user's id [sno]) not WHERE operationId in $sql

So echo $sql; be like:

SELECT operationId FROM posts WHERE sno = :sno OR sno = :sno0 OR sno = :sno1 ORDER BY operationId DESC LIMIT 5

And print_r($array) is:

Array
(
    [:sno] => 1
    [:sno0] => 2
    [:sno1] => 75
)

At least i am happy to resolve this problem and even resolving it my own :)

EDIT: I noticed two logical error too in my sql query.

1- Comparing date string with operationId of post I am comparing posts with their dates (you can also compare with operationId of post) to see which one is newest. But in the code i compared operationId with $from variable (which is a date string).

So final code;

$sql = $sql.') AND createdAt < :from ORDER BY operationId DESC LIMIT 5';

2- OR clauses should be separeted with AND clause by parentheses.

start of parenthese

$sql = 'SELECT operationId FROM posts WHERE (sno= :sno';

end of parenthese

if($from == null)
    $sql = $sql.') ORDER BY operationId DESC LIMIT 5';
else {
    $sql = $sql.') AND createdAt < :from ORDER BY operationId DESC LIMIT 5';
    $array[':from'] = $from;
}