Welcome to the LimeSurvey Community Forum

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

Automatically pull response data from survey database

KhemrajC
Topic Author
Offline
Premium Member

More

5 years 1 month ago #198179 by KhemrajC

Automatically pull response data from survey database was created by KhemrajC

Hi,

Once survey is launched, the next big step is to publish respondent location data with attributes onto a map/heatmap through json or geojson.

The location is gathered along with attributes representing scores calculated using EM. These values would become the intensity values.

So I will pull the index, location and intensity to create layered json or geojson. These can then be displayed through Leaflet using heatmap plugin.

I read that I may connect remotely to the survey database through JSON and it is already enabled.

There is also the possibility to generate json/geojson from PHP on LS itself and I noticed that I cannot upload a json file to the resource folder of a survey project, it is prohibited.

I am thinking of the best way to gather the data and publish it.

Anyone with a near similar experience of pulling data to feed somewhere else?

Or having it done on the LS hosting itself?

Thank you very much.

Best regards

The topic has been locked.

tpartner
Offline
LimeSurvey Community Team

More

5 years 1 month ago #198184 by tpartner

Replied by tpartner on topic Automatically pull response data from survey database

Search the forum for AJAX and API - there are lots of examples.

- www.limesurvey.org/manual/RemoteControl_2_API

Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

The topic has been locked.

KhemrajC
Topic Author
Offline
Premium Member

More

5 years 1 month ago #198188 by KhemrajC

Replied by KhemrajC on topic Automatically pull response data from survey database

Hi,

I did that already prior to adopt LS to build the questionnaire.

I am an open source fanatics!!!

I am ok with Python coding and I can use LimePy.

Just wanted suggestions from those who did near similar stuffs.

While working on the map based question and until i received your master code which i adopted,
I managed to have leaflet custom settings through a normal question and to serve custom layers.

However, I may not store JSON on the LS resource files.

Since I am doing so many things at the same time, I preferred the forum for a proper guidance.

Can I a survey be submitted and then the respondent is directed to a question which was not part of the survey but is on the questionnaire?

Thanks and regards

The topic has been locked.

gabrieljenik
Offline
Official LimeSurvey Partner

More

5 years 1 month ago #198196 by gabrieljenik

Replied by gabrieljenik on topic Automatically pull response data from survey database

Hi,

>>
Can I a survey be submitted and then the respondent is directed to a question which was not part of the survey but is on the questionnaire?
>>

Can you provide more detail. I don't understand.

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

Checkout our Reporting Solutions and our plugin shop at www.encuesta.biz .

The topic has been locked.

KhemrajC
Topic Author
Offline
Premium Member

More

5 years 1 month ago #198198 by KhemrajC

Replied by KhemrajC on topic Automatically pull response data from survey database

Hi,

Say I have 10 questions in a questionnaire amongst is a dummy question.

The respondent will be asked only 9 questions and not that dummy question.

After submission, the dummy question will then appear.

Since the public statistics is very basic and does not add sufficient value to a project, i was thinking of having a custom mini dashboard through a question text area using javascript, leaflet etc...

So after submission, the user will be directed to the dummy question for visual display only.

Just thinking of some tweaking in a proper way.

Thanks and regards

The topic has been locked.

holch
Offline
LimeSurvey Community Team

More

5 years 1 month ago - 5 years 1 month ago #198205 by holch

Replied by holch on topic Automatically pull response data from survey database

After submission, the dummy question will then appear.

No questions can appear after submission.

You might be able to put something into the end message, but probably better to create your own script (where you pull all the information via AJAX call or API) and redirect your respondents to this script via END URL.

Help us to help you!

Provide your LS version and where it is installed (own server, uni/employer, SaaS hosting, etc.).
Always provide a LSS file (not LSQ or LSG).

Note: I answer at this forum in my spare time, I'm not a LimeSurvey GmbH employee.

Last edit: 5 years 1 month ago by holch.

The topic has been locked.

KhemrajC
Topic Author
Offline
Premium Member

More

5 years 1 month ago #198206 by KhemrajC

Replied by KhemrajC on topic Automatically pull response data from survey database

Hi,

That is clever.

Any basic example please, if possible?

Thanks and regards

The topic has been locked.

gabrieljenik
Offline
Official LimeSurvey Partner

More

5 years 1 month ago #198208 by gabrieljenik

Replied by gabrieljenik on topic Automatically pull response data from survey database

Do you have a draft of how you picture that mini dashboard?

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

Checkout our Reporting Solutions and our plugin shop at www.encuesta.biz .

The topic has been locked.

Joffm
Offline
LimeSurvey Community Team

More

5 years 1 month ago - 5 years 1 month ago #198214 by Joffm

Replied by Joffm on topic Automatically pull response data from survey database

Hi,
if you are familiar with Python, you know how to create a simple webite.
With the bootstrap library it should be done rather quickly.

And what did I do (here the examples are in php, sorry)
I queried the database directly, but you may use the API as well.

1. Connected to the database (either with mysqli, PDO, ...; here I use meekrodb)

Code:

require_once 'meekrodb.class.php';
$pos = strpos($_SERVER["HTTP_HOST"],'ocalhost');
  DB::$host='myHost';
  DB::$user='myUsername';
  DB::$password='myPassword';
  DB::$dbName='myDatabaseName';
}

2. I mapped the necessary column names of LimeSurvey (which use the SGQA.syntax) to some variables

Code:

$survey=111111;
$tbl_daten='lime_survey_'.$survey;
$col_name=$survey.'X183X20282';
$col_gender=$survey.'X183X20284';
$col_age=$survey.'X183X20283';
...

3. Ran the necessary queries

Code:

$daten_male=DB::query("SELECT {$col_height}/100 as height, {$col_weight} as weight FROM {$tbl_daten} WHERE {$col_gender}=1 AND not isnull(submitdate)");
 
$daten_female=DB::query("SELECT {$col_height}/100 as height, {$col_weight} as weight FROM {$tbl_daten} WHERE {$col_gender}=2 AND not isnull(submitdate)");
 
$daten_BMI=DB::query("SELECT {$col_BMI},
    SUM(IF({$col_BMI} <20, 1, 0)) AS A1,
    SUM(IF({$col_BMI} BETWEEN 20 and 30, 1, 0)) AS A2,
    SUM(IF({$col_BMI} > 30, 1, 0)) AS A3
FROM {$tbl_daten} WHERE not isnull(submitdate) GROUP BY {$col_gender}");
 
$daten_Verlauf=DB::query("SELECT Date(submitdate) as Datum,sum(IF ({$col_gender}=1,1,0)) as male,sum(IF ({$col_gender}=2,1,0)) as female FROM {$tbl_daten} WHERE not isnull(submitdate) group by Datum order by Datum");

4. I used the HighCharts library to display the charts.
(You wrote you are going to use leaflet.)
Just entered the appropriate data of the queries.

Code:

...
       series: [{
            name: 'weiblich',
            type: 'scatter',
            color: 'rgba(83,223,  83, .5)',
            data: [
            <?php
            $cm=count($daten_female);
            for ($i=0;$i<$cm;$i++) {
              echo '['.$daten_female[$i]['height'].','.$daten_female[$i]['weight'].'],';
            }
            ?>
            ]
        }, {
            name: 'männlich',
            type: 'scatter',
            ...

This is an old example of mine where I played around a bit with the libraries to get familiar to them.
Maybe I'd do it a bit different today.
But you get the idea.

Now it is up to you.
This is not LimeSurvey related. So you prpbably get more help in forums related to web design, leaflet, bootstrap, ...

Joffm

Volunteers are not paid.
Not because they are worthless, but because they are priceless

Attachments:

s1_2020-04-29.PNG

Last edit: 5 years 1 month ago by Joffm.

The topic has been locked.

KhemrajC
Topic Author
Offline
Premium Member

More

5 years 1 month ago - 5 years 1 month ago #198227 by KhemrajC

Replied by KhemrajC on topic Automatically pull response data from survey database

Hi,

Thanks for sharing this example. I will comment on it in the next post.

I can manage with Php as well. I am just a curious geek.

Just I did not install a local copy of LS to understand its architecture and backend database, and this has heavily restricted my flexibility to control settings and parameters. Trying to do things from a frontend access without access to backend settings, is a bit of a pain. This forum has helped me a lot. My first experience with JQuery!

I have been generously been provided LS hosting to attempt this geocrowd survey experiment. This is huge plus and LS is open source! I do not like to remain vendor-locked.

For this experiment, I need now to do things outside LS and use the remote API to proceed.

I can use Python to query the LS database and prepare my layers for detail analysis in statistics or GIS software and publish the results afterwards. I will eventually manage through these steps because it would all happened local to my laptop and I have the tools needed. And mostly, this will make sense after reaching a significant amount of participation and the analysis itself would a lengthy process.

Once this survey is launched, the immediate expectation of the public would be see a dashboard with basic near realtime results displayed on map and charts. That will encourage other people to participate in this experiment. And I still need to find an another hosting provider for it. For this case, I think Php would be lot better for generating the dynamic json layers. I may also prepare the layers with Python and push to the new host provider. Php/Javascript would consume these layers and you know it better. However, it would not be realtime though this can be achieved with online cloud automation.

I understood that LS provide an URL to direct to a website after submission.

Since I am doing everything over-here (not that true cause you guys are also assisting) and soon my time would also be limited as confinement is ending this week. I just want to make the right choice of technology and hosting provider to move on while taking into consideration of the expectation of the public and my available time.

At present, I do not have any hosting elsewhere nor any github account.

Out of your experience, what is the best and most affordable way to quickly come out with something?

I took time to narrate my thoughts as any answers may help others as well.

Thanks and regards

Last edit: 5 years 1 month ago by KhemrajC.

The topic has been locked.

holch
Offline
LimeSurvey Community Team

More

5 years 1 month ago #198241 by holch

Replied by holch on topic Automatically pull response data from survey database

Just I did not install a local copy of LS to understand its architecture and backend database, and this has heavily restricted my flexibility to control settings and parameters. Trying to do things from a frontend access without access to backend settings, is a bit of a pain.

I don't get this. Limesurvey CE is free. You can install it on your local computer via XAMPP or any other webserver/php/mysql environment. It literally costs you 10min of your time to install it.

Then you can play around, have all access to the source code, database, etc. to get familiar with.

Help us to help you!

Provide your LS version and where it is installed (own server, uni/employer, SaaS hosting, etc.).
Always provide a LSS file (not LSQ or LSG).

Note: I answer at this forum in my spare time, I'm not a LimeSurvey GmbH employee.

The topic has been locked.

KhemrajC
Topic Author
Offline
Premium Member

More

5 years 1 month ago #198262 by KhemrajC

Replied by KhemrajC on topic Automatically pull response data from survey database

Hi,

I already have XAMPP and now I am a bit more relaxed to install LS locally to get familiar with.

The first phase of my very first survey is over, just fine tuning prior to mass release.

I was seeking suggestions for the second phase of this project as described in my precedent post.

Thanks and regards

The topic has been locked.

Moderators: tpartner, holch

Lime-years ahead

Online-surveys for every purse and purpose

Pricing & Plans

Get started

Welcome to the LimeSurvey Community Forum

Automatically pull response data from survey database

Lime-years ahead

Legal

About Us

Open Source