Welcome, Guest
Username: Password: Remember me

TOPIC: Where to find SQL query that creates Excel output

Where to find SQL query that creates Excel output 3 years 1 month ago #63678

  • aukevann
  • aukevann's Avatar
  • OFFLINE
  • Senior Lime
  • Posts: 51
  • Karma: 1
When exporting data to Excel the spreadsheet nicely gives the Question codes as column headers.
I am using an MySQL connector in Excel to retrieve the data and the column headers are the fields from the Lime_Survey_XXXXX table.
I would like to get the Question Codes instead and need to create a query that finds them in the Lime_Question table using the fields from the Lime_Survey_XXXXX table.
Since I know little about SQL it would greatly help if someone can point me to this query in the code.
The administrator has disabled public write access.

Re: Where to find SQL query that creates Excel output 3 years 1 month ago #63704

  • greg_d
  • greg_d's Avatar
  • OFFLINE
  • Senior Lime
  • Posts: 47
  • Thank you received: 7
  • Karma: 4
Hi,

When exporting to survey results a lot of operations are done by PHP code so there are no simple SQL queries to reuse. Just take a look at exportresults.php if you are interested.

If you want to query database directly there is a useful document with LS database structure:
docs.limesurvey.org/LimeSurvey+1.8+datab...meSurvey+development

To see data that you are interested to use as column headers try to run this:
select sid, gid, qid, parent_qid, question, title from Lime_questions where sid=XXXXX;

The problem with this query is that it returns data in columns so if you want to use it as headers and still use pure sql you may be forced to use something (really ugly) like this:
select 
	(select title from lime_questions where qid=25) as q1,
	(select title from lime_questions where qid=26) as q2
from lime_questions limit 1;
The administrator has disabled public write access.

Re: Where to find SQL query that creates Excel output 3 years 1 month ago #63740

  • aukevann
  • aukevann's Avatar
  • OFFLINE
  • Senior Lime
  • Posts: 51
  • Karma: 1
Thanks, helpful.
I decided to use VBA in Excel to extract the QID, then query the database for the rest of the data. Bit cumbersome but it will do the job.
The administrator has disabled public write access.
Moderators: ITEd
Time to create page: 0.212 seconds
Donation Image