Page 1 of 1

SQL Query Issue

Posted: Thu Dec 06, 2007 8:55 am
by mad_phpq
Hello,

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.name
The last column in the result set returns ID referencing another table of answers. Most are referenced to only one field but a few have multiple choices and are referenced to multiple records in the answer table. The multiple choice answers are all seperated by a comma. I want to be able to pull the answer text using the qste.answer field. Does anyone know how i can do this?

Ignore 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

Posted: Sun Dec 09, 2007 4:27 pm
by califdon
Looks like the issue is normalization.

Posted: Sun Dec 09, 2007 5:54 pm
by John Cartwright

Code: Select all

  INNER JOIN mdl_question_answers qans 
Change this to a LEFT JOIN, and you'll need a big of php logic to avoid repeating the same question for multiple answers.

Code: Select all

$lastid = 0;
while ($row = mysql_fetch_assoc($result)) {
   if ($row['question_id'] != $lastid)) {
      echo 'Question: '. $row['question'] .'<br>';
      //its a new question so lets output the question
   } 

   echo 'Answer: '. $row['answer'] .'<br>';
   $lastid = $row['question_id'];
}

Posted: Tue Dec 11, 2007 4:46 am
by mad_phpq
thanks. I cant use php in this case because it is a report i'm trying to construct using ireport and jasperreports.