Willkommen, Gast
Benutzername: Passwort: Angemeldet bleiben:
  • Seite:
  • 1
  • 2

THEMA: Retrieve surveys survey results in normalized form

Retrieve surveys survey results in normalized form 1 Jahr 3 Monate her #103903

  • lordzoster
  • lordzosters Avatar
  • OFFLINE
  • Junior Lime
  • Beiträge: 29
  • Dank erhalten: 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
Der Administrator hat öffentliche Schreibrechte deaktiviert.

Retrieve surveys survey results in normalized form 1 Jahr 3 Monate her #103904

  • holch
  • holchs Avatar
  • OFFLINE
  • LimeSurvey Team
  • Beiträge: 3376
  • Dank erhalten: 457
  • Karma: 148
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!
Der Administrator hat öffentliche Schreibrechte deaktiviert.

Retrieve surveys survey results in normalized form 1 Jahr 3 Monate her #103917

  • lordzoster
  • lordzosters Avatar
  • OFFLINE
  • Junior Lime
  • Beiträge: 29
  • Dank erhalten: 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?
Der Administrator hat öffentliche Schreibrechte deaktiviert.

Retrieve surveys survey results in normalized form 1 Jahr 3 Monate her #103918

  • holch
  • holchs Avatar
  • OFFLINE
  • LimeSurvey Team
  • Beiträge: 3376
  • Dank erhalten: 457
  • Karma: 148
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!
Der Administrator hat öffentliche Schreibrechte deaktiviert.
Folgende Benutzer bedankten sich: itsme

Retrieve surveys survey results in normalized form 1 Jahr 3 Monate her #103940

  • itsme
  • itsmes Avatar
  • OFFLINE
  • Senior Lime
  • Beiträge: 55
  • Dank erhalten: 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?
Der Administrator hat öffentliche Schreibrechte deaktiviert.

Retrieve surveys survey results in normalized form 1 Jahr 3 Monate her #103971

  • lordzoster
  • lordzosters Avatar
  • OFFLINE
  • Junior Lime
  • Beiträge: 29
  • Dank erhalten: 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.
Der Administrator hat öffentliche Schreibrechte deaktiviert.

Retrieve surveys survey results in normalized form 1 Jahr 3 Monate her #103980

  • itsme
  • itsmes Avatar
  • OFFLINE
  • Senior Lime
  • Beiträge: 55
  • Dank erhalten: 1
  • Karma: 0
Lovely! :) tuned
Der Administrator hat öffentliche Schreibrechte deaktiviert.

Retrieve surveys survey results in normalized form 1 Jahr 3 Monate her #103983

  • lordzoster
  • lordzosters Avatar
  • OFFLINE
  • Junior Lime
  • Beiträge: 29
  • Dank erhalten: 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();
 
?>
Der Administrator hat öffentliche Schreibrechte deaktiviert.

Retrieve surveys survey results in normalized form 1 Jahr 3 Monate her #103985

  • itsme
  • itsmes Avatar
  • OFFLINE
  • Senior Lime
  • Beiträge: 55
  • Dank erhalten: 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
Der Administrator hat öffentliche Schreibrechte deaktiviert.

Retrieve surveys survey results in normalized form 1 Jahr 3 Monate her #103987

  • lordzoster
  • lordzosters Avatar
  • OFFLINE
  • Junior Lime
  • Beiträge: 29
  • Dank erhalten: 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.
Der Administrator hat öffentliche Schreibrechte deaktiviert.
  • Seite:
  • 1
  • 2
Moderatoren: ITEd
Ladezeit der Seite: 0.263 Sekunden
Donation Image