I'm looking to build a report and I have the following SQL.
Code: Select all
SELECT
u.id,
CONCAT(u.firstname, ' ', u.lastname) As 'Full Name',
qa.id,
c.fullname AS 'Course Name',
q.name AS 'Quiz Name',
qt.name AS 'Question',
SUBSTRING_INDEX(qste.answer,':',-1) AS 'Answer IDs',
qans.answer AS 'Answer Text'
FROM mdl_quiz_attempts qa INNER JOIN mdl_question_states qste
ON qa.id = qste.attempt
INNER JOIN mdl_user u
ON u.id = qa.userid
INNER JOIN mdl_quiz q
ON qa.quiz = q.id
INNER JOIN mdl_course c
ON q.course = c.id
INNER JOIN mdl_question qt
ON qste.question = qt.id
INNER JOIN mdl_question_answers qans
ON SUBSTRING_INDEX(qste.answer,':',-1) = qans.id
WHERE qste.seq_number = 1
GROUP BY qa.id,qt.name
ORDER BY u.lastname,qa.id,qt.nameIgnore the SUBSTRING_INDEX(qste.answer,':',-1) The colon actually seperates the questions with the answers id, but i've got the questions from somewhere else.
EDIT: I have made a change to the SQL. I've added the answer field. If there is only one answer it will display the answer ok. But for the multiple choice questions, it only displays the first answer. I want to be able to explode the answer id's and display all the answers given for that question.
Thanks