Retrieve surveys survey results in normalized form

More
3 years 9 months ago #103903 by lordzoster
lordzoster created the topic: Retrieve surveys survey results in normalized form
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

Please Log in to join the conversation.

More
3 years 9 months ago #103904 by holch
holch replied the topic: Retrieve surveys survey results in normalized form
Sorry, but it is not really clear what you want.

I'm not a LimeSurvey GmbH member. I answer at the LimeSurvey forum in my spare time. No support via private message.
Some helpful links: Manual (EN) | Question Types | Workarounds

Please Log in to join the conversation.

More
3 years 9 months ago #103917 by lordzoster
lordzoster replied the topic: Retrieve surveys survey results in normalized form
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?

Please Log in to join the conversation.

More
3 years 9 months ago #103918 by holch
holch replied the topic: Retrieve surveys survey results in normalized form
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'm not a LimeSurvey GmbH member. I answer at the LimeSurvey forum in my spare time. No support via private message.
Some helpful links: Manual (EN) | Question Types | Workarounds
The following user(s) said Thank You: itsme

Please Log in to join the conversation.

More
3 years 9 months ago #103940 by itsme
itsme replied the topic: Retrieve surveys survey results in normalized form
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?

Please Log in to join the conversation.

More
3 years 9 months ago #103971 by lordzoster
lordzoster replied the topic: Retrieve surveys survey results in normalized form
Hi
I'm going to write a PHP about it, I need to finish in hours, so I'll post then. Stay tuned.

Please Log in to join the conversation.

More
3 years 9 months ago #103980 by itsme
itsme replied the topic: Retrieve surveys survey results in normalized form
Lovely! :) tuned

Please Log in to join the conversation.

More
3 years 9 months ago #103983 by lordzoster
lordzoster replied the topic: Retrieve surveys survey results in normalized form
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();
 
?>

Please Log in to join the conversation.

More
3 years 9 months ago #103985 by itsme
itsme replied the topic: Retrieve surveys survey results in normalized form
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

Please Log in to join the conversation.

More
3 years 9 months ago #103987 by lordzoster
lordzoster replied the topic: Retrieve surveys survey results in normalized form
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.

Please Log in to join the conversation.

More
3 years 9 months ago #103990 by itsme
itsme replied the topic: Retrieve surveys survey results in normalized form
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

Please Log in to join the conversation.

More
3 years 9 months ago #103991 by lordzoster
lordzoster replied the topic: Retrieve surveys survey results in normalized form
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.

Please Log in to join the conversation.

More
3 years 9 months ago #104011 by Mazi
Mazi replied the topic: Retrieve surveys survey results in normalized form
lordzoster, Limesurvey 2.05 offers a new plugin system which can be used to code custom export scripts and easily share such scripts with others.

Please have a look, maybe you can code one of the first Limesurvey export plugins.

Links:
manual.limesurvey.org/Plugins
www.limesurvey.org/en/extensions?sid=62:Export


Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)survey-consulting.com'"

Please Log in to join the conversation.

More
3 years 9 months ago #104021 by lordzoster
lordzoster replied the topic: Retrieve surveys survey results in normalized form
Actually I wanted to contribute in that way :)
Thank you for pointing it
The following user(s) said Thank You: itsme

Please Log in to join the conversation.

More
3 years 9 months ago #104022 by itsme
itsme replied the topic: Retrieve surveys survey results in normalized form
Mazi, couldn't upgrade to 2.05 at all, it requires php 5.3 and other thing that i can change in the server not to affect other applications, so kept with 2.0 and installation went smooth
any idea for 2.0 limitation or things that i will stop with? i mean does it have preventive issues that will force me later to change the server or upgrade the php version so i can install the newer 2.05?

Please Log in to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now