Menampilkan Data dari Database Kedalam DataTables di Codeigniter

DataTables merupakan sebuah library javascript yang mempermudah kita dalam menampilkan data kedalam bentuk table. Penggunaannya yang sangat simple dan memiliki banyak fitur membuat datatables banyak digunakan.

Pada postingan kali ini kita akan mencoba menampilkan data dari database kedalam table menggunakan DataTables dan framework Codeigniter 3. Langsung saja ikuti langkah-langkah di bawah.

1. Buat struktur database yang akan ditampilkan.

2. Hubungkan Codeigniter dengan database yang akan ditampilkan, caranya masuk ke application -> config -> database.php lalu masukkan atur sesuai database kalian contohnya seperti dibawah.


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

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => '',
	'database' => 'cmsku',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

3. Buat view dengan nama coba.php kemudian isi dengan kode di bawah :


<!DOCTYPE html>
<html>
<head>
	<title>Coba Data Tables</title>
	<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.6/css/jquery.dataTables.css">

	<!--Import jQuery before export.js-->
	<script type="text/javascript" src="https://code.jquery.com/jquery-2.1.1.min.js"></script>


	<!--Data Table-->
	<script type="text/javascript"  src=" https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
</head>
<body>
<table id="tabelku" class="display" style="width:100%">
        <thead>
            <tr>
                <th>No</th>
                <th>Judul</th>
                <th>Isi</th>
                <th>Status</th>
                <th>Aksi</th>
            </tr>
        </thead>
        <tbody>
           
            
        </tbody>
    </table>

<script type="text/javascript">
	$(document).ready(function(){ 
       let RefTable = {
        init() {
                 RefTable.table=$('#tabelku').DataTable({

                 bSort              : true,
                 destroy            : true, 
                 iDisplayLength     : 10,
                 processing         : true, 
                 serverSide         : true, 
                 order              : [], 
                 ajax               : {
                     "url"  : "<?php echo base_url()?>Coba/load",
                     "type" : "POST",
                 },
                 
              });

            $("body").on("click", "[data-action]", function(e) {
                e.preventDefault();

                let action = $(this).data("action");
                if (action === "edit") {
                    //aksi edit disini
                }
            });
         }
         
     }
        RefTable.init(); 

    }); 

</script>





</body>
</html>

4. Buat Model dengan nama DataModel.php lalu isi dengan kode dibawah


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

class DataModel extends CI_Model {

	var $table = 'postingan a';//nama tabel

	var $column_order = array(null,'a.tanggal',null); //order berdasarkan

	var $column_search = array('a.judul_postingan, a.tanggal', 'a.isi'); //cari berdasarkan

	var $select_column = array('a.*');  //select all
	var $order = array('a.tanggal' => 'desc'); // default order 


	public function __construct()
	{
		parent::__construct();
		$this->load->database(); 
	}		

	private function _get_datatables_query()
	{
		$this->db->select($this->select_column);  
		$this->db->from($this->table);
		$i = 0;
	
		foreach ($this->column_search as $item) // loop column 
		{
			if($_POST['search']['value']) // if datatable send POST for search
			{
				
				if($i===0) // first loop
				{
					$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
					$this->db->like($item, $_POST['search']['value']);
				}
				else
				{
					$this->db->or_like($item, $_POST['search']['value']);
				}

				if(count($this->column_search) - 1 == $i) //last loop
					$this->db->group_end(); //close bracket
			}
			$i++;
		}
		
		if(isset($_POST['order'])) // here order processing
		{
			$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
		} 
		else if(isset($this->order))
		{
			$order = $this->order;
			$this->db->order_by(key($order), $order[key($order)]);
		}
	}
	
	function get_datatables()
	{
		
		$this->_get_datatables_query();
		if($_POST['length'] != -1)
		$this->db->limit($_POST['length'], $_POST['start']);
		$query = $this->db->get();
		return $query->result();
	}

	function count_filtered()
	{
		$this->_get_datatables_query();
		$query = $this->db->get();
		return $query->num_rows();
	}

	public function count_all()
	{
		$this->db->from($this->table);
		return $this->db->count_all_results();
	}
}

Silahkan sesuaikan baris berikut sesuai dengan database kalian.


var $table = 'postingan a';//nama tabel

var $column_order = array(null,'a.tanggal',null); //order berdasarkan

var $column_search = array('a.judul_postingan, a.tanggal', 'a.isi'); //cari berdasarkan

var $select_column = array('a.*');  //select all
var $order = array('a.tanggal' => 'desc'); // default order

5. Buat Controller dengan nama Coba.php lalu isi dengan kode dibawah


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

class Coba extends CI_Controller {

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

	public function index()
	{
		$this->load->view('coba');
	}

	public function load()
	{
		
		$data = array();
		
		$tampil = $this->DataModel->get_datatables();
		
		$no = $_POST['start'];

		foreach ($tampil as $key => $r) {
			$no++;
			$row = array();
			$row[] = $no;
			$row[] = $r->judul_postingan;
			$row[] = $r->isi;
			$row[] = $r->status;
			$row[] = '<a href="javascript:; type="button" class="btn btn-xs yellow btn-outline btn-edit tooltips " title="Edit" data-action="edit" data-id="'.$r->id_postingan.'"><i class="fa fa-edit"></i> Edit</a>
			';
			$data[] = $row;
		}		

		$output = array(
			"draw" => $_POST['draw'],
			"recordsTotal" => $this->DataModel->count_all(),
			"recordsFiltered" => $this->DataModel->count_filtered(),
			"data" => $data,
		);

		echo json_encode($output);
	}
}