Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: Retrieve surveys survey results in normalized form

Retrieve surveys survey results in normalized form 7 months 1 week ago #103903

  • lordzoster
  • lordzoster's Avatar
  • OFFLINE
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
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 administrator has disabled public write access.

Retrieve surveys survey results in normalized form 7 months 1 week ago #103904

  • holch
  • holch's Avatar
  • NOW ONLINE
  • LimeSurvey Team
  • Posts: 2643
  • Thank you received: 313
  • Karma: 115
Sorry, but it is not really clear what you want.
Have a look at the manual! It is a really valuable source for information. Here some helpful links:
Manual (EN) | Question Types | Question Attributes | Workarounds

If you found this answer helpful and it saved you some time please consider a donation to the project to keep Limesurvey going!
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 7 months 1 week ago #103917

  • lordzoster
  • lordzoster's Avatar
  • OFFLINE
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
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 administrator has disabled public write access.

Retrieve surveys survey results in normalized form 7 months 1 week ago #103918

  • holch
  • holch's Avatar
  • NOW ONLINE
  • LimeSurvey Team
  • Posts: 2643
  • Thank you received: 313
  • Karma: 115
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.
Have a look at the manual! It is a really valuable source for information. Here some helpful links:
Manual (EN) | Question Types | Question Attributes | Workarounds

If you found this answer helpful and it saved you some time please consider a donation to the project to keep Limesurvey going!
The administrator has disabled public write access.
The following user(s) said Thank You: itsme

Retrieve surveys survey results in normalized form 7 months 1 week ago #103940

  • itsme
  • itsme's Avatar
  • OFFLINE
  • Senior Lime
  • Posts: 53
  • Thank you received: 1
  • Karma: 0
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 administrator has disabled public write access.

Retrieve surveys survey results in normalized form 7 months 1 week ago #103971

  • lordzoster
  • lordzoster's Avatar
  • OFFLINE
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
Hi
I'm going to write a PHP about it, I need to finish in hours, so I'll post then. Stay tuned.
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 7 months 1 week ago #103980

  • itsme
  • itsme's Avatar
  • OFFLINE
  • Senior Lime
  • Posts: 53
  • Thank you received: 1
  • Karma: 0
Lovely! :) tuned
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 7 months 1 week ago #103983

  • lordzoster
  • lordzoster's Avatar
  • OFFLINE
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
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:
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:
<?php
 
error_reporting(E_ALL & ~E_NOTICE & ~E_STRICT & ~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 administrator has disabled public write access.

Retrieve surveys survey results in normalized form 7 months 1 week ago #103985

  • itsme
  • itsme's Avatar
  • OFFLINE
  • Senior Lime
  • Posts: 53
  • Thank you received: 1
  • Karma: 0
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 administrator has disabled public write access.

Retrieve surveys survey results in normalized form 7 months 1 week ago #103987

  • lordzoster
  • lordzoster's Avatar
  • OFFLINE
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
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 administrator has disabled public write access.
  • Page:
  • 1
  • 2
Moderators: ITEd
Time to create page: 0.257 seconds
Donation Image