加入三张桌子

In MySQL, I am using the following three tables (their fields are listed after their titles):

comment:

commentid loginid submissionid comment datecommented

login:

loginid username password email actcode disabled activated created points

submission:

submissionid loginid title url displayurl datesubmitted

I would like to display "datecommented" and "comment" for a given "username," where "username" equals a variable called $profile. I would also like to make the "comment" a hyperlink to 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"].' where countComments equals COUNT(c.commentid) and $rowc is part of the query listed below.

I tried using the code below to do what I want, but it didn't work. How could I change it to make it do what I want?

Thanks in advance,

John

$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 &
b\sp &
b\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>";

Do you mean to show the last 10 comments from certain user? In that case drop the GROUP BY c.loginid clause. Your where clause has already selected the comment from that particular user. Group by will try to aggregate them into one row. Aggregate and the detail value like s.url don't mix.

If I understand your goal correctly, you should just chain the tables:

SELECT <yourfields>
FROM comment c
JOIN login l ON l.loginid = c.loginid
JOIN submission s ON s.submissionid = c.submissionid
WHERE l.username = ?