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.
You have not terminated the
fetch
loop.gets the "next" row or closes the 'cursor' and terminates.
Are you expecting to get exactly one row? If so, consider doing fetchAll. (Caution: the resultset may be an extra level deep in arrays.)