CODEWITHSUNDEEP
phpmysql
Axiom
Axiom

Reputation: 902

Compare columns from multiple tables in one query PHP/SQL

I'm trying to change my user's news feed to only show posts from friends, instead of from all users on the site. I managed to do so doing something like this: 

function friend_posts(){
global $session_user_id;
if(friends_exist($session_user_id) === true){
    $friends = mysql_query("SELECT * FROM `friendship` WHERE `user_id` = $session_user_id AND `pending` = 0");
    while($friend = mysql_fetch_array($friends)){
            $friendID = $friend['friend_id'];
            $posts = mysql_query("SELECT * FROM `posts` WHERE `userid` = $friendID ORDER BY `timestamp` DESC");
            while($post = mysql_fetch_array($posts)){
                $friendData = user_data($post['userid'],'username','first_name','last_name');
                echo $friendData['username'].": ".$post['status']."<br>";
            }
        }
    } else { 
    echo "Nothing to display. Try adding some friends!";
    }
}

However, this isn't that convenient. For example, if I want to paginate, I don't know how I'd even start to do that using something like this. Also if multiple users post, how would I sort them by 'timestamp' descending?

I'm guessing the only route I can take is accessing columns from multiple tables somehow, then sorting by the timestamp, which is stored in the posts table. The friendship table just has id, user_id, friend_id and pending.

How would I go about doing that? Is there an easier way to do what I'm trying to do?

I'm very new to SQL. I don't know too much other than inserting/deleting/updating.

Upvotes: 1

Views: 497

Answers (1)

mithrandir
mithrandir

Reputation: 738

You could use a single statement like this:

SELECT * FROM posts WHERE userid in 
(SELECT friend_id FROM friendship WHERE user_id = $session_user_id AND pending = 0) 
ORDER BY `timestamp` DESC

This way you get only the posts of the friends of the current user. If you also need data from the friendship table then use a join.

Upvotes: 1

Related Questions