0

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.

enter image description here

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.

Improve this question

1 Answer 1

Reset to default
1

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 !

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thanks for the answer. so i need to change my result array first?
I don't know where to put it :( . After some try data still duplicate and kodekehadiran didn't show up

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.