Remove extra generated td values from the table - PHP

glitchy
October 30, 2022
133 views
1 vote
2 Answers

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 values image:

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).

Tags: php

Answers

- ChrisHaas
- October 30, 2022 at 3:47 pm
- 0 votes
0
You’ve got one too many for loops, the innermost foreach 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.
```
$data = [
    ['month' => 'Jan', 'year' => 2022, 'data' => ['Jan' => 1563, 'Mar' => 6]],
    ['month' => 'Feb', 'year' => 2022, 'data' => ['Feb' => 345, 'Mar' => 76, 'May' => 8]],
];

for($row = 1; $row <= 12; $row++){
    $monthNameLong = date('F', mktime(0, 0, 0, $row, 1, date('Y')));
    $monthNameShort = date('M', strtotime("2022-$row-01"));
    
    echo '<tr>';
    printf('<td>%1$s</td>', $monthNameLong);
    if(($data[$row - 1]['month'] ?? null) === $monthNameShort && isset($data[$row - 1]['data'][$monthNameShort])){
        $total = $data[$row - 1]['data'][$monthNameShort];
    } else {
        $total = '';
    }
    
    printf('<td>%1$s</td>', $total);
    
    for($col = 1; $col <= 12; $col++){
        $colMonthNameShort = date('M', strtotime("2022-$col-01"));
        printf('<td>%1$s</td>', $data[$row - 1]['data'][$colMonthNameShort] ?? 0);
    }
    
    echo '</tr>';
    echo PHP_EOL;
}
```
Demo: https://3v4l.org/JecP1
Login or Signup to reply.

Do it all in SQL by using JSON_EXTRACT() so that you don’t need any of that PHP monkey-business.

SQL: (Demo)

SELECT CASE `month`
          WHEN 'Jan' THEN 'January'
          WHEN 'Feb' THEN 'February'
          WHEN 'Mar' THEN 'March'
          WHEN 'Apr' THEN 'April'
          WHEN 'May' THEN 'May'
          WHEN 'Jun' THEN 'June'
          WHEN 'Jul' THEN 'July'
          WHEN 'Aug' THEN 'August'
          WHEN 'Sep' THEN 'September'
          WHEN 'Oct' THEN 'October'
          WHEN 'Nov' THEN 'November'
          WHEN 'Dec' THEN 'December'
      END `Month`,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, CONCAT('$.', `month`)), 0)) AS `Total Register`,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Jan'), 0)) AS January,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Feb'), 0)) AS February,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Mar'), 0)) AS March,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Apr'), 0)) AS April,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.May'), 0)) AS May,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Jun'), 0)) AS June,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Jul'), 0)) AS July,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Aug'), 0)) AS August,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Sep'), 0)) AS September,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Oct'), 0)) AS October,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Nov'), 0)) AS November,
      TRIM('"' FROM COALESCE(JSON_EXTRACT(data, '$.Dec'), 0)) AS December
FROM monthly_report

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.

Please signup or login to give your own answer.

Click here to cancel reply.

Remove extra generated td values from the table – PHP

Answers