PDO Memory Exhausted

Question

This is common issue but I have no choice to code it like this just to get appropriate header and body in Excel file

here how it starts

When a request been made to print, I first began make a query to fetch the headers in the database

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 

and this will produce numbers of headers that I will make it to be like this

DATE | Instrument1 | Instrument2 | Instrument3

The Instrument? will be dynamic based on the query above. I store this in new variable. But the original variable that holds the database results remain intact.

Later, using the same parameters, :di_id and :rpt_type, also another additional parameters, startDt and endDt to make another query just to return a long list of available dates in database. This is based on the startDt and endDt.

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

When it finish getting the dates, I make two loop like this

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() .'"}}';          }     } } 

First, it starts looping the date and second it begins looping the headers (based on the query made right before getting the dates). My problem I always stuck here

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

What do you think? Is there any better way to handle this?

For your information, I use Slim and PHPExcel. PHPExcel might have memory issue and I'm thinking to switch to Spout but the documents still about the basic stuff.

Answer 1

In your SQL, you may consider a limit clause to ease the memory load as follows:

$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); 

Alternatively, you could use system and call SELECT INTO, set the permissions (if necessary) and Bob's your uncle.