SQL for handling Radio Btns

SQL for handling Radio Btns

by Scott Kotz -
Number of replies: 0

I figured out how to handle the problem with the reports only having numeric value from the radio button instead of the value on the feedback report. 


SELECT 

i.name AS Questions,

CASE 

/*Determines if the type of answer is a radio button */

    WHEN i.typ = 'multichoice' THEN 

 CASE 

    /*Grabs the value from the radio button and combines it with the presentation part of the radio button */

     WHEN v.value = 1 THEN SUBSTRING_INDEX(IF (SUBSTRING(i.presentation,1,6)='r>>>>>', SUBSTRING(i.presentation,7), i.presentation), '|', 1) 

     WHEN v.value = 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation,'|',2), '|', -1) 

     WHEN v.value = 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation,'|',3), '|', -1) 

     WHEN v.value = 4 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation,'|',4), '|', -1) 

     WHEN v.value = 5 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation,'|',5), '|', -1) 

     WHEN v.value = 6 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation,'|',6), '|', -1) 

     WHEN v.value = 7 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation,'|',7), '|', -1) 

     WHEN v.value = 8 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation,'|',8), '|', -1) 

    END

    ELSE v.value     /* this states that if it's not a multichoice answer then just give the answer that's in the input field*/

 END AS "Answers", 


u.username AS 'User',

c.shortname AS 'Course', 

DATE_FORMAT(FROM_UNIXTIME(fc.timemodified),'%Y-%m-%d') AS "Completed"


FROM prefix_feedback f

JOIN prefix_course c ON c.id=f.course 

JOIN prefix_feedback_item AS i ON f.id=i.feedback

JOIN prefix_feedback_completed fc ON f.id=fc.feedback

LEFT JOIN prefix_feedback_value v ON v.completed=fc.id AND v.item=i.id

LEFT JOIN prefix_user AS u ON fc.userid=u.id

WHERE v.value != 'pagebreak'



Average of ratings: -