Compare columns from multiple tables in one query PHP/SQL

Question

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.

mithrandir · Accepted Answer · 2015-01-29 09:58:07Z

1

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.

answered Jan 29, 2015 at 9:58

mithrandir

7381 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare columns from multiple tables in one query PHP/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related