PHP Excel 导出仅显示 mysql 表中的最后一行

Question

大家好，我正在尝试使用 PHP Excel 下载 Excel 格式的用户报告，当我尝试下载它时，它只显示一条记录，即我的 Excel 中的最后一条记录。谁能帮我看看我犯了什么错误。

用户将根据我尝试下载 Excel 的日期选择起始日期和截止日期。

当我运行查询时，我得到 3 行数据。当我尝试使用 phpexcel 创建 Excel 电子表格时，仅打印最后 1 条记录。

我发现了类似的帖子，但没有一个答案对我有用。感谢您的帮助。

这是我的代码:

    <?php
error_reporting(E_ALL ^ E_NOTICE);
if (isset($_POST['submit'])) {
    $fromdate =$_POST['datepicker'];
    $todate =  $_POST['datepicker1'];
    $usertype= $_POST['trackforums'];

 if($todate > $fromdate)
 {
require('../config.php'); 
require_once('../login/lib.php'); 
global $DB,$USER, $CFG;

require_once($CFG->dirroot . '/mod/scorm/lib.php');
require_once($CFG->dirroot . '/mod/scorm/locallib.php');
require_once($CFG->dirroot . '/course/lib.php');
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

$companyid= $_SESSION['companyid'];

//course table fetch
$sql1 = "SELECT cm.id as cmid,c.id,c.fullname,c.startdate,instance,c.timecreated FROM {course} c LEFT JOIN {course_modules} cm ON c.id=cm.course where c.category != 0 and requested='0' AND cm.id!=''";
$courses = $DB->get_records_sql($sql1);

if (sizeof($courses)) {
  foreach ($courses as $row) {

        $scorm = $DB->get_record("scorm", array("id" => $row->instance));
        $scoid = 0;
        $orgidentifier = '';
        if ($sco = scorm_get_sco($scorm->launch, SCO_ONLY)) {
            if (($sco->organization == '') && ($sco->launch == '')) {
                $orgidentifier = $sco->identifier;
            } else {
                $orgidentifier = $sco->organization;
            }
            $scoid = $sco->id;
        }
        $cmid = $row->cmid;
        $coursename = $row->fullname;
         $assigned_date = date('Y-M-d H-i-s', $row->timecreated);

        $useridQry = '';
        $userIds = '';
        $login_id = $_SESSION['USER']->id;

        if ($_SESSION['idnumber'] == 1) {

        } elseif ($_SESSION['idnumber'] == 2) {
            $records = $DB->get_records_sql("select * from {user} where maildigest=$login_id");
            if (count($records)) {
                foreach ($records as $row) {
                    $userIds[] = "'" . $row->id . "'";
                }
                if ($userIds != '') {
                    $userIds = implode(',', $userIds);
                    $useridQry = " and st.userid IN ($userIds)";
                }
            }
        } else {
            $useridQry = "and st.userid = $login_id";
        }


        $scom_id = $row->cmid;
        $cm = get_coursemodule_from_id('scorm', $scom_id, 0, false, MUST_EXIST);
        $course = $DB->get_record('course', array('id' => $cm->course), '*', MUST_EXIST);
        $scorm = $DB->get_record('scorm', array('id' => $cm->instance), '*', MUST_EXIST);

        if($usertype == 3)
        {
        $query = "SELECT DISTINCT CONCAT(u.id, '#', COALESCE(st.attempt, 0)) AS uniqueid, st.scormid AS scormid, st.attempt AS attempt, u.id AS userid,u.picture,u.firstname,u.lastname,u.username,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.idnumber,u.icq,u.department,u.skype,pd.department,pd.did,pg.gid,pg.group_name,pc.cid,pc.company FROM mdl_user u LEFT JOIN mdl_scorm_scoes_track st ON st.userid = u.id AND st.scormid = $scorm->id  LEFT JOIN mdl_ppc_department pd ON pd.did=u.department LEFT JOIN mdl_ppc_groups pg On pg.gid=u.icq LEFT JOIN mdl_ppc_company pc On pc.cid=u.skype WHERE (st.userid IS NOT NULL) and u.idnumber != 0 and u.skype ='$companyid' and u.trackforums IN (1,0)"; 
        }
        else
        {
            $query = "SELECT DISTINCT CONCAT(u.id, '#', COALESCE(st.attempt, 0)) AS uniqueid, st.scormid AS scormid, st.attempt AS attempt, u.id AS userid,u.picture,u.firstname,u.lastname,u.username,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.idnumber,u.icq,u.department,u.skype,pd.department,pd.did,pg.gid,pg.group_name,pc.cid,pc.company FROM mdl_user u LEFT JOIN mdl_scorm_scoes_track st ON st.userid = u.id AND st.scormid = $scorm->id  LEFT JOIN mdl_ppc_department pd ON pd.did=u.department LEFT JOIN mdl_ppc_groups pg On pg.gid=u.icq LEFT JOIN mdl_ppc_company pc On pc.cid=u.skype WHERE (st.userid IS NOT NULL) and u.idnumber != 0 and u.skype ='$companyid' and u.trackforums='$usertype'";  
        }
        $attempts = $DB->get_records_sql($query);
        $row = array();
        $statusval = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                                            $totaltimeval = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                                            $scoreval = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                                            $valueu1 = 1;
                                            $valueu2 = 1;
                                            $valueu3 = 1;

        if (isset($attempts)) {
            foreach ($attempts as $scouser) {

                $type = '';
                $status = '';
                $totaltime = '';
                $score = '';

                if (!empty($scouser->attempt)) {
                    $timetracks = scorm_get_sco_runtime($scorm->id, false, $scouser->userid, $scouser->attempt);
                } else {
                    $timetracks = '';
                }


                 global $DB;

                $tracks = $DB->get_records_sql("SELECT id, element, value FROM {scorm_scoes_track}  WHERE scormid = ? AND userid = ? AND element IN('cmi.core.lesson_status','cmi.core.total_time','cmi.core.score.raw','cmi.core.exit') and DATE(FROM_UNIXTIME($timetracks->finish))>= '$fromdate' AND DATE(FROM_UNIXTIME($timetracks->finish))<= '$todate'" , array($scorm->id, $scouser->userid));
                if (sizeof($tracks)) {
                     $exInc = 2;
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Course Title ')
            ->setCellValue('B1', 'Name')
            ->setCellValue('C1', 'Company')
            ->setCellValue('D1', 'Department')
            ->setCellValue('E1', 'Branch')
             ->setCellValue('F1', 'Assigned Date')
           ->setCellValue('G1', 'Start Date')
            ->setCellValue('H1', 'Last Visited')
             ->setCellValue('I1', 'Total Time')
            ->setCellValue('J1', 'Course Score')
            ->setCellValue('K1', 'Course Status');
                foreach ($tracks as $trac) {
                    if ($trac->element == 'cmi.core.lesson_status') {
                        $status = $trac->value;
                    }
                    if ($trac->element == 'cmi.core.total_time') {
                        $totaltime = $trac->value;
                    }
                    if ($trac->element == 'cmi.core.score.raw') {
                        $score = $trac->value;
                        $scoreval[$valueu3] = $score;
                        $valueu3 = $valueu3 + 1;
                    }


                    $scouser->times = $timetracks;
                    $scouser->status = $status;
                    $scouser->totaltime = $totaltime;
                    $scouser->score = $score;
                    $row[] = $scouser;

                    $start_date = $finish_date = '';

                    if (isset($scouser->times)) {
                        if (isset($scouser->times->start))
                            $start_date = date('Y-M-d H-i-s', $scouser->times->start);
                        if (isset($scouser->times->finish))
                            $finish_date = date('Y-M-d H-i-s', $scouser->times->finish);
                    }

                }

                $objPHPExcel->getActiveSheet()->setCellValue('A' . $exInc, $coursename)
                    ->setCellValue('B' . $exInc, $scouser->firstname.''. $scouser->lastname)
                    ->setCellValue('C' . $exInc, $scouser->company)
                    ->setCellValue('D' . $exInc, $scouser->department)
                     ->setCellValue('E' . $exInc, $scouser->group_name)
                    ->setCellValue('F' . $exInc, $assigned_date)
                    ->setCellValue('G' . $exInc, $start_date)
                    ->setCellValue('H' . $exInc, $finish_date)
                    ->setCellValue('I' . $exInc, $totaltime)
                    ->setCellValue('J' . $exInc, $score)
                    ->setCellValue('K' . $exInc, ucfirst($status));

                $exInc++;
                }
            }
        }
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="reports.xls"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);

    ob_end_clean();
    ob_start();
    $objWriter->save('php://output');
    exit();
}
}
else
{
    echo "Your To date should be greater than from date";
}
}

?>

谁能帮我看看我犯了什么错误。

提前致谢。

Answer 1

我通过像这样更改查询解决了我的问题

  if($usertype == 3)
        {
        $query = "SELECT DISTINCT CONCAT(u.id, '#', COALESCE(st.attempt, 0)) AS uniqueid, st.scormid AS scormid, st.attempt AS attempt, u.id AS userid,u.picture,u.firstname,u.lastname,u.username,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.idnumber,u.icq,u.department,u.skype,pd.department,pd.did,pg.gid,pg.group_name,pc.cid,pc.company FROM mdl_user u LEFT JOIN mdl_scorm_scoes_track st ON st.userid = u.id AND st.scormid = $scorm->id  LEFT JOIN mdl_ppc_department pd ON pd.did=u.department LEFT JOIN mdl_ppc_groups pg On pg.gid=u.icq LEFT JOIN mdl_ppc_company pc On pc.cid=u.skype WHERE (st.userid IS NOT NULL) and u.idnumber != 0 and u.skype ='$companyid' and DATE(FROM_UNIXTIME(st.timemodified))>= '$fromdate' AND DATE(FROM_UNIXTIME(st.timemodified))<= '$todate' and u.trackforums IN (1,0)"; 
        }
        else
        {
          $query = "SELECT DISTINCT CONCAT(u.id, '#', COALESCE(st.attempt, 0)) AS uniqueid, st.scormid AS scormid, st.attempt AS attempt, u.id AS userid,u.picture,u.firstname,u.lastname,u.username,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.idnumber,u.icq,u.department,u.skype,pd.department,pd.did,pg.gid,pg.group_name,pc.cid,pc.company FROM mdl_user u LEFT JOIN mdl_scorm_scoes_track st ON st.userid = u.id AND st.scormid = $scorm->id  LEFT JOIN mdl_ppc_department pd ON pd.did=u.department LEFT JOIN mdl_ppc_groups pg On pg.gid=u.icq LEFT JOIN mdl_ppc_company pc On pc.cid=u.skype WHERE (st.userid IS NOT NULL) and u.idnumber != 0 and u.skype ='$companyid'  and DATE(FROM_UNIXTIME(st.timemodified))>= '$fromdate' AND DATE(FROM_UNIXTIME(st.timemodified))<= '$todate' and u.trackforums='$usertype'";  
        }


$tracks = $DB->get_records_sql("SELECT id, element, value FROM {scorm_scoes_track}  WHERE scormid = ? AND userid = ? AND element IN('cmi.core.lesson_status','cmi.core.total_time','cmi.core.score.raw','cmi.core.exit')" , array($scorm->id, $scouser->userid));