SQL Query Issue

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

SQL Query Issue

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Looks like the issue is normalization.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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'];
}
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Post 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.
Post Reply