How can I get the column names instead of the column ID when using SQL queries?

More
11 months 2 days ago #212088 by ppseverin
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 :)

Please Log in to join the conversation.

More
11 months 2 days ago #212107 by DenisChenu
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

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development . I don't answer to private message.

Please Log in to join the conversation.

More
11 months 2 days ago #212124 by tpartner
2) Probably junk copied from MS Word.

Cheers,
Tony Partner

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

Please Log in to join the conversation.

More
11 months 1 day ago #212139 by DenisChenu

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:

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development . I don't answer to private message.

Please Log in to join the conversation.

More
11 months 1 day ago #212177 by tpartner
No, I have no idea how it was pasted in but the Calibri font is a strong clue.

Cheers,
Tony Partner

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

Please Log in to join the conversation.

More
5 months 21 hours ago #219000 by Gordon55M
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:

   DECLARE  @surveyid varchar(50)
SET @surveyid = 'ENTER YOUR SID HERE'
DECLARE @importquestions as varchar(3000)
    SET @importquestions = 'SELECT * INTO MSLimeSurvey25.dbo.lime_questions FROM openquery(LIMESURVEY25, ''SELECT * FROM limesurvey25.lime_questions WHERE language=''''en'''' AND sid='+@surveyid+''')'
    EXEC(@importquestions)
 
    IF COL_LENGTH('dbo.[lime_questions]','LimeColumnName') IS NULL BEGIN ALTER TABLE dbo.[lime_questions] ADD LimeColumnName nvarchar(128) END
    UPDATE [dbo].[lime_questions] SET [LimeColumnName] = cast([sid] as varchar(50))+'X'+cast([gid] as varchar(50))+(case when [parent_qid] = 0 then 'X'+ cast([qid] as varchar(50)) ELSE 'X'+ cast([parent_qid] as varchar(50))+[title] END)
    IF COL_LENGTH('dbo.[lime_questions]','question') IS NOT NULL BEGIN ALTER TABLE dbo.[lime_questions] Alter Column question varchar(MAX) END


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.
USE [MSLimeSurvey25]
GO
/****** Object:  UserDefinedFunction [dbo].[StripHtml]    Script Date: 8/18/2021 11:30:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER FUNCTION [dbo].[StripHtml] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
 DECLARE @Start  INT
 DECLARE @End    INT
 DECLARE @Length INT
 DECLARE @TempStr varchar(MAX)
 
 SET @Start = CHARINDEX('<',@HTMLText)
 SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
 SET @Length = (@End - @Start) + 1
 
 WHILE @Start > 0 AND @End > 0 AND @Length > 0
 BEGIN
   
      SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
 
   SET @Start = CHARINDEX('<',@HTMLText, @End)
   SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText, @Start))
   SET @Length = (@End - @Start) + 1
 END
 
 RETURN RTRIM(LTRIM(@HTMLText))
END

 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.
Attachments:

Please Log in to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now