Where am I going wrong? SQL Query

Question

$foot = mysql_query("SELECT count(*) 
                       FROM tblQA 
                      WHERE intResponseID = '' 
                        AND cCategory = 'Football' as qcount, 
                    (SELECT max(dPostDateTime) 
                       FROM tblQA 
                      WHERE intResponseID = '' 
                        AND cCategory = 'Football') as lastq");


$football = mysql_fetch_array($foot);

echo "<td class='forum'>" . $footbll['qcount'] . "</td>";
echo "<td class='forum'>" . $footbll['lastq'] . "</td>";

This doesn't display anything in my table. I didn't post the entire HTML code, i have the table structure fine.

Answer 1

You should have something like:

while ($football = mysql_fetch_array($foot)) {
    echo "<td class='forum'>" . $football['qcount'] . "</td>";
    /* yadda yadda yadda */
}

You also had a few typos in your code.

Answer 2

If this is your literal code, you have a typo in $footbll.

Answer 3

I think you're missing a combinator where the , sits: ...qcount, (SELECT...

Answer 4

Use:

$foot = mysql_query("SELECT COUNT(*) AS qcount,
                            MAX(dPostDateTime) AS lastq
                      FROM tblQA 
                     WHERE intResponseID = '' 
                       AND cCategory = 'Football' ");

$football = mysql_fetch_array($foot);

echo "<td class='forum'>" . $football['qcount'] . "</td>";
echo "<td class='forum'>" . $football['lastq'] . "</td>";

I re-wrote your query, it could all be done within a single statement.

Answer 5

The query doesn't make any sense, I am extremely surprised it returns something in your DB GUI.

If you didn't want to change the syntax, you would have to do something like this :

SELECT (SELECT COUNT(*) FROM tblQA) AS qcount, (SELECT MAX(dPOstDateTime) FROM tblQA) AS lastq -- i didn't add the conditions

But it can be easily done by doing this query instead :

SELECT COUNT(*) AS qcount,
       MAX(dPostDateTime) AS lastq
FROM tblQA
WHERE intResponseID = ''
    AND cCategory = 'Football'

Please note also intResponseID sounds like it is an integer, it seems weird you are comparing it to an empty string...

Answer 6

Ok, here is my solution. I figured it out:

$foot = mysql_query("SELECT count(*), max(dPostDateTime) FROM tblQA WHERE intResponseID = '' AND cCategory = 'Football'");

$football = mysql_fetch_assoc($foot);


echo "<td class='forum'><center>" . $football['count(*)'] . "</center></td>";
echo "<td class='forum'><center>" . $football['max(dPostDateTime)'] . "</center></td>";