Check out the LimeSurvey source code on GitHub!
Welcome, Guest
Username: Password:

TOPIC: Survey Statistic without an Useraccount

Survey Statistic without an Useraccount 2 years 9 months ago #100885

  • urbana
  • urbana's Avatar
  • Offline
  • Premium Lime
  • Posts: 21
  • Thank you received: 1
  • Karma: 1
Hey there,

when ever we set up a survey for a customer - he want's us to report him daily how the participation is developing.
This can be annoying and costly and so I wrote a small PHP application, which provides a detail statistic without the need of an user account.

I want to share the script with you and also discuss, wether you think that could be a useful feature in future versions of limesurvey or not.

There are three key-features:
1. The necessary survey ID's and the title of the project are provide with GET variables so anyone can set up the statistic without the need to adapt the source code
2. The client can see the development with the help of Google Charts
3. Real time count of the overall number of participants

Further the script can handle also surveys where the dates weren't logged and the surveys can also be mixed.

All you need is to provide a URL following this pattern
http://yourdomain/survey_stat.php?survey=1010101,2020202,3030303&title=Client%20Name

survey=
Here you provide the survey ID's from one or different surveys (when the client runs more than one survey or you want to compare different surveys)
title =
Here you provide the title

That's it.
It is pretty easy and can be set up from anyone - but of course, if it is a build-in feature of limesurvey it would be more easy and flexible.

Here is the complete source code with the PHP script and the Google-Chart integration.
Check the attached screenshot for an real example.
<?php
 
//Connect to the database you use for limesurvey
try {
    $db = new PDO('mysql:host=yourhost.com;dbname=lime_db', 'user', 'pass');
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
 
function participants_pd($sid, $db) {
	//get the number of participants for each day of the survey
	$facts = array();
	$x = 0;
	if(!is_numeric($sid)) {
		echo "Survery ID must be a valid number";
		exit();
	}
	else {
		$survey_tbl = "lime_survey_".$sid;
		$query = "select date(`submitdate`) AS date, count(`id`) AS number from `$survey_tbl` where `submitdate` != 'NULL' group by date(`submitdate`)";
		$stmt = $db->query($query);
		while($r = $stmt->fetch(PDO::FETCH_OBJ)) {
				$facts['date'][$x] = $r->date;
				$facts['count'][$x] = $r->number;
				$x++;
		}
		$db = null;
		return $facts;
	}
}
 
function participants_sum($sid, $db) {
	//get number of participants of the survey
	if(!is_numeric($sid)) {
		echo "Survery ID must be a valid number";
		exit();
	}
	else {
	$survey_tbl = "lime_survey_".$sid;
	$sum = "";
	$survey_tbl = "lime_survey_".$sid;
	$query = "select count(`id`) AS sum from `$survey_tbl` where `submitdate` != 'NULL'";
	$stmt = $db->query($query);
	$r = $stmt->fetch(PDO::FETCH_OBJ);
	$sum = $r->sum;
	return $sum;	
	}
}
 
function titles($sid, $db) {
	//get the titles
	$title = "";
	$query_t = "select `surveyls_title` from `lime_surveys_languagesettings` where `surveyls_survey_id` = :sid";
	$stmt_t = $db->prepare($query_t);
	$stmt_t->bindParam(':sid', $sid);
	$stmt_t->execute();
	$r = $stmt_t->fetch(PDO::FETCH_OBJ);
	$title = $r->surveyls_title;
	return $title;	
}
 
//Getting the ID's of the surveys
if(isset($_GET['survey'])) {
	$titles = array();
	$participants = array();
	$surveys = array();
	//check if there are more then one surveys-ids
	if(preg_match("/,/", $_GET['survey'])) {
		//there are more then one survey 
		$surveys = explode(",", $_GET['survey']);
	}
	else {
		$surveys[] = $_GET['survey'];
	}
	foreach ($surveys as $key => $value) {
		//Get the number of participants for each day of the survey of each survey
		$arrayname = "survey".$value;
		${$arrayname} = participants_pd($value,$db);
 
		//Get number of participants for the whole periode
		$participants[] = participants_sum($value,$db);
 
		//Get the titles of the surveys
		$titles[] = titles($value,$db);
	}
}
 
/**
* generating the data string for the google chart
* Will look something like that:
* ['Day','Title S1','Title S2','Title S3'],
* ['2013-10-08',35,462,101],
* ['2013-10-09',15,101,33],
* ['2013-10-10',7,37,10],
* ['2013-10-11',5,14,1],
* ['2013-10-13',2,10,1]]);
*/
 
//First the column names of the chart (in our case Day and the titles )
$data = "['Day'";
foreach ($titles as $key => $value) {
	$data = $data.",'".$value."'";
}
$data = $data."]";
 
//Check which survey the most days
//The x-axis of the chart will filled with the dates of this survey
$max = 0;
$max_sid = "";
foreach ($surveys as $key => $value) {
	$arrayname = "survey".$value;
	if($max < count(${$arrayname}['date'])){
		$max = count(${$arrayname}['date']);
		$max_sid = $key;
	}
}
 
//fill in the numbers of participants 
$x = 0;
while ($x < $max) {
	$data = $data.",['".${"survey".$surveys[$max_sid]}['date'][$x]."'";
	foreach ($surveys as $key => $value) {
		$arrayname = "survey".$value;
		if(!empty(${$arrayname}['count'][$x])) {
		$data = $data.",".${$arrayname}['count'][$x];
		}
		else {
			$data = $data.",0";
		}
	}
	$data = $data."]";
	$x++;
}
//$data contains now a string with the JS Data Array 
//<?=$data has to be placed in the JS containing the google chart function
 
 
//Setting the title of the site (can be the name of the project or of the costumer)
$title = "Development of the numbers of participants ";
if(isset($_GET['title'])) {
	trim($_GET['title']);
	htmlentities($_GET['title']);
	$title = $title.$_GET['title'];
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
 
	<title><?=$title?></title>
 
	<style type="text/css">
	body,html {
		font-family: Droid Sans;
		font-size: 13px;
		margin:0px;
		width: 100%;
		height: 100%;
		background-color: grey;
 
	}
	h1 {
		font-size: 18px;
		background-color: white;
		margin-left: 0px;
		width: 50%;
		padding: 5px;
	}
	h2 {
		font-size: 15px;
 
	}
	.stat {
		width: 50%;
		background-color: white;
		padding: 5px;
	}
	.stat_li {
		width: 25%;
		float: left;
		text-align: right;
	}
	.stat_re {
		width: 45%;
		float: left;
		margin-left: 5%;
		font-weight: bold;
	}
	</style>
 
  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
 
      //set up the chart
      //check out https://developers.google.com/chart/ for more options
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      <?php
      if(isset($_GET['survey'])) {
      ?>
      function drawChart() {
 
        var data = google.visualization.arrayToDataTable([
          <?=$data?>
        ]);
 
        var options = {
 
        };
 
        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
      <?php
  		}
      ?>
    </script>
 
</head>
 
<body>
<h1><?=$title?></h1>
<h2>Overall participants</h2>
<div class="stat">
<?php
if(isset($_GET['survey'])) {
	$x = 0;
	foreach ($participants as $key => $value) {
		echo "<div class='stat_li'>".$titles[$x].":</div><div class='stat_re'>".$value." Participants</div><div style='clear:both;'></div>";
		$x++;
	}
}
?>
</div>
<h2>Development of the participation</h2>
<div id="chart_div" style="width: 51%; height: 50%;"></div>
</body>
</html>
 

Just save the whole script as .php file on your webserver.

Feel free to use it as you wish and I'll appreciate your feedback and ideas.
But more important:
What do you think - could that be a useful feature in a next limesurvey version?

all the best
urban-a :)
Attachments:
Last Edit: 2 years 9 months ago by urbana.
The administrator has disabled public write access.
The following user(s) said Thank You: Ben_V

Survey Statistic without an Useraccount 2 years 9 months ago #100889

  • Ben_V
  • Ben_V's Avatar
  • Offline
  • Platinum Lime
  • Posts: 1790
  • Thank you received: 439
  • Karma: 110
Hello,

I've just tried and it's a very very....very useful contribution and a valuable idea of plugin. Works like a charm!
( IMHO, only need a small adaptation to connect directly to the db )

Thank you very much for sharing!
Benoît

EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
All LS releases => bit.ly/1VMuTDu | 2.06lts => bit.ly/1Qv44A1
Demo surveys => bit.ly/20NW9V8 (already included in /docs/demosurveys)
The administrator has disabled public write access.

Survey Statistic without an Useraccount 2 years 9 months ago #100892

  • urbana
  • urbana's Avatar
  • Offline
  • Premium Lime
  • Posts: 21
  • Thank you received: 1
  • Karma: 1
Thank you very much for your feedback.
What do you mean with "connect directly to the database"?
The administrator has disabled public write access.

Survey Statistic without an Useraccount 2 years 9 months ago #100893

  • Ben_V
  • Ben_V's Avatar
  • Offline
  • Platinum Lime
  • Posts: 1790
  • Thank you received: 439
  • Karma: 110
I mean to link the script with LS key files handling db details, variables and connection...

For example, in several scripts shared in this forum, it's commonly used starting with the following code:
(Paths and/or filenames must be adapted for current LS version...)
require_once(dirname(__FILE__).'/config-defaults.php');
require_once ($rootdir.'/classes/adodb/adodb.inc.php');
$DB = NewADOConnection($databasetype);
$DB->Connect($databaselocation, $databaseuser, $databasepass, $databasename);

Note: Your script is very useful, because public statistics don't need to be enabled to get it working :)
Benoît

EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
All LS releases => bit.ly/1VMuTDu | 2.06lts => bit.ly/1Qv44A1
Demo surveys => bit.ly/20NW9V8 (already included in /docs/demosurveys)
The administrator has disabled public write access.

Survey Statistic without an Useraccount 4 months 5 days ago #133210

  • Gordon55M
  • Gordon55M's Avatar
  • Offline
  • Junior Lime
  • Posts: 29
  • Thank you received: 1
  • Karma: 0
Does this still work? I don't see any google charts on my testing. I am wondering if in the two years since this was posted has the google chart API or chart format changed perhaps?
The administrator has disabled public write access.

Survey Statistic without an Useraccount 4 months 5 days ago #133211

  • Ben_V
  • Ben_V's Avatar
  • Offline
  • Platinum Lime
  • Posts: 1790
  • Thank you received: 439
  • Karma: 110
Yes still working & useful; Note that you can perfectly call this script into an iframe.
For use with LS 2.06 (not tested with 2.5 but should work), just need such minor adaptations to connect to the db :
<?php
/**
 * https://www.limesurvey.org/forum/future-features/94882-survey-statistic-without-an-useraccount
 * Adapted for with LimeSurvey 2.05 & 2.06
 * Url pattern => example.org/statfile.php?sid=12345
*/
 
 
//  DB credentials
define('DB_SERVER', "XXX");
define('DB_DATABASE', "XXX");
define('DB_USER', "XXX");
define('DB_PASSWORD', "XXX");
//
define('DB_DRIVER', "mysql");
 
try {
  $db = new PDO(DB_DRIVER . ":dbname=" . DB_DATABASE . ";host=" . DB_SERVER . ";charset=utf8", DB_USER, DB_PASSWORD);
}
 
catch(PDOException $e) {
//    echo $e->getMessage();
    print "ERROR - " . $e->getMessage() . "<br/>";
    die();
}
 
 
function participants_pd($sid, $db)
{
 
    //get the number of participants for each day of the survey
    $facts = array();
    $x     = 0;
    if (!is_numeric($sid)) {
        echo "Survery ID must be a valid number";
        exit();
    } else {
        $survey_tbl = "lime_survey_" . $sid;
        $query      = "select date(`submitdate`) AS date, count(`id`) AS number from `$survey_tbl` where `submitdate` != 'NULL' group by date(`submitdate`)";
        $stmt       = $db->query($query);
        while ($r = $stmt->fetch(PDO::FETCH_OBJ)) {
            $facts['date'][$x]  = $r->date;
            $facts['count'][$x] = $r->number;
            $x++;
        }
        $db = null;
        return $facts;
    }
 
}
 
function participants_sum($sid, $db)
{
    //get number of participants of the survey
    if (!is_numeric($sid)) {
        echo "Survery ID must be a valid number";
        exit();
    } else {
        $survey_tbl = "lime_survey_" . $sid;
        $sum        = "";
        $survey_tbl = "lime_survey_" . $sid;
        $query      = "select count(`id`) AS sum from `$survey_tbl` where `submitdate` != 'NULL'";
        $stmt       = $db->query($query);
        $r          = $stmt->fetch(PDO::FETCH_OBJ);
        $sum        = $r->sum;
        return $sum;
    }
}
 
function titles($sid, $db)
{
    //get the titles
    $title   = "";
    $query_t = "select `surveyls_title` from `lime_surveys_languagesettings` where `surveyls_survey_id` = :sid";
    $stmt_t  = $db->prepare($query_t);
    $stmt_t->bindParam(':sid', $sid);
    $stmt_t->execute();
    $r     = $stmt_t->fetch(PDO::FETCH_OBJ);
    $title = $r->surveyls_title;
    return $title;
}
 
//Getting the ID's of the surveys
if (isset($_GET['sid'])) {
    $titles       = array();
    $participants = array();
    $surveys      = array();
    //check if there are more then one surveys-ids
    if (preg_match("/,/", $_GET['sid'])) {
        //there are more then one survey 
        $surveys = explode(",", $_GET['sid']);
    } else {
        $surveys[] = $_GET['sid'];
    }
    foreach ($surveys as $key => $value) {
        //Get the number of participants for each day of the survey of each survey
        $arrayname    = "survey" . $value;
        ${$arrayname} = participants_pd($value, $db);
 
        //Get number of participants for the whole periode
        $participants[] = participants_sum($value, $db);
 
        //Get the titles of the surveys
        $titles[] = titles($value, $db);
    }
}
 
/**
 * generating the data string for the google chart
 * Will look something like that:
 * ['Day','Title S1','Title S2','Title S3'],
 * ['2013-10-08',35,462,101],
 * ['2013-10-09',15,101,33],
 * ['2013-10-10',7,37,10],
 * ['2013-10-11',5,14,1],
 * ['2013-10-13',2,10,1]]);
 */
 
//First the column names of the chart (in our case Day and the titles )
$data = "['Day'";
foreach ($titles as $key => $value) {
    $data = $data . ",'" . $value . "'";
}
$data = $data . "]";
 
//Check which survey the most days
//The x-axis of the chart will filled with the dates of this survey
$max     = 0;
$max_sid = "";
foreach ($surveys as $key => $value) {
    $arrayname = "survey" . $value;
    if ($max < count(${$arrayname}['date'])) {
        $max     = count(${$arrayname}['date']);
        $max_sid = $key;
    }
}
 
//fill in the numbers of participants 
$x = 0;
while ($x < $max) {
    $data = $data . ",['" . ${"survey" . $surveys[$max_sid]}['date'][$x] . "'";
    foreach ($surveys as $key => $value) {
        $arrayname = "survey" . $value;
        if (!empty(${$arrayname}['count'][$x])) {
            $data = $data . "," . ${$arrayname}['count'][$x];
        } else {
            $data = $data . ",0";
        }
    }
    $data = $data . "]";
    $x++;
}
//$data contains now a string with the JS Data Array 
//<?=$data has to be placed in the JS containing the google chart function
 
 
//Setting the title of the site (can be the name of the project or of the costumer)
$title = "Progression of survey participation ";
if (isset($_GET['title'])) {
    trim($_GET['title']);
    htmlentities($_GET['title']);
    $title = $title . $_GET['title'];
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
 
 
  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
  <script type="text/javascript">
 
      //set up the chart
      //check out https://developers.google.com/chart/ for more options
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      <?php
      if(isset($_GET['sid'])) {
      ?>
      function drawChart() {
 
        var data = google.visualization.arrayToDataTable([
          <?=$data?>
        ]);
 
        var options = {
 
        };
 
        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
      <?php
          }
      ?>
    </script>
</head>
 
<body>
 
<center>
<div class="stat">
 
<?php
if(isset($_GET['sid'])) {
    $x = 0;
    foreach ($participants as $key => $value) {
    //    echo "<span class='stat_li'>".$titles[$x]." / </span><span class='stat_re'><b>".$value." </b>participants</span><div style='clear:both;'></div>";
        echo "\n<p class=\"surveys\" >&#147;".$titles[$x]."&#148; : <b>".$value."</b> participants</p>\n";
 
 
        $x++;
    }
}
 
?>
</div>
<div id="chart_div" style="width: 91%; height: 50%;"></div>
</center>
 
</body>
</html>
Benoît

EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
All LS releases => bit.ly/1VMuTDu | 2.06lts => bit.ly/1Qv44A1
Demo surveys => bit.ly/20NW9V8 (already included in /docs/demosurveys)
Last Edit: 4 months 5 days ago by Ben_V.
The administrator has disabled public write access.

Survey Statistic without an Useraccount 3 months 3 weeks ago #133642

  • Mazi
  • Mazi's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 5974
  • Thank you received: 366
  • Karma: 260
Great solution, would you be so kind to copy this to the "workarounds" section of our manual?!

Thanks!

Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

Survey Statistic without an Useraccount 3 months 1 week ago #134488

  • OMdev
  • OMdev's Avatar
  • Offline
  • Platinum Lime
  • Posts: 326
  • Thank you received: 34
  • Karma: 15
Which limesurvey installation folder you would you suggest me to copy this script . Means I just don't want to paste anywhere . I want to keep it where it makes most sense.
Survey Designer and Programmer
"I always suggest to have a group by group display layout and create a seprate group for each question. This will be extra work but you will get flexiblity to create your question by using more than more lime survey question types."
The administrator has disabled public write access.

Survey Statistic without an Useraccount 3 months 1 week ago #134492

  • Ben_V
  • Ben_V's Avatar
  • Offline
  • Platinum Lime
  • Posts: 1790
  • Thank you received: 439
  • Karma: 110
I personally would create a new folder for this kind of script. Remember that you can also perfectly place it into a separated domain or subdomain on the same server
Benoît

EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
All LS releases => bit.ly/1VMuTDu | 2.06lts => bit.ly/1Qv44A1
Demo surveys => bit.ly/20NW9V8 (already included in /docs/demosurveys)
The administrator has disabled public write access.

Survey Statistic without an Useraccount 2 weeks 2 days ago #138899

  • blocka
  • blocka's Avatar
  • Offline
  • Premium Lime
  • Posts: 23
  • Karma: 0
HI, I just tried this on Version 2.50+ Build 160620 , and I just get a blank screen when I call the URL with the survey= value. Not sure what I can share to help figure out why it has a blank page....
The administrator has disabled public write access.

Survey Statistic without an Useraccount 2 weeks 1 day ago #138940

  • Ben_V
  • Ben_V's Avatar
  • Offline
  • Platinum Lime
  • Posts: 1790
  • Thank you received: 439
  • Karma: 110
Just tested with LS 2.50 (Build 160606); it's ok with the provided script.. Try again !
Benoît

EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
All LS releases => bit.ly/1VMuTDu | 2.06lts => bit.ly/1Qv44A1
Demo surveys => bit.ly/20NW9V8 (already included in /docs/demosurveys)
The administrator has disabled public write access.

Survey Statistic without an Useraccount 2 weeks 1 day ago #138943

  • blocka
  • blocka's Avatar
  • Offline
  • Premium Lime
  • Posts: 23
  • Karma: 0
Hi, I tried again, and then thought to set PHP to display errors, and this is what I see:

Warning: Invalid argument supplied for foreach() in /home/survdemo/public_html/reports.php on line 121
Warning: Invalid argument supplied for foreach() in /home/survdemo/public_html/reports.php on line 130
The administrator has disabled public write access.

Survey Statistic without an Useraccount 2 weeks 1 day ago #138945

  • Ben_V
  • Ben_V's Avatar
  • Offline
  • Platinum Lime
  • Posts: 1790
  • Thank you received: 439
  • Karma: 110
Sorry I've got no idea about this kind of issue... :(
Benoît

EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
All LS releases => bit.ly/1VMuTDu | 2.06lts => bit.ly/1Qv44A1
Demo surveys => bit.ly/20NW9V8 (already included in /docs/demosurveys)
The administrator has disabled public write access.

Survey Statistic without an Useraccount 2 weeks 1 day ago #138946

  • blocka
  • blocka's Avatar
  • Offline
  • Premium Lime
  • Posts: 23
  • Karma: 0
I found the issue! In original post ( #100885 ) the URL is either wrong, or the parameter for survey id got changed. Original post shows:

http://yourdomain/survey_stat.php?survey=1010101,2020202,3030303&title=Client%20Name

but the latest iteration of the script replaced survey with sid, so URL needs to be:

http://yourdomain/survey_stat.php?sid=1010101,2020202,3030303&title=Client%20Name
The administrator has disabled public write access.
Time to create page: 0.674 seconds
Imprint                   Data Protection Statement                  Revocation information and revocation form