· 10 years ago · Sep 10, 2016, 09:10 AM
1<?php
2/*******************************
3// PIVOT TABLE
4// created by : Cahya DSN
5// create date : 2012-09-30
6// update date : 2012-10-03
7*******************************/
8?>
9<!DOCTYPE html>
10<html>
11 <head>
12 <title>Pivot Table Stock</title>
13 <style type="text/css">
14 * {padding:0;margin:0}
15 .barang {width:100px;color:#900;}
16 .merk {width:100px;color:#900;}
17 .stok {width:50px;color:#900;}
18 h3 {margin:10px 10px 0px;color:#090;}
19 table {margin-left:10px;border:solid 1px #333;}
20 th {background-color:#eee;border:solid 1px #333;}
21 td {text-align:center;border:solid 1px #333;}
22 td.list_barang {text-align:left;font-weight:bold;color:#009;padding-left:5px;}
23 td._in {background-color:#ffc;}
24 </style>
25 </head>
26 <body>
27<?php
28/*
29-- Sample Data SQL
30-- use test;
31DROP TABLE IF EXISTS `tbl_stock`;
32CREATE TABLE IF NOT EXISTS `tbl_stock` (
33 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
34 `barang` varchar(30) NOT NULL,
35 `merk` varchar(30) NOT NULL,
36 `_in` int(11) NOT NULL DEFAULT '0',
37 `_out` int(11) NOT NULL DEFAULT '0',
38 `stok` int(11) NOT NULL DEFAULT '0',
39 PRIMARY KEY (`id`)
40) ENGINE=MyISAM DEFAULT CHARSET=latin1;
41
42INSERT INTO `tbl_stock`(`barang`,`merk`,`_in`,`_out`,`stok`)
43VALUES("Mouse","Acer",12,2,10),("Keyboard","Logitech",10,0,10),("Mouse","Microsoft",5,0,5),("Printer","Canon IP 145",10,7,3);
44*/
45//-- konfigurasi koneksi database
46$dbhost='localhost';
47$dbuser='root';
48$dbpass='';
49$dbname='test';
50//-- connect to database
51$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
52//-- query data ke database
53$sql="SELECT barang,merk,_in,_out,stok FROM tbl_stock";
54//-- inisialisasi variabel penampung
55$record=array();
56$data=array();
57$barang=array();
58$merk=array();
59//-- menjalankan query ke database
60if($result=$db->query($sql)){
61 //-- ambil data dan masukkan ke variabel penampung
62 while($row=$result->fetch_object()){
63 //-- copy data record ke variable $record utk table tanpa pivot
64 $record[]=$row;
65 //-- penyiapan data untuk pivot table
66 if(isset($data[$row->barang])){
67 if(isset($data[$row->barang][$row->merk])){
68 $data[$row->barang][$row->merk][0]+=$row->_in;
69 $data[$row->barang][$row->merk][1]+=$row->_out;
70 $data[$row->barang][$row->merk][2]+=$row->stok;
71 }else{
72 $data[$row->barang][$row->merk]=array($row->_in,$row->_out,$row->stok);
73 $merk[]=$row->merk;
74 }
75 }else{
76 $data[$row->barang]=array();
77 $barang[]=$row->barang;
78 $data[$row->barang][$row->merk]=array($row->_in,$row->_out,$row->stok);
79 $merk[]=$row->merk;
80 }
81 }
82 $result->close();
83}
84$db->close();
85$merk=array_unique($merk);
86//-- cetak dalam bentuk table
87//======= Table Data sebelum di Pivot ========
88echo "<h3>SEBELUM</h3>";
89echo "<table>n"
90 ."<tr>"
91 ."<th class="barang">Barang</th>"
92 ."<th class="barang">Merk</th>"
93 ."<th class="stok">In</th>"
94 ."<th class="stok">Out</th>"
95 ."<th class="stok">Stok</th>"
96 ."</tr>";
97for($i=0;$i<count($record);$i++){
98 echo "<tr>"
99 ."<td class="list_barang">".$record[$i]->barang."</td>"
100 ."<td class="list_barang">".$record[$i]->merk."</td>"
101 ."<td>".$record[$i]->_in."</td>"
102 ."<td>".$record[$i]->_out."</td>"
103 ."<td>".$record[$i]->stok."</td>"
104 ."</tr>n";
105}
106echo "</table>n";
107//======= Table Data sesudah di Pivot ========
108echo "<h3>SESUDAH</h3>";
109echo "<table>n"
110 ."<thead>n"
111 ."<tr><th rowspan="2" class="barang">Barang</th>";
112for($i=0;$i<count($merk);$i++){
113 echo "<th colspan="2" class="merk">".$merk[$i]."</th>";
114}
115echo "<th rowspan="2" class="stok">Stok</th></tr>n<tr>n";
116for($i=0;$i<count($merk);$i++){
117 echo "<th>in</th><th>out</th>";
118}
119echo "</tr>n</thead>n<tbody>n";
120for($i=0;$i<count($barang);$i++){
121 echo "<tr>"
122 ."<td class="list_barang">".$barang[$i]."</td>";
123 $stok=0;
124 for($j=0;$j<count($merk);$j++){
125 echo (isset($data[$barang[$i]][$merk[$j]])?"<td class="_in">".$data[$barang[$i]][$merk[$j]][0]."</td><td>".$data[$barang[$i]][$merk[$j]][1]."</td>":"<td class="_in">0</td><td>0</td>");
126 $stok+=(isset($data[$barang[$i]][$merk[$j]])?$data[$barang[$i]][$merk[$j]][2]:0);
127 }
128 echo "<td class="_in">".$stok."</td></tr>n";
129}
130echo "</tbody><table>n";
131?>
132</body>
133</html>