Matching feedback answers to question alternatives via MySQL

Matching feedback answers to question alternatives via MySQL

by Tiago Hillerman -
Number of replies: 1

Hi guys

I'm trying to extract survey questions and answers, using SQL, from a Moodle server located in a MySQL database.

(Moodle Version 3.1.7 (Build: 20170710))

I see that the mdl_feedback_item table the stores questions, while the answers are obtained via a join with the mdl_feedback_value table).


This is my current sql script:

select fi.id, fi.name as fb_question, fi.presentation as answer_options, fv.value as answer

from 

mdl_feedback fb

left join mdl_feedback_item fi on fi.feedback=fb.id

left join mdl_feedback_value fv on fv.item=fi.id


Which results in:

id / fb_question  / answer_options         / answer
68 / Do you ...   / Agree|Neutral|Disagree / 3
69 / Are you...   / Yes|No|Maybe           / 1
70 / Which day... / Monday|Tuesday         / 2
(...)

My objective is to check the 'answer' value, and replace it with the corresponding answer_option:
id / fb_question  / answer_options         / answer
68 / Do you ...   / Agree|Neutral|Disagree / Disagree
69 / Are you...   / Yes|No|Maybe           / Yes
70 / Which day... / Monday|Tuesday         / Tuesday

There isn't a fixed minimum/maximum number of alternatives per question, so I think I'd need check the answer 'value' and then match it with the appropriate position in the pipe-delimited answer_options column. 

Does anyone have any experience doing this type of extraction?

Thanks!




Average of ratings: -
In reply to Tiago Hillerman

Re: Matching feedback answers to question alternatives via MySQL

by Tiago Hillerman -

I think I got it.


So, I can basically find out how many answer alternatives I'll have for a specific Course, by using LENGTH:


    Max((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)  as max_num_options,


Let's say the result is 5. 


So I then use SUBSTRING_INDEX in a CASE statement, which will give me the values for each of the 5 alternatives.


    CASE WHEN fv.value = 1 THEN

    (IF(SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', ((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) ), '|', -1 ) = SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', (((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)-1) ), '|', -1 ),"",SUBSTRING_INDEX( SUBSTRING_INDEX(fi.presentation, '|', 1 ), '|', -1 )))`


    WHEN fv.value = 2 THEN

    (IF(SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', ((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) ), '|', -1 ) = SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', (((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)-1) ), '|', -1 ),"",SUBSTRING_INDEX( SUBSTRING_INDEX(fi.presentation, '|', 2 ), '|', -1 )))`

                     

    WHEN fv.value = 3 THEN

    (IF(SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', ((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) ), '|', -1 ) = SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', (((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)-1) ), '|', -1 ),"",SUBSTRING_INDEX( SUBSTRING_INDEX(fi.presentation, '|', 3 ), '|', -1 )))`

                     

    WHEN fv.value = 4 THEN

    (IF(SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', ((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) ), '|', -1 ) = SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', (((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)-1) ), '|', -1 ),"",SUBSTRING_INDEX( SUBSTRING_INDEX(fi.presentation, '|', 4 ), '|', -1 )))`

                     

    WHEN fv.value = 5 THEN

    (IF(SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', ((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) ), '|', -1 ) = SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', (((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)-1) ), '|', -1 ),"",SUBSTRING_INDEX( SUBSTRING_INDEX(fi.presentation, '|', 5 ), '|', -1 )))`


    ELSE 'No answer given / text field'`  

    END AS fv_response_text`


It's a bit messy, and I'll always have to check how many alternatives there are beforehand, but it gets the job done.

Average of ratings: Useful (1)