yJTDp2CE

1<?php
2/*
3USE test;
4
5DROP TABLE IF EXISTS karyawan;
6CREATE TABLE IF NOT EXISTS karyawan(
7    id INT NOT NULL AUTO_INCREMENT,
8    nid INT,
9    nama VARCHAR(25),
10    PRIMARY KEY(id)
11);
12INSERT INTO karyawan(nid,nama)
13VALUES
14(699,'Hindun'),
15(900,'Sholeh');
16
17DROP TABLE IF EXISTS lembur;
18CREATE TABLE IF NOT EXISTS lembur(
19    no_lembur INT NOT NULL AUTO_INCREMENT,
20    nid INT,
21    tanggal DATE,
22    f1 INT,
23    f2 INT,
24    f3 INT,
25    f4 INT,
26    PRIMARY KEY(no_lembur)
27);
28INSERT INTO lembur(nid,tanggal,f1,f2,f3,f4)
29VALUES
30(699,'2016-08-05',1,6,0,0),
31(900,'2016-08-06',1,3,2,0),
32(699,'2016-08-07',1,7,0,0);
33*/
34$dbhost='localhost';
35$dbuser='root';
36$dbpass='password';
37$dbname='test';
38$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
39$sql="
40SELECT a.nid, b.nama, a.f1, a.f2, a.f3, a.f4, DAY(tanggal) AS tgl
41FROM lembur a 
42JOIN karyawan b USING(nid)
43ORDER BY b.nama,a.tanggal";
44$qry=$db->query($sql);
45$data=array();
46while($row=$qry->fetch_object()){
47    if(!isset($data[$row->nid])){
48        $data[$row->nid]=array();
49        $data[$row->nid]['nama']=$row->nama;
50    }
51    $data[$row->nid][$row->tgl]=$row;
52}
53?>
54<table border='1'>
55    <tr>
56        <th rowspan='2'>No</th>
57        <th rowspan='2'>NID</th>
58        <th rowspan='2'>Nama</th>
59        <th rowspan='2'>Fakorial</th>
60        <th colspan='31'>Tanggal</th>
61        <th rowspan='2'>Total Lembur</th>
62    </tr>
63    <tr>
64    <?php for($i=1;$i<=31;$i++) echo "<th>$i</th>"; ?>
65    </tr>
66    <?php
67    $nid=0;
68    $no=0;
69    foreach($data as $n=>$d){
70        echo "<tr><td rowspan='4'>".(++$no)."</td><td rowspan='4'>{$n}</td><td rowspan='4'>{$d['nama']}</td>";
71        for($f=1;$f<=4;$f++){  
72            echo ($f!=1?"</tr><tr>":"")."<td>f{$f}</td>";
73            $fak="f$f";
74            for($i=1;$i<=31;$i++){
75                if(isset($d[$i])){
76                    echo "<td>{$d[$i]->$fak}</td>";
77                }else{
78                    echo "<td></td>";
79                }
80            }
81            echo "<td></td>";
82        }
83        echo "</tr>";
84    }
85?>
86</table>