a three way join mysql tables and php ?

Question

user_table

 user_id | username
       1 | solomon
       2 | muna

message table

id    user_id| message
 1       1   | this is my first message
 2       1   | this is my seocnd message
 3       2   | this is muna messgae    

relationship table

  leader | follower
       1 | 2
         |    

what i want to do is a three table join to bring out muna's friend messages, currently muna is following solomon(as shown on the followers table),

so i want the display to be like this on mona homepage

Solomon "this is my my first message
----------------------------------
solomon "this is my second message"

p.s. this is just an example database, i just wanted to see how to approach the problem, and this is what i have attempted so far!

 select username, message, leader
    from user, message, relationship where user.user_id =notes.user_id 
and user.user_id = relationship.leader and where user_id = $_SESSION['user_id']

*the session id is muna which is user_id 2

Answer 1

I'm not really that sure what your question is. Does this look like an answer to it?

SELECT u.username,
       m.message 
FROM   user u
       JOIN message m
       ON     u.user_id =m.user_id
       JOIN relationship r
       ON     u.user_id = r.leader
WHERE  u.user_id          = $_SESSION['user_id']

Answer 2

heres one way of doing it

SELECT m.message,u.username FROM relationships r, messages m,users u WHERE m.user_id = r.leader AND r.leader = u.user_id AND u.user_id = 1

Spaced out

SELECT
    m.message,u.username #here you can use m.* to retrieve all data
FROM
    relationships r,     #Here you can define what tables you want to use
    messages m,          #within your query, always remember to do table_name letter
    users u              #this will stop ambiguity within the queries 
WHERE
    m.user_id = r.leader #make sure the message user id is the same as the leader id
AND
    r.leader = u.user_id #same as above just making the messages be filtered to the user_id 
AND
    u.user_id = 1        #your overall id will be here, if this was 2 it would affect WHERE, and AND above.

Within application

$query = mysql_query('SELECT m.message,u.username FROM relationships r, messages m,users u WHERE m.user_id = r.leader AND r.leader = u.user_id AND u.user_id = ' .  intval($_SESSION['user_id']));

while($row = mysql_fetch_assoc($query))
{
    echo sprintf("%s: %s",$row['username'],$row['message']);
    echo "\r\n--------------------------------------------";
}

This is tested and works fine on the following table structure.

---

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `message` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `messages` (`id`, `user_id`, `message`) VALUES
(1, 1, 'test'),
(2, 1, 'test 2'),
(3, 2, 'test 3');

CREATE TABLE IF NOT EXISTS `relationships` (
  `leader` int(11) NOT NULL,
  `follower` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `relationships` (`leader`, `follower`) VALUES
(1, 2);

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `users` (`user_id`, `username`) VALUES
(1, 'solomon'),
(2, 'muna');