· 10 years ago · Aug 12, 2016, 07:38 PM
1<?php
2/*
3--
4-- Database: `crosstab`
5--
6
7--
8-- Table structure for table `nm_anggota`
9--
10
11CREATE TABLE IF NOT EXISTS `nm_anggota` (
12 `no_id` int(11) NOT NULL auto_increment,
13 `nama` varchar(100) NOT NULL,
14 PRIMARY KEY (`no_id`)
15) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
16
17--
18-- Dumping data for table `nm_anggota`
19--
20
21INSERT INTO `nm_anggota` (`no_id`, `nama`) VALUES
22(1, 'Andi'),
23(2, 'Iwan'),
24(3, 'Santi'),
25(4, 'Didi'),
26(5, 'Rani'),
27(6, 'sinta'),
28(7, 'Novi');
29
30-- --------------------------------------------------------
31
32--
33-- Table structure for table `transaksi`
34--
35
36CREATE TABLE IF NOT EXISTS `transaksi` (
37 `no` varchar(11) NOT NULL,
38 `no_id` varchar(11) NOT NULL,
39 `tgl` date NOT NULL,
40 `rupiah` varchar(11) NOT NULL
41) ENGINE=MyISAM DEFAULT CHARSET=latin1;
42
43--
44-- Dumping data for table `transaksi`
45--
46
47INSERT INTO `transaksi` (`no`, `no_id`, `tgl`, `rupiah`) VALUES
48('1', '1', '2016-08-01', '1000'),
49('2', '2', '2016-08-01', '1500'),
50('3', '3', '2016-08-01', '3000'),
51('4', '1', '2016-08-02', '2500'),
52('5', '2', '2016-08-02', '2000'),
53('6', '3', '2016-08-02', '1500'),
54('7', '4', '2016-08-02', '3000'),
55('8', '1', '2016-08-03', '3500'),
56('9', '2', '2016-08-03', '2500'),
57('10', '3', '2016-08-03', '2000'),
58('11', '4', '2016-08-03', '2500');
59*/
60
61$dbhost='localhost';
62$dbuser='root';
63$dbpass='';
64$dbname='cosstab';
65$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
66$sql="SELECT b.nama,a.tgl,a.rupiah FROM transaksi a JOIN nm_anggota b USING(no_id) ORDER BY a.tgl,b.nama";
67$result=$db->query($sql);
68$data=array();
69$nama=array();
70while($row=$result->fetch_object()){
71 if(!isset($data[$row->tgl])){
72 $data[$row->tgl]=array();
73 }
74 $data[$row->tgl][$row->nama]=$row->rupiah;
75 $nama[]=$row->nama;
76}
77$column=array_unique($nama);
78?>
79<table border='1'>
80 <tr>
81 <th>Tanggal</th>
82 <?php
83 foreach($column as $col){
84 echo "<th>{$col}</th>";
85 }
86 ?>
87 <th>Jumlah</th>
88 </tr>
89 <?php
90 $total=array();
91 foreach($data as $tgl=>$row){
92 echo "<tr>
93 <td>{$tgl}</td>";
94 $subtotal=0;
95 foreach($column as $col){
96 echo "<td align='right'>".(isset($row[$col])?$row[$col]:"0")."</td>";
97 $subtotal+=$row[$col];
98 if(!isset($total[$col]))$total[$col]=0;
99 $total[$col]+=$row[$col];
100 }
101 echo "<td align='right'>{$subtotal}</td></tr>";
102 }
103 ?>
104 <tr>
105 <th>JUMLAH</th>
106 <?php
107 $grandTotal=0;
108 foreach($column as $col){
109 echo "<th align='right'>{$total[$col]}</th>";
110 $grandTotal+=$total[$col];
111 }
112 ?>
113 <th align='right'><?php echo $grandTotal;?></th>
114 </tr>
115</table>