SQL Query to calculate number of occurrences from another query?

Question

I have some code outputting data into html tables. I'm trying to have a column that shows the total number of answers for that specific questions (defined by intQAID). Since $result is a query defined in the same php script, how can I run a second query to run a 'mysql_num_rows' on to calculate how many answers for the question?

$result = mysql_query("SELECT * FROM tblQA WHERE cCategory = 'Football' AND (TO_DAYS(CURDATE()) - TO_DAYS(dPostDateTime) < 1) ORDER BY dPostDateTime DESC, intQAID DESC");


while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['intQAID'] . "</td>";
echo "<td>" . $row['cUsername'] . "</td>";
echo "<td>" . $row['dPostDateTime'] . "</td>";
echo "<td>" . Num of Responses . "</td>";
echo "<td><a href=answer.php?id=" . $row['intQAID'] . ">" . $row['cBody'] . "</a></td>";
echo "</tr>";
}
echo "</table>";

Answer 1

Not knowing your structure, this is doing some guess work.

SELECT *, (SELECT count(*) 
           FROM answers_table at 
           WHERE at.intQAID = tqa1.intQAID) as answercount 
FROM tblQA tqa1 
WHERE cCategory = 'Football' 
    AND (TO_DAYS(CURDATE()) - TO_DAYS(dPostDateTime) < 1) 
ORDER BY dPostDateTime DESC, intQAID DESC");

Then you just reference the $row['answercount'] where you want to display it.

Answer 2

Is there a reason for using SELECT *? Here is a query which does not make use of subselects. Join the second table, chose the columns you need and place them in the GROUP BYclause and replace the *.

SELECT a, b, COUNT(fk_question_id) AS responseCount FROM tQuestions
LEFT JOIN tAnswers
    ON fk_question_id = question_id
GROUP BY a, b

Update: Use a left join, and count the occurences of the foreign key to find results with no answers.