Some help or advice would be much appreaciated
Regards
Jason : I'm trying to build a report that will display students and their answers from quizzes that they have already completed.
But this already exists in standard Moodle. Why do you want to re-invent the wheel?
Joseph
Where can one generate a report on moodle?
Actually the report Jason wants is the 'Detailed responses' report, which is a third-party plugin in Moodle 1.9 and before, but has been added to core for moodle 2.0.
I dont really mind doing the code to develop the report, just interested to know about the mdl_question_states:
I see the one column is called answer, that i would assume contains the answers to the quiz. But the 'answer' column in filled with random numbers.
Is there a way to get the actual answers frm the table that the student entered in the quiz?
I see the one column is called answer, that i would assume contains the answers to the quiz. But the 'answer' column in filled with random numbers.
Is there a way to get the actual answers frm the table that the student entered in the quiz?
Well, question_states.answer contains question-type specific information. For multiple-choice questions, the seemingly random numbers relate to the question_answers table. Development:Quiz_database_structure may help.
Ok great thanx for that link, will come in handy.
But i actually wasn't specific enough in my post.
On our site we have a student feedback(survey) quiz which is available to students to compete(used for our marketing department). It contains a number of questions and the students reply to each question by entering there HTML editor box.
I'm interested to know what table in the moodle database it is stored in.
But i actually wasn't specific enough in my post.
On our site we have a student feedback(survey) quiz which is available to students to compete(used for our marketing department). It contains a number of questions and the students reply to each question by entering there HTML editor box.
I'm interested to know what table in the moodle database it is stored in.
Right, so for essay questions, the HTML they type is in question_states.answers. You just need to find the right rows!
thanx for helping me out with this, only got about 3 months experience of moodle under my belt.
There are 2 queries from me:
1: i'm get getting multiple rows for one student,
this is the current sql i'm using, it obviously is incorrect and needs to be edited:
SELECT DISTINCT q.id AS 'quiz id', q.course AS 'course id', q.name AS 'quiz name', qs.answer AS 'answers', qa.userid AS 'user id', u.firstname, u.lastname
FROM mdl_quiz q
LEFT JOIN mdl_quiz_attempts qa
ON q.id = qa.quiz
LEFT JOIN mdl_user u
ON u.id = qa.userid
LEFT JOIN mdl_question_states qs
ON qs.question = q.id
WHERE qa.quiz = 49
AND qa.attempt = 1
AND u.id = 758
*This query is just for reference *
Thank you for that table structure diagram of moodle quiz tables.
2: The column called 'answers' in mdl_question_states, it contains nothing but numbers.
How do i extract the actual answer the student entered into the quiz?
Thanx for all ur help
Regards
There are 2 queries from me:
1: i'm get getting multiple rows for one student,
this is the current sql i'm using, it obviously is incorrect and needs to be edited:
SELECT DISTINCT q.id AS 'quiz id', q.course AS 'course id', q.name AS 'quiz name', qs.answer AS 'answers', qa.userid AS 'user id', u.firstname, u.lastname
FROM mdl_quiz q
LEFT JOIN mdl_quiz_attempts qa
ON q.id = qa.quiz
LEFT JOIN mdl_user u
ON u.id = qa.userid
LEFT JOIN mdl_question_states qs
ON qs.question = q.id
WHERE qa.quiz = 49
AND qa.attempt = 1
AND u.id = 758
*This query is just for reference
Thank you for that table structure diagram of moodle quiz tables.
2: The column called 'answers' in mdl_question_states, it contains nothing but numbers.
How do i extract the actual answer the student entered into the quiz?
Thanx for all ur help
Regards
Did you follow the link from the quiz DB docs to the question bank DB docs? That question DB page contains a very useful query.
Where's that if u don't mind me asking...
Development:Question_database_structure#Following_what_happens_when_questions_are_attempted
You probably want either the newest or newgraded state from each question.
Also, you will need to add in
JOIN mdl_quiz_attempts qa ON qa.uniqueid = st.attempt
so you can extract only the attempts for the values of qa.quiz that you are interested in.
You probably want either the newest or newgraded state from each question.
Also, you will need to add in
JOIN mdl_quiz_attempts qa ON qa.uniqueid = st.attempt
so you can extract only the attempts for the values of qa.quiz that you are interested in.
You are an absolute star!! 
That join made the difference
The get the correct number of rows for each student.
Thank you so much
How do you know so much about moodle
That join made the difference
The get the correct number of rows for each student.
Thank you so much
How do you know so much about moodle
Well, I have been a full time Moodle developer for 3.5 years, first at the OU, then at Moodle.com. And for most of that time, the Quiz has been my responsibility. It is very nice to be paid to work full time on Moodle.
Wow so you must know moodle backwards
Thanx again for the help
Thanx again for the help
Ah!
I've got the answers, have have far too many rows for each student, 766 rows.
May need to change the structure of my query
SELECT DISTINCT qa.userid AS 'user id', q.id AS 'quiz id', q.course AS 'course id', u.firstname, u.lastname, qs.answer AS 'answers'
FROM mdl_quiz_question_instances qqi
LEFT JOIN mdl_quiz q
ON qqi.quiz = q.id
LEFT JOIN mdl_quiz_attempts qa
ON qa.quiz = q.id
LEFT JOIN mdl_question quest
ON quest.id = qqi.question
LEFT JOIN mdl_question_states qs
ON qs.question = quest.id
LEFT JOIN mdl_user u
ON u.id = qa.userid
WHERE q.id = 49
AND qa.attempt = 1
AND u.id = 758
I've got the answers, have have far too many rows for each student, 766 rows.
May need to change the structure of my query
SELECT DISTINCT qa.userid AS 'user id', q.id AS 'quiz id', q.course AS 'course id', u.firstname, u.lastname, qs.answer AS 'answers'
FROM mdl_quiz_question_instances qqi
LEFT JOIN mdl_quiz q
ON qqi.quiz = q.id
LEFT JOIN mdl_quiz_attempts qa
ON qa.quiz = q.id
LEFT JOIN mdl_question quest
ON quest.id = qqi.question
LEFT JOIN mdl_question_states qs
ON qs.question = quest.id
LEFT JOIN mdl_user u
ON u.id = qa.userid
WHERE q.id = 49
AND qa.attempt = 1
AND u.id = 758