Query not returning results when a "WHERE" condition is added to it

Question

The query below is not returning any results. I have copied it from a query that was working and the only thing different is the addition of the following condition:

WHERE submissionid=$submissionid

Any ideas why this query is not returning any results?

$sqlStr2 = "SELECT 
                s.loginid
                ,s.title
                ,s.url
                ,s.displayurl
                ,s.datesubmitted
                ,l.username
                ,s.submissionid
                ,COUNT(c.commentid) countComments
             FROM submission s
            INNER JOIN login l ON s.loginid = l.loginid
       LEFT OUTER JOIN comment c ON s.submissionid = c.submissionid
              GROUP BY s.submissionid
             WHERE submissionid=$submissionid
             ORDER BY s.datesubmitted DESC
             LIMIT 10";

$tzFrom2 = new DateTimeZone('America/New_York'); 
$tzTo2 = new DateTimeZone('America/Phoenix'); 

$result2 = mysql_query($sqlStr2);

$arr2 = array(); 
echo "<table class=\"samplesrec\">";
while ($row2 = mysql_fetch_array($result2)) { 
    $dt2 = new DateTime($row2["datesubmitted"], $tzFrom2); 
    $dt2->setTimezone($tzTo2);
    echo '<tr>';
    echo '<td class="sitename1"><a href="http://www.'.$row2["url"].'" TARGET="_blank">'.$row2["title"].'</a>  <div class="dispurl">'.$row2["displayurl"].'</div></td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td class="sitename2name">Submitted by <a href="http://www...com/.../members/index.php?profile='.$row2["username"].'"&gt;'.$row2["username"].'&lt;/a&gt; on '.$dt2->format('F j, Y &\nb\sp &\nb\sp g:i a').'</td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td class="sitename2"><a href="http://www...com/.../comments/index.php?submissionid='.$row2["submissionid"].'"&gt;'.$row2["countComments"].' comments</a></td>';
    echo '</tr>';
    }
echo "</table>";

Answer 1

Just a hunch, but are you sure there are any rows that meet the condition?

WHERE submissionid=$submissionid

Also, have you checked the value of $submissionid to make sure you know what is in it when the query returns no rows?

Answer 2

I don't really use php but it looks like it might not be filling in the variable. So maybe do:

WHERE submissionid=".$submissionid."

Just to be sure print out $sqlStr2 to see what it is.

Answer 3

Try this:

$result2 = mysql_query($sqlStrt2) or die("Error " . mysql_error());

To see if there is a problem with the query.

Answer 4

• There is c.submissionid and s.submissionid -- maybe You should state in Your query which one You want to use. Example: WHERE s.submissionid=$submissionid
• I think GROUP BY should go after WHERE clause.

Answer 5

your query $sqlStr2 is not quieted.it's be usefully quiet all fisrt and i guess this WHERE submissionid=$submissionid should like this WHERE submissionid=\'$submissionid\'.