Making a query result equal to zero when a condition is null

Question

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"].'"&gt;'.$row["title"].'&lt;/a&gt;&lt;/td&gt;';
    echo '</tr>';
    echo '<tr>';
    echo '<td class="sitename2"><a href="http://www...com/sandbox/members/index.php?profile='.$row["username"].'"&gt;'.$row["username"].'&lt;/a&gt;&lt;a href="http://www...com/sandbox/comments/index.php?submission='.$row["title"].'"&gt;'.$row["countComments"].'&lt;/a&gt;&lt;/td&gt;';
    echo '</tr>';
    }
echo "</table>";

Answer 1

$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
         LEFT JOIN comment AS c ON (s.submissionid = c.submissionid)
        WHERE s.loginid = l.loginid              
     GROUP BY s.loginid, s.submissionid s.title, s.url, s.displayurl, l.username
     ORDER BY s.datesubmitted DESC
        LIMIT 10"

Answer 2

You should run your queries at the command line or through something like phpMyAdmin/HeidiSQL/etc. to make sure you are getting the right data and the syntax is correct.

You are missing a few commas, one after s.submissionid on the select line and again on the group by line.

a1ex07's query should work if you add the missing commas.