I typed as i was thinking, but didnt come up with a solution. Hopefully this can point you in the right direction. Might need a sub query in the last query example to get to want you want, but my brain isn't quite up to the task atm.
Its also possible its not the right direction ....
I think you need to rethink you tables, and add a third since you will need a many to many relationship between users and their friends
Table users:
id
username
name
<more fields if needed>
Table friends
user_id
friend_id
(these two fields would make up the primary key/unique combination)
Table my_page
id
user_id
page_owner
date_posted
text
In the friends table you store users.id in friends.user_id for the user, and users.id in friends.friend_id for all the friends of the current user. The tables could then look like this
Users
1, crowly, Crowly
2, p1, Person1
3, p2, Person2
4, p3, Person3
Crowly is friends with Person1 and 2, Person2 is friends with Crowly and Person3
Friends
1, 2
1, 3
2, 1
2, 4
The select would then look like this to list all the friends of one user, untested
SELECT u.name AS users_name, u2.name AS friends_name
FROM users AS u
INNER JOIN friends AS f ON u.id=f.user_id
INNER JOIN users AS u2 ON u2.id=f.friend_id
WHERE u.username='crowly'
should return:
Crowly Person1
Crowly Person2
Something along these lines
SELECT u.name AS users_name, u2.name AS friends_name, mp.date_posted , mp.text, mp.user_id
FROM my_page AS mp
INNER JOIN users AS u ON mp.user_id=u.id
INNER JOIN friends AS f ON u.id=f.user_id
INNER JOIN users AS u2 ON u2.id=f.friend_id
WHERE mp.user_id=1
but this will return all rows from my_page with user_id=1, which isnt exactly what you are after 🙁
