How to create html table with PHP for show month by month payments Based on MySQL

Question

I need to create a anual table with the months from left to right knowing that the first column will be the apartment unit.

I realize I'm stuck with something wrong in the query on the PHP code or both... not sure what is the correct approach. My brain is on a loop for more than a week...

The SQL query returns the following results:

My PHP code:

<?php
        require_once(__DIR__."/../config.php");

?>
        <div class="box-body">
          <table id="mapa_table" class="table sakotable table-striped">
            <thead>
              <tr style="background-color:#3c8dbc;color:#d9edf7">
                <th>APTO</th>
                <th>JAN</th>
                <th>FEV</th>
                <th>MAR</th>
                <th>ABR</th>
                <th>MAI</th>
                <th>JUN</th>
                <th>JUL</th>
                <th>AGO</th>
                <th>SET</th>
                <th>OUT</th>
                <th>NOV</th>
                <th>DEZ</th>

              </tr>

            </thead>
            <tbody>
<?php

    $result = $dbcondo->query("SELECT lcs_quotas_fraccoes.mes_id, lcs_unit.unit_no, lcs_quotas_fraccoes.EstadoPagamento FROM lcs_quotas_fraccoes INNER JOIN lcs_unit ON (lcs_quotas_fraccoes.Unit_ID = lcs_unit.uid)");
    while($row = $result->fetch()){

?>
      <tr>
        <td><?=$row['unit_no']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>
        <td><?=$row['EstadoPagamento']; ?></td>

        </tr>
<?php } ?>
    </tbody>


            </table>
        </div>

mes_id is the month.

The first column is the apartment unit and should not be repeated and I'm not able to achieve that.

The second column should apear in the first row side by side and not one over the other.

Thanks in advance for your time.

Answer 1

One way to make this work as intended would be to build an array in PHP, then populate that array with the results from the database, then build the table based on that array.

However, a more efficient way would be to build the SQL query to return the data in the format you want, using GROUP BY with aggregated columns:

SELECT uu.unit_no,
       MAX(CASE WHEN qf.mes_id = 1 THEN qf.EstadoPagamento ELSE NULL END) as Jan,
       MAX(CASE WHEN qf.mes_id = 2 THEN qf.EstadoPagamento ELSE NULL END) as Fav,
       MAX(CASE WHEN qf.mes_id = 3 THEN qf.EstadoPagamento ELSE NULL END) as Mar,
       MAX(CASE WHEN qf.mes_id = 4 THEN qf.EstadoPagamento ELSE NULL END) as Abr,
       MAX(CASE WHEN qf.mes_id = 5 THEN qf.EstadoPagamento ELSE NULL END) as Mai,
       MAX(CASE WHEN qf.mes_id = 6 THEN qf.EstadoPagamento ELSE NULL END) as Jun,
       MAX(CASE WHEN qf.mes_id = 7 THEN qf.EstadoPagamento ELSE NULL END) as Jul,
       MAX(CASE WHEN qf.mes_id = 8 THEN qf.EstadoPagamento ELSE NULL END) as Ago,
       MAX(CASE WHEN qf.mes_id = 9 THEN qf.EstadoPagamento ELSE NULL END) as Set,
       MAX(CASE WHEN qf.mes_id = 10 THEN qf.EstadoPagamento ELSE NULL END) as Out,
       MAX(CASE WHEN qf.mes_id = 11 THEN qf.EstadoPagamento ELSE NULL END) as Nov,
       MAX(CASE WHEN qf.mes_id = 12 THEN qf.EstadoPagamento ELSE NULL END) as Dez
  FROM lcs_quotas_fraccoes qf INNER JOIN lcs_unit uu ON qf.Unit_ID = uu.uid 
 GROUP BY uu.unit_no
 ORDER BY uu.unit_no;

With this query, every unit will be on its own row, simplifying the table construction in PHP. Be sure to update the query to filter on years, and to double check the accuracy of the results.