从PHP、mysql导出到excel（页面中的多个查询），代码无法正常运行？

Question

您好，下面是我用来将数据从 php 页面导出到 excel 文件的代码。

$con = mysql_connect("localhost","root","testp");
mysql_select_db("dbone1", $con);

$ts = date("d/m/y : H:i:s", time()) ;

$datav=$_GET["datav"];
$pathogen=$_GET["pathogen"];
$topic1 = $_GET['toc1'];
$testvar1 = unserialize(rawurldecode($_GET['ind1']));
$subindg1 = $_GET['isg1'];
$topic2 = $_GET['toc2'];
$testvar2 = unserialize(rawurldecode($_GET['ind2']));
$subindg2 = $_GET['isg2'];
$topic3 = $_GET['toc3'];
$testvar3 = unserialize(rawurldecode($_GET['ind3']));
$subindg3 = $_GET['isg3'];
$topic4 = $_GET['toc4'];
$testvar4 = unserialize(rawurldecode($_GET['ind4']));
$subindg4 = $_GET['isg4'];
$topic5 = $_GET['toc5'];
$testvar5 = unserialize(rawurldecode($_GET['ind5']));
$subindg5 = $_GET['isg5'];
$testvar6 = unserialize(rawurldecode($_GET['cnty']));

function addWrapper (&$value, $key, $wrapper) {
$value = $wrapper.$value.$wrapper;
//no return, passed by reference
}

if(empty($testvar6))
{
}
else
{
array_walk($testvar6, 'addWrapper', "");
$sql_cntys = implode("','", $testvar6);
//echo $sql_cntys;
}

$num1 = mysql_query("SELECT * FROM mtable WHERE DataVersionDate='$datav' && Pathogen='$pathogen' && Topic='$topic1' && Indicator='$ind1' && IndicatorSubGroup='$subindg1' && (Country IN ('$sql_cntys') OR  Location_Who IN ('$sql_cntys')) ");
$num2 = mysql_query("SELECT * FROM mtable WHERE DataVersionDate='$datav' && Pathogen='$pathogen' && Topic='$topic2' && Indicator='$ind2' && IndicatorSubGroup='$subindg2' && (Country IN ('$sql_cntys') OR  Location_Who IN ('$sql_cntys')) ");
$num3 = mysql_query("SELECT * FROM mtable WHERE DataVersionDate='$datav' && Pathogen='$pathogen' && Topic='$topic3' && Indicator='$ind3' && IndicatorSubGroup='$subindg3' && (Country IN ('$sql_cntys') OR  Location_Who IN ('$sql_cntys')) ");
$num4 = mysql_query("SELECT * FROM mtable WHERE DataVersionDate='$datav' && Pathogen='$pathogen' && Topic='$topic4' && Indicator='$ind4' && IndicatorSubGroup='$subindg4' && (Country IN ('$sql_cntys') OR  Location_Who IN ('$sql_cntys')) ");
$num5 = mysql_query("SELECT * FROM mtable WHERE DataVersionDate='$datav' && Pathogen='$pathogen' && Topic='$topic5' && Indicator='$ind5' && IndicatorSubGroup='$subindg5' && (Country IN ('$sql_cntys') OR  Location_Who IN ('$sql_cntys')) ");

$data = array();

while($row = mysql_fetch_assoc($num1))
{
    $c = $row['Country'];
    if (!isset($data[$c]))
    {
        $data[$c] = array('Country' => $c);
    }
    $data[$c]['MidEstimate1'] = $row['MidEstimate'];
}
while($row = mysql_fetch_assoc($num2))
{
    $c = $row['Country'];
    if (!isset($data[$c]))
    {
        $data[$c] = array('Country' => $c);
    }
    $data[$c]['MidEstimate2'] = $row['MidEstimate'];
}

while($row = mysql_fetch_assoc($num3))
{
    $c = $row['Country'];
    if (!isset($data[$c]))
    {
        $data[$c] = array('Country' => $c);
    }
    $data[$c]['MidEstimate3'] = $row['MidEstimate'];
}

while($row = mysql_fetch_assoc($num4))
{
    $c = $row['Country'];
    if (!isset($data[$c]))
    {
        $data[$c] = array('Country' => $c);
    }
    $data[$c]['MidEstimate4'] = $row['MidEstimate'];
}

while($row = mysql_fetch_assoc($num5))
{
    $c = $row['Country'];
    if (!isset($data[$c]))
    {
        $data[$c] = array('Country' => $c);
    }
    $data[$c]['MidEstimate5'] = $row['MidEstimate'];
}

require_once '/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Country')
            ->setCellValue('B1', 'MidEstimate1')
            ->setCellValue('C1', 'MidEstimate2')
            ->setCellValue('D1', 'MidEstimate3')
            ->setCellValue('E1', 'MidEstimate4')
            ->setCellValue('F1', 'MidEstimate5');

$r=2;

$i = 0;

foreach ($data as $row)
{
    ($i % 5);

            $a="A".$r;
        $b="B".$r;
        $c="C".$r;
        $d="D".$r;
        $e="E".$r;
        $f="F".$r;

        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue($a, $row['Country'])
                ->setCellValue($b, $row['MidEstimate1'])
                ->setCellValue($c, $row['MidEstimate2'])
                ->setCellValue($d, $row['MidEstimate3'])
                ->setCellValue($e, $row['MidEstimate4'])
                ->setCellValue($f, $row['MidEstimate5']);

        $r++;

}



$objPHPExcel->setActiveSheetIndex(0);

header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=$ts.xls");
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

导出时只有标题（列标题）而没有数据？请帮我纠正这个代码。

Answer 1

您刚刚将完整的代码放在这里，所以我假设您对它失败的地方一无所知。嗯，这是最基本的故障排除技术：隔离无法按预期工作的代码部分。在您的例子中，有趣的点是：

1. SQL 查询

首先，在调试时，您必须在屏幕上打印 SQL 代码并在您最喜欢的 MySQL 客户端中运行它，以确保它返回您想要的数据。

其次，您始终必须检查 mysql_query() 的返回值，并在失败时采取措施（例如，记录mysql_error() 并中止脚本）。

2. 变量

使用 var_dump() 检查变量并确保它们具有您认为具有的信息。

3. PHPExcel

在成功填充 $data 之前，不要尝试创建 Excel 电子表格。

---

编辑：如果您按照我的建议并打印了 SQL 查询：

var_dump(
    "SELECT * FROM mtable WHERE DataVersionDate='$datav' && Pathogen='$pathogen' && Topic='$topic1' && Indicator='$ind1' && IndicatorSubGroup='$subindg1' && (Country IN ('$sql_cntys') OR  Location_Who IN ('$sql_cntys')) "
);

您就会知道 $datav 是否具有正确的值。如果该值不正确，那么您就知道问题出在这里：

$datav=$_GET["datav"];

否则，您可能会忘记该 URL。等等。