php - mysql表和php的三种方式？

user_table

 user_id | username
       1 | solomon
       2 | muna

信息表

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

关系表

  leader | follower
       1 | 2
         |

我想要做的是一个三表联接，以显示muna的好友消息，当前muna正在关注所罗门（如followers表所示），

所以我希望在mona主页上的显示像这样

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

ps。这只是一个示例数据库，我只想看看如何解决该问题，这是我到目前为止所尝试的！

 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']

*会话ID是muna，是user_id 2

最佳答案

这是做这件事的一种方法

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

间隔开

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.

申请内

$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--------------------------------------------";
}

这已经过测试，并且可以在以下表结构上正常运行。

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');

关于php - mysql表和php的三种方式？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3688763/