在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之类的明细值不要混用。

10-05 21:08