ffCCBot/2.0 (http://commoncrawl.org/faq/)
Update: 15-11-2016

Pivot Table Dengan PHP dan MySQL

Pada kesempatan kali ini, kita akan membahas cara membuat pivot table dengan PHP dan MySQL.

Tutorial ini merupakan tutorial lanjutan dari tutorial sebelumnya: Pivot Table Dengan MySQL – Panduan Lengkap. Tutorial ini kita akan langsung menggunakan SQL yang sudah jadi, SQL ini sudah kita bahas pada tutorial tersebut, selain itu, kita juga akan melakukan pengolahan data dengan menggunakan PHP?

Mengapa perlu bahasa pemrogrman lain? tidak cukup di SQL saja?

Ketika menampilkan data dalam bentuk pivot table, terdapat banyak sekali kemungkinan layout data yang ingin disajikan, dengan keterbatasan SQL, hal tersebut terkadang sulit diwujudkan, sehingga kita perlu mengolahnya lebih lanjut menggunakan bahasa pemrograman lain, dalam hal ini PHP

I. Persiapan DATA

Data yang akan kita gunakan pada tutorial ini adalah data penjualan yang tersimpan pada tabel tabel_sales. Tabel ini memiliki lima kolom yaitu: id_trx, nama, tgl_trx, tim, dan nilai_trx.

Layout data pada tabel tersebut seperti tampak pada tabel berikut ini:

id_trx nama tgl_trx tim nilai_trx
1 Alfa 2016-01-10 1 250000
2 Charlie 2016-01-02 2 175000
3 Bravo 2016-01-01 1 310000
4 Bravo 2016-02-04 1 250000
5 Alfa 2016-01-15 1 125000
6 Charlie 2016-01-13 2 325000
7 Bravo 2016-02-07 1 275000
8 Bravo 2016-03-06 1 150000
9 Alfa 2016-02-05 1 215000
10 Alfa 2016-02-22 1 350000
11 Alfa 2016-02-02 1 250000
12 Alfa 2016-03-12 1 150000
13 Alfa 2016-03-17 1 225000
14 Bravo 2016-03-11 1 425000
15 Bravo 2016-03-18 1 250000
16 Charlie 2016-01-23 2 350000
17 Charlie 2016-02-22 2 300000
18 Charlie 2016-03-21 2 275000
19 Charlie 2016-03-12 2 450000
20 Delta 2016-02-11 2 450000
21 Delta 2016-02-17 2 550000
22 Delta 2016-03-11 2 370000

Data ini sudah include pada file .zip yang dapat didownload di bagian bawah tutorial ini.

II. Pivot Table dengan PHP dan MySQL – Menampilkan Data Dengan PHP Dengan Total dan Subtotal Dari SQL

Pada bagian ini, kita akan membahas cara untuk menampilkan data pivot table hasil query dari MySQL dengan PHP.

Pada bagian ini, data total dan subtotal sudah kita buat pada query SQL, sehingga kita benar-benar hanya menampilkan datanya saja. Adapun query yang akan kita jalankan adalah:

SELECT  
	IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,
	IFNULL( tim, 'TOTAL' ) AS sales_tim,
	 
	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
	COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
	COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
	COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
	
	COUNT(id_trx) AS jml_trx,
	SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY tim, nama
WITH ROLLUP

Ketika dijalankan pada aplikasi SQL manager (HEIDI SQL) hasil yang kita peroleh adalah:

+------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+
| nama_sales | sales_tim | januari | trx_1 | februari | trx_2 | maret   | trx_3 | jml_trx | total_trx |
+------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+
| Alfa       | 1         |  375000 |     2 |   815000 |     3 |  375000 |     2 |       7 |   1565000 |
| Bravo      | 1         |  310000 |     1 |   525000 |     2 |  825000 |     3 |       6 |   1660000 |
| SUB TOTAL  | 1         |  685000 |     3 |  1340000 |     5 | 1200000 |     5 |      13 |   3225000 |
| Charlie    | 2         |  850000 |     3 |   300000 |     1 |  725000 |     2 |       6 |   1875000 |
| Delta      | 2         |       0 |     0 |  1000000 |     2 |  370000 |     1 |       3 |   1370000 |
| SUB TOTAL  | 2         |  850000 |     3 |  1300000 |     3 | 1095000 |     3 |       9 |   3245000 |
| SUB TOTAL  | TOTAL     | 1535000 |     6 |  2640000 |     8 | 2295000 |     8 |      22 |   6470000 |
+------------+-----------+---------+-------+----------+-------+---------+-------+---------+-----------+

Selanjutnya, mari kita tampilkan data tersebut menggunakan PHP. Adapun script yang kita gunakan adalah:

<?php
$db_host = 'localhost';
$db_port = '3306';
$db_name = 'tutorial';
$db_user = 'root';
$db_pass = '';

$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) {
	die ('Gagal terhubung MySQL: ' . mysqli_connect_error());	
}

$sql = "
SELECT  
	IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,
	IFNULL( tim, 'TOTAL' ) AS sales_tim,
	 
	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS bln_januari,
	COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS bln_februari,
	COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS bln_maret,
	COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
	
	COUNT(id_trx) AS jml_trx,
	SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY tim, nama
WITH ROLLUP";

$query = mysqli_query($conn, $sql);
$result = mysqli_fetch_all($query,MYSQLI_ASSOC);

echo '<html>
		<head>
			<title>Pivot Table Dengan PHP dan MySQL</title>
			<style>
				body {font-family:"open sans", "segoe ui", tahoma, arial}
				table {border-collapse: collapse}
	
				
				.total td {background-color: #f5f5f5 !important;}
				.right{text-align: right}
				table tr:nth-child(odd) td {
					background-color: #fbfbfb;
					border-bottom: 1px solid #efefef;
					border-top: 1px solid #ececec;
				}
				table th {
					color: #616161;
					margin: 0;
					padding: 10px 10px;
					border: 1px solid #e4e4e4;
					text-align: center;
					font-size: 14px;
					text-transform: uppercase;
					background: #efefef;
				}
				table td {
					border-right: 1px solid #ececec;
					border-left: 1px solid #ececec;
					padding: 7px 15px;
					color: #676767;
					font-size: 14px;
				}
				table td:nth-child(n+3) {
					text-align: right;
				}
			</style>
		</head>
		<body>';

$header_row1 = $header_row2 = '';
foreach ($result[0] as $key => $val)
{
	if (strpos($key, 'bln_') !== false)
	{
		$bln = explode('_', $key);
		$header_row1 .= '<th colspan="2">' . $bln[1] . '</th>';
		$header_row2 .= '<th>Nilai</th>
					<th>Trx</th>';
	}
}

echo '<table>
		<thead>
			<tr>
				<th rowspan="2">No</th>
				<th rowspan="2">Nama Sales</th>
				<th rowspan="2">Tim</th>'
				. $header_row1 .
				'<th rowspan="2">Jml Trx</th>
				<th rowspan="2">Total Trx</th>
			</tr>
			<tr>'
				. $header_row2 .
			'</tr>
		</thead>
		<tbody>';

$no = 1;		
foreach ($result as $array)
{
	$class = '';
	$print_no = $no;
	
	if ($array['nama_sales'] == 'SUB TOTAL') {
		$class = ' class="total"';
		$print_no =  '';
	} else {
		$no++;
	}
	
	echo  '<tr'.$class.'>
			<td>' . $print_no . '</td>';
		
	foreach ($array as $key => $val)
	{
		if ($key !== 'nama_sales' && $key !== 'sales_tim')
		{
			$val = number_format($val, '0', ',', '.');
		}
		
		echo '<td>' . $val . '</td>';
		
	}
	echo '</tr>';
}

echo '
	</tbody>
</table>
</body>
</html>';

Hasil yang kita peroleh tampak seperti gambar berikut:

Pivot Table Dengan PHP dan MySQL - Hasil SQL

Penjelasan script PHP:

  • Pada script diatas, kita jalankan perintah query menggunakan mysqli_query selanjutnya kita simpan output hasil query tersebut dalam bentuk array.
    $query = mysqli_query($conn, $sql);
    $result = mysqli_fetch_all($query, MYSQLI_ASSOC);

    Sehingga sekarang variabel $result berisi:

    Array
    (
        [0] => Array
            (
                [nama_sales] => Alfa
                [sales_tim] => 1
                [bln_januari] => 375000
                [trx_1] => 2
                [bln_februari] => 815000
                [trx_2] => 3
                [bln_maret] => 375000
                [trx_3] => 2
                [jml_trx] => 7
                [total_trx] => 1565000
            )
    
        [1] => Array
            (
                [nama_sales] => Bravo
                [sales_tim] => 1
                [bln_januari] => 310000
                [trx_1] => 1
                [bln_februari] => 525000
                [trx_2] => 2
                [bln_maret] => 825000
                [trx_3] => 3
                [jml_trx] => 6
                [total_trx] => 1660000
            )
    
        [2] => Array
            (
                [nama_sales] => SUB TOTAL
                [sales_tim] => 1
                [bln_januari] => 685000
                [trx_1] => 3
                [bln_februari] => 1340000
                [trx_2] => 5
                [bln_maret] => 1200000
                [trx_3] => 5
                [jml_trx] => 13
                [total_trx] => 3225000
            )
    
        [3] => Array
            (
                [nama_sales] => Charlie
                [sales_tim] => 2
                [bln_januari] => 850000
                [trx_1] => 3
                [bln_februari] => 300000
                [trx_2] => 1
                [bln_maret] => 725000
                [trx_3] => 2
                [jml_trx] => 6
                [total_trx] => 1875000
            )
    
        [4] => Array
            (
                [nama_sales] => Delta
                [sales_tim] => 2
                [bln_januari] => 0
                [trx_1] => 0
                [bln_februari] => 1000000
                [trx_2] => 2
                [bln_maret] => 370000
                [trx_3] => 1
                [jml_trx] => 3
                [total_trx] => 1370000
            )
    
        [5] => Array
            (
                [nama_sales] => SUB TOTAL
                [sales_tim] => 2
                [bln_januari] => 850000
                [trx_1] => 3
                [bln_februari] => 1300000
                [trx_2] => 3
                [bln_maret] => 1095000
                [trx_3] => 3
                [jml_trx] => 9
                [total_trx] => 3245000
            )
    
        [6] => Array
            (
                [nama_sales] => SUB TOTAL
                [sales_tim] => TOTAL
                [bln_januari] => 1535000
                [trx_1] => 6
                [bln_februari] => 2640000
                [trx_2] => 8
                [bln_maret] => 2295000
                [trx_3] => 8
                [jml_trx] => 22
                [total_trx] => 6470000
            )
    
    )
    

    Hal ini kita lakukan karena kita perlu melakukan loop data hasil query tersebut dua kali, yaitu untuk membuat header dan menampilkan isi.

  • Untuk membuat header tabel, kita ambil data pertama yaitu $result[0] selanjutnya dilakukan loop untuk mendapatkan data kolom, loop ini dilakukan karena jumlah kolom bersifat dinamis, bisa saja nanti muncul data bulan April, dst….
    $header_row1 = $header_row2 = '';
    foreach ($result[0] as $key => $val)
    {
    	if (strpos($key, 'bln_') !== false)
    	{
    		$bln = explode('_', $key);
    		$header_row1 .= '<th colspan="2">' . $bln[1] . '</th>';
    		$header_row2 .= '<th>Nilai</th>
    					<th>Trx</th>';
    	}
    }

    Karena header terdiri dari dua baris, baris tersebut kita simpan pada variabel masing masing $header_row1 untuk baris pertama dan $header_row2 untuk baris kedua. Variabel ini akan kita gunakan nanti ketika membuat tabel HTML.

  • Untuk membuat isi tabel, kita loop data yang tersimpan dalam variabel $result.
  • foreach ($result as $array)
    {
    	...
    }

    Selanjutnya kita identifikasi baris SUB TOTAL

  • if ($array['nama_sales'] == 'SUB TOTAL') {
    	$class = ' class="total"';
    	$print_no =  '';
    } else {
    	$no++;
    }

    Jika pada kolom nama_sales berisi ‘SUB TOTAL’ maka kita beri baris tersebut atribut class="total" disamping itu, nomor urut juga tidak kita tampilkan, jika tidak (else), maka tambahkan nilai variabel $no.

  • Selanjutnya kita loop lagi isi dari variabel $result
  • foreach ($array as $key => $val)
    {
    	if ($key !== 'nama_sales' && $key !== 'sales_tim')
    	{
    		$val = number_format($val, '0', ',', '.');
    	}
    	
    	echo '<td>' . $val . '</td>';
    }

    Selanjutnya kita tes, jika nilai tidak berasal dari kolom nama_sales dan sales_tim, maka kita buat format rupiah pada nilai tersebut.

Demikian cara menampilkan data hasil pivot table dengan PHP.

III. Pivot Table dengan PHP dan MySQL – Membuat Total dan Subtotal dengan PHP

Pada bagian sebelumnya, total dan subtotal kita buat pada query MySQL, pada kondisi tertentu, total dan subtotal ini tidak bisa (rumit) jika kita buat pada SQL, melainkan harus dilakukan pengolahan lebih lanjut menggunakan bahasa pemrograman lain.

Contoh kondisi tersebut adalah ketika kita ingin menampilkan data berdasarkan hasil penjualan diurutkan dari total penjualan terbesar yang dikelompokkan berdasarkan nama dan tim.

Pertama, kita buat query untuk menampilkan data urut berdasarkan total penjualan (kolom total_trx ). Adapun querynya adalah sebagai berikut:

SELECT  
	IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,
	IFNULL( tim, 'TOTAL' ) AS sales_tim,
	 
	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS bln_januari,
	COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS bln_februari,
	COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS bln_maret,
	COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
	
	COUNT(id_trx) AS jml_trx,
	SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY tim, nama
ORDER BY SUM( nilai_trx ) DESC, tim

Query diatas sama seperti query sebelumnya, bedanya, kita hapus klausa WITH ROLLUP dan kita tambahkan klausa ORDER BY yaitu ORDER BY SUM( nilai_trx ) DESC, tim

Hasil dari query tersebut adalah:

+------------+-----------+-------------+-------+--------------+-------+-----------+-------+---------+-----------+
| nama_sales | sales_tim | bln_januari | trx_1 | bln_februari | trx_2 | bln_maret | trx_3 | jml_trx | total_trx |
+------------+-----------+-------------+-------+--------------+-------+-----------+-------+---------+-----------+
| Charlie    | 2         |      850000 |     3 |       300000 |     1 |    725000 |     2 |       6 |   1875000 |
| Bravo      | 1         |      310000 |     1 |       525000 |     2 |    825000 |     3 |       6 |   1660000 |
| Alfa       | 1         |      375000 |     2 |       815000 |     3 |    375000 |     2 |       7 |   1565000 |
| Delta      | 2         |           0 |     0 |      1000000 |     2 |    370000 |     1 |       3 |   1370000 |
+------------+-----------+-------------+-------+--------------+-------+-----------+-------+---------+-----------+

Selanjutnya kita buat script PHP sebagai berikut:

<?php
$db_host = 'localhost';
$db_port = '3306';
$db_name = 'tutorial';
$db_user = 'root';
$db_pass = '';

$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) {
	die ('Gagal terhubung MySQL: ' . mysqli_connect_error());	
}

$sql = "
SELECT  
	IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,
	IFNULL( tim, 'TOTAL' ) AS sales_tim,
	 
	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS bln_januari,
	COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS bln_februari,
	COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS bln_maret,
	COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
	
	COUNT(id_trx) AS jml_trx,
	SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY tim, nama
ORDER BY SUM( nilai_trx ) DESC, tim";

$query = mysqli_query($conn, $sql);
$fetch = mysqli_fetch_all($query,MYSQLI_ASSOC);

foreach ($fetch as $key => $val)
{
	$result[$val['sales_tim']][] = $val;
}
// echo '<pre>'; print_r($result); die;
echo '<html>
		<head>
			<title>Pivot Table Dengan PHP dan MySQL</title>
		</head>
		<body>';

$header_row1 = $header_row2 = '';
foreach ($fetch[0] as $key => $val)
{
	if (strpos($key, 'bln_') !== false)
	{
		$bln = explode('_', $key);
		$header_row1 .= '<th colspan="2">' . $bln[1] . '</th>';
		$header_row2 .= '<th>Nilai</th>
					<th>Trx</th>';
	}
}

echo '<table>
		<thead>
			<tr>
				<th rowspan="2">No</th>
				<th rowspan="2">Nama Sales</th>
				<th rowspan="2">Tim</th>'
				. $header_row1 .
				'<th rowspan="2">Jml Trx</th>
				<th rowspan="2">Total Trx</th>
			</tr>
			<tr>'
				. $header_row2 .
			'</tr>
		</thead>
		<tbody>';

$no = 1;
$total = array();		
foreach ($result as $tim => $array) // Nilai $ tim adalah 1 dan 2
{
	$subtotal =  array();
	foreach ($array as $index => $array2) // $index (mulai 0, 1, dst...)
	{
		echo '<tr>
			  <td>' . $no . '</td>';
		foreach ($array2 as $nama_kolom => $val) // $nama_kolom (nama_sales, sales_tim, dst..)
		{
			if ($nama_kolom != 'nama_sales' && $nama_kolom != 'sales_tim')
			{
				@$subtotal[$nama_kolom] += $val;
				@$total[$nama_kolom] += $val;
				$val = number_format($val, '0', ',', '.');
			}
			
			echo '<td>' . $val . '</td>';
		}
		$no++;
		echo '</tr>';
	}
	
	// SUB TOTAL
	echo '
	<tr class="total">
		<td></td>
		<td>SUB TOTAL</td>
		<td>' . $tim . '</td>';
		foreach ($subtotal as $nilai)
		{
			echo '<td>' . number_format($nilai, '0', ',', '.') . '</td>';
		}
	echo '</tr>';
}
// TOTAL
echo '
<tr class="total">
	<td></td>
	<td>TOTAL</td>
	<td></td>';
	foreach ($total as $nilai)
	{
		echo '<td>' . number_format($nilai, '0', ',', '.') . '</td>';
	}
echo '</tr>';

echo '
	</tbody>
</table>
</body>
</html>';

Hasil yang kita peroleh:

Pivot Table Dengan PHP dan MySQL - Total dan Subtotal Dengan PHP

Pada gambar diatas, terlihat bahwa data diurutkan berdasarkan kolom total_trx mulai dari nilai terbesar, selain itu, data juga dikelompokkan berdasarkan sales_tim.

Penjelasan script PHP:

  • Sama seperti sebelumnya, kita simpan semua hasil query SQL kedalam sebuah variabel berbentuk array, pada contoh kali ini kita gunakan variabel $fetch.

    Selanjutnya, kita perlu menata ulang data array tersebut sehingga memudahkan kita untuk menghitung total dan subtotal.

    $query = mysqli_query($conn, $sql);
    $fetch = mysqli_fetch_all($query,MYSQLI_ASSOC);
    
    foreach ($fetch as $key => $val)
    {
    	$result[$val['sales_tim']][] = $val;
    }

    Hasil penataan ulang tersebut kita simpan pada variabel $result. Variabel $result berisi data sebagai berikut:

    Array
    (
        [2] => Array
            (
                [0] => Array
                    (
                        [nama_sales] => Charlie
                        [sales_tim] => 2
                        [bln_januari] => 850000
                        [trx_1] => 3
                        [bln_februari] => 300000
                        [trx_2] => 1
                        [bln_maret] => 725000
                        [trx_3] => 2
                        [jml_trx] => 6
                        [total_trx] => 1875000
                    )
    
                [1] => Array
                    (
                        [nama_sales] => Delta
                        [sales_tim] => 2
                        [bln_januari] => 0
                        [trx_1] => 0
                        [bln_februari] => 1000000
                        [trx_2] => 2
                        [bln_maret] => 370000
                        [trx_3] => 1
                        [jml_trx] => 3
                        [total_trx] => 1370000
                    )
            )
    		
        [1] => Array
            (
                [0] => Array
                    (
                        [nama_sales] => Bravo
                        [sales_tim] => 1
                        [bln_januari] => 310000
                        [trx_1] => 1
                        [bln_februari] => 525000
                        [trx_2] => 2
                        [bln_maret] => 825000
                        [trx_3] => 3
                        [jml_trx] => 6
                        [total_trx] => 1660000
                    )
    				
                [1] => Array
                    (
                        [nama_sales] => Alfa
                        [sales_tim] => 1
                        [bln_januari] => 375000
                        [trx_1] => 2
                        [bln_februari] => 815000
                        [trx_2] => 3
                        [bln_maret] => 375000
                        [trx_3] => 2
                        [jml_trx] => 7
                        [total_trx] => 1565000
                    )
            )
    )

    Array diatas memiliki index nomor tim dari petugas, dalam hal ini berada di kolom sales_tim (kolom alias).

    Sebagai pedoman kolom mana yang harus digunakan sebagai index, perhatikan klausa GROUP BY pada query. Pada query diatas, pada klausa GROUP BY kita menggunakan kolom tim dan nama, sehingga kita perlu menggunakan index kolom tim, yang pada query diatas kita ubah nama kolom tersebut menjadi sales_tim

  • Selanjutnya kita loop variabel $result dan tambahkan baris SUB TOTAL
    // SUB TOTAL
    echo '
    <tr>
    	<td></td>
    	<td>SUB TOTAL</td>
    	<td>' . $tim . '</td>';
    	foreach ($subtotal as $nilai)
    	{
    		echo '<td>' . number_format($nilai, '0', ',', '.') . '</td>';
    	}
    echo '</tr>';

    Nilai total ($total) dan subtotal ($subtotal) kita akumulasi ketika membuat loop pada isi data.

  • $subtotal =  array();
    foreach ($array as $index => $array2) // $index (mulai 0, 1, dst...)
    {
    	echo '<tr>
    		  <td>' . $no . '</td>';
    	foreach ($array2 as $nama_kolom => $val) // $nama_kolom (nama_sales, sales_tim, dst..)
    	{
    		if ($nama_kolom != 'nama_sales' && $nama_kolom != 'sales_tim')
    		{
    			@$subtotal[$nama_kolom] += $val;
    			@$total[$nama_kolom] += $val;
    			$val = number_format($val, '0', ',', '.');
    		}
    		
    		echo '<td>' . $val . '</td>';
    	}
    	$no++;
    	echo '</tr>';
    }

    Agar tidak terakumulasi ke SUB TOTAL di bawahnya, maka setap awal loop, variabel $subtotal kita reset: $subtotal = array().

  • Terakhir, kita buat baris total.
  • // TOTAL
    echo '
    <tr>
    	<td></td>
    	<td>TOTAL</td>
    	<td></td>';
    	foreach ($total as $nilai)
    	{
    		echo '<td>' . number_format($nilai, '0', ',', '.') . '</td>';
    	}
    echo '</tr>';

Selesai.

Dalam praktiknya, model total dan subtotal ini akan sangat bervariasi, sehingga logika pemrogramannya juga berbeda-beda. Contoh diatas dapat memberi gambaran bagaimana membuat total dan subtotal, silakan dikembangkan sesuai kebutuhan.

Download file yang digunakan dalam latihan ini.

Demikian pembahasan mengenai cara membuat pivot table dengan PHP dan MySQL, semoga bermanfaat.

Recomended Post

12 Feedback dari pembaca

  • untuk layout header di tampilkan sesuai kriteria tanggal 2016-03-11 sampai 2016-03-20 ,dan data di tampilkan kriteria tanggal yang di pilih, untuk TIM (rowspan=”2″) dibuat group dan AVERAGE (rowspan=”2″) di buat group (bravo/alfa) . ?

    contoh layout

    No Nama Sales Tim januari februari maret Jml Trx Total Trx Average Trx
    Nilai Nilai Nilai
    1 Alfa 1 375.000 815.000 375.000 7 1.565.000 106%
    2 Bravo 310.000 525.000 825.000 6 1.660.000
    3 Charlie 2 850.000 300.000 725.000 6 1.875.000 73%
    4 Delta 0 1.000.000 370.000 3 1.370.000

    terima kasih atas bantuan nya, sukses selalu

    • Untuk layout kita harus pintar pintar mengakalinya mas, pada kasus diatas, kita harus tahu berapa jumlah row pada tim yang sama, salah satu caranya kita buat bentuk array sedemikian rupa sehingga bisa kita ketahui jumlah row pada tim yang sama

      Yang saya belum paham kolom average trx menghitungnya seperti apa mas?

      Contoh script PHP jadi nya (tanpa kolom average trx):

      <?php
      $header_row1 = $header_row2 = '';
      foreach ($result[0] as $key => $val)
      {
      	if (strpos($key, 'bln_') !== false)
      	{
      		$bln = explode('_', $key);
      		$header_row1 .= '<th>' . $bln[1] . '</th>';
      		$header_row2 .= '<th>Nilai</th>';
      	}
      }
      
      foreach ($result as $key => $val)
      {
      	$output[$val['sales_tim']][] = $val;
      }
      // echo '<pre>'; print_r($output); die;
      echo '<table>
      		<thead>
      			<tr>
      				<th rowspan="2">No</th>
      				<th rowspan="2">Nama Sales</th>
      				<th rowspan="2">Tim</th>'
      				. $header_row1 .
      				'<th rowspan="2">Jml Trx</th>
      				<th rowspan="2">Total Trx</th>
      			</tr>
      			<tr>'
      				. $header_row2 .
      			'</tr>
      		</thead>
      		<tbody>';
      
      $no = 1;		
      foreach ($output as $tim => $array)
      {
      	foreach ($array as $key => $val)
      	{
      		echo '<tr>';
      		echo '<td>' . $no . '</td>
      			  <td>' . $val['nama_sales'] . '</td>';
      		
      		if($key == 0) {
      			$rowspan = count($output[$tim]) > 1 ? ' rowspan="' . count($output[$tim]) . '"' : '';
      			echo '<td' . $rowspan . '>' . $val['sales_tim'] . '</td>';
      		}
      		
      		foreach ($val as $field => $nilai_trx) {
      			if (strpos($field, 'bln_') !== false) {
      				echo '<td>' . number_format($nilai_trx, '0', ',', '.') . '</td>';
      			}
      		}
      		
      		echo	'<td>' . $val['jml_trx'] . '</td>
      				<td>' . number_format($val['total_trx'], '0', ',', '.') . '</td>';
      		
      		$no++;
      		echo '</tr>';
      	}
      }
      
      echo '
      	</tbody>
      </table>';

      Hasil:

      No Nama Sales Tim januari februari maret Jml Trx Total Trx
      Nilai Nilai Nilai
      1 Alfa 1 375.000 815.000 375.000 7 1.565.000
      2 Bravo 310.000 525.000 825.000 6 1.660.000
      3 Charlie 2 850.000 300.000 725.000 6 1.875.000
      4 Delta 0 1.000.000 370.000 3 1.370.000

    • Maksudnya pecahan, atau hanya .00 mas? kalau pecahan, tipe data kolom nya dibuat DECIMAL, dengan 2 desimal dibelakang koma, misal DECIMAL(8,2), nanti nilai dan TOTAL nya otomatis menyesuaikan

      Jika menambahkan .00, bisa menggunakan fungsi CAST(), CONVERT(), atau CONCAT()

        • Kalau menggungakan fungsi number_format() cba diubah argumen nya mas, misal: number_format($val, ‘2’, ‘,’, ‘.’) Argumen kedua dari fungsi tersebut menunjukkan banyaknya desimal di belakang koma.

          • Pertama:
            Saya menggunakan number_format($val, ‘2’, ‘,’, ‘.’), namun kolom “Jlm TRX” juga muncul angka ,00 dibelakang koma, saya menginginkan data seperti contoh: Jml Trx = 6, Total Trx = 1.660.000,25

            Kedua:
            Bagaimana menampilkan datanya sesuai dengan pilihan tanggal di listbox, combobox, atau checkbox,

            Ketiga:
            Bagaimana mengubah layout menjadi seperti di bawah ini:

            -------------------------------------------------------------------------------------------
            |        |                    |  Januari  |  Februari |    Maret  |           |           |
            |  No    |  Nama Sales / Tim  |-----------|-----------|-----------|  Jml Trx  | Total Trx |
            |        |                    |  Nilai    |  Nilai    |   Nilai   |           |           |
            -------------------------------------------------------------------------------------------
            |  A     |  Tim 1                                                                          
            -------------------------------------------------------------------------------------------
            |  1     |  Alfa              |   375.000 |   815.000 |   375.000 | 	   7  |	1.565.000 |
            -------------------------------------------------------------------------------------------
            |  2     |  Bravo             |   310.000 |   525.000 |   825.000 |   	   6  |	1.660.000 |
            -------------------------------------------------------------------------------------------
            |  B     |  Tim 2                                                                          
            -------------------------------------------------------------------------------------------
            |  3     |  Charlie           |   850.000 |   300.000 |   725.000 |        6  |	1.875.000 |
            -------------------------------------------------------------------------------------------
            |  4     |  Delta             |         0 | 1.000.000 |   370.000 |        3  |	1.370.000 |
            -------------------------------------------------------------------------------------------

          • Untuk yang pertama, kita cek apakah index mengandung kata bln atau total, jika ya maka tambahkan decimal:

            $dec = strpos($key, 'bln') !== false ||  strpos($key, 'total') !== false ? 2 : 0;

            File pivot-table.php

            foreach ($array as $key => $val)
            {
            	if ($key !== 'nama_sales' && $key !== 'sales_tim')
            	{
            		$dec = strpos($key, 'bln') !== false ||  strpos($key, 'total') !== false ? 2 : 0;
            		$val = number_format($val, $dec, ',', '.');
            	}
            	
            	echo '<td>' . $val . '</td>';
            }

            Untuk yang kedua, tinggal buat form, kemudian tangkap data di form menggunakan variabel $_POST, tutorialnya bisa dibaca di: Tutorial PHP Form II: Menampilkan Hasil Input Form HTML dengan PHP

            Contoh:

            <html>
            <head>
            	<title>Pivot Table Dengan PHP dan MySQL</title>
            	<style>
            		body {font-family:"open sans", "segoe ui", tahoma, arial}
            		table {border-collapse: collapse}
            
            		
            		.total td {background-color: #f5f5f5 !important;}
            		.right{text-align: right}
            		table tr:nth-child(odd) td {
            			background-color: #fbfbfb;
            			border-bottom: 1px solid #efefef;
            			border-top: 1px solid #ececec;
            		}
            		table th {
            			color: #616161;
            			margin: 0;
            			padding: 10px 10px;
            			border: 1px solid #e4e4e4;
            			text-align: center;
            			font-size: 14px;
            			text-transform: uppercase;
            			background: #efefef;
            		}
            		table td {
            			border-right: 1px solid #ececec;
            			border-left: 1px solid #ececec;
            			padding: 7px 15px;
            			color: #676767;
            			font-size: 14px;
            		}
            		table td:nth-child(n+3) {
            			text-align: right;
            		}
            	</style>
            </head>
            <body>
            <form method="post" action="">
            	<label>Tim</label>:
            	<select name="tim">
            		<option value="1">1</option>
            		<option value="2">2</option>
            		<option value="3">3</option>
            	</select>
            	<input type="submit" name="submit" value="Submit"/>
            </form>
            
            <?php
            if (@$_POST['submit']) 
            {
            	$db_host = 'localhost';
            	$db_port = '3306';
            	$db_name = 'test';
            	$db_user = 'root';
            	$db_pass = '';
            
            	$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
            	if (!$conn) {
            		die ('Gagal terhubung MySQL: ' . mysqli_connect_error());	
            	}
            
            	$sql = "
            	SELECT  
            		IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,
            		IFNULL( tim, 'TOTAL' ) AS sales_tim,
            		 
            		SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS bln_januari,
            		COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
            		SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS bln_februari,
            		COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
            		SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS bln_maret,
            		COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
            		
            		COUNT(id_trx) AS jml_trx,
            		SUM( nilai_trx ) AS total_trx
            	FROM tabel_sales
            	WHERE tim = " . $_POST['tim'] . "
            	GROUP BY tim, nama
            	WITH ROLLUP";
            
            	$query = mysqli_query($conn, $sql);
            	$result = mysqli_fetch_all($query,MYSQLI_ASSOC);
            
            	$header_row1 = $header_row2 = '';
            	foreach ($result[0] as $key => $val)
            	{
            		if (strpos($key, 'bln_') !== false)
            		{
            			$bln = explode('_', $key);
            			$header_row1 .= '<th colspan="2">' . $bln[1] . '</th>';
            			$header_row2 .= '<th>Nilai</th>
            						<th>Trx</th>';
            		}
            	}
            
            	echo '<table>
            			<thead>
            				<tr>
            					<th rowspan="2">No</th>
            					<th rowspan="2">Nama Sales</th>
            					<th rowspan="2">Tim</th>'
            					. $header_row1 .
            					'<th rowspan="2">Jml Trx</th>
            					<th rowspan="2">Total Trx</th>
            				</tr>
            				<tr>'
            					. $header_row2 .
            				'</tr>
            			</thead>
            			<tbody>';
            
            	$no = 1;		
            	foreach ($result as $array)
            	{
            		$class = '';
            		$print_no = $no;
            		
            		if ($array['nama_sales'] == 'SUB TOTAL') {
            			$class = ' class="total"';
            			$print_no =  '';
            		} else {
            			$no++;
            		}
            		
            		echo  '<tr'.$class.'>
            				<td>' . $print_no . '</td>';
            			
            		foreach ($array as $key => $val)
            		{
            			if ($key !== 'nama_sales' && $key !== 'sales_tim')
            			{
            				$val = number_format($val, '0', ',', '.');
            			}
            			
            			echo '<td>' . $val . '</td>';
            			
            		}
            		echo '</tr>';
            	}
            }
            ?>
            
            	</tbody>
            </table>
            </body>
            </html>

            Untuk yanng ketiga, kita cukup bermain di logika loopnya mas:
            Coba buka file pivot-table.php, tambahkan script:

            if ( (!$tim || $tim != $array['sales_tim']) && $array['sales_tim'] != 'TOTAL') {
            	echo  '<tr'.$class.'>
            		  <td colspan="11">TIM '.$array['sales_tim'].'</td>
            	      </tr>';
            	$tim = $array['sales_tim'];
            }

            Script lengkap:

            <?php
            $db_host = 'localhost';
            $db_port = '3306';
            $db_name = 'test';
            $db_user = 'root';
            $db_pass = '';
            
            $conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
            if (!$conn) {
            	die ('Gagal terhubung MySQL: ' . mysqli_connect_error());	
            }
            
            $sql = "
            SELECT  
            	IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,
            	IFNULL( tim, 'TOTAL' ) AS sales_tim,
            	 
            	SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS bln_januari,
            	COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
            	SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS bln_februari,
            	COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
            	SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS bln_maret,
            	COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
            	
            	COUNT(id_trx) AS jml_trx,
            	SUM( nilai_trx ) AS total_trx
            FROM tabel_sales
            GROUP BY tim, nama
            WITH ROLLUP";
            
            $query = mysqli_query($conn, $sql);
            $result = mysqli_fetch_all($query,MYSQLI_ASSOC);
            
            echo '<html>
            		<head>
            			<title>Pivot Table Dengan PHP dan MySQL</title>
            			<style>
            				body {font-family:"open sans", "segoe ui", tahoma, arial}
            				table {border-collapse: collapse}
            	
            				
            				.total td {background-color: #f5f5f5 !important;}
            				.right{text-align: right}
            				table tr:nth-child(odd) td {
            					background-color: #fbfbfb;
            					border-bottom: 1px solid #efefef;
            					border-top: 1px solid #ececec;
            				}
            				table th {
            					color: #616161;
            					margin: 0;
            					padding: 10px 10px;
            					border: 1px solid #e4e4e4;
            					text-align: center;
            					font-size: 14px;
            					text-transform: uppercase;
            					background: #efefef;
            				}
            				table td {
            					border-right: 1px solid #ececec;
            					border-left: 1px solid #ececec;
            					padding: 7px 15px;
            					color: #676767;
            					font-size: 14px;
            				}
            				table td:nth-child(n+3) {
            					text-align: right;
            				}
            			</style>
            		</head>
            		<body>';
            
            $header_row1 = $header_row2 = '';
            foreach ($result[0] as $key => $val)
            {
            	if (strpos($key, 'bln_') !== false)
            	{
            		$bln = explode('_', $key);
            		$header_row1 .= '<th colspan="2">' . $bln[1] . '</th>';
            		$header_row2 .= '<th>Nilai</th>
            					<th>Trx</th>';
            	}
            }
            
            echo '<table>
            		<thead>
            			<tr>
            				<th rowspan="2">No</th>
            				<th rowspan="2">Nama Sales</th>
            				<th rowspan="2">Tim</th>'
            				. $header_row1 .
            				'<th rowspan="2">Jml Trx</th>
            				<th rowspan="2">Total Trx</th>
            			</tr>
            			<tr>'
            				. $header_row2 .
            			'</tr>
            		</thead>
            		<tbody>';
            
            $no = 1;	
            $tim = '';
            // echo '<pre>'; print_r($result);
            foreach ($result as $array)
            {
            	$class = '';
            	$print_no = $no;
            	
            	if ($array['nama_sales'] == 'SUB TOTAL') {
            		$class = ' class="total"';
            		$print_no =  '';
            	} else {
            		$no++;
            	}
            	
            	if ( (!$tim || $tim != $array['sales_tim']) && $array['sales_tim'] != 'TOTAL') {
            		echo  '<tr'.$class.'>
            				<td colspan="11">TIM '.$array['sales_tim'].'</td>
            				</tr>';
            		$tim = $array['sales_tim'];
            	}
            			
            	echo  '<tr'.$class.'>
            			<td>' . $print_no . '</td>';
            		
            	foreach ($array as $key => $val)
            	{
            		if ($key !== 'nama_sales' && $key !== 'sales_tim')
            		{
            			$dec = strpos($key, 'bln') !== false ||  strpos($key, 'total') !== false ? 2 : 0;
            			$val = number_format($val, $dec, ',', '.');
            		}
            		
            		echo '<td>' . $val . '</td>';
            		
            	}
            	echo '</tr>';
            }
            
            echo '
            	</tbody>
            </table>
            </body>
            </html>';

            Hasil:
            Pivot Table Custom Rows

    • Bisa mas, tinggal bikin logic nya

      if ($key != 'nama_sales' && $key != 'sales_tim')
      {
      	$dec = strpos($key, 'bln') !== false ||  strpos($key, 'total') !== false ? 2 : 0;
      	$val = number_format($val, $dec, ',', '.');
      }
      if ($key != 'sales_tim')
      {
      	echo '<td>' . $val . '</td>';
      }

Silakan tinggalkan komentar

Like Us

Dapatkan update artikel terbaru via E-Mail
  1. Tutorial PHP Form II: Menampilkan Hasil Input Form HTML dengan PHP
  2. Tutorial PHP Form I: Menghubungkan Form HTML dengan PHP
  3. Menampilkan Data Dari Beberapa Tabel MySQL – JOIN Pada MYSQL
  4. Memahami dan Menampilkan Tanggal dan Waktu Pada PHP
  5. Menghitung Selisih Waktu Dengan PHP – Cara Termudah
  6. Memahami Fungsi Date Pada PHP
  7. Memahami Fungsi Time, Strtotime, dan Mktime Pada PHP – Memanipulasi Waktu
  8. Memahami Zona Waktu (Timezone) dan Selisih Waktu Pada PHP
  9. Setting ODBC MySQL Untuk Gammu
  10. Cara Install dan Menggunakan Gammu di Windows