Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Connecting variable names to column titles in database

  • ralfkunze
  • ralfkunze's Avatar Topic Author
  • Offline
  • Senior Member
  • Senior Member
More
1 month 1 week ago #269076 by ralfkunze
Hi there, 

I have a general question. I would like to access the data directly in the database but I noticed that that limesurvey not only uses own column titles but also other sequence in the database than in the survey. 

Is it possible to get an overview about which variable belongs to which column in the database? 
This would really help a lot. 
Thanks, 
Ralf
 

Please Log in to join the conversation.

  • Joffm
  • Joffm's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
1 month 1 week ago - 1 month 1 week ago #269080 by Joffm
The column headers follow the SGQA syntax,
SurveyIDXGroupIDXQuestionID[subquestion code]

As you see here
 

Here you find
GroupID
 
QuestionID
 
and Subquestion Code
 

You also get this with ExpressionScript:
join(SID,'X',GID,'X',QID)

 


When I create a program to access the database directly I use a "translation table" with more convenient column names like

$survey=117615;
$tbl_daten='lime_survey_'.$survey;
$tbl_timing='lime_survey_'.$survey.'_timings';
$col_branche=$survey.'X857X33016';
$col_gender=$survey.'X825X32956';
$col_age=$survey.'X824X329551';
$col_Kenn=$survey.'X837X32969';
$col_Kunde=$survey.'X839X32972';
$col_BQ1=$survey.'X858X33018';
$col_BQ2=$survey.'X856X33015';


Joffm

Volunteers are not paid.
Not because they are worthless, but because they are priceless
Last edit: 1 month 1 week ago by Joffm.

Please Log in to join the conversation.

  • ralfkunze
  • ralfkunze's Avatar Topic Author
  • Offline
  • Senior Member
  • Senior Member
More
1 month 6 days ago #269112 by ralfkunze
Wow, Joffm - you just know everything! Thanks a lot for the quick reply and I had sorted quite a similar way already, but the "translation table" was new for me.

But I would like to connect 4 or more surveys in the background so this way appears a little too complex for me. I was more looking for the "translation" LimeSurvey itself uses when you export the responses. Here you can also choose, if you have the variable in the head of the table (= the question code) or the full question. I use this to connect the variables and the full questions. Is there a way to export the responses with the database column names? Then I could connect the variables and the database column names myself.

Cheers,
Ralf

Please Log in to join the conversation.

  • ralfkunze
  • ralfkunze's Avatar Topic Author
  • Offline
  • Senior Member
  • Senior Member
More
1 month 2 days ago #269209 by ralfkunze
Anybody knows a more comfortable way than translate the column head piece by piece?

Please Log in to join the conversation.

  • Joffm
  • Joffm's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
1 month 2 days ago - 1 month 2 days ago #269210 by Joffm
You can use the API 
With "list_questions" you get all parameters of a question
especially "sid", "gid" and "qid", "title"
Like this:
 

Now you may construct your desired result.

What do you want to achieve?

Here a small example how to get the question code and the column headers
F1: 122280X2514X29983
F2: 122280X2514X29984
F3: 122280X2514X29985
F4: 122280X2514X29986
F5: 122280X2514X29987
F6: 122280X2514X29988
F7: 122280X2514X29989
F8: 122280X2514X29991
F9: 122280X2514X29992
 

Joffm
 

Volunteers are not paid.
Not because they are worthless, but because they are priceless
Last edit: 1 month 2 days ago by Joffm.

Please Log in to join the conversation.

  • ralfkunze
  • ralfkunze's Avatar Topic Author
  • Offline
  • Senior Member
  • Senior Member
More
3 weeks 6 days ago #269244 by ralfkunze
Good question - what do I want to achieve? I think I would like to generate a human readable table automatically from the database.

When I use the frontend and do it by hand then I get a totally different table than when I go to the database. Identical content, only different in the heads. I was hoping to get an easier way from one table to the other than to do via gid , sid, qid, etc.

But from your explanations I have to conclude, that there is no "easy" way, right?
Ralf

Please Log in to join the conversation.

  • holch
  • holch's Avatar
  • Away
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
3 weeks 6 days ago #269245 by holch
Well, both tables are made for different audiences. The table you export is for humans, the table in the database is for machines. So what is "better readable" depends very much on who is the target group.

While I would appreciate law texts being in more "human" language, lawyers and judges probalby would disagree... :-)

Also: The database headers are most probably historically grown and have never been adapted and made the steps that the GUI did. Today you can reference answers in the database with the question ID, before you had to use the SGQA identifier.

Help us to help you!
  • Provide your LS version and where it is installed (own server, uni/employer, SaaS hosting, etc.).
  • Always provide a LSS file (not LSQ or LSG).
Note: I answer at this forum in my spare time, I'm not a LimeSurvey GmbH employee.
The following user(s) said Thank You: ralfkunze

Please Log in to join the conversation.

  • Joffm
  • Joffm's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
3 weeks 6 days ago #269246 by Joffm

I think I would like to generate a human readable table automatically from the database.

Why didn't you say this right at the start?

I thought you want to ease some queries developping some kind of dashboard or similar things.

Only to get a response table, use the API.
Here a rough example without any styling of the table.
Either with full header
 
or short header
 

Here the main part of the API script (in php)
Code:
/* Get the responses */
$response = $myJSONRPCClient->export_responses(
    $sessionKey,
    $survey_id,
    'json', // Document type : pdf,csv,xls,doc,json
    null, // Language code : null : default from survey
    'complete', // Stautus complete|incomplete|all
    'full', // Heading : code|full|abbreviated : question text, default code
    'long', // answer : short|long , default : long
    1,  // First exported SAVEDID   or NULL
    100  // Last exported SAVEDID   or NULL
    );
 
$decodedString = base64_decode($response);
$aResponses = json_decode($decodedString, True);
 
// Find the first response ID
$aFirstResponse = $aResponses['responses'][0];
 
echo '<table style="border-collapse: collapse; text-align: left;">';
echo '<tr>';
// Insert column headers
foreach($aFirstResponse as $key => $value) {
  echo '<th style="border: 1px solid #CCC; padding: 2px 7px;">'.$key .'</th>';
}
echo '</tr>';
 
foreach($aResponses['responses'] as $key => $row) {
  echo '<tr>';
  // Insert the data
  foreach($row as $key => $item) {
     echo '<td style="border: 1px solid #CCC; padding: 2px 7px;">'.$item .'</td>';
  }
  echo '</tr>';
}
echo '</table>';
 
// release the session key
$myJSONRPCClient->release_session_key( $sessionKey );

Joffm
 

Volunteers are not paid.
Not because they are worthless, but because they are priceless
The following user(s) said Thank You: ralfkunze

Please Log in to join the conversation.

  • ralfkunze
  • ralfkunze's Avatar Topic Author
  • Offline
  • Senior Member
  • Senior Member
More
3 weeks 5 days ago #269250 by ralfkunze
Thanks a lot for the detailed explanation and the code - I will continue trying in this direction!
Ralf

Please Log in to join the conversation.

Moderators: tpartnerholch

Lime-years ahead

Online-surveys for every purse and purpose