MySQL query JOIN questions and survey

More
5 years 3 months ago - 5 years 3 months ago #76047 by support_kingventures_net
support_kingventures_net created the topic: MySQL query JOIN questions and survey
Hi all,
We are importing survey questions and answers into our own interface for our staff to read. We had initially done this with unwieldy static shell script. We were looking to do a straight MySQL INSERT SELECT but are having difficulty with the JOIN. In the lime_survey_tokenID table there are field names that appear to be a CONCAT(sid,'X',gid,'X',parent_qid,title) from lime_question and in other cases it appears to be a CONCAT(sid,'X',gid,'X',qid) and some end in "comment".
Is there one format or naming convention for the field names?
Is there a JOIN method that can link the survey with the actual questions?

Thanks in advance.
Last Edit: 5 years 3 months ago by support_kingventures_net.

Please Log in to join the conversation.

More
5 years 3 months ago #76058 by mdekker
mdekker replied the topic: MySQL query JOIN questions and survey
Something like the below SQL should do the trick, some questions also have the answer code / title appended. If you really want to know how this works, check out the createFieldMap function in LimeSurvey. Easiest would be to create a custom script using LimeSurvey internal functions to assist in the mapping of database field / question.

SELECT CONCAT(q.sid, 'X', q.gid, 'X', q.qid) AS sgq, q.type, q.qid, q.gid, q.question, q.title, q.other, q.question_order, g.group_order, g.group_name, g.description, sq.title AS sq_title, sq.question_order, sq.question AS sq_question, sq.scale_id
FROM <questions_table> AS q
LEFT JOIN <group_table> AS g ON q.sid = g.sid AND q.gid = g.gid AND q.language=g.language
LEFT JOIN <questions_table> AS sq ON q.qid = sq.parent_qid AND q.language = sq.language
WHERE g.sid = <sid> AND g.language = <lang code> AND q.parent_qid = 0
ORDER BY g.group_order, q.question_order, sq.scale_id DESC, sq.question_order

---
Menno Dekker

Please Log in to join the conversation.

More
5 years 3 months ago #76065 by TMSWhite
TMSWhite replied the topic: MySQL query JOIN questions and survey
Another option is to do it this way .

Please Log in to join the conversation.

More
5 years 3 months ago #76176 by Mazi
Mazi replied the topic: MySQL query JOIN questions and survey
I think a simple SQL will get very complex. Better make use of a script that uses the createfieldmap() feature inside Limesurvey and then create several inserts, maybe depending on the question type.


Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)survey-consulting.com'"

Please Log in to join the conversation.

More
5 years 3 months ago #76235 by support_kingventures_net
support_kingventures_net replied the topic: MySQL query JOIN questions and survey
Thank you all for the replies. We are going to look at the limesurvey functions to do this.

Please Log in to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now