Welcome, Guest
Username: Password: Remember me

TOPIC: MySQL query JOIN questions and survey

MySQL query JOIN questions and survey 2 years 6 months ago #76047

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: 2 years 6 months ago by support_kingventures_net.
The administrator has disabled public write access.

Re: MySQL query JOIN questions and survey 2 years 6 months ago #76058

  • mdekker
  • mdekker's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 341
  • Thank you received: 68
  • Karma: 38
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
The administrator has disabled public write access.

Re: MySQL query JOIN questions and survey 2 years 6 months ago #76065

  • TMSWhite
  • TMSWhite's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 82
  • Karma: 36
Another option is to do it this way.
The administrator has disabled public write access.

Re: MySQL query JOIN questions and survey 2 years 6 months ago #76176

  • Mazi
  • Mazi's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 5325
  • Thank you received: 296
  • Karma: 249
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)limesurvey.org'"
The administrator has disabled public write access.

Re: MySQL query JOIN questions and survey 2 years 6 months ago #76235

Thank you all for the replies. We are going to look at the limesurvey functions to do this.
The administrator has disabled public write access.
Moderators: ITEd
Time to create page: 0.119 seconds
Donation Image