The survey responses need to be mapped to the variable names that we use when the data is processed for display. Here are several queries that can be used in the database to generate lists of the variable names:
USE `surveys`;
Show the full hierarchy of survey pages, sections, and questions:
SELECT * FROM `questions` JOIN survey_sections ON question_section = section_id JOIN survey_pages ON section_page = page_id JOIN surveys ON page_survey = survey_id WHERE survey_id = 4 ORDER BY page_order, section_order, question_order;
Show question data only:
SELECT questions.* FROM `questions` JOIN survey_sections ON question_section = section_id JOIN survey_pages ON section_page = page_id JOIN surveys ON page_survey = survey_id WHERE survey_id = 4 ORDER BY page_order, section_order, question_order;
Show data elements (LRS codes) only:
SELECT question_name, question_key FROM `questions` JOIN survey_sections ON question_section = section_id JOIN survey_pages ON section_page = page_id JOIN surveys ON page_survey = survey_id WHERE survey_id = 4 ORDER BY page_order, section_order, question_order;