This attempt is a bit above my SQL/query skills, so I am seeking advice on how to go about getting it done.
Summary:
Have a table that logs a user's attempt at answering some quiz questions.
Here is the table layout:
create table quiz_answers(
id int,
submit_date datetime,
session_id varchar(255),
quiz_name varchar(255),
question_num varchar(255),
answer varchar(255),
correct_answer varchar(255),
user_ip varchar(255)
);
insert into quiz_answers(id, submit_date, session_id, quiz_name, question_num, answer, correct_answer, user_ip) values
(1, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer1', 'correct_answer', 'xx.xx.xx.xxxx'),
(2, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer2', 'correct_answer', 'xx.xx.xx.xxxx'),
(3, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'correct_answer', 'correct_answer', 'xx.xx.xx.xxxx'),
(4, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer1', 'correct_answer', 'xx.xx.xx.xxxx'),
(5, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer3', 'correct_answer', 'xx.xx.xx.xxxx'),
(6, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer4', 'correct_answer', 'xx.xx.xx.xxxx'),
(7, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer3', 'correct_answer', 'xx.xx.xx.xxxx'),
(8, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'correct_answer', 'correct_answer', 'xx.xx.xx.xxxx'),
(9, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'answer0', 'correct_answer', 'xx.xx.xx.xxxx'),
(10, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question1', 'answer1', 'correct_answer', 'xx.xx.xx.xxxx'),
(11, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question1', 'answer1', 'correct_answer', 'xx.xx.xx.xxxx'),
(12, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question1', 'answer3', 'correct_answer', 'xx.xx.xx.xxxx'),
(13, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question1', 'answer4', 'correct_answer', 'xx.xx.xx.xxxx'),
(14, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question1', 'answer2', 'correct_answer', 'xx.xx.xx.xxxx'),
(15, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question1', 'answer2', 'correct_answer', 'xx.xx.xx.xxxx'),
(16, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question1', 'correct_answer', 'correct_answer', 'xx.xx.xx.xxxx'),
(17, '2019-09-03 11:39:07', 'xxxxx', 'q1_xxx_quiz', 'q1_question0', 'correct_answer', 'correct_answer', 'xx.xx.xx.xxxx');
select * from quiz_answers;
| | id | submit_date | session_id | quiz_name | question_num | answer | correct_answer | user_ip |
|----|----|---------------------|------------|-------------|--------------|----------------|----------------|---------------|
| 1 | 1 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question0 | answer1 | correct_answer | xx.xx.xx.xxxx |
| 2 | 2 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question0 | answer2 | correct_answer | xx.xx.xx.xxxx |
| 3 | 3 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question0 | correct_answer | correct_answer | xx.xx.xx.xxxx |
| 4 | 4 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question0 | answer1 | correct_answer | xx.xx.xx.xxxx |
| 5 | 5 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question0 | answer3 | correct_answer | xx.xx.xx.xxxx |
| 6 | 6 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question0 | answer4 | correct_answer | xx.xx.xx.xxxx |
| 7 | 7 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question0 | answer3 | correct_answer | xx.xx.xx.xxxx |
| 8 | 8 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question0 | correct_answer | correct_answer | xx.xx.xx.xxxx |
| 9 | 9 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question0 | answer0 | correct_answer | xx.xx.xx.xxxx |
| 10 | 10 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question1 | answer1 | correct_answer | xx.xx.xx.xxxx |
| 11 | 11 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question1 | answer1 | correct_answer | xx.xx.xx.xxxx |
| 12 | 12 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question1 | answer3 | correct_answer | xx.xx.xx.xxxx |
| 13 | 13 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question1 | answer4 | correct_answer | xx.xx.xx.xxxx |
| 14 | 14 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question1 | answer2 | correct_answer | xx.xx.xx.xxxx |
| 15 | 15 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question1 | answer2 | correct_answer | xx.xx.xx.xxxx |
| 16 | 16 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question1 | correct_answer | correct_answer | xx.xx.xx.xxxx |
| 17 | 17 | 03.09.2019 11:39:07 | xxxxx | q1_xxx_quiz | q1_question0 | correct_answer | correct_answer | xx.xx.xx.xxxx |
I am using PHP. I have the possible question answers provided for the user to select from (in an array or delimited string, perhaps to be used in the IN() portion of query?)
So for question: q1_question0
There are many 'submissions'
Possible answers: answer1, answer2, answer3, answer4 & correct answer
It is set up to always save the correct answer with every 'attempt' at answering the question (thought it may be easier to get stats later this way?)..
I'm looking for a way to get a count/max or better yet a percentage of each answer vs correct answer.
ie: something similar to-
answer1 - 25%answer2 - 50%answer3 - 10%answer4 - 5%correct answer - 10%or even:
answer1 - 4/10answer2 - 2/10answer3 - 1/10answer4 - 1/10correct answer - 2/10I'd prefer the % I think.. so I can use some sort of CSS/markup to visually create a progress bar or something..
This example is only for 1 question... that has 'x' number of possibly answer.. but each question differs in the amount of possibly answers there are. (hence why I thought having the possibly answers in an array/delimited string.. to be used in the dynamic query creation).. <- but don't let my lack of experience here set the tone in ANY way! LOL
I'm not sure if this involves several sub-set queries as well? (to get the 'stats' on each possible answer?) or where to even begin on this one?
Do I try to collect all entries with the same question name.. then somehow attempt to break it down by specific answers that are in the IN() clause and get a % from the total count of records by question name?
EDIT:
How to get a percentage of submitted answers vs correct answers? A working table example was even provided, WITH DATA.
Getting percentage stats based on the answers submitted vs correct answer (for each possible available answer)
Here is what I ultimately used in the end.
I then used some PHP to do the math on things:
$statChecker_sql = "SELECT COUNT(*) as total,
(SELECT COUNT(*) FROM image_quiz WHERE answer = 'xx1')as answer1,
(SELECT COUNT(*) FROM image_quiz WHERE answer = 'xx2') as answer2,
(SELECT COUNT(*) FROM image_quiz WHERE answer = 'xx3') as answer3,
(SELECT COUNT(*) FROM image_quiz WHERE answer = 'xx4') as answer4,
(SELECT COUNT(*) FROM image_quiz WHERE answer = 'xx5') as answer5
FROM image_quiz;";
Here is a dynamic example using PDO:
$statChecker_sql = "SELECT COUNT(*) as TOTAL, ";
for($i=0; $i<$totalPossibleAnswers; $i++){
if($i != ($totalPossibleAnswers - 1)){
$statChecker_sql .= "(SELECT COUNT(*) FROM image_quiz WHERE answer = :answerValue$i) as answer$i, ";
}else{
$statChecker_sql .= "(SELECT COUNT(*) FROM image_quiz WHERE answer = :answerValue$i) as answer$i "; //remove trailing comma (Im sure there is a better way)..lol!!!!!
}
}
$statChecker_sql .= " FROM image_quiz WHERE question_num = :targetQuestion";
$statChecker_stmt = $conn->prepare($statChecker_sql);
//dynamically add the potential values for parameterized query
for($i=0; $i<$totalPossibleAnswers; $i++){
$statChecker_stmt->bindValue(':answerValue'.$i, $quiz['question'][0]['answer'][$i]);}
$statChecker_stmt->bindValue(':targetQuestion',$targetQuestion);
$statChecker_stmt->execute();
$statChecker_stmt->setFetchMode(PDO::FETCH_ASSOC);
$statResults = $statChecker_stmt->fetch();
$statResultsCount = count($statResults); //current query index count
// submitted answer count * 100 \ total answers count
//output
echo '<br><br>Total Submitted Answers 1: ' . $statResults['TOTAL'] . '<br>';
echo 'Total Submitted Answers 2: ' . $statResultsCount . '<br>';
echo 'Detailed Answers breakdown: <br>';
for($z=1; $z<$statResultsCount; $z++){
echo $z . ' - ' . $possibleAnswerArray[$z-1] . ': ' . $statResults['answer'.($z-1)] . ' / ' . $statResults['TOTAL'] . ' (' . round(($statResults['answer'.($z-1)] * 100) / $statResults['TOTAL']) . '%)' . '<br>';
}