ketan ketan - 1 month ago 5x
MySQL Question

Comparing two rows in mysql and manipulating according to result

Suppose I have an Answer Key of questions Q1, Q2, and so on. Also, I have responses of the same questions from many students. Is there anyway I can compare the responses of each student with the answer key and store the marks obtained (say 4, 0, -1 for correct, omitted and incorrect) in a separate table?


I think you can make your task easier with redesigning your database. Rather than trying to loop over columns, put the questions in rows and use the power of an SQL JOIN... Make a questions table something like this

create table questions(
question_id int,
question_description VARCHAR(100),
rightanswer varchar(100)

Now get your student responses:

create table student_responses(
student_id int,
question_id int,
answer varchar(100)

Now your output:

SELECT student_id,COUNT(*) as numberofrightanswers FROM
student_responses sr
questions q on q.question_id = sr.question_id
where answer = rightanswer
GROUP BY student_id

Of course, you can use any logic you like for scoring the answers.