好吧,我将尝试重新解释这个问题,因为似乎没有人理解我的目的。现在是凌晨3点,所以这可能是我有点懈怠的时候……但我会尽力的。
我目前正在做一个post &评论部分,用户可以在自己或其他用户的墙上发表文章,因此,其他用户可以对这些帖子发表评论。和社交网络状态或者普通论坛是一样的。我有3张表…
Users
Statuses
Comments
Users包含标准的…
userid
username
first_name
last_name
photo
status包含关于每个用户发布的主要状态的任何信息,这些状态显示在用户配置文件中。因此,用户1可以在user2的个人资料上发布状态。这是状态表的设计…
status_id (auto-i)
user_id (The users ID whos profile the post was added too)
sender_id (The user who sent the post or wrote it)
date (Date/Time was sent)
rate (This doesn't matter for a moment)
comments (This will count all the comments and display the number)
status (The actual status written out)
这些表在我的脚本中工作得很好,它连接了两个表,并显示了用户信息(发布状态的人),如他们的个人资料照片和姓名等…这是我目前的脚本,没有任何问题…
//// GET STATUSES
$user_id = $profile_data['userid'];
$data = mysql_query("
SELECT
statuses.status_id,
statuses.user_id,
statuses.sender_id,
statuses.date,
statuses.rate,
statuses.comments,
statuses.status,
users.userid,
users.username,
users.first_name,
users.last_name,
users.photo
FROM
statuses
LEFT JOIN
users
ON
statuses.sender_id=users.userid
WHERE
statuses.user_id = '{$profile_data['userid']}'
ORDER BY
`statuses`.`date` DESC
") or die(mysql_error());
while($status = mysql_fetch_array( $data ))//added this
{
$statusid = $status['status_id'];
$date = $status['date'];
$rate = $status['rate'];
$comments = $status['comments'];
$userid = $status['user_id'];
$senderid = $status['sender_id'];
$statusbody = $status['status'];
$username = $status['username'];
$firstname = $status['first_name'];
$lastname = $status['last_name'];
$photo = $status['photo'];
?>
<form action="" method="POST" role="form" enctype="multipart/form-data" id="statusupdate" class="facebook-share-box">
<div class="share">
<div class="panel panel-default">
<div class="panel-heading"><a href="<? echo 'http://basecentre.co.uk/',$status["username"]; ?>"><img alt="" align="left" hspace="20" height="70" width="70" src="<? echo 'http://basecentre.co.uk/userimages/',$status["photo"]; ?>"> </a> <font size="+2"><i class="icon icon-comment-o"></i></font> <a href="<? echo 'http://basecentre.co.uk/',$status["username"]; ?>"><font size="+2"><?php echo $status['first_name']; ?> <?php echo $status['last_name'] ; ?></font></a> | <i class="icon icon-clock-o"></i> <a rel="tooltip" href="#" data-original-title="<? echo "". date('F j, Y, g:i a', strtotime($status['date']) + 60*60) .""; ?>"><?php echo "<strong>". date('j F', strtotime($status['date']) + 60*60) ."</strong>"; ?></a></div>
<div class="panel-body">
<div class="">
<?php echo $status['status']; ?>
</div>
</div>
<div class="panel-footer">
<div class="row">
<div class="col-md-7">
Comment | Like
</div>
</div>
</div>
</div>
</div>
</form>
</br>
<?
}
?>
我为注释建立了一个新表。所以我可以去一个用户的个人资料,阅读一个"状态",然后我可以添加评论…这是新表
comment_id (auto-i)
status_id (added depending on which status you comment on. If the comment is on status id #5, the same number will be sent to this to connect the comments with the correct statuses)
sender_id (the id of the user who is sending the comment which would be $_session['userid'];
date (date the comment was sent)
rate (Doesn't matter yet)
comment (the actual comment written out).
我需要以某种方式将新的注释表连接到其他两个表?我试着添加另一个"左连接",但没有工作?我不太擅长这种事。我的目标是让每个评论正确地显示其连接状态,按发布日期的顺序。最近在底部…与用户信息显示,所以你知道谁发表了评论。
希望这能给你更多的理解,我希望有人能帮助!!抱歉太长了,但你们要求更多的信息…谢谢!
编辑数据库记录:
状态表:
CREATE TABLE IF NOT EXISTS `statuses` (
`status_id` int(25) NOT NULL AUTO_INCREMENT,
`user_id` int(25) NOT NULL,
`sender_id` int(25) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`rate` int(25) NOT NULL DEFAULT '0',
`comments` int(25) NOT NULL DEFAULT '0',
`status` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=30 ;
--
-- Dumping data for table `statuses`
--
INSERT INTO `statuses` (`status_id`, `user_id`, `sender_id`, `date`, `rate`, `comments`, `status`) VALUES
(15, 11, 22, '2014-05-11 21:22:00', 0, 0, 'This is pretty damn cool! '),
(16, 11, 91, '2014-05-11 21:22:35', 0, 0, 'LOL how did you do this shit man?? Alll you gotta do now is add the comments ;) and likes! '),
(14, 11, 22, '2014-05-11 21:21:35', 0, 0, 'Hey budddy how are ya ? '),
(13, 11, 11, '2014-05-11 21:18:10', 0, 0, 'eerer'),
(11, 11, 11, '2014-05-11 21:10:33', 0, 0, 'Ho Ho!'),
(10, 11, 11, '2014-05-11 21:10:27', 0, 0, 'Hey hey ! '),
(28, 11, 11, '2014-05-12 00:47:02', 0, 0, 'LOL just another quick test ;) '),
(29, 22, 22, '2014-05-12 02:30:44', 0, 0, 'I should be able to delete this status... '),
(20, 11, 11, '2014-05-11 21:30:17', 0, 0, 'LOL WINNER'),
(21, 22, 11, '2014-05-11 23:31:18', 0, 0, 'Hey mate :D '),
(19, 11, 11, '2014-05-11 21:24:47', 0, 0, 'Not bad eh guys? ;P ');
评论表:
--
-- Table structure for table `comments`
--
CREATE TABLE IF NOT EXISTS `comments` (
`comment_id` int(100) NOT NULL AUTO_INCREMENT,
`status_id` int(25) NOT NULL,
`user_id` int(25) NOT NULL,
`sender_id` int(25) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`rate` int(25) NOT NULL DEFAULT '0',
`comment` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`comment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
--
-- Dumping data for table `comments`
--
INSERT INTO `comments` (`comment_id`, `status_id`, `user_id`, `sender_id`, `date`, `rate`, `comment`) VALUES
(1, 28, 11, 11, '2014-05-12 01:23:58', 0, 'Hmmm shall we see if we can get this working too!? Comments, here we come! '),
(2, 28, 11, 22, '2014-05-12 02:55:33', 0, 'This is not aidans comment! another username should appear!');
Users表:
CREATE TABLE IF NOT EXISTS `users` (
`userid` int(25) NOT NULL AUTO_INCREMENT,
`first_name` varchar(25) COLLATE latin1_general_ci NOT NULL,
`middle_name` varchar(25) COLLATE latin1_general_ci NOT NULL,
`last_name` varchar(25) COLLATE latin1_general_ci NOT NULL,
`email_address` varchar(50) COLLATE latin1_general_ci NOT NULL,
`username` varchar(25) COLLATE latin1_general_ci NOT NULL,
`password` varchar(255) COLLATE latin1_general_ci NOT NULL,
`photo` varchar(50) COLLATE latin1_general_ci NOT NULL DEFAULT 'default.png',
`date1` varchar(25) COLLATE latin1_general_ci NOT NULL,
`date2` varchar(25) COLLATE latin1_general_ci NOT NULL,
`date3` varchar(25) COLLATE latin1_general_ci NOT NULL,
`birthplace` varchar(50) COLLATE latin1_general_ci NOT NULL,
`gender` varchar(25) COLLATE latin1_general_ci NOT NULL,
`about` varchar(250) COLLATE latin1_general_ci NOT NULL DEFAULT 'This information has not yet been updated.',
`user_level` enum('0','1','2','3','4') COLLATE latin1_general_ci NOT NULL DEFAULT '0',
`signup_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`activated` enum('0','1') COLLATE latin1_general_ci NOT NULL DEFAULT '0',
`location` varchar(30) COLLATE latin1_general_ci NOT NULL,
`premium` enum('0','1') COLLATE latin1_general_ci NOT NULL DEFAULT '0',
`blocked` varchar(5) COLLATE latin1_general_ci NOT NULL DEFAULT '0',
`fr_alert` varchar(6) COLLATE latin1_general_ci NOT NULL DEFAULT '0',
`um_alert` varchar(6) COLLATE latin1_general_ci NOT NULL DEFAULT '0',
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Membership Information' AUTO_INCREMENT=92 ;
可能是这样的?不确定你的数据看起来像什么,所以它可能是错误的…连接发送评论的用户和状态id然后按评论DATE desc排序然后按状态DATE desc排序这样它就会给出最近帖子的最近评论
SELECT -- added alias to your tables so its easier to read
s.status_id,
s.user_id,
s.sender_id,
s.date,
s.rate,
s.comments as s_comment,
s.status,
u.userid as u_id,
u.username as u_name,
u.first_name as u_first,
u.last_name as u_last,
u.photo as u_photo,
o.userid as o_id,
o.username as o_name,
o.first_name as o_first,
o.last_name as o_last,
o.photo as o_photo,
c.comment as c_comment
FROM statuses s
LEFT JOIN users u ON s.sender_id=u.userid
LEFT JOIN comments c ON c.user_id = u.userid AND c.status_id = s.status_id
LEFT JOIN users o on o.userid = c.sender_id
WHERE s.user_id = '{$profile_data['userid']}'
ORDER BY c.date DESC, s.date DESC
试一试,有什么问题让我知道。另外,如果你可以用几行实际数据编辑你的帖子,比如一些带有几个帖子和状态的评论,就像一个虚拟用户,我可以自己测试并澄清(将更容易加载)
这个答案稍微改变了一些表的结构,但其背后的核心思想是相同的。
使用我在这里创建的示例http://sqlfiddle.com/#!2/c1fa6e/1
所以基本上你可以INNER JOIN(来自两个表的结果)status
表,对users
表两次,并使用AS
操作符为它们创建别名。这使您可以根据它加入用户以获得配置文件所有者信息,以及发布状态的人的信息。(下面的例子只显示了在配置文件所有者#1上发布的状态)
SELECT status.status_id,
profile_owner.username AS owner,
status_sender.username AS sender
FROM status
INNER JOIN users AS profile_owner
ON profile_owner.user_id = status.user_id
INNER JOIN users AS status_sender
ON status_sender.user_id = status.sender_id
WHERE profile_owner.user_id = 1;
结果
STATUS_ID OWNER SENDER
1 User 1 User 2
2 User 1 User 4
然后添加评论,您LEFT JOIN
评论表(可能并不总是有评论,在这种情况下,这些字段将为空),然后获得评论的用户详细信息,我们再次INNER JOIN
用户表,针对评论ID获得他们的详细信息
SELECT status.status_id,
profile_owner.username AS owner,
status_sender.username AS sender,
status.status,
status_comment.comment_body,
commentor.username AS commentor
FROM status
INNER JOIN users AS profile_owner
ON profile_owner.user_id = status.user_id
INNER JOIN users AS status_sender
ON status_sender.user_id = status.sender_id
LEFT JOIN status_comment
ON status.status_id = status_comment.status_id
LEFT JOIN users AS commentor
ON status_comment.commentor_id = commentor.user_id
WHERE profile_owner.user_id = 1;
给出这些结果
STATUS_ID OWNER SENDER STATUS COMMENT_BODY COMMENTOR
1 User 1 User 2 Test status on User1 by User2 User3 commenting on status by User2. User 3
1 User 1 User 2 Test status on User1 by User2 User4 commenting on status by User2. User 4
2 User 1 User 4 Test status on User1 by User4 (null) (null)
这显示User1在他们的个人资料上发布了2个状态,一个是User2发布的,有2条评论,另一个是User4发布的,没有评论。然后,您可以在PHP中解析这些结果并适当地显示。
一旦你把它分解,它就相当简单了。
您可以在这里查看所有的操作,并在sqlfiddle上自己玩。
Update:使用您编辑到您的帖子中的数据库信息,我创建了一个SQLFiddle,试图向您展示如何将其应用于您现有的数据库。
这是相当直接的,你只是似乎把我的例子翻译成你的代码略有错误。下面是修改了适当的表名后的正确查询:
SELECT statuses.status_id,
profile_owner.first_name AS owner,
status_sender.first_name AS sender,
statuses.status,
comments.comment,
commentor.first_name AS commentor
FROM statuses
INNER JOIN users AS profile_owner
ON profile_owner.userid = statuses.user_id
INNER JOIN users AS status_sender
ON status_sender.userid = statuses.sender_id
LEFT JOIN comments
ON statuses.status_id = comments.status_id
LEFT JOIN users AS commentor
ON comments.sender_id = commentor.userid
WHERE profile_owner.userid = 11;
这将提取用户id为11的个人资料上发布的所有状态。它还会提取这些状态可能拥有的任何评论。
**顺便说一句…**我不确定为什么你在comments
表中存储sender_id
和user_id
。您的评论已经与一个状态相关,因此您知道它是针对谁发布的,您只需要存储发表评论的人。
试着把每个实体想象成它自己的对象。
你有3个对象
- 用户
- <
- 状态/gh>
一个状态有一个发布它的用户(status.user_id
)和一个发布它的用户(status.sender_id
)。
一个评论有一个发布的状态(comments.status_id
)和发布它的用户(comments.sender_id
)。
不需要在注释表中再次存储status.userid
,因为您总是可以连接它以获得该信息。