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.
I need to use this function to search in the array the value of $data['period']. If the value is found the function returns the position of the value in the array and I can read the related $data['balance'] value:
function recursive_array_search($needle,$haystack) {
foreach($haystack as $key=>$value) {
$current_key=$key;
if($needle===$value OR (is_array($value) && recursive_array_search($needle,$value) !== false)) {
return $current_key;
}
}
return false;
}
Note that this function fails with array key 0 so to get the proper results you need to put as first item in the array an empty value: this way the correct results will start from key 1.
You first need a tr - represents a row and then a td/th - represent data in tr
$table_header_part = $table_body_part = "";
for($year = $yearMin; $year<=$yearMax; $year++){
for($month = 1; $month <= 12; $month++){
$month = sprintf("%02s", $month);
$table_header_part .='<th class="report">'.$month.'/'.$year.'</th>';
}
}
for($year = $yearMin; $year<=$yearMax; $year++){
for($month = 1; $month <= 12; $month++){
$month = sprintf("%02s", $month);
$period = $month.'/'.$year;
$table_body_part.= '<td>';
$table_body_part.= ($period==$data['period']) ? $data['balance'] : '0';
$table_body_part.= '</td>';
}
}
somewhere in your table
echo '<html>
<table>
<tr>
<th>Some Headings</th>
<th>Some Headings</th>
<th>Some Headings</th>
'.
$table_header_part
.'
</tr>
<tr>
<td>Some Data</td>
<td>Some Data</td>
<td>Some Data</td>
'.
$table_body_part
.'
</tr>
</table>
</html>';