In WebPA, find the score given for each assessment question, and the potential range using a hand-made function.

find-WebPA-score-range.sql

drop function if exists LOCATE_OFFSET;
DELIMITER //
CREATE FUNCTION LOCATE_OFFSET(substr text, str text, offset int)
RETURNS int
DETERMINISTIC
BEGIN
DECLARE loc INT DEFAULT 1;
DECLARE i INT DEFAULT 0;
WHILE(i<offset) DO
SET loc=LOCATE(substr, str, loc+1);
SET i=i+1;
END WHILE;
RETURN(loc);
END//
DELIMITER ;

SELECT score, 
( SELECT SUBSTRING( form_xml, LOCATE_OFFSET('<range>', form_xml, um.question_id+1) + 7, LOCATE_OFFSET('</range>', form_xml, um.question_id+1) - LOCATE_OFFSET('<range>', form_xml, um.question_id+1) - 7 ) `range`
FROM pa2_assessment a
WHERE a.assessment_id = um.assessment_id
LIMIT 1 )
FROM pa2_user_mark um;