WordPress.org

Ready to get started?Download WordPress

Forums

Wp-Pro-Quiz
[resolved] Export registered participants data with their total score and date in excel (9 posts)

  1. preetee
    Member
    Posted 5 months ago #

    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/

  2. preetee
    Member
    Posted 5 months ago #

    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

  3. preetee
    Member
    Posted 5 months ago #

    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/>';
     }
    
          }     
    
        }
    
    }
    
    ?>
  4. lesdavy
    Member
    Posted 3 months ago #

    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.

  5. wattsyourwebsite
    Member
    Posted 3 months ago #

    That would be great.

    We need this too.

  6. kncha1
    Member
    Posted 1 month ago #

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

  7. kncha1
    Member
    Posted 1 month ago #

    we need this asap

  8. Epox
    Member
    Posted 1 month ago #

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

  9. Muhammad Muhaimin
    Member
    Posted 1 month ago #

    yeah_ me too!

Reply

You must log in to post.

About this Plugin

About this Topic

Tags