- Posts: 15
- Thank you received: 1
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
- Topic Author
- Offline
- New Member
Less
More
10 years 2 months ago #103903
by lordzoster
Retrieve surveys survey results in normalized form was created 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
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
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 11639
- Thank you received: 2737
10 years 2 months ago #103904
by holch
I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.
Replied by holch on topic Retrieve surveys survey results in normalized form
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
- Topic Author
- Offline
- New Member
Less
More
- Posts: 15
- Thank you received: 1
10 years 2 months ago #103917
by lordzoster
Replied by lordzoster on 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?
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
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 11639
- Thank you received: 2737
10 years 2 months ago #103918
by holch
I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.
Replied by holch on 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 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.
- itsme
- Offline
- Junior Member
Less
More
- Posts: 23
- Thank you received: 0
10 years 2 months ago #103940
by itsme
Replied by itsme on 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?
The topic has been locked.
- lordzoster
- Topic Author
- Offline
- New Member
Less
More
- Posts: 15
- Thank you received: 1
10 years 2 months ago #103971
by lordzoster
Replied by lordzoster on 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.
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.
- itsme
- Offline
- Junior Member
Less
More
- Posts: 23
- Thank you received: 0
10 years 2 months ago #103980
by itsme
Replied by itsme on topic Retrieve surveys survey results in normalized form
Lovely! tuned
The topic has been locked.
- lordzoster
- Topic Author
- Offline
- New Member
Less
More
- Posts: 15
- Thank you received: 1
10 years 2 months ago #103983
by lordzoster
Replied by lordzoster on 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:
The PHP:
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 & ~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 topic has been locked.
- itsme
- Offline
- Junior Member
Less
More
- Posts: 23
- Thank you received: 0
10 years 2 months ago #103985
by itsme
Replied by itsme on 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
but thank you indeed for efforts, seems taken time to accomplish
Mike
The topic has been locked.
- lordzoster
- Topic Author
- Offline
- New Member
Less
More
- Posts: 15
- Thank you received: 1
10 years 2 months ago #103987
by lordzoster
Replied by lordzoster on 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.
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.
- itsme
- Offline
- Junior Member
Less
More
- Posts: 23
- Thank you received: 0
10 years 2 months ago #103990
by itsme
Replied by itsme on 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
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
- Topic Author
- Offline
- New Member
Less
More
- Posts: 15
- Thank you received: 1
10 years 2 months ago #103991
by lordzoster
Replied by lordzoster on 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.
In case, I'll post here.
The topic has been locked.