Join with Three Tables
- by John
Hello,
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 &\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>";
