Hello,
I believe the query below should work. However, when I run it, the results are blank. I think this is happening since for now, the table "comment" is empty. So there is no instance where s.submissionid = c.submissionid. I would like to have the query below to work even if there if no s.submissionid that equals a c.submissionid. In this case, I would like countComments to equal zero.
How can I do this?
Thanks in advance,
John
$sqlStr = "SELECT s.loginid, s.submissionid s.title, s.url, s.displayurl, l.username, count(c.comment) AS countComments
FROM submission AS s,
login AS l,
comment AS c,
WHERE s.loginid = l.loginid
AND s.submissionid = c.submissionid
GROUP BY s.loginid, s.submissionid s.title, s.url, s.displayurl, l.username
ORDER BY s.datesubmitted DESC
LIMIT 10";
$result = mysql_query($sqlStr);
$arr = array();
echo "<table class=\"samplesrec\">";
while ($row = mysql_fetch_array($result)) {
echo '<tr>';
echo '<td class="sitename1"><a href="http://www.'.$row["url"].'">'.$row["title"].'</a></td>';
echo '</tr>';
echo '<tr>';
echo '<td class="sitename2"><a href="http://www...com/sandbox/members/index.php?profile='.$row["username"].'">'.$row["username"].'</a><a href="http://www...com/sandbox/comments/index.php?submission='.$row["title"].'">'.$row["countComments"].'</a></td>';
echo '</tr>';
}
echo "</table>";
