Pivot Table di CodeIgniter

Menampilkan data baris menjadi bentuk kolom sangat mudah menggunakan aplikasi Microsoft Excel atau aplikasi pengolah data lainnya. Salah satu fitur Microsoft Excel yang kerap digunakan untuk menganalisis data adalah Pivot Table yang memungkinkan penggunanya untuk mengambil informasi secara cepat dari kumpulan data dalam jumlah besar. Selain itu, fitur ini juga kerap digunakan untuk mengelompokkan atau meringkas suatu data.

Bagaimana jika kita ingin menampilkan data seperti dalam pivot table ke dalam aplikasi di codeigniter?

Kali ini kita akan membahas bagaimana cara menampilkan data baris ke dalam kolom seperti yang dapat dilakukan oleh Microsoft Excel ke dalam aplikasi berbasis codeigniter.

Langkah yang harus kita lakukan, yang pertama adalah membuat tabel. Kita akan mengambil contoh tabel jumlah penjualan mobil tiap bulan berdasarkan jenisnya.

Buat tabel sebagai berikut:

Atau copy perintah sql berikut dan jalankan



SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for pivot
-- ----------------------------
DROP TABLE IF EXISTS `pivot`;
CREATE TABLE `pivot`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kode_bulan` int(2) NULL DEFAULT NULL,
  `bulan` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `jenis_mobil` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `jumlah_penjualan` int(5) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of pivot
-- ----------------------------
INSERT INTO `pivot` VALUES (1, 1, 'Januari', 'Sedan', 15);
INSERT INTO `pivot` VALUES (2, 1, 'Januari', 'MPV', 22);
INSERT INTO `pivot` VALUES (3, 1, 'Januari', 'SUV', 11);
INSERT INTO `pivot` VALUES (4, 2, 'Februari', 'Pickup', 14);
INSERT INTO `pivot` VALUES (5, 2, 'Februari', 'Sedan', 11);
INSERT INTO `pivot` VALUES (6, 3, 'Maret', 'Sedan', 18);
INSERT INTO `pivot` VALUES (7, 3, 'Maret', 'MPV', 11);
INSERT INTO `pivot` VALUES (8, 3, 'Maret', 'SUV', 20);
INSERT INTO `pivot` VALUES (9, 3, 'Maret', 'Pickup', 13);
INSERT INTO `pivot` VALUES (10, 4, 'April', 'Sedan', 6);
INSERT INTO `pivot` VALUES (11, 4, 'April', 'MPV', 9);
INSERT INTO `pivot` VALUES (12, 4, 'April', 'SUV', 19);
INSERT INTO `pivot` VALUES (13, 5, 'Mei', 'Sedan', 5);
INSERT INTO `pivot` VALUES (14, 5, 'Mei', 'SUV', 10);
INSERT INTO `pivot` VALUES (15, 5, 'Mei', 'Pickup', 11);
INSERT INTO `pivot` VALUES (16, 5, 'Mei', 'MPV', 17);

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for pivot

-- ----------------------------

DROP TABLE IF EXISTS `pivot`;

CREATE TABLE `pivot` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`kode_bulan` int(2) NULL DEFAULT NULL,

`bulan` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,

`jenis_mobil` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,

`jumlah_penjualan` int(5) NULL DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of pivot

-- ----------------------------

INSERT INTO `pivot` VALUES (1, 1, 'Januari', 'Sedan', 15);

INSERT INTO `pivot` VALUES (2, 1, 'Januari', 'MPV', 22);

INSERT INTO `pivot` VALUES (3, 1, 'Januari', 'SUV', 11);

INSERT INTO `pivot` VALUES (4, 2, 'Februari', 'Pickup', 14);

INSERT INTO `pivot` VALUES (5, 2, 'Februari', 'Sedan', 11);

INSERT INTO `pivot` VALUES (6, 3, 'Maret', 'Sedan', 18);

INSERT INTO `pivot` VALUES (7, 3, 'Maret', 'MPV', 11);

INSERT INTO `pivot` VALUES (8, 3, 'Maret', 'SUV', 20);

INSERT INTO `pivot` VALUES (9, 3, 'Maret', 'Pickup', 13);

INSERT INTO `pivot` VALUES (10, 4, 'April', 'Sedan', 6);

INSERT INTO `pivot` VALUES (11, 4, 'April', 'MPV', 9);

INSERT INTO `pivot` VALUES (12, 4, 'April', 'SUV', 19);

INSERT INTO `pivot` VALUES (13, 5, 'Mei', 'Sedan', 5);

INSERT INTO `pivot` VALUES (14, 5, 'Mei', 'SUV', 10);

INSERT INTO `pivot` VALUES (15, 5, 'Mei', 'Pickup', 11);

INSERT INTO `pivot` VALUES (16, 5, 'Mei', 'MPV', 17);

SET FOREIGN_KEY_CHECKS = 1;

Membuat Model

Buat model dengan nama Pivot_model.php, kemudian ketikkan script berikut:


<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Pivot_model extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }

    public function get_bulan()
    {
        $this->db->select('bulan');
        $this->db->from('pivot');
        $this->db->group_by('bulan');
        $this->db->order_by('kode_bulan', 'asc');
        $query = $this->db->get();
        return $query->result();
    }

    public function get_jenis()
    {
        $this->db->select('jenis_mobil');
        $this->db->from('pivot');
        $this->db->group_by('jenis_mobil');
        $query = $this->db->get();
        return $query->result();
    }

    public function rekap_penjualan()
    {
        $this->db->select('bulan, jenis_mobil');
        $this->db->from('pivot');
        $query = $this->db->get();
        $temp = $query->result();
        $result['data'] = array();
        foreach ($temp as $data) {
            $data->jmlh = $this->jml_penjualan($data->bulan, $data->jenis_mobil);
            $result['data'][] = $data;
        }
        return $result;
    }

    public function jml_penjualan($bulan, $jenis)
    {
        $this->db->select('bulan, jenis_mobil, SUM(jumlah_penjualan) as jumlah');
        $this->db->from('pivot');
        $this->db->where(array('bulan' => $bulan, 'jenis_mobil' => $jenis));       
        $query = $this->db->get();
        $result = $query->result();
        return $result;
    }

}

<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Pivot_model extends CI_Model

{

function __construct()

{

parent::__construct();

}

public function get_bulan()

{

$this->db->select('bulan');

$this->db->from('pivot');

$this->db->group_by('bulan');

$this->db->order_by('kode_bulan', 'asc');

$query = $this->db->get();

return $query->result();

}

public function get_jenis()

{

$this->db->select('jenis_mobil');

$this->db->from('pivot');

$this->db->group_by('jenis_mobil');

$query = $this->db->get();

return $query->result();

}

public function rekap_penjualan()

{

$this->db->select('bulan, jenis_mobil');

$this->db->from('pivot');

$query = $this->db->get();

$temp = $query->result();

$result['data'] = array();

foreach ($temp as $data) {

$data->jmlh = $this->jml_penjualan($data->bulan, $data->jenis_mobil);

$result['data'][] = $data;

}

return $result;

}

public function jml_penjualan($bulan, $jenis)

{

$this->db->select('bulan, jenis_mobil, SUM(jumlah_penjualan) as jumlah');

$this->db->from('pivot');

$this->db->where(array('bulan' => $bulan, 'jenis_mobil' => $jenis));

$query = $this->db->get();

$result = $query->result();

return $result;

}

Membuat Controller

Buat sebuah controller dengan nama Pivot.php, kemudian ketikkan script berikut:


<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Pivot extends CI_Controller
{

    public function __construct()
    {
        parent::__construct();
        $this->load->model('Pivot_model', 'Pivot');
    }

    public function index()
    {
        $pivot = $this->Pivot->rekap_penjualan();
        $data  = array(
            'bulan'      => $this->Pivot->get_bulan(),
            'jenis'      => $this->Pivot->get_jenis(),
            'rekap_data' => $pivot['data'],
        );
        $this->load->view('pivot', $data);
    }
}

<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Pivot extends CI_Controller

{

public function __construct()

{

parent::__construct();

$this->load->model('Pivot_model', 'Pivot');

}

public function index()

{

$pivot = $this->Pivot->rekap_penjualan();

$data = array(

'bulan' => $this->Pivot->get_bulan(),

'jenis' => $this->Pivot->get_jenis(),

'rekap_data' => $pivot['data'],

);

$this->load->view('pivot', $data);

}

Membuat View

Buat view dengan nama pivot.php, kemudian ketikkan script berikut:


<!DOCTYPE html>
<html>

<head>
    <title>Codeigniter - Pivot Table</title>

    <!-- Font -->
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    <link href="https://fonts.googleapis.com/css2?family=Oswald:wght@300;400;600&display=swap" rel="stylesheet">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.0/dist/css/bootstrap.min.css" integrity="sha384-B0vP5xmATw1+K9KRQjQERJvTumQW0nPEzvF6L/Z6nronJ3oUOFUFpCjEUQouq2+l" crossorigin="anonymous">

    <!-- JQuery -->
    <script src="https://code.jquery.com/jquery-3.3.1.min.js" integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8=" crossorigin="anonymous"></script>

    <!-- Datatables -->
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>

    <style>
        body {
            font-family: 'Oswald', sans-serif;
            font-weight: 300;
        }
    </style>
</head>

<body>
    <div class="container">
        <div class="row mt-4">
            <div class="col-12">
                <div class="card">
                    <div class="card-header">
                        Pivot Table
                    </div>
                    <div class="card-body">
                        <div class="responsive">
                            <table id="list_data" class="table display row-border table-striped compact" style="width: 100%;">
                                <thead>
                                    <tr class="text-center">
                                        <th>Jenis \ Bulan</th>
                                        <?php foreach ($bulan as $header) { ?>
                                            <th><?php echo $header->bulan; ?></th>
                                        <?php } ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($jenis as $kolom) { ?>
                                        <tr>
                                            <th><?php echo $kolom->jenis_mobil; ?></th>
                                            <?php foreach ($bulan as $baris) { ?>
                                                <td class="text-center">
                                                    <?php foreach ($rekap_data as $rekap) { ?>
                                                        <?php foreach ($rekap->jmlh as $cell) { ?>
                                                            <?php if ($baris->bulan == $cell->bulan && $kolom->jenis_mobil == $cell->jenis_mobil) {
                                                                echo $cell->jumlah;
                                                            } ?>
                                                        <?php } ?>
                                                    <?php } ?>
                                                </td>
                                            <?php } ?>
                                        </tr>
                                    <?php } ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <script type="text/javascript">
            var dataTable_;
            $(document).ready(function() {
                dataTable_ = $('#list_data').DataTable({});
            })
        </script>
</body>
<div style="clear:both; margin-top:0em; margin-bottom:1em;"><a href="https://mylitenote.com/codeigniter/parsing-data-json-pada-codeigniter-3-dari-sumber-di-luar-aplikasi/" target="_blank" rel="dofollow" class="ud29b58d2dc967a3a7e4954955316fac9"><!-- INLINE RELATED POSTS 3/3 //--><style> .ud29b58d2dc967a3a7e4954955316fac9 { padding:0px; margin: 0; padding-top:1em!important; padding-bottom:1em!important; width:100%; display: block; font-weight:bold; background-color:#eaeaea; border:0!important; border-left:4px solid #34495E!important; box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); -moz-box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); -o-box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); -webkit-box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); text-decoration:none; } .ud29b58d2dc967a3a7e4954955316fac9:active, .ud29b58d2dc967a3a7e4954955316fac9:hover { opacity: 1; transition: opacity 250ms; webkit-transition: opacity 250ms; text-decoration:none; } .ud29b58d2dc967a3a7e4954955316fac9 { transition: background-color 250ms; webkit-transition: background-color 250ms; opacity: 1; transition: opacity 250ms; webkit-transition: opacity 250ms; } .ud29b58d2dc967a3a7e4954955316fac9 .ctaText { font-weight:bold; color:inherit; text-decoration:none; font-size: 16px; } .ud29b58d2dc967a3a7e4954955316fac9 .postTitle { color:#000000; text-decoration: underline!important; font-size: 16px; } .ud29b58d2dc967a3a7e4954955316fac9:hover .postTitle { text-decoration: underline!important; } </style><div style="padding-left:1em; padding-right:1em;"><span class="ctaText">See also</span>  <span class="postTitle">Parsing Data Json pada CodeIgniter 3 dari Sumber di Luar Aplikasi</span></div></a></div>
</html>

<!DOCTYPE html>

<html>

<head>

<title>Codeigniter - Pivot Table</title>

<style>

body {

font-family: 'Oswald', sans-serif;

font-weight: 300;

}

</style>

</head>

<body>

Pivot Table

</div>

<thead>

<th>Jenis \ Bulan</th>

<?php foreach ($bulan as $header) { ?>

<th><?php echo $header->bulan; ?></th>

<?php } ?>

</tr>

</thead>

<tbody>

<?php foreach ($jenis as $kolom) { ?>

<tr>

<th><?php echo $kolom->jenis_mobil; ?></th>

<?php foreach ($bulan as $baris) { ?>

<?php foreach ($rekap_data as $rekap) { ?>

<?php foreach ($rekap->jmlh as $cell) { ?>

<?php if ($baris->bulan == $cell->bulan && $kolom->jenis_mobil == $cell->jenis_mobil) {

echo $cell->jumlah;

} ?>

<?php } ?>

</td>

<?php } ?>

</tr>

<?php } ?>

</tbody>

</table>

</div>

var dataTable_;

$(document).ready(function() {

dataTable_ = $('#list_data').DataTable({});

})

</script>

</body>

</html>

Jika langkah-langkah di atas sudah sesuai maka akan didapatkan hasil seperti berikut ini:

Demikian, semoga bermanfaat.

Pivot Table di CodeIgniter

1 Comment

Leave a Reply Cancel reply

CATEGORIES

ARCHIVES

Statistik

QUOTES

Pivot Table di CodeIgniter

YOU MIGHT BE INTERESTED IN

1 Comment

Leave a Reply Cancel reply

CATEGORIES

ARCHIVES

Statistik

QUOTES