When we need to make a PDF of the responses and email it to a course teacher, we need to generically address the live survey tables extracting only the response columns - to achieve this, the following sql is in order - change the table prefix and sid as appropriate:
SELECT COLUMN_NAME
, SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_NAME, 'X', 1), 'X', -1) AS sid
, SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_NAME, 'X', 2), 'X', -1) AS gid
, SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_NAME, 'X', 3), 'X', -1) AS QNo
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA='limesurvey'
AND TABLE_NAME='###_survey_123456' -- example of live survey table
AND COLUMN_NAME NOT IN ('id', 'token', 'submitdate','lastpage', 'startlanguage', 'startdate', 'datestamp', 'ipaddr')
ORDER BY COLUMN_NAME;
When we need to make a PDF of the responses and email it to a course teacher, we need to generically address the live survey tables extracting only the response columns - to achieve this, the following sql is in order - change the table prefix and sid as appropriate:
References: https://stackoverflow.com/questions/34992575/mysql-substring-extraction-using-delimiter https://blog.fedecarg.com/2014/02/22/mysql-split-string-function/ https://github.com/KRSatpute/mysql_csv_parser/blob/master/sp_mysql_csv_parser.sql https://dba.stackexchange.com/questions/40930/extract-part-of-string-based-on-nth-instance-of-character