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