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.

Subscibe Now

Suka dengan artikel di Jagowebdev.com? jangan sampai ketinggalan, segera join ke milis kami untuk update informasi terbaru dari Jagowebdev.com

Komitmen Kami: Kami senantiasa menghargai privasi Anda dan tidak akan membagikan identitas Anda ke pihak manapun.

93 Feedback dari pembaca

  • selamat siang pak,

    mohon bantuannya ngimana kalau data yang di atas di tampilkan
    berdasarkan kriteria tanggal 2016-03-11 sampai 2016-03-20,

    terima kasih.

  • 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
  • Tanya pak:
    Kalo mau membuat nilai desimal 2 angka di belakang koma khusus hanya untuk nilai dan total trx

    • 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()

      • Iya betul, maksud saya pecahan, tipe data kolom saya sudah dibuat DECIMAL(8,2), namun saat ditampilkan kembali, angka 2 desimal di belakang koma tetap tidak muncul.

        • 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

  • Untuk yang ketiga, apakah kolom TIM yang terletak setelah kolom NAMA SALES itu bisa dihapus/tidak ditampilkan?

    • 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>';
      }
      • ok, sukses pak
        oh iya, terus kalo misalkan angka 1,2, dst. yang ada di kolom TIM diganti dengan huruf Satu, Dua, dst. bagaimana script php nya pak

        Terima kasih…

        • Untuk mengubah angka menjadi terbilang, kita definisikan nama terbilang tersebut mas, misal:

          $terbilang = array(1=>'Satu', 'Dua', 'Tiga');

          Selanjutnya ketika menamppilkan data tim, tinggall dipanggil variabel $terbilang tersebut, misal:

          'TIM '.$terbilang[$array['sales_tim']].'
          • Saya sudah coba tapi muncul error
            Notice: Undefined index: sales_tim in C:\xampp\htdocs\tpk\pivot-table\pivot-table-total-manual-custom-sort.php on line 136

            bagaimana solusinya pak?

          • Coba ini mas, masih kelanjutan jawaban sebelumnya:

            $terbilang = array(1=>'Satu', 'Dua', 'Tiga');
            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 '. strtoupper($terbilang[$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>';
            }
  • Maaf mas mau tanya,

    Jika dalam isi colom NILAI tersebut adalah tanggal ? bagaimana cara menampilkannya ya mas ? saya coba diSQL hasilnya dalam setahun hanya menampilkan data terakhir saja.

    Terimakasih

  • Selamat sore mas agus, kalau misalkan nama sales A tidak ada transaksi dalam 1 tahun tapi mau tetap di tamilkan

    +------------+-----------+-------------+-------+--------------+-------+-----------+-------+---------+-----------+
    | 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 |
    | A          | 0         |            0|      0|             0|      0|          0|      0|        0|          0|
    +------------+-----------+-------------+-------+--------------+-------+-----------+-------+---------+-----------+

    Terimaksih sebelumya

    • Itu hanya satu tabel mbak, jika tidak ada transaksi nama tidak muncul.
      Mungkin yang dimaksud jika tabelnya ada dua, tabel pelanggan dan transaksi, jika demikian, bisa menggunakan left join mbak:

      SELECT * FROM pelanggan LEFT JOIN transaksi

      Jika hubungan tabel one to many, maka query untuk transaksinya bisa diletakkan dalam subquery

  • hallo salam, pak klo mau query dari row ke kolom, tidak harus di declare isina bisa ? contoh ky select 2016,2015, dll, from table, klo isinya ada sekitar 50 baris ke kolom bagaimana ya, di sub lagi baris tadi jadi kelompok, contoh 1984, 2017, 1984 – 1990 (tahun awal) tahun area1, 1991-2000 (tahun tengah) tahun area2, dst, di query dan class php n function gimana ya atau keywordna di google. trims

  • Pak MasterSuhu, saya punya 2 tabel ygmana saya ingin menjumlahkan “pokok” senilai 400.000,- di tabel anggota dan “wajib+sukarela” senilai 4.400.000,- di tabel simpanan. saya menggunakan rumus: select sum(pokok+wajib+sukarela) from anggota, simpanan. yang saya harapkan seharusnya 4.800.000,-. tapi hasilnya malah 23.600.000,-. mohon pencerahannya pak

  • Salam master, tanya pak Agus.
    Dengan kondisi Pivot Table seperti ini apakah bisa ditambahkan 1 baris lagi dibawah “TOTAL”, baris untuk mengetahui selisih atau perbandingan antara TIM 1 dan 2, misalnya:
    Subtotal TIM 1 = 100.000
    Subtotal TIM 2 = 60.000
    Total = 160.000
    Perbandingan ( TIM 1 – TIM 2 ) = 40.000

    Demikian pak mohon pencerahannya

    Regards,

  • Pak Master yth.. Bagaimana kalau nilai datanya tidak tersimpan di database, dalam artian nilainya tersebut merupakan olahan dari nilai yang ada dalam database. Apakah memungkinkan..?! kemudian, kalau tabelnya ada 3 atau 4… itu gimana gabungin nya..??!!

  • Dengan kondisi Pivot Table seperti ini apakah bisa ditambahkan 1 baris lagi dibawah “TOTAL”, baris untuk mengetahui selisih atau perbandingan antara TIM 1 dan 2, misalnya:
    Subtotal TIM 1 = 100.000
    Subtotal TIM 2 = 60.000
    Total = 160.000
    Perbandingan ( Subtotal TIM 1 – Subtotal TIM 2 ) = 40.000

    Tks,

  • Bagaimana dengan header yang dinamis Pak Agus ?
    jika isi DISTINCT field1 = [KEPALA, IGA, BUNTUT, PAHA, USUS],
    akan dijadikan header kolom yang dinamis, yang kita tidak tahu user akan input apa pun itu.

    sample table1 :
    kolom NAMA berisi
    ANDA,
    DIA,
    KAMU,
    DAKU,
    KALIAN,
    KAMI,
    DIA,
    KAKAK,
    ADIK

    kolom BAGIAN berisi
    KEPALA,
    IGA,
    KEPALA,
    USUS,
    BUNTUT,
    IGA,
    PAHA,
    IGA,
    USUS


    menjadi query1 :
    kolom KEPALA berisi 2
    kolom IGA berisi 3
    kolom USUS berisi 2
    kolom BUNTUT berisi 1
    kolom PAHA berisi 1

    maksudnya di sini, isi field1 menjadi header secara dinamis. kita tidak tahu isi field1 berikutnya.

    terimakasih

  • Numpang bertanya, logic untuk tidak menampilkan kolom tim bagaimana ya mas ? mohon bantuannya terima kasih

  • kalo yang dimaksud menghilangkan bagian select
    IFNULL( tim, ‘TOTAL’ ) AS sales_tim,

    sudah saya coba, namun yang muncul sub total menjadi tiap item dari list nya ,

    terima kasih

  • selamat malam.
    saya mau tanya pak,
    1. klo sub total atau total tidak di tampilkan, bagian mana yang di hapus?
    2. bagaimana membuat kondisi, jika nilai lebih besar makan kategori = Tinggi jika lebih kecil kategori = rendah
    saya buat seperti ini, hasil ny rendah semua
    $sql = ”
    SELECT
    IFNULL( unit, ” ) AS nama_sales,
    SUM( IF( tahun = 2017, kepuasan_pasien, 0) ) AS bln_2017,
    IF(‘bln_2017’ >= 0.85, ‘Tinggi’, ‘Rendah’) AS Kategori,
    SUM( IF( tahun = 2018, kepuasan_pasien, 0) ) AS bln_2018,
    IF(‘bln_2018’ >= 0.85, ‘Tinggi’, ‘Rendah’) AS Kategori2,
    SUM( IF( tahun = 2019, kepuasan_pasien, 0) ) AS bln_2019,
    IF(‘bln_2019’ >= 0.85, ‘Tinggi’, ‘Rendah’) AS Kategori3

    FROM data_kinerja_r_ss
    GROUP BY unit
    WITH ROLLUP”;

    terimakasih sehat selalu pak 😀

  • hasilnya setiap nilai jdi rendah semua pak,
    nilai kepuasan pasien misal
    tahun = 2017
    kepuasan pasien = 0.90
    kategori tinggi

    tahun = 2018
    kepuasan pasien = 0.70
    kategori rendah

    • Saya, belum paham maksudnya mas.. Coba ditampilkan tabel awal seperti apa, tabel hasil yang diinginkan seperti apa, query yang dijalankan seperti apa, hasilnya seperti apa?

    • Coba nilai pembandingnya tidak paka tanda kutip mas IF(‘tahun’ = 2017 AND kepuasan_pasien >= 0.85, ‘Tinggi’, ‘Rendah’) AS Kategori_2017. Di tabel contoh tidak ada kolom kepuasan_pasien

  • sudah bisa pak, saya tambah kan seperti ini

    if($array[‘bln_2015’] >= 0.85 ){$val2015 = “Tinggi”;}else{$val2015 = “Rendah”;}
    if($array[‘bln_2016’] >= 0.85 ){$val2016 = “Tinggi”;}else{$val2016 = “Rendah”;}
    if($array[‘bln_2017’] >= 0.85 ){$val2017 = “Tinggi”;}else{$val2017 = “Rendah”;}
    if($array[‘bln_2018’] >= 0.85 ){$val2018 = “Tinggi”;}else{$val2018 = “Rendah”;}
    echo”$array[UNIT]$array[bln_2015]$val2015″;
    echo”$array[bln_2016]$val2016″;
    echo”$array[bln_2017]$val2017″;
    echo”$array[bln_2018]$val2018″;

    mungkin ada cara yg sederhana lgi hehehe

    terima kasih pak

  • Pak Master,
    bagaimana jkodingnya ika saya akan melakukan perhitungan komulatif per nama dari data di atas :

    id_trx nama tgl_trx tim nilai_trx nilai_kum
    1 Alfa 1/10/2016 1 250000 250000
    2 Alfa 1/15/2016 1 125000 375000
    3 Alfa 2/2/2016 1 250000 625000
    4 Alfa 2/5/2016 1 215000 840000
    5 Alfa 2/22/2016 1 350000 1190000
    6 Alfa 3/12/2016 1 150000 1340000
    7 Alfa 3/17/2016 1 225000 1565000
    8 Bravo 1/1/2016 1 310000 310000
    9 Bravo 2/4/2016 1 250000 560000
    10 Bravo 2/7/2016 1 275000 835000
    11 Bravo 3/6/2016 1 150000 985000
    12 Bravo 3/11/2016 1 425000 1410000
    13 Bravo 3/18/2016 1 250000 1660000
    14 Charlie 1/2/2016 2 175000 175000
    15 Charlie 1/13/2016 2 325000 500000
    16 Charlie 1/23/2016 2 350000 850000
    17 Charlie 2/22/2016 2 300000 1150000
    18 Charlie 3/12/2016 2 450000 1600000
    19 Charlie 3/21/2016 2 275000 1875000
    20 Delta 2/11/2016 2 450000 450000
    21 Delta 2/17/2016 2 550000 1000000
    22 Delta 3/11/2016 2 370000 1370000

  • Mohon maaf mas,
    Jadi ada 3 tabel saling berelasi :
    https://ibb.co/xD3xvXH

    tabel siswa :
    https://ibb.co/VqGR5TT

    tabel rombel :
    https://ibb.co/JnJmjPj

    tabel pembayaran :
    https://ibb.co/R24bjnY

    saya ingin menampilkan :
    nis dari tabel siswa,
    nama_siswa dari tabel siswa,
    nama_rombel dari tabel rombel,
    tglbayar berdasarkan bulan jatuhtempo dari tabel pembayaran.

    tapi hasilnya seperti ini :
    https://ibb.co/ZM7dc5F

    dengan memodifikasi beberapa source code pada artikel ini sebagai berikut :
    https://codeshare.io/G6rAqY

    letak kesalahannya sebelah mana ya mas? Mohon pencerahannya, terimakasih.

    • Itu pakai IF di querynya mas, jadi nanti hasil querynya:

      $data = array(	array("nama" => "Nama Siswa"
      			, "07" => 1
      			, "08" => 10
      			, "09" => 5
      			...
      		),
      		array("nama" => "Nama Siswa 2"
      			, "07" => 4
      			, "08" => 3
      			, "09" => 6
      			...
      		)
      		...
      	)

      Baru kemudian ditampilkan datanya di tabel html

  • source nya saya modif seperti ini :

    $sql = ”
    SELECT

    IFNULL( idsiswa, ” ) AS siswa,
    IFNULL( idsiswa, ” ) AS sales_tim,

    SUM( IF( MONTH(jatuhtempo) = 7, tglbayar, 0) ) AS bln_Jul,
    COUNT( IF( MONTH(jatuhtempo) = 7, idspp, NULL) ) AS trx_1,

    SUM( IF( MONTH(jatuhtempo) = 8, tglbayar, 0) ) AS bln_Ags,
    COUNT( IF( MONTH(jatuhtempo) = 8, tglbayar, NULL) ) AS trx_2,

    SUM( IF( MONTH(jatuhtempo) = 9, tglbayar, 0) ) AS bln_Sept,
    COUNT( IF( MONTH(jatuhtempo) = 9, tglbayar, NULL) ) AS trx_3,

    SUM( IF( MONTH(jatuhtempo) = 10, tglbayar, 0) ) AS bln_Okt,
    COUNT( IF( MONTH(jatuhtempo) = 10, tglbayar, NULL) ) AS trx_4,

    SUM( IF( MONTH(jatuhtempo) = 11, tglbayar, 0) ) AS bln_Nov,
    COUNT( IF( MONTH(jatuhtempo) = 11, tglbayar, NULL) ) AS trx_5,

    SUM( IF( MONTH(jatuhtempo) = 12, tglbayar, 0) ) AS bln_Des,
    COUNT( IF( MONTH(jatuhtempo) = 12, tglbayar, NULL) ) AS trx_6,

    SUM( IF( MONTH(jatuhtempo) = 1, tglbayar, 0) ) AS bln_Jan,
    COUNT( IF( MONTH(jatuhtempo) = 1, tglbayar, NULL) ) AS trx_7,

    SUM( IF( MONTH(jatuhtempo) = 2, tglbayar, 0) ) AS bln_Feb,
    COUNT( IF( MONTH(jatuhtempo) = 2, tglbayar, NULL) ) AS trx_8,

    SUM( IF( MONTH(jatuhtempo) = 3, tglbayar, 0) ) AS bln_Mar,
    COUNT( IF( MONTH(jatuhtempo) = 3, tglbayar, NULL) ) AS trx_9,

    SUM( IF( MONTH(jatuhtempo) = 4, tglbayar, 0) ) AS bln_Apr,
    COUNT( IF( MONTH(jatuhtempo) = 4, tglbayar, NULL) ) AS trx_10,

    SUM( IF( MONTH(jatuhtempo) = 5, tglbayar, 0) ) AS bln_Mei,
    COUNT( IF( MONTH(jatuhtempo) = 5, tglbayar, NULL) ) AS trx_11,

    SUM( IF( MONTH(jatuhtempo) = 6, tglbayar, 0) ) AS bln_Jun,
    COUNT( IF( MONTH(jatuhtempo) = 6, tglbayar, NULL) ) AS trx_12

    FROM pembayaran

    GROUP BY idsiswa

    WITH ROLLUP”;

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

    penempatan innerjoin nya sebelah mana ? saya coba ditempatkan sesudah SELECT dan sebelum FROM tetap ga tampil.

    terus menggunakan IF pada query nya itu maksudnya di sebelah mana ya mas?

    terus “07”, “08”, “09” itu maskudnya apa mas?

    dan 1, 10, 5 itu apa?

    mohon maaf mas, agak panjang…hihi

  • yang ini mas:

    $data = array( array(“nama” => “Nama Siswa”
    , “07” => 1
    , “08” => 10
    , “09” => 5

    ),
    array(“nama” => “Nama Siswa 2”
    , “07” => 4
    , “08” => 3
    , “09” => 6

    )

    )

    “07”, “08”, “09” itu apa?
    1,10,5 itu apa?

  • mas agus nanya ya..

    saya sudah berhasil buat pivot dinamis mysql sesuai contoh dari tulisan mas agus..
    pas implementasiannya di php (ci) kok susahnya ..

    1. apakah harus pakai store procedure.. (masih belajar buat store procedure salah terus dipenggunaan parameternya.. )

    2. apakah tidak bisa klo ikut cara diatas langsung lewat query..

  • Mas agus saya sudah berhasil membuat PIVOT TABLE dengan data saya sendiri,
    tapi selalu muncul Notice in .\libraries\classes\Display\Results.php#4241, tapi data berhasil muncul sesuai keinginan mas, apa itu berpengaruh? soalnya pas mau saya tampilkan hasilnya di PHP, selalu NULL

      • Ini query saya mas

        SET @sql_dinamis = (SELECT GROUP_CONCAT(DISTINCT CONCAT(“(SELECT STATUS FROM data_absen WHERE DAY(DATE_FORMAT(PERIODE,’%Y-%m-%d’)) = “,DAY(DATE_FORMAT(PERIODE,’%Y-%m-%d’)),” AND NIK IN(a.NIK)) AS TGL_”,DAY(DATE_FORMAT(PERIODE,’%Y-%m-%d’)))) FROM data_absen);

        SET @sql = CONCAT(‘SELECT a.NIK ,’,@sql_dinamis,’ FROM data_absen a GROUP BY a.NIK’);

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        Tapi sekarang sudah solved dan bisa tampil di PHP hanya saja tidak seperti query di atas
        Saya ubah, Kira kira bagaimana ya menampilkan query seperti di atas di PHP ?

  • halo mas, saya mau bertanya jika kita sudah SUM beberapa kolom dan sudah kita GROUP BY, apakah hasil dari SUM sebelumnya bisa kita SUM lagi untuk mendapatkan totalnya?

    terima kasih mas

  • Pa Agus ijin bertanya, saya punya case, kalo untuk tahunnya lebih dari satu gimana ya pa, dan tahunya belum diketahui, bisa 2021-2022?
    —————————————————————-
    | | | 2020 | 2021 | |
    | No. | Name Product |———————————| Total |
    | | | Okt |Nov |Des | Jan | feb | Mar | |
    —————————————————————
    | 1 | Product 1 | 1 | – | 1 | 2 | – | – | 4 |
    | 2 | Product 2 | – | 1 | 1 | – | – | – | 2 |
    | 3 | Product 3 | 1 | – | 1 | 2 | – | 1 | 5 |
    —————————————————————-

  • Halo mas Agus, mau tanya tutorial diatas kan itu untuk barisnya yang sudah fix jumlahnya untuk dijadikan kolom, kalau semisal kasus barisnya nambah terus dan setiap baris tersebut harus dijadikan kolom tapi setiap penambahan baris tidak perlu menambahkan query seperti ini bisa ga ya mas?

    SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS bln_januari,
    COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,

    Jadi langsung otomatis kolomnya nambah sesuai jumlah baris yang baru tanpa merubah query, terima kasih mas.

  • Selamat siang pak agus
    mas agus mohon bantuannya untuk scripth php nyaa, saya gagal terus tpi untuk query di xampp berhasil
    untuk query seperti ini:
    SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
    ‘SUM(IF (K.id_barang = “‘,
    K.id_barang,
    ‘”, K.jumlah_keluar,0)) AS `’,
    B.id_barang,
    ‘`’
    )
    ) INTO @sql
    FROM is_barang_keluar K INNER JOIN is_barang B ON K.id_barang=B.id_barang WHERE B.id_jenis = 3;

    SET @sql = CONCAT(
    ‘SELECT K.id_penerima,K.tanggal_keluar,P.nama_penerima,P.alamat,’,
    @sql,
    ‘FROM is_barang_keluar as K INNER JOIN is_barang as B INNER JOIN is_penerima as P ON K.id_barang=B.id_barang AND K.id_penerima=P.id_penerima WHERE B.id_jenis=3 GROUP BY K.id_penerima ORDER BY K.tanggal_keluar ASC’
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    — Clear something
    DEALLOCATE PREPARE stmt;
    SET @sql = NULL;

  • Mas mau tanya, saya kan sudah berhasil untuk melakukan pivot dinamis di mysql. nah saya mau menampilkan discript php
    “SET @sql_dinamis = (SELECT GROUP_CONCAT(
    DISTINCT CONCAT(‘COUNT(IF(hasil_id=”’, hasil_id,”’, nilai,NULL)) AS’, hasil_id))
    FROM log_hasil
    );
    SET @sql = CONCAT(‘SELECT provinsi,’,@sql_dinamis,’ FROM log_hasil INNER JOIN penyakit_tbl ON log_hasil.hasil_id = penyakit_tbl.id_penyakit GROUP BY provinsi’);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt”;

    tapi error mas,
    Fatal error
    : Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘SET @sql = CONCAT(‘SELECT provinsi,’,@sql_dinamis,’ FROM log_hasil INNER JOIN…’ at line 7 in C:\xampp\htdocs\webPenyakit\admin\grafik.php:80 Stack trace: #0 C:\xampp\htdocs\webPenyakit\admin\grafik.php(80): mysqli_query(Object(mysqli), ‘SET @sql_dinami…’) #1 {main} thrown in
    C:\xampp\htdocs\webPenyakit\admin\grafik.php
    on line
    80
    tolong solusinya

Silakan tinggalkan komentar

Newsletter

Jadilah yang pertama tahu berita terbaru dari Jagowebdev.com