I'm counting answers and I don't know how to automate this

Question

I have answers from a database and i can count 1 now but i want to show the amount of times a answer is chosen. I have no qlue how to do that. For instance I want to count the amount of times 'answer_id' (the id of the answer) 1 and 2 are answered in 'question_id'(the question)

I tried setting a different if while above it but that didn't work out for me.

<?php  
$conn = mysqli_connect('localhost', 'root','','survey_cms_4people');
if (!$conn){
    echo "DATABSE ERROR!";
}

$variable = 1;
$countAnswerQuery = "SELECT COUNT(`answer_id`) FROM survey_answers WHERE question_id=$variable AND answer_id=$variable ";
$countanswerresult = mysqli_query($conn, $countAnswerQuery);
while ($row = mysqli_fetch_array($countanswerresult)) {
    echo $row[0];
}


?>

I want to be able to count them automaticly and not write a sql query for every question / answer.

score 1 · Answer 1 · answered Sep 30 '19 at 09:54

For instance I want to count the amount of times 'answer_id' (the id of the answer) 1 and 2 are answered in 'question_id'(the question)

You can aggregate the results by question_id and answer_id to find out how many time each answer was choosen for each question, like:

SELECT question_id, answer_id, COUNT(*) cnt
FROM survey_answers 
GOUP BY question_id, answer_id

If yiou want to filter on a given question, you can add a WHERE clause:

SELECT question_id, answer_id, COUNT(*) cnt
FROM survey_answers 
WHERE question_id = ?
GOUP BY question_id, answer_id

UrsolSolutions · Accepted Answer · 2019-09-30T11:03:48.043

1

You want to group by based on the answer id.

SELECT answer_id, COUNT(*) as count, question_id FROM survey_answers WHERE survey_id = $survey GROUP BY question_id , answer_id ORDER BY count DESC;

This will show you a count of all answers from all questions from a given survey.

This is probably a duplicate of:

MySQL: Count the occurrences of DISTINCT values

edited Sep 30 '19 at 11:03

answered Sep 30 '19 at 09:58

UrsolSolutions

218
1
8

What is $survey? – Ya like jazz Sep 30 '19 at 09:59
$survey is a variable, the id of the survey you want the answers from. I just noticed I made the group by wrong. question_id should be first, then answer_id. Edited* – UrsolSolutions Sep 30 '19 at 10:10
https://stackoverflow.com/questions/29781511/echo-results-from-mysqli-query Should give you all the info you need on getting results from your query. – UrsolSolutions Sep 30 '19 at 10:11
but then it echo's two two numbers is it possible to show only one – Ya like jazz Sep 30 '19 at 10:24
You're echoing the whole row. You can select a certain value with row['count'], row['question_id'], row['answer_id']. – UrsolSolutions Sep 30 '19 at 11:03
You should just follow a mysql php tutorial somewhere for this stuff. https://www.w3schools.com/php/php_mysql_intro.asp – UrsolSolutions Sep 30 '19 at 11:04

I'm counting answers and I don't know how to automate this

2 Answers2