Hi,

I'm new to Lime Survey, and I'm having a little bit of a hard time getting the column names instead of the column ID when doing SQL queries to the Lime Survey's survey.

For example, if I have a survey that has the ID 123456, then I wll have a table named lime_survey_123456 and then all columns related to questions will be in the form 123456XGIDXQID, right?
So, basically, all question columns are formed as SIDXGIDXQID.

On the other hand, in the lime_questions table, there is a column named "question" that has the question name in it, but some questions appear with strings like <p style="margin-bottom:.0005pt; margin:0cm 0cm 8pt"><span style="font-size:14pt"><span style="line-height:normal"><span style="font-family:&quot;Calibri&quot;,&quot;sans-serif&quot;"><b>1.2- How old are you?</b></span></span></span></p>.

So, my questions are:

1) How can I get the actual name of a question and have it as an actual column name in the SQL query result?

2) Is it normal to have those strings on the question column? And if it is, how can I retrieve the actual questions text to get the column names as if I were exporting a csv/xlsx report through the lime survey's website?

3) How I should be assembling the columns for a given survey? Is there a way to get/know all column names for every survey without having to look into the table itself and that can be done in a single query?

Thank you in advance

1. You can't , BUT : you can use API to export with Expression Name for each column (for example)
2. No, how do you find this string? Where ?
3. Plugin ? gitlab.com/SondagesPro/coreAndTools/getQ...ionInformation#usage

2) Probably junk copied from MS Word.

tpartner wrote: 2) Probably junk copied from MS Word.
 

Yes, sure , but : only when editing without HTML editor, it's not shown on browse or export etc … i never see such issue.
You have an idea wher it shown like this ?

I have a lot of user uing copy/paste from word … :smiley with a lot of tears:

No, I have no idea how it was pasted in but the Calibri font is a strong clue.

I am not sure if this helps, but I have some really old bad queries that I think covers all of this. I used to have to use MSSQL for a scoring application from a MariaDB LimeSurvey 2.5 install. Here are some code snippets:

#1 To get the question and question code together:



This should be the result:


#2 If you are seeing all the html in the database question fields, you can always use a custom function to strip out the html so it is human readable. You could apply this function to the import routine above. I use this in MSSQL for this purpose.

 3. I only handle each survey at a time and loop through them, however you could easily stack the code in question 1 to not be dependent on a single survey id.

/*
análisis limesurvey, como se construyen o codifican los nombres de columnas

#ingeniería inversa
#mysql
#query
#nameCol

*/

SELECT 
    a.qid,
    a.title,
    a.parent_qid,
    #level1
    if(a.parent_qid=0 AND (a.type = 'L' or a.type = 'T'), 
    a.title ,    if(a.parent_qid>0 AND (a.type = 'F' or a.type = 'T' ) AND 
    
    (select xx.type from lce_questions xx where xx.qid = a.parent_qid) = 'F'
    , 
    #level2
    (SELECT CONCAT(b.title,'')
from lce_questions b
WHERE b.qid = a.parent_qid
) 
    ,
    #level3
        if(     (select xx.type from lce_questions xx where xx.qid = a.parent_qid) = ';' AND a.question!='' , 
    (SELECT CONCAT(b.title,'')
from lce_questions b
WHERE b.qid = a.parent_qid #level3
)
    ,'--')
    
    )
    ) as result
    , 
    
    
    if(a.parent_qid=0 AND (a.type = 'L' or a.type = 'T'), 
    CONCAT(a.sid,'X',a.gid,'X', a.qid)     ,    
        if(a.parent_qid>0 AND (a.type = 'F' or a.type = 'T' ) AND 
    (select xx.type from lce_questions xx where xx.qid = a.parent_qid) = 'F'
    , 
    (SELECT CONCAT(a.sid,'X',a.gid,'X', a.parent_qid,a.title)
from lce_questions b
WHERE b.qid = a.parent_qid
) ,
        if(     (select xx.type from lce_questions xx where xx.qid = a.parent_qid) = ';' AND a.question!='' , 
    (SELECT CONCAT(a.sid,'X',a.gid,'X', a.parent_qid,a.title,'_SQ001') 
from lce_questions b
WHERE b.qid = a.parent_qid 

)    ,'--')
    )
    ) as colName
    
    
FROM
    lce_questions a
WHERE
    a.sid = 'YOUR SID CODE' 
        HAVING result !='--'
    ORDER BY a.qid, a.question_order