I am building a very huge table and I'd like to reduce the number of query I execute. Now it takes up to 120 seconds to build a 5000 rows for 50 columns table. Column headers are defined with this for loop:
for($year = $yearMin; $year<=$yearMax; $year++){
for($month = 1; $month <= 12; $month++){
$month = sprintf("%02s", $month);
echo '<th class="report">'.$month.'/'.$year.'</th>';
}
}
Each row is a bank account. Each cell is a specific mont/year couple. Actually I run a query to check how many money where in that specific month/year in that bank account. The query returns a value only if the balance is not 0 and this happens three or four times on 50 columns. So my thought have been to run the query once for row instead of 50 and to extract an array with this structure:
$data['period']='06/2015';
$data['balance']='1000';
repeated for all the balances the query returns for that row.
My question is:
How can I correctly iterate the $data array to put the values in the exact cells? I was thinking about something like this but consider that I'll repeat this code for 1000 rows. EDIT: I have put here only the code that builds those specific columns and not the entire row. So I know I miss <tr>
tags.
for($year = $yearMin; $year<=$yearMax; $year++){
for($month = 1; $month <= 12; $month++){
$month = sprintf("%02s", $month);
$period = $month.'/'.$year;
echo '<td>';
echo ($period==$data['period']) ? $data['balance'] : '0';
echo '</td>';
}
}
SIDENOTE: I can also build the query only once adding the bank account number (that identify the row) and then I'd do something like this (just an idea of code):
foreach($creds as $cred){
for($year = $yearMin; $year<=$yearMax; $year++){
for($month = 1; $month <= 12; $month++){
if($cred==$data['number'])[
$month = sprintf("%02s", $month);
$period = $month.'/'.$year;
echo '<td>';
echo ($period==$data['period']) ? $data['balance'] : '0';
echo '</td>';
}
}
}
}
Also note that a solution that implies to build an empty table and iterate the json encoded php array $data to put the values in is also applicable but IMHO can be difficult to set up due to the need to assign to each cell classes to refer to bank account and month/year.