Welcome to the LimeSurvey Community Forum

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

Retrieve surveys survey results in normalized form

  • lordzoster
  • lordzoster's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 1 month ago #103903 by lordzoster
Hallo
how could I retrieve survey results in a recordset like:
question code | answer code ?

That is a record for each answer instead than a column like it is now in lime_survey_yyyyy table.

Thanks in advance
The topic has been locked.
  • holch
  • holch's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
10 years 1 month ago #103904 by holch
Sorry, but it is not really clear what you want.

I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.

The topic has been locked.
  • lordzoster
  • lordzoster's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 1 month ago #103917 by lordzoster
Hallo holch and thank you for replyig.

Currently, surveys are saved within a table in the database a record for each participant, and a column for each question and a column for each answer. Something like:
[participant data] | [other data] | question1 | answer1 | question2 | answer2 |... | question-n | answer-n

I would like to retrieve data in the form:

[participant data] | [other data] | question1 | answer1
[participant data] | [other data] | question2 | answer2
[participant data] | [other data] | question-n | answer-n

Does LS offer this capability or do I have to rely on some cross-fields SQL trick?
The topic has been locked.
  • holch
  • holch's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
10 years 1 month ago #103918 by holch
OK, so you want a line for each question / and or answer. I am not sure why you would want this, but this is nothing that Limesurvey offers. You would need to write your own script to get this data either directly from the database or from the exported result file.

I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.

The following user(s) said Thank You: itsme
The topic has been locked.
More
10 years 1 month ago #103940 by itsme
hello guys, I would like to know the same answer, I want to export specific data from exact script to external page, using custom script,, anybody can help how to start with this?
The topic has been locked.
  • lordzoster
  • lordzoster's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 1 month ago #103971 by lordzoster
Hi
I'm going to write a PHP about it, I need to finish in hours, so I'll post then. Stay tuned.
The topic has been locked.
More
10 years 1 month ago #103980 by itsme
Lovely! :) tuned
The topic has been locked.
  • lordzoster
  • lordzoster's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 1 month ago #103983 by lordzoster
here a VERY VERY VERY BADLY written code, not integrated in the MVC of LimeSurvey - just a quick try.
Columns/questions names are in italian, but should not be an issue.
It a couple MySQL stored proc + PHP code, returning a JSON.

The MYSQL procedure:
Code:
DELIMITER //
 
DROP PROCEDURE IF EXISTS spLime_SurveyXcode //
 
CREATE PROCEDURE spLime_SurveyXcode(survey_code INT)
BEGIN
  /* return the set of question codes in SGQ format
    transcoded with group codes and question codes
  */
  select c.column_name
    , RIGHT(g.group_name, 5) codice_reato
    , q.Title codice_domanda
  from information_schema.columns c
  left join lime_groups g
    ON LOCATE(CONCAT('X', g.gid, 'X') , c.column_name) = 7
  left join lime_questions q
    ON g.gid = q.gid
    and LOCATE(CONCAT('X', q.qid, 'E'), CONCAT(c.column_name, 'E')) > 8
  where c.table_name = CONCAT('lime_survey_', CAST(survey_code AS CHAR(8)))   #869765'
    and left(c.column_name, 7) = CONCAT(CAST(survey_code AS CHAR(8)), 'X');  #'869765X';

 
END//
 
CALL `dbLime`.`spLime_SurveyXcode`(869765);

The PHP:
Code:
<?php
 
error_reporting(E_ALL &amp; ~E_NOTICE &amp; ~E_STRICT &amp; ~E_DEPRECATED);
 
$config['base_url'] = 'http://localhost/limesurvey/'; 
 
$config['db_host'] = 'localhost'; // Database host (e.g. localhost)
$config['db_name'] = 'dbLime'; // Database name
$config['db_username'] = 'root'; // Database username
$config['db_password'] = ''; // Database password
$config['survey_code'] = '869765';
 
 
function logMe($msg)
{
  // appends logs to a "message.log" file in the Lime's TMP directory
  if ($f=fopen('tmp/message.log', 'a'))
  {
    fputs($f, date("Y-m-d H:i:s")." $msg\n");
    fclose($f);
  }
 
}
 
// Open connection
$mysqli = new mysqli(
  $config['db_host']
  ,$config['db_username']
  ,$config['db_password']
  ,$config['db_name']
  ); 
 
// Check connection
if ($mysqli->connect_errno)
{
  logMe("Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
  die($mysqli->connect_error);
}
 
 
// execute xcodeproc
$resx = $mysqli->query(sprintf('call spLime_SurveyXcode(%d)', $config['survey_code']), MYSQLI_STORE_RESULT);
 
if(!$resx) {
  logMe("CALL failed: (" . $mysqli->errno . ") " . $mysqli->error);
  die($mysqli->error);
}
 
 
// we build the union that traverse every SGQ-code column of the survey
// and pair the results with the actual codes of the group and of the question
while($obj = $resx->fetch_object()) {
 
  list($S, $G, $Q) = split('X', $obj->column_name);
 
  $the_unions[]  = sprintf("SELECT '%s' codice_reato \n
                                  , '%s' codice_domanda \n
                                  , %s codice_risposta \n
                                  , a.assessment_value \n
                            FROM lime_survey_%s AS s \n
                            LEFT JOIN lime_answers AS a \n
                              ON a.code = s.%s \n
                              AND a.qid = '%s' \n"
      , $obj->codice_reato
      , $obj->codice_domanda
      , $obj->column_name
      , $config['survey_code']
      , $obj->column_name
      , $Q);
}
$resx->close();
$mysqli->next_result();
 
$the_union = implode( "\nUNION ALL\n", $the_unions);
 
$the_union .= "\nORDER BY codice_reato, codice_domanda, codice_risposta, assessment_value;";
 
 
$res = $mysqli->query($the_union );
if($res) {
  while($row = $res->fetch_object()) {
    $results[] = $row;
  }
 
  // Free result set
  $res->close();
  $mysqli->next_result();
 
  //FINAL OUTPUT
  // array_values() removes the original keys and replaces
  // with plain consecutive numbers
  print json_encode(array_values($results));
 
}
else {
  logMe("UNION failed (" . $mysqli->errno . ") " . $mysqli->error);
}
 
 
// Close connection
logMe("Close connection.");
$mysqli->close();
 
?>
The topic has been locked.
More
10 years 1 month ago #103985 by itsme
looks good, how to apply and test? should be all inserted in php file and published? why 2 parts you made them, please more information to try out
but thank you indeed for efforts, seems taken time to accomplish :)

Mike
The topic has been locked.
  • lordzoster
  • lordzoster's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 1 month ago #103987 by lordzoster
Hi I made into 2 parts since I started from the db side. Since the logic in that code is separated from the rest, and since writing that in PHP would result in a messy code, I'd go for that.
Actually this dualism means some weird facts like duplicating parameters and the like - but I'm going quick & dirty to verify other things.

I made it running executing the SQL into Lime's database and the PHP into the root of LS installation.
Please notice paths and column names.
The topic has been locked.
More
10 years 1 month ago #103990 by itsme
sorry, too complicated for my level, i appreciate your efforts and your time, it seems needs to try, but without further instructions where to put that and how to execute on my localhost i wouldn't be able to apply :(
lets see the guys when apply, perhaps they can tell us where to put that and how step by step :)
thanks a lot again

Mike
The topic has been locked.
  • lordzoster
  • lordzoster's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 1 month ago #103991 by lordzoster
In a few hours I'll have the OK from my client and thus in next days I rewrite the whole code in a more usable way, maybe an addon for LS.
In case, I'll post here.
The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose