I'm trying to create an attendance table in a month. Please check it

I have this query

select d.Nik,d.tanggal_absensi,d.kodekehadiran,c.NamaSiswa,d.tanggal_absensi,day(d.tanggal_absensi) tanggal from absensi d left join mastersiswa c on d.NIK = c.NIK left join siswa_kelas z on d.Nik = z.NIK left join kelas a on a.ID = z.Kelas where a.ID = '1' and month(d.tanggal_absensi) = '12' GROUP BY d.Nik,d.tanggal_absensi,d.kodekehadiran,c.NamaSiswa,a.Kelas,d.tanggal_absensi,day(d.tanggal_absensi)

with my query i get this result

    Nik   tanggal_absensi   kodekehadiran NamaSiswa   tanggal_absensi tanggal
    1111       2016­12­18         H           Nama A       2016­-12-­18    18
    1111       2016­12­19         I           Nama A       2016­-12-­19    19
    123456     2016­12­18         H         ADI SURIONO    2016-­12-­18    18
    123456    2016­12­19         H         ADI SURIONO    2016-­12-­19    19

Here is my HTML & PHP

 <table width="100%" class="table table-bordered table-striped">
                        <thead>
                            <tr align="center">
                            <th width="6%">No</th>
                            <th width="19%">Nama</th>
                            <?php for($x=1;$x<=31;$x++){ ?><th><?php echo $x;?></th> <? } ?>
                          </tr>
                        </thead>
                        <tbody>
                        <?
                        $no = 0;
                        foreach($absensi as $tampil){
                            $no++;
                        ?>
                            <tr>
                            <td><?=$no?></td>
                            <td><?=$tampil->NamaSiswa;?></td>

                            <?php for($x=1;$x<=31;$x++){ ?>
                                <td>
                                    <?php if($tampil->tanggal == $x){echo $tampil->kodekehadiran;} ?>
                                </td>
                            <?php } ?>    
                            </tr>
                            <?php } ?>
                        </tbody>
                    </table>    

I get this result in my website.

How can i achieve ?

No  Nama     1 - 2 - 3 - 4 - 5 - 18  19 - 20 - 21 - 22
1   NAMA A   -   -   -   -   -   H   I     -   -    -
2   Nama B   -   -   -   -   -   I   H     -   -    -   

So, I don't know how to list all day in a month so, i using for with php to create it. Sorry for my bad english.

You can do it, but you need to refactor a part of your code/logic:

What you have as data:

<?php
// Data from DB
$data[] = ['Nik' => '111', 'tanggal_absensi' => '2016­12­18', 'kodekehadiran' => 'H', 'NamaSiswa' => 'Nama A']
$data[] = ['Nik' => '111', 'tanggal_absensi' => '2016­12­19', 'kodekehadiran' => 'I', 'NamaSiswa' => 'Nama A']
?>

What you need - a multi level data array

<?php
$dataCalculated[111] = [
  '2016­12­18' => ['Nik' => '111', 'tanggal_absensi' => '2016­12­18', 'kodekehadiran' => 'H', 'NamaSiswa' => 'Nama A'],
  '2016­12­19' => ['Nik' => '111', 'tanggal_absensi' => '2016­12­19', 'kodekehadiran' => 'I', 'NamaSiswa' => 'Nama A']
];
?>

How to do it ? Loop on each $data and create another $dataCalculated that will include the logic of your code. Here: All row by user (Nik), and after by date. As you can see this is the first column and the header of the table :)

<?php
$dataCalculated = [];
foreach( $data as $row ) {
  // We create the first array level, if this is the first time
  if( !isset($dataCalculated[ $row['Nik'] ]) ) {
    $dataCalculated[ $row['Nik'] ] = [];
  }
  // We fill the row data
  $dataCalculated[ $row['Nik'] ][ $row['tanggal_absensi'] ] = $row;
}
?>

Refactor your HTML/PHP view code:

After, you can iterate on this new array on your HTML file.

<?php for($x=1 ; $x<=31 ; $x++): ?>
  <td>
  <?php if( isset($dataCalculated[ $tampil->Nik ][ date('Y-m-d', mktime(0, 0, 0, '12', $x, '2016')) ]) ): ?>
    <?php echo $dataCalculated[ $tampil->Nik ][ date('Y-m-d', mktime(0, 0, 0, '12', $x, '2016')) ]['kodekehadiran']; ?>
  <?php endif; ?>
  </td>
<?php endfor; ?>

Be careful, I entered manually some data (mktime -> month & year)

Last change -> duplicates lines

Of course, some lines will not be useful now. You just need one line by user.

• You can skip the line if the user was already displayed (need a $previousNik variable and a check.
• You can also create another array with the list of all users

Ex:

<?php
$dataCalculated = [];
$dataUsers = [];

foreach( $data as $row ) {
  // We create the first array level, if this is the first time
  if( !isset($dataCalculated[ $row['Nik'] ]) ) {
    $dataCalculated[ $row['Nik'] ] = [];
  }
  // We fill the row data
  $dataCalculated[ $row['Nik'] ][ $row['tanggal_absensi'] ] = $row;
  $dataUsers[ $row['Nik'] ] = ['Nik' => $row['Nik'], 'NamaSiswa' => $row['NamaSiswa']];
}
?>

And use it in your code :) (Variable $dataUsers)

Have fun !