Databases: Questionairre Database

Databases: Questionairre Database

by Stuart Watson -
Number of replies: 3

Hi

I'm new to the dev side of Moodle, and I'm hopeless when it comes to OOP, so please bear with me on this.

I am trying to write a custom report to interrogate the various table of the Moodle database.

I have several users who have completed a quiz containing random questions from categories and some description fields.

I am trying to read the database using simple php and mysql, and I have so far managed to find out a unique user attempt number, however when I then use this to interrogate the mdl_question table, I get no question text as any attempt number just relates to a value of 1 in the questiontext column.  All the actual text seems to be in other rows in this table which I can't find any reference to.

In an ideal world I'd like to be able to produce a report user by user, which shows what questions they attempted, what the correct answer is and what they submitted.

I appreciate that I may not be doing this strictly in the right way, but I'd prefer to continue the way I'm going rather than start calling functions I'm not sure of theough moodle.

PLease, Please Please can someone let me know how to relate the attempt number to the question text in the tables.  From this point onwards it should be a breeze.

Cheers

Stu

Average of ratings: -
In reply to Stuart Watson

Re: Databases: Questionairre Database

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Note that Questionnaire != Quiz.

Also note that the way that random questions are stored in the database is really horrible, and hard to work with in SQL. (With luck that will be changed in Moodle 2.1.)

With those warnings taken care of, see Development:Question database structure#Following_what_happens_when_questions_are_attempted

Average of ratings: -
In reply to Tim Hunt

Re: Databases: Questionairre Database

by Stuart Watson -

Thanks for that, even more confused now!!! lol

Does anyone have a work around for the random question things using simple Select statements?

At the moment I can find user id, attempt id etc using the following,

$uniqueid = mysqli_query($link, 'SELECT uniqueid FROM mdl_quiz_attempts WHERE userid =' . $input);
if (!$uniqueid)
{
 $error = 'Error Fetching uniqueid: ' . mysqli_error ($link);
 include 'error.html.php';
 exit();
}
else
{
 while($uniquerow = mysqli_fetch_array($uniqueid))
 {
  $attempt = mysqli_query($link, 'SELECT question FROM mdl_question_states WHERE event > "0" AND attempt =' . $uniquerow['uniqueid']);
  if (!$attempt)
   {
   $error = 'Error Fetching questionref: ' . mysqli_error ($link);
   include 'error.html.php';
   exit();
   }
   else
    {
    while($attemptid= mysqli_fetch_array($attempt))
     {
     $qtext=mysqli_query($link, 'SELECT answer FROM mdl_question_answers WHERE id =' . $attemptid['question']);
     if (!$qtext)
      {
      $error = 'Error Fetching question text: ' . mysqli_error ($link);
      include 'error.html.php';
      exit();
      }
      else
      {
       while($questiontext = mysqli_fetch_array($qtext))
       {
        echo $attemptid['question'] . " ";

etc

etc

 

Average of ratings: -
In reply to Stuart Watson

Re: Databases: Questionairre Database

by Stuart Watson -

Can anyone give me a simple noob overview as to how Moodle 1.9 deals with random questions?

Average of ratings: -