CODEWITHSUNDEEP
phpmysql
David Tkachuk
David Tkachuk

Reputation: 116

Get profile posts from friends

I'm making a small CMS, where you can have friends, and post on their walls. I'm making a page where you can view all the posts your friends have posted (on their profiles) however, I cannot get it to properly function.

Table: friends

+---------------+---------+------+-----+---------+----------------+
| Field         | Type    | Null | Key | Default | Extra          |
+---------------+---------+------+-----+---------+----------------+
| friend_id     | int(10) | NO   | PRI | NULL    | auto_increment |
| user1_id      | int(10) | NO   |     | NULL    |                |
| user2_id      | int(10) | NO   |     | NULL    |                |
| friends_since | int(10) | NO   |     | NULL    |                |
+---------------+---------+------+-----+---------+----------------+

Table: profile_posts

+----------------+---------+------+-----+---------+----------------+
| Field          | Type    | Null | Key | Default | Extra          |
+----------------+---------+------+-----+---------+----------------+
| post_id        | int(12) | NO   | PRI | NULL    | auto_increment |
| posted_user_id | int(12) | YES  |     | NULL    |                |
| user_id        | int(12) | YES  |     | NULL    |                |
| date_posted    | int(11) | YES  |     | NULL    |                |
| total_likes    | int(12) | YES  |     | NULL    |                |
| users_liked    | text    | YES  |     | NULL    |                |
| total_dislikes | int(12) | YES  |     | NULL    |                |
| users_dislikes | text    | YES  |     | NULL    |                |
| message        | text    | YES  |     | NULL    |                |
+----------------+---------+------+-----+---------+----------------+

Basically, if I want to determine if a user has posted on their profile as a status, the posted_user_id and user_id columns in the table profile_posts have to match (posted_user_id is the user's id of the post being posted on, and user_id is the user_id is the id of the person who posted that message).

However, I've tried multiple times to get friend's statuses.

SELECT *
FROM `friends` AS `f`
LEFT JOIN `profile_posts` AS `pp`
    ON `pp`.posted_user_id = `pp`.user_id
WHERE 
    `f`.user1_id != 2 OR
    `f`.user2_id != 2
ORDER BY `pp`.`date_posted` DESC
LIMIT 0,15;

(Hurray for not working! :D)

SELECT `f`.*,`pp`.*,
    IF(`f`.user1_id=2, `f`.user2_id, `f`.user1_id) AS `uid`
FROM `friends` AS `f`
LEFT JOIN `profile_posts` AS `pp`
    ON `pp`.posted_user_id =`uid`
WHERE 
    `f`.user1_id = `uid` OR
    `f`.user2_id = `uid`
ORDER BY `pp`.`date_posted` DESC
LIMIT 0,15;

I'm not sure how to do this honestly, It's starting to make me rage as I cannot figure how to do this. :/

Upvotes: 1

Views: 140

Answers (1)

David Tkachuk
David Tkachuk

Reputation: 116

Never mind, figured out how to do it. :D

EDIT:

SELECT 
    `f`.*,
    `pp`.*,
    `ai`.`name`,
    `ai`.`lastname`
FROM `friends` AS `f`
LEFT JOIN `profile_posts` AS `pp`
    ON
        `pp`.`posted_user_id` = `pp`.`user_id` AND
        `pp`.`user_id` = `f`.user1_id OR
        `pp`.`user_id` = `f`.user2_id
LEFT JOIN `account_info` AS `ai`
    ON `ai`.`u_id` = `pp`.`user_id`
WHERE
    `pp`.`user_id` = `pp`.`posted_user_id` AND
    `f`.user1_id = 1 OR
    `f`.user2_id = 1 AND
    `pp`.`user_id` = `pp`.`posted_user_id`
GROUP BY `pp`.post_id
ORDER BY `pp`.date_posted DESC
LIMIT 0,20;

Selects the current user's friends, then it grabs all profile posts of their friends and then joins the account information table to get the name, and other information.

I'm trying to see if I can make this smaller, if not better..

Upvotes: 1

Related Questions