Support » Plugin: Wp-Pro-Quiz » Export registered participants data with their total score and date in excel

  • Resolved preetee

    (@preetee)


    Dear all,

    Is there a way or an existing query to retrieve all the participants registered details with their respective score and date of parcipation and export these data in excel sheet.

    I have used the customs field to allow user to register their details like surname,name,age,country,email address.

    All registered details are stored in the wp_wp_pro_quiz_statistic_ref table in form_data column as text.

    To get the total score we must sum up the point values found in table wp_wp_pro_quiz_statistic per statistic_ref_id

    To retrieve the custom field name data we must use the table wp_wp_pro_quiz_form

    My skill sql is not good that why am asking help to retrieve these data in the following EXCEL SHEET format

    surname|name|age|country|emailaddress|dateofparticipation|score

    Thank you for your kind reponse and help

    http://wordpress.org/plugins/wp-pro-quiz/

Viewing 9 replies - 1 through 9 (of 9 total)
  • Dear all,

    I was able to retrieve the list of all participants with their respective score and date of participation with the following query

    SELECT  create_time  AS registrationtime, form_data, user_id, SUM( points ) AS score
    FROM <code>wp_wp_pro_quiz_statistic_ref</code> AS ref
    LEFT JOIN <code>wp_wp_pro_quiz_statistic</code> AS stat ON ref.statistic_ref_id = stat.statistic_ref_id
    WHERE quiz_id =3
    GROUP BY ref.statistic_ref_id
    ORDER BY create_time ASC

    RESULT OF THE QUERY

    registrationtime  |  form_data  | user_id | score
    1383642471  {"9":"sds","10":"sds","11":"sds","12":"sds@dfd.com	1 	2
    1383642491  {"9":"re","10":"test","11":"test1","12":"test@dtes.com	1 	6
    
    1383642410  {"9":"steeds","10":"sdrsrss","11":"sereds","12":"res@ssd.com	1 	4

    In the following class WpProQuiz_Model_Participant it return an array

    <?php
    class WpProQuiz_Model_Participant extends WpProQuiz_Model_Mapper {
    
        public function fetchparicipantlist($quizId) {
    
    		$r = array();
    
    		$results = $this->_wpdb->get_results(
    			$this->_wpdb->prepare(
    				"SELECT
    				 	create_time AS registrationtime,
    
    				    form_data,user_id,SUM(points) as score
    				 FROM
    				 	{$this->_tableStatisticRef} AS ref
                     LEFT JOIN
                     	 wp_wp_pro_quiz_statistic AS stat
                     ON ref.statistic_ref_id = stat.statistic_ref_id
    				 WHERE
    				 	quiz_id = %d
    				 GROUP BY ref.statistic_ref_id
    				 ORDER BY
    				 	create_time ASC
    				 "
    			, $quizId)
    		, ARRAY_A);
    
    		return json_encode($results);

    In my controller

    <?php
    class WpProQuiz_Controller_Participant extends WpProQuiz_Controller_Controller
    {
    
        public function route()
        {
    
            $quizId = $_GET['id'];
            $action = isset($_GET['action']) ? $_GET['action'] : 'showParticipantlist';
            $this->showParticipantlist($quizId);
    
        }
    
        public static function showParticipantlist($quizId)
        {
    
            $obj             = new WpProQuiz_Model_Participant();
            $view            = new WpProQuiz_View_Participant();
            $items           = array();
            $participantlist = $obj->fetchparicipantlist($quizId);
            $participantlist = json_decode($participantlist, true);
    
            $member = array();
            foreach ($participantlist as $data) {
                foreach ($data as $key => $value) {
                    echo "
    <pre>", print_r($data), "</pre>
    ";
                    if ($key == 'registrationtime') {
                        $value          = WpProQuiz_Helper_Until::convertTime($value, 'Y/m/d g:i A');
                        $member['Date'] = $value;
                    }
    
                    if ($key == 'form_data') {
    
                        $formData = str_replace("{", '', $value);
                        $formData = str_replace("}", '', $formData);
                         $formData = explode(',',$formData);
                        foreach ($formData as $key => $value) {
                            $formvalues = explode(':', $value);
                            foreach ($formvalues as $key => $value) {
    
                                if ($key == 0)
                                    unset($formvalues[$value]);
    
                                else {
                                    $member['Surname']       = $value;
                                    $member['Name']          = $value;
                                    $member['Nationality']   = $value;
                                    $member['Email address'] = $value;
                                  //  echo "
    <pre>", print_r($member, 1), "</pre>
    ";
                                }
    
                            }
    
                        }
                    }
    
                }
    
            }
    
        }
    
    }
    
    ?>

    Am unable to store the values for each participant form values in the member array

    I need to export these data in excel format
    EXCEL SHEET format

    Date of registration|surname|name|nationality|Email address|score

    Thanks for kind help and response

    Dear all,

    I have managed to retrieve my data via the following, hope this help someone

    <?php
    class WpProQuiz_Controller_Participant extends WpProQuiz_Controller_Controller {
    
    public function route() {
    
    		$quizId = $_GET['id'];
    		$action = isset($_GET['action']) ? $_GET['action'] : 'showParticipantlist';
    		$this->showParticipantlist($quizId);		
    
    	}
    
     public static function showParticipantlist($quizId) {
       $obj = new WpProQuiz_Model_Participant();
       $view = new WpProQuiz_View_Participant();
       $items = array();
       $participantlist = $obj->fetchparicipantlist($quizId);
      $participantlist=json_decode($participantlist,true);
    
        $member=array();
       foreach ($participantlist  as $data) {
    	   	foreach ($data  as $key=>$value) {
    	   	 	if($key=='registrationtime'){
    	   	 		$value=WpProQuiz_Helper_Until::convertTime($value,'Y/m/d g:i A');
    	   	 		$member['Date']=$value;
    	   			}	
    
                if($key=='form_data')
                 {
    
                 $formData=str_replace("{",'',$value);
                 $formData=str_replace("}",'',$formData);
    
                  $formData=explode(',',$formData);
                 //echo "<pre>" , print_r( $formData) , "</pre>";
                   for($i=0;$i<=count($formData);$i++){
              	 foreach ($formData as $key => $value) {
    
                    $value=strstr($value, ":");            
    
                    $data=explode(" ",$value);
    
                    	 if($key==0)
                    		$member['Surname']=str_replace(":",'',$value);
                    	 if($key==1)
    	                 	$member['Name']=str_replace(":",'',$value);
    	                 if($key==2)
    	                	$member['Nationality']=str_replace(":",'',$value);
    	                 if($key==3)
    	                 	$member['Email address']=str_replace(":",'',$value);
    
                    }
    
                   }
                }
                if($key=='score'){
                	$member['score']=$value;
                } 
    
           }
            foreach ($member as $key => $value) {
     echo $key .'=>'. $value.'<br/>';
     }
    
          }     
    
        }
    
    }
    
    ?>

    Could this sort of functionality be incorporated into the next release? A simple button at the bottom of the statistics page reading “download statistics” would be greatly appreciated.

    That would be great.

    We need this too.

    Please this should be made esay for us. The users.

    we need this asap

    It could be a great and easy to implement feature for us.

    yeah_ me too!

    Hi guys,

    I’m trying to extract the data gathered by the a test. I’m not a guru of databases nor SQL…
    I am using a custom form at the beginning of the test. I can’t see in what database (what’s the name) I can go an grab the data per user. Same for score.

    Any help would be highly appreciated 🙂
    T

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘Export registered participants data with their total score and date in excel’ is closed to new replies.