How to get number of rows from table based on ID from another table

Question

So I have an index.php page that spits out data from two tables on my database. The first table, questions contains one column for my QuestionID and looks like this:

qid
----
 1
 2
 3

The second table called answers contains two columns, one for my AnswersID and the other which links to my QuestionsID on the questions table, and it looks like this:

aid | aqid
----|-----
 1  |  3
 2  |  1
 3  |  1

So on my index.php page, I basically want to echo out every qid row from the questions table and beside it the total number of answers (aid rows from the answers table) corresponding to each QuestionID. To put simply, the result I want achieved on my index.php page is this:

QuestionID: 1 / Answers: 2
QuestionID: 2 / Answers: 0
QuestionID: 3 / Answers: 1

But based on my current code I am having trouble achieving this result. It keeps echoing out the wrong number of rows for each table. This is the unwanted result I am getting:

QuestionID: 1 / Answers: 3
QuestionID: 2 / Answers: 3
QuestionID: 3 / Answers: 3

So how would I fix this to achieve the correct result? This is my current code:

<?php 
$sql = "
SELECT questions.*, GROUP_CONCAT(answers.aqid) AS aqid
FROM questions 
LEFT JOIN answers
ON questions.qid=answers.aqid
GROUP BY questions.qid
";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
    $qid = $row["qid"];
    $aqid = $row["aqid"];
    $row_cnt = $result->num_rows;
?>
<div>
    QuestionID: <?php echo $qid; ?>
    <?php foreach($answers as $answer): ?>
        Answers: <?php echo $row_cnt; ?>
    <?php endforeach; ?>
</div>
<?php } ?>

Thank you for your help.

[`group_concat()`](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat) returns a concatenated string. You might be looking for [`count()`](https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html) — showdev, Jul 22 '19 at 22:14
Possible duplicate of [Count table rows](https://stackoverflow.com/questions/1893424/count-table-rows) — showdev, Jul 22 '19 at 22:16
I replaced group_concat() with count() and it just returns the exact same result — misner3456, Jul 22 '19 at 22:20
`count()` returns a row count, which will be stored in the `$aqid` variable. There's no need for `$row_cnt` or the `foreach`. — showdev, Jul 22 '19 at 22:20
Ahh so that's where the error was. Thanks so much for pointing that out. Totally fixed my code and it was that simple. Thank you! — misner3456, Jul 22 '19 at 22:23

showdev · Accepted Answer · 2019-07-22T22:36:11.550

1

group_concat() returns a concatenated string. You might be looking for count().

Also, count() returns a row count.
There doesn't seem to be a need for $row_cnt or the foreach.

Here's an example:

$sql = "
    SELECT
        q.`qid`,
        COUNT(a.`aqid`) AS `answerCount`
    FROM `questions` q
    LEFT JOIN `answers` a
        ON a.`aqid` = q.`qid`
    GROUP BY q.`qid`
    ";

$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {
    ?>
    <div>
      QuestionID: <?php echo $row["qid"]; ?>
      Answers: <?php echo $row["answerCount"]; ?>
    <div>
    <?php
}
?>

edited Jul 22 '19 at 22:36

answered Jul 22 '19 at 22:23

showdev

28,454
37
55
73

Your advice earlier had easily fixed my code. Thanks greatly for the further explanation and for providing an example! (Apologies for doubting you earlier, complete mistake on my end.) – misner3456 Jul 22 '19 at 22:42
No worries! I overlooked the rest of your code at first glance. – showdev Jul 22 '19 at 22:44

How to get number of rows from table based on ID from another table

1 Answers1