how can i get all post for me and for user who i follow him

Question

i have 3 table.

1st is for user info. 2nd is for post where user insert to it and relation with user table by user_id. 3rd is for user who i follow him and relation with user table by user_id.

now in home page i need to show all posts where i insert on it and where user i follow him insert into table post.

i will try to make this MySQL by :-

SELECT * FROM users , 
        (SELECT * FROM events where ev_user_id in 
        (
        ( select * from follow where follow.fo_user_id = '".$user_id."' )
         ,  '".$user_id."'
         )
         ) as post
        where post.ev_user_id = users.id
        order by post.ev_date DESC limit $to , $from

where $user_id is id for user.

here i get error that:-

Operand should contain 1 column(s)

if i follow one user its work, but when i follow more than one user, its display above error.

how can i get all post for me and for user who i follow him

==================================== events table is the table for post

CREATE TABLE `events` (
  `ev_id` int(11) NOT NULL auto_increment,
  `ev_user_id` int(11) NOT NULL,
  `ev_type` varchar(222) NOT NULL,
  `ev_text` text NOT NULL,
  `ev_pic` varchar(222) NOT NULL,
  `ev_date` varchar(22) NOT NULL,
  PRIMARY KEY  (`ev_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=211 ;

table for user which i follow him

   CREATE TABLE `follow` (
      `fo_id` int(11) NOT NULL auto_increment,
      `fo_user_id` int(11) NOT NULL,
      `fo_user_id_follow` int(11) NOT NULL,
      `fo_date` varchar(22) NOT NULL,
      PRIMARY KEY  (`fo_id`),
      UNIQUE KEY `fo_user_id` (`fo_user_id`,`fo_user_id_follow`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

table for user info

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `fullname` varchar(222) NOT NULL,
  `username` varchar(60) NOT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(50) NOT NULL,
  `address` varchar(300) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `skype` varchar(20) NOT NULL,
  `facebook` varchar(40) NOT NULL,
  `msn` varchar(90) NOT NULL,
  `mobile` varchar(20) NOT NULL,
  `more` text NOT NULL,
  `time` varchar(50) NOT NULL,
  `lastlogin` varchar(50) NOT NULL,
  `role_id` tinyint(1) NOT NULL default '2',
  `code` varchar(7) NOT NULL,
  `active` tinyint(4) NOT NULL default '3',
  `wieght` int(11) NOT NULL,
  `tall` int(11) NOT NULL,
  `birthdate` varchar(20) NOT NULL,
  `gender` varchar(5) NOT NULL,
  `fat` int(11) NOT NULL,
  `fittnes` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `pic` varchar(222) NOT NULL,
  `regdate` varchar(22) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=396 ;

Answer 1

This should get you the posts and user info of the users your $user_id follows ordered by descending date.

SELECT * FROM events
JOIN follow ON events.ev_user_id = follow.fo_user_id
JOIN users ON events.ev_user_id = user.id
WHERE follow.fo_user_id_follow = '".$user_id."'
ORDER BY events.ev_date DESC
LIMIT $to , $from

Is this what you wanted? not completelly sure.

EDIT: to add also your own posts as well as the ones form the users you follow.

SELECT * FROM events
JOIN follow ON events.ev_user_id = follow.fo_user_id
JOIN users ON events.ev_user_id = user.id
WHERE follow.fo_user_id_follow = '".$user_id."'
    OR events.ev_user_id = '".$user_id."'
ORDER BY events.ev_date DESC
LIMIT $to , $from

EDIT: the enquirer's exact solution, Daren had understood the follow relationship reversed.

SELECT * FROM events
JOIN follow ON events.ev_user_id = follow.fo_user_id_follow
JOIN users ON events.ev_user_id = users.id
WHERE follow.fo_user_id = '".$user_id."'
    OR events.ev_user_id = '".$user_id."'
ORDER BY events.ev_date DESC
LIMIT $to , $from"

Answer 2

what about the following?

SELECT * FROM users 
INNER JOIN follow ON fo_user_id=users.id
INNER JOIN events ON ev_user_id IN (users.id,fo_user_id_follow)
WHERE users.id='$user_id' ORDER BY post.ev_date DESC LIMIT $to , $from

You will probably get too many columns as it will list all the columns from all joined tables.

1. Edit: added ' around user_idjust in case users.id is a varchar column ...
2. Edit: widened criterion for user_id: ev_user_id IN (users.id,fo_user_id_follow)

rewrite:

SELECT * FROM follow 
  INNER JOIN users ON users.id=ev_user_id IN (users.id,fo_user_id_follow)
  INNER JOIN events ON ev_user_id=users.id
WHERE users.id='$user_id' ORDER BY post.ev_date DESC LIMIT $to , $from

This should give you information about all users (including yourself) and all posts from these users that follow the user identified by $user_id. Probably not a good choice either, since you will have the full user information given again and again for each post.

It would be better to have two queries and two result tables: 1. user info, 2. posts of these users.

SELECT * FROM user 
  WHERE users.id IN ( 
    SELECT fo_user_id_follow FROM follow WHERE fo_user_id=$user_id ) 
  OR users.id=$user_id

and

SELECT * FROM events 
  WHERE ev_user_id IN ( 
    SELECT fo_user_id_follow FROM follow WHERE fo_user_id=$user_id ) 
  OR ev_user_id=$user_id

Answer 3

I guess - You should provide specific column instead of * at the line - ( select * from follow where follow.fo_user_id = '".$user_id."' ) you could try with the below line - select fo_user_id_follow from follow where follow.fo_user_id = '".$user_id."'

Answer 4

The error message you're getting, is because you're using the IN operator against a subquery that returns more than one column. Maybe rewrite your SQL to something like this:

SELECT * FROM users , 
    (SELECT * FROM events where ev_user_id in 
    (
    ( select user_id from follow where follow.fo_user_id = '".$user_id."' )
     ,  '".$user_id."'
     )
     ) as post
    where post.ev_user_id = users.id
    order by post.ev_date DESC limit $to , $from