PDO 内存耗尽

php mysql pdo mariadb

2022-08-30 18:36:55

这是常见问题，但我别无选择，只是为了在Excel文件中获得适当的标题和正文

这里它是如何开始的

当请求打印时，我首先开始进行查询以提取数据库中的标头

SELECT instruments.in_id, instrument_parameters.ip_id,
CASE WHEN gv_x_ipid = -1 THEN 'datetime' ELSE '' END xlabel,
CASE WHEN ip_label LIKE '%Reservoir%' THEN 0 ELSE in_order END legendIndex,
CASE WHEN in_name = 'General' THEN ip_label ELSE in_name END ylabel            
FROM graph_plot
LEFT JOIN attributes gptype ON gp_type = gptype.at_id
LEFT JOIN graph_value ON gp_id = gv_gpid
LEFT JOIN instrument_parameters ON gv_y_ipid = ip_id
LEFT JOIN attributes pmunit ON ip_unit = pmunit.at_id
LEFT JOIN instrument_reading yvalue ON gv_y_ipid = iv_ipid
LEFT JOIN instruments ON iv_inid = in_id
WHERE gp_diid = :di_id AND 
      gp_type = :rpt_type AND 
      iv_status = 'Y' AND
      iv_inid in (".implode(",", $coll->inid).") AND
      gv_y_ipid in (".implode(",", $coll->ipid).")
GROUP BY ylabel
ORDER BY legendIndex

这将产生大量的标题，我将使它成为这样的

DATE | Instrument1 | Instrument2 | Instrument3

将基于上面的查询是动态的。我将其存储在新变量中。但是保存数据库结果的原始变量保持不变。Instrument?

稍后，使用相同的参数，和，也是另一个附加参数，并进行另一个查询只是为了返回数据库中可用日期的长列表。这是基于和。:di_id:rpt_typestartDtendDtstartDtendDt

$sql2 = "SELECT iv_reading FROM instrument_reading WHERE iv_inid = :inid AND iv_ipid = :ipid AND iv_date = :dt AND iv_status = 'Y'";

当它完成获取日期时，我像这样进行两个循环

foreach ($dates as $key => $dt) {       
    foreach ($resp as $InstNo => $InstRow) {
        try {
            $stmt2->execute(array(':dt' => $dt, ':inid' => $InstRow->in_id, ':ipid' => $InstRow->ip_id));
            $rowDb = $stmt2->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT);
        } catch(PDOException $e) {
            echo '{"error":{"text":"'. $e->getMessage() .'"}}'; 
        }
    }
}

首先，它开始循环日期，其次开始循环标头（基于在获取日期之前进行的查询）。我的问题我总是卡在这里

$stmt2->execute(array(':dt' => $dt, ':inid' => $InstRow->in_id, ':ipid' => $InstRow->ip_id));

你觉得怎么样？有没有更好的方法来解决这个问题？

供您参考，我使用Slim和PHPExcel。PHPExcel可能有内存问题，我正在考虑切换到Spout，但文档仍然关于基本的东西。

答案 1

在 SQL 中，可以考虑使用 limit 子句来缓解内存负载，如下所示：

$handle = fopen("file.csv", "wb");
$statement = "
SELECT  instruments.in_id, instrument_parameters.ip_id,
       CASE WHEN gv_x_ipid = -1 THEN 'datetime' ELSE '' END xlabel,
       CASE WHEN ip_label LIKE '%Reservoir%' THEN 0 ELSE in_order END legendIndex,
       CASE WHEN in_name = 'General' THEN ip_label ELSE in_name END ylabel
    FROM  graph_plot
    LEFT JOIN  attributes gptype ON gp_type = gptype.at_id
    LEFT JOIN  graph_value ON gp_id = gv_gpid
    LEFT JOIN  instrument_parameters ON gv_y_ipid = ip_id
    LEFT JOIN  attributes pmunit ON ip_unit = pmunit.at_id
    LEFT JOIN  instrument_reading yvalue ON gv_y_ipid = iv_ipid
    LEFT JOIN  instruments ON iv_inid = in_id
    WHERE  gp_diid = :di_id
      AND  gp_type = :rpt_type
      AND  iv_status = 'Y'
      AND  iv_inid in (".implode(",", $coll->inid).")
      AND  gv_y_ipid in (".implode(",", $coll->ipid).")
    GROUP BY  ylabel
    ORDER BY  legendIndex
    LIMIT  250
";
$prep = $dbh->prepare($statement);
for ($i = 0; $prep -> rowCount < 250; $i+= 250) {
    fputcsv(prep->fetchAll());
    $prep = $dbh->prepare($statement.' OFFSET'.$i);
}
fclose($handle);

或者，您可以使用系统并调用SELECT INTO，设置权限（如有必要）和Bob的叔叔。

答案 2

您尚未终止循环。fetch

$rowDb = $stmt2->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT);

获取“下一个”行或关闭“游标”并终止。

您是否希望只获得一行？如果是这样，请考虑执行 fetchAll。（警告：结果集可能是数组中的额外级别。