Facebook type wall display Sql query

[jammyxml]

i need to display users posts/feed to their friend’s wall, similar to facebook but im not sure what should be my Sql query

here is my database structure

tbl_users
-id
-fullname

---

tbl_friendships
-user_key_id
-friend_keyid

---

tbl_feeds
-id
-user_key_id
-post
-image_name
-timestamp

what should be my sql query to display friend’s post to my wall? and my posts to friend’s wall?

[r937]

please, show us what you tried

[jammyxml]

im currently using this SQL only

“SELECT * FROM tbl_feeds WHERE user_key_id = ‘$viewing_userid’ order by id DESC”

displays only the posts of the user whose profile page is open

[r937]

try this –

SELECT tbl_feeds.id , tbl_friendships.friend_keyid , tbl_feeds.post , tbl_feeds.image_name , tbl_feeds.timestamp FROM tbl_friendships INNER JOIN tbl_feeds ON tbl_feeds.user_key_id = tbl_friendships.friend_keyid WHERE tbl_friendships.user_key_id = $viewing_userid ORDER BY id DESC

[jammyxml]

thanks for the query, i will test it and let you know.

[jammyxml]

SELECT tbl_feeds.id
, tbl_friendships.friend_keyid
, tbl_feeds.post
, tbl_feeds.image_name
, tbl_feeds.timestamp
FROM tbl_friendships
INNER
JOIN tbl_feeds
ON tbl_feeds.user_key_id = tbl_friendships.friend_keyid
WHERE tbl_friendships.user_key_id = $viewing_userid
ORDER
BY id DESC

im so sorry for late reply,

this sql query works fine to get friends feed, but this query should also display my own feed that i have posted.

when i visit my profile it only displays my friend’s feed and does not fetch my own feed.

and when i visit my friend’s profile then it only displays their friend’s feed not their own feeds.

what can be done to this situation?

[r937]

i gave you the query for your friends’ posts

for your own posts, that’s a separate query – and here’s a hint, it doesn’t require a join

[jammyxml]

@r937

i gave you the query for your friends’ posts

for your own posts, that’s a separate query – and here’s a hint, it doesn’t require a join

yes, i was using this query before to get my own feeds. But i need to show my friend’s feed and my own feeds with the same query. This is my problem im not able to write such complicated queries

“SELECT * FROM tbl_feeds WHERE user_key_id = ‘$viewing_userid’ order by id DESC”

[r937]

@jammyxml

But i need to show my friend’s feed and my own feeds with the same query.

you want to show both feeds in the same result?

take the query for the friend’s feed, and the query for your feed, make sure they are returning exactly the same columns, and just UNION them together

[jammyxml]

@r937

you want to show both feeds in the same result?

take the query for the friend’s feed, and the query for your feed, make sure they are returning exactly the same columns, and just UNION them together

i will use union and get back to you with the result

[jammyxml]

[quote="r937, post:9, topic:238879"]
take the query for the friend's feed, and the query for your feed, make sure they are returning exactly the same columns, and just UNION them together
[/quote]

using this query now and it seems to work very fine.
please confirm it this query is best one?

SELECT tbl_feeds.id
     , tbl_friendships.friend_keyid
     , tbl_feeds.user_key_id
     , tbl_feeds.post
     , tbl_feeds.image_name
     , tbl_feeds.timestamp 
  FROM tbl_friendships
INNER
  JOIN tbl_feeds 
    ON tbl_feeds.user_key_id = tbl_friendships.friend_keyid
 WHERE tbl_friendships.user_key_id = $viewing_userid 
 UNION SELECT id, null, user_key_id, post, image_name, timestamp FROM tbl_feeds WHERE user_key_id = '$viewing_userid' order by timestamp DESC

[r937]

@jammyxml

please confirm it this query is best one?

um…

[jammyxml]

@r937

um…

thank you very very much for your help