absence monthly report

<?php
/*********************************
FILENAME    : absen.php
CREATE BY   : cahya dsn
PURPOSE     : display monthly absence report
CREATE DATE : 2012-12-18
**********************************
#table creation

USE `test`;

DROP TABLE IF EXISTS `tbl_siswa`;
CREATE TABLE IF NOT EXISTS `tbl_siswa` (
  `id_siswa` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key untuk table tbl_siswa',
  `nama` varchar(30) NOT NULL COMMENT 'nama siswa',
  PRIMARY KEY(`id_siswa`)
) ENGINE=MyISAM COMMENT='tabel untuk menyimpan data siswa';

INSERT INTO `tbl_siswa`(`id_siswa`,`nama`) VALUES
(NULL,'Agus'),(NULL,'Budi'),(NULL,'Citra'),(NULL,'Dian'),(NULL,'Edo');

DROP TABLE IF EXISTS `tbl_absen`;
CREATE TABLE IF NOT EXISTS `tbl_absen` (
  `id_absen` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key untuk table tbl_absen',
  `id_siswa` bigint(20) unsigned NOT NULL COMMENT 'foreign key ke tabel tbl_siswa',
  `absen_date` date NOT NULL COMMENT 'tanggal absen',
  `status` ENUM('a','i','s') NOT NULL COMMENT 'status absen',
  PRIMARY KEY (`id_absen`)
) ENGINE=MyISAM COMMENT='tabel untuk menyimpan data absensi';

INSERT INTO `tbl_absen`(`id_absen`,`id_siswa`,`absen_date`,`status`) VALUES
(NULL,1,'2012-12-01','a'),(NULL,3,'2012-12-01','s'),(NULL,3,'2012-12-02','s'),
(NULL,4,'2012-12-04','i'),(NULL,2,'2012-12-07','a'),(NULL,1,'2012-12-15','s'),
(NULL,2,'2012-12-20','s'),(NULL,2,'2012-12-21','s');
*/
//database configuration
$dbhost='localhost';
$dbuser='root';
$dbpass='';
$dbname='test';
//database connection
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
//query to get absen datas for current month
$sql="SELECT b.nama,a.absen_date,a.status FROM tbl_absen a INNER JOIN tbl_siswa b ON a.id_siswa=b.id_siswa "
    ."WHERE a.absen_date BETWEEN '".date('Y-m-1')."' AND '".date('Y-m-t')."' "
    ."ORDER BY b.nama, a.absen_date ";
$result=$db->query($sql);
//variables initialization
$absen=array();
$nama='';
//generate absen datas from database
while($records=$result->fetch_object()){
  if($records->nama!=$nama){
    $nama=$records->nama;
    $absen[$nama]=array();
  }
  $absen[$nama][substr($records->absen_date,-2)]=$records->status;
}
//get number of days in given month (current month)
$num_days=date("t");
?>
<!DOCTYPE html>
<html>
<head>
<title>pelajaran table session 1</title>
<style>
table {padding:1px;}
.a {background-color:#c00;}
.s {background-color:#0c0;}
.i {background-color:#00c;}
td {text-align:center;padding:1px 5px;}
</style>
</head>
<body>
<table border=1>
<tr><th colspan="<?php echo (1+$num_days);?>"><?php echo date("F Y");?></th></tr>
<tr><th rowspan="2">Nama</th><th colspan="<?php echo ($num_days);?>">Tanggal</th></tr>
<tr><?php for($i=1;$i<=$num_days;$i++){ echo "<th>$i</th>";}?></tr>
<?php
foreach($absen as $key=>$value){
  echo "<tr><td>".$key."</td>";
  for($i=1;$i<=$num_days;$i++){
    echo "<td"
        .(
          isset($value[$i<10?'0'.$i:$i])
          ?
          ' class="'.$value[$i<10?'0'.$i:$i].'">'.$value[$i<10?'0'.$i:$i]
          :
          '>&nbsp;'
         )
        ."</td>";
  }
  echo "</tr>\n";
}
?>
</table>
</body>
</html>