在MySQL中,我使用以下三个表(它们的字段在其标题后列出):
评论:
commentid loginid submissionid comment datecommented
登录:
loginid username password email actcode disabled activated created points
提交:
submissionid loginid title url displayurl datesubmitted
我想显示给定“用户名”的“ datecommented”和“ comment”,其中“ username”等于一个名为
$profile
的变量。我还想使“注释”成为http://www...com/.../comments/index.php?submission='.$rowc["title"].'&submissionid='.$rowc["submissionid"].'&url='.$rowc["url"].'&countcomments='.$rowc["countComments"].'&submittor='.$rowc["username"].'&submissiondate='.$rowc["datesubmitted"].'&dispurl='.$rowc["displayurl"].'
的超链接,其中countComments等于COUNT(c.commentid)
,而$rowc
是下面列出的查询的一部分。我尝试使用下面的代码做我想做的事,但是没有用。我如何更改它以使其达到我想要的效果?
提前致谢,
约翰
$sqlStrc = "SELECT
s.loginid
,s.title
,s.url
,s.displayurl
,s.datesubmitted
,l.username
,l.loginid
,s.title
,s.submissionid
,c.comment
,c.datecommented
,COUNT(c.commentid) countComments
FROM
submission s
INNER
JOIN
login l
ON
s.loginid = l.loginid
LEFT OUTER
JOIN
comment c
ON
c.loginid = l.loginid
WHERE l.username = '$profile'
GROUP
BY
c.loginid
ORDER
BY
s.datecommented DESC
LIMIT
10";
$resultc = mysql_query($sqlStrc);
$arrc = array();
echo "<table class=\"samplesrec1c\">";
while ($rowc = mysql_fetch_array($resultc)) {
$dtc = new DateTime($rowc["datecommented"], $tzFromc);
$dtc->setTimezone($tzToc);
echo '<tr>';
echo '<td class="sitename3c">'.$dtc->format('F j, Y &\nb\sp &\nb\sp g:i a').'</a></td>';
echo '<td class="sitename1c"><a href="http://www...com/.../comments/index.php?submission='.$rowc["title"].'&submissionid='.$rowc["submissionid"].'&url='.$rowc["url"].'&countcomments='.$rowc["countComments"].'&submittor='.$rowc["username"].'&submissiondate='.$rowc["datesubmitted"].'&dispurl='.$rowc["displayurl"].'">'.stripslashes($rowc["comment"]).'</a></td>';
echo '</tr>';
}
echo "</table>";
最佳答案
您是要显示特定用户的最后10条评论吗?在这种情况下,请删除GROUP BY c.loginid
子句。您的where子句已从该特定用户选择了注释。分组依据将尝试将它们聚合为一行。聚合和s.url之类的明细值不要混用。