I’m trying to fill table with values which I’m getting from the database but when I loop though array some values are getting out of the table.
DB structure with dump:
https://www.db-fiddle.com/f/9gTDRBFnguHtagJC4YNnRg/5
DB Values Explanation:
Data column contains JSON format data of users activity.
For example
January:
{
"Jan":"1563", // Total users registered in January
"Feb":"0",
"Mar":"0",
"Apr":"0",
"May":"0",
"Jun":"0",
"Jul":"13", // Out of 1563 registered users only 13 users are active in July
"Aug":"7", // Out of 1563 registered users only 7 users are active in August
"Sep":"4", // Out of 1563 registered users only 4 users are active in September
"Oct":"0",
"Nov":"0",
"Dec":"0"
}
February:
{
"Feb":"1727", // Total users registered in February
"Mar":"0",
"Apr":"0",
"May":"0",
"Jun":"0",
"Jul":"10", // Out of 1727 registered users only 10 users are active in July
"Aug":"11", // Out of 1727 registered users only 11 users are active in August
"Sep":"8", // Out of 1727 registered users only 8 users are active in September
"Oct":"0",
"Nov":"0",
"Dec":"0"
}
Code:
$query = "SELECT * FROM monthly_report";
$result = $this->db->query($query)->result_array();
$data['data'] = $result;
<table class="table table-hover table-striped">
<thead>
<tr>
<th style="text-align:center;">Month</th>
<th style="text-align:center;">Total Registered</th>
<th style="text-align:center;">January</th>
<th style="text-align:center;">February</th>
<th style="text-align:center;">March</th>
<th style="text-align:center;">April</th>
<th style="text-align:center;">May</th>
<th style="text-align:center;">June</th>
<th style="text-align:center;">July</th>
<th style="text-align:center;">August</th>
<th style="text-align:center;">September</th>
<th style="text-align:center;">October</th>
<th style="text-align:center;">November</th>
<th style="text-align:center;">December</th>
</tr>
</thead>
<tbody>
<?php
$num = 0;
for ($m = 1; $m <= 12; $m++) {
$month = date('F', mktime(0, 0, 0, $m, 1, date('Y')));
echo "<tr style='text-align:center;'>";
echo "<td>" . $month . "</td>";
$short_month_name = date('M', strtotime("2022-$m-01"));
if (isset($data[$num]['month']) && $data[$num]['month'] == $short_month_name) {
$decode_data = json_decode($data[$num]['data'], true);
$decode_isset = isset($decode_data[$short_month_name]) ? $decode_data[$short_month_name] : '';
echo "<td>" . $decode_isset . "</td>";
}
$num_1 = 0;
for ($day = 1; $day <= 12; $day++) {
$short_month_name_1 = date('M', strtotime("2022-$day-01"));
if (isset($data[$num]['month']) && $data[$num]['month'] == $short_month_name_1) {
$decode_data_1 = json_decode($data[$num]['data'], true);
$decode_isset_1 = isset($decode_data_1) ? $decode_data_1 : [];
foreach ($decode_isset_1 as $v) {
if (!empty($v)) {
echo "<td>" . $v . "</td>";
} else {
echo "<td>0</td>";
}
}
} else {
echo "<td>0</td>";
}
$num_1++;
}
echo "</tr>";
$num++;
}
?>
</tbody>
</table>
And output is:
In above output vertical months are registered users count and horizontal rows are their activity.
Example:
In vertical January month 1563 users are registered, in same row horizontal January has 1563 because registration will count as their activity. In same row July month 13 users are active out of 1563 registered users and same goes on for other months of same row.
I’m getting values in expected tds but want to remove extra generated td (Marked in red box).
2
Answers
You’ve got one too many
for
loops, the innermostforeach
isn’t needed. In fact, because you are working with data that is constrained to known values that must exist in a specific order, you shouldn’t even trust it.Because you have a database, are dealing with JSON inside an array, and have a lot of data, it is unfortunately hard to reproduce your code. Instead, I’ve made a simple array that mimics your database with the JSON already decoded, but hopefully it makes sense still.
I usually comment my code more but I think it mostly speaks relative to your code. One major change I added was to use
??
which can make missing array items much easier to read. One known potential bug is if the dates coming out of the database aren’t in the correct order this will render strangely.Demo: https://3v4l.org/JecP1
Do it all in SQL by using JSON_EXTRACT() so that you don’t need any of that PHP monkey-business.
SQL: (Demo)
Where I work, we choose to omit 0 values from reports similar to this because it allows users to scan for non-zero values easier.