Join Tables Relationships with SQL

[raphaelb]

I trying to create a facebook style news feed, where it display contents from your friend and yourself. At the moment I am really struggling, not sure if am doing it right.

 

I got a table called user_followers

 

CREATE TABLE IF NOT EXISTS `user_Followers` (

`user_follower_id` int(11) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL,

`follower_id` int(11) NOT NULL,

`date_followed` datetime NOT NULL,

PRIMARY KEY (`user_follower_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

 

and another one called usersActivity

 

CREATE TABLE IF NOT EXISTS `usersActivity` (

`activity_id` int(11) NOT NULL AUTO_INCREMENT,

`activity_identifier` enum('1','2','3') NOT NULL,

`user_identifier` int(11) NOT NULL,

`row_id` int(11) NOT NULL,

`timestamp` datetime NOT NULL,

PRIMARY KEY (`activity_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

 

the user_id stores the id of the user, who friends with/following another user, and the follower_id is the id of person being followed

 

What am trying to do is get follower_id of all users that member 1 is following, then get their activities plus member 1 activity and display it on page. by checking if user_identifier is member 1 or people his/her is following.

 

so far this is wat i got:

 

 

// Fetch userActivity data

$activites = mysql_query("SELECT a.activity_id, a.row_id, a.activity_identifier, b.follower_id FROM usersActivity a, user_Followers b WHERE a.user_identifier='$id' OR a.user_identifier=b.follower_id LIMIT 10"); 

 

I hope you can understand what i wrote above.

 

Any help appreciated

[raphaelb]

Sorry post the wrong code

 

 

// Fetch userActivity data
$activites = mysql_query("SELECT a.activity_id, a.row_id, a.activity_identifier, b.follower_id FROM usersActivity a INNER JOIN user_Followers b ON a.user_identifier = b.follower_id WHERE a.user_identifier='$id' LIMIT 0,10");

 

at the moment it only display member1 activity

[fenway]

I fail to see the problem -- do you have the queries working separately