CODEWITHSUNDEEP
phpmysqlsqllogic
Core
Core

Reputation: 335

How to select ONLY posts from friends and viewing user

I'm making a social plugin for my website, and I have a friends table that holds all accepted friend requests, and I need to display all posts from the users friend AND the users posts in order of the date, so I've tried this sql query:

SELECT DISTINCT `social_posts`.*, `social_friends`.*, `users`.* 
FROM `social_posts` 
JOIN `social_friends` 
ON `fUID` = '1' AND `friend` = `pUID` OR `pUID` = '1' 
JOIN `users` 
ON `friend` = `uid`
ORDER BY `date` DESC

Structure

CREATE TABLE `social_friends` (
  `fID` int(11) NOT NULL AUTO_INCREMENT,
  `fUID` int(11) NOT NULL,
  `friend` int(11) NOT NULL,
  PRIMARY KEY (`fID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE `social_posts` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `pUID` int(11) NOT NULL,
  `body` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

fUID is the users (viewing) user ID, and friend is the users friend, and pUID is the user ID of the user who made the post.

But this shows two of each post, even with SELECT DISTINCT, and I'm out of ideas on how to figure this out.

Upvotes: 0

Views: 355

Answers (1)

xkothe
xkothe

Reputation: 674

1 - Can you give as more info (the fields) of the tables? What is fUID and pUID

2 - Try and change 

ON `fUID` = '1' AND `friend` = `pUID` OR `pUID` = '1'

to ON friend = pUID and put fUID = 1 OR pUID = 1 in the WHERE clause

Upvotes: 1

Related Questions