EDIT: Got it!
SELECT `update`, `date` from updates, users_friends
WHERE updates.userID = users_friends.friendID
and users_friends.userID = "5"
and users_friends.status = "2"
I realised I was trying to call an update that didn't exist. In order to fix the problem I created a new update, this time written by user with id 6.
--
I'm building a social networking website. I wish to display a list of updates from friends. My database contains two tables to achieve this: updates and users_friends.
Here is my query:
SELECT 'update' from updates WHERE 'updates.userID' in (SELECT 'friendID' from users_friends WHERE 'users_friends.userID' = $myID and 'status' = 2);
(status value 2 means their friendship is approved)
--
-- Table structure for table updates
CREATE TABLE IF NOT EXISTS updates (
update text NOT NULL,
date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
userID int(11) NOT NULL,
PRIMARY KEY (date,userID),
KEY fk_updates_users1 (userID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table users_friends
CREATE TABLE IF NOT EXISTS users_friends (
userID int(11) NOT NULL,
friendID int(11) NOT NULL,
status int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (userID,friendID),
KEY fk_users_has_friends_users1 (userID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The problem I'm having is that I always get an empty set. I have added a sample update to the updates table with userID = 5 and a sample row to the users_friends table where user 6 is friends with user 5 and myID = 6.
Is my logic correct? What am I doing wrong?
I realised I was trying to call an update that didn't exist. In order to fix the problem I created a new update, this time written by user with id 6.