Alternative Query Untuk Datatables

.Alternative Query untuk datatables, beberapa waktu yang lalu onphpid menerima banyak sekali pertanyaan baik melalui email, komentar maupun inbox facebook tentang bagaimana sih cara menggunakan table join atau SQL Join di datatables ? mungkin ini terdengar sangat mudah, “masa join aja gak bisa ? ” tapi ternyata setelah onphpid mencoba membuatnya memang cukup sulit, meskipun sudah menggunakan fasilitas Server-Side yang disediakan datatables yaitu ssp.class.php seperti yang pernah ditulis pada tutorial sebelumnya Menangani Jumlah Data yang Besar pada Datatables masih sangat sulit. Hingga akhirnya onphpid memutuskan untuk membuat script Alternative untuk menangani query table join atau SQL Join di datatables.

Mempersiapkan Datatables di localhost

Dalam mempersiapkan datatables di localhost kalian bisa mengikuti tutorial menangani jumlah data yang besar pada datatables atau tutorial-tutorial datatables sebelumnya karena hampir sama, kita hanya akan mengganti script yang berada di file load-data.php agar kita bisa melakukan query join.

kemudian untuk melakukan uji coba sql join kita perlu membuat database, buatlah dengan nama “datatables-join” dan dua buah table karyawan dan position untuk di-join-kan. Kalian bisa langsung mengimportkan file .sql (dot sql) dari sini.

Setelah database dan table sudah selesai dibuat kita langsung saja menuju load-data.php dan mengeksekusinya.

Membuat Script Query Join untuk Datatables

Pertama : Membuat koneksi dengan database.

<?php
// memanggil file config.php
require 'config.php';
// koneksi ke database
$database = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($database->connect_error) {
    die('Oops!! database Not Connect : ' . $database->connect_error);
}

<?php

// memanggil file config.php

require 'config.php';

// koneksi ke database

$database = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($database->connect_error) {

die('Oops!! database Not Connect : ' . $database->connect_error);

}

file config.php berisi informasi untuk koneksi database.

Kedua : Menentukan id table. id table ini bisa kalian manfaatkan untuk keperluan edit dan delete.

$id_table = 'id_karyawan';

1	$id_table = 'id_karyawan';

Ketiga : Menentukan kolom yang akan ditampilkan di bagian table view.

$columns = array(
             'first_name',
             'last_name',
             'position_name',
             'office',
             'start_date'
           );

$columns = array(

'first_name',

'last_name',

'position_name',

'office',

'start_date'

);

Keempat : variable FROM yang bisa kalian gunakankan untuk query join.

// gunakan join disini
$from = 'karyawan K INNER JOIN position P ON K.position = P.id_position';

1 2	// gunakan join disini $from = 'karyawan K INNER JOIN position P ON K.position = P.id_position';

Kelima : SQL custome, kita menggunakan implode untuk menyatukan array columns menjadi sebuah string dan ditambahkan $id_table yang telah disisipkan tanda (,) koma ketika id table di set.

$id_table = $id_table != '' ? $id_table . ',' : '';
// custom SQL
$sql = "SELECT {$id_table} ".implode(',', $columns)." FROM {$from}";

$id_table = $id_table != '' ? $id_table . ',' : '';

// custom SQL

$sql = "SELECT {$id_table} ".implode(',', $columns)." FROM {$from}";

Keenam : Kita akan mulai dengan fungsi Search Datatables yang idenya adalah mencari semua keyword di dalam semua table yang ada dengan bantuan looping (for) dari array columns dan pada akhirnya membentuk sebuah string (syntax SQL wildcard).

berikut hasilnya :

first_name LIKE '%keyword%' OR
last_name LIKE '%keyword%' OR
position_name LIKE '%keyword%' OR
office LIKE '%keyword%' OR
start_date LIKE '%keyword%' OR

first_name LIKE '%keyword%' OR

last_name LIKE '%keyword%' OR

position_name LIKE '%keyword%' OR

office LIKE '%keyword%' OR

start_date LIKE '%keyword%' OR

diakhir syntax terdapat ‘OR’ yang bisa membuat SQL kita error karena kelebihan ‘OR’ agar tidak kelebihan kita menggunakan pengkondisian jika nilai $i kurang dari total $columns dikurangi 1. sehingga OR dipaling akhir dihilangkan.

berikut kode lengkapnya :

// search
if (isset($_GET['search']['value']) && $_GET['search']['value'] != '') {
    $search = $_GET['search']['value'];
    $where  = '';
    // create parameter pencarian kesemua kolom yang tertulis
    // di $columns
    for ($i=0; $i < count($columns); $i++) {
        $where .= $columns[$i] . ' LIKE "%'.$search.'%"';
        // agar tidak menambahkan 'OR' diakhir Looping
        if ($i < count($columns)-1) {
            $where .= ' OR ';
        }
    }
    $sql .= ' WHERE ' . $where;
}

// search

if (isset($_GET['search']['value']) && $_GET['search']['value'] != '') {

$search = $_GET['search']['value'];

$where = '';

// create parameter pencarian kesemua kolom yang tertulis

// di $columns

for ($i=0; $i < count($columns); $i++) {

$where .= $columns[$i] . ' LIKE "%'.$search.'%"';

// agar tidak menambahkan 'OR' diakhir Looping

if ($i < count($columns)-1) {

$where .= ' OR ';

}

$sql .= ' WHERE ' . $where;

}

terakhir pada fungsi Search kita melakukan penambahan $sql .= ‘ WHERE ‘. $where; baca maksud dari ( .= / operator string ).

Ketujuh : Selanjutnya adalah pengurutan ASC atau DESC percolumn, mula-mula kita tangkap dulu nilai yang dikirimkan via AJAX untuk sorting columns.

$sortColumn = isset($_GET['order'][0]['column']) ? $_GET['order'][0]['column'] : 0;
$sortDir    = isset($_GET['order'][0]['dir']) ? $_GET['order'][0]['dir'] : 'asc';

1 2	$sortColumn = isset($_GET['order'][0]['column']) ? $_GET['order'][0]['column'] : 0; $sortDir = isset($_GET['order'][0]['dir']) ? $_GET['order'][0]['dir'] : 'asc';

karena nilai dari sorting berupa angka kita perlu mengubahnya menjadi nama column dulu agar bisa menjalakan fungsi sort. untungnya kita membuat list table yang ingin kita tambilkan dengan array sehingga kita cukup menggunakan kode berikut dan outputnya adalah nama table.

$sortColumn = $columns[$sortColumn];
$sql .= " ORDER BY {$sortColumn} {$sortDir}";

$sortColumn = $columns[$sortColumn];

$sql .= " ORDER BY {$sortColumn} {$sortDir}";

Kedelapan : Menghitung total data, tahap 1 hingga 7 diatas merupakan tahap-tahap memanipulasi SQL SELECT saja jadi belum tereksekusi ditahap ke 8 ini kita akan melakukan eksekusi sekaligus menghitung total datanya.

$count = $database->query($sql);
// hitung semua data
$totaldata = $count->num_rows;
$count->close();

$count = $database->query($sql);

// hitung semua data

$totaldata = $count->num_rows;

$count->close();

kesembilan : Menambahkan Limit, untuk menghitung total data dan menggunakan limit kita memerlukan dua eksekusi query karena jika kita menggunakan satu eksekusi query maka total data akan sama dengan data yang dibatasi. misalkan kita mempunyai data 100 tapi di berikan LIMIT 10 maka total data kita hanya 10 saja bukan 100.

kalau tadi kita memberi nama eksekusi pertama dengan nama $count, maka yang kedua kita berinama $data. namun sebelum itu kita akan memanipulasi SQL lagi dengan menambahkan LIMIT.

// memberi Limit
$start  = isset($_GET['start']) ? $_GET['start'] : 0;
$length = isset($_GET['length']) ? $_GET['length'] : 10;
$sql .= " LIMIT {$start}, {$length}";
$data  = $database->query($sql);

// memberi Limit

$start = isset($_GET['start']) ? $_GET['start'] : 0;

$length = isset($_GET['length']) ? $_GET['length'] : 10;

$sql .= " LIMIT {$start}, {$length}";

$data = $database->query($sql);

Kesepuluh : Menyusun data Json, karena datatables menginginkan outputnya adalah json maka kita harus membuatnya menjadi json.

// create json format
$datatable['draw']            = isset($_GET['draw']) ? $_GET['draw'] : 1;
$datatable['recordsTotal']    = $totaldata;
$datatable['recordsFiltered'] = $totaldata;
$datatable['data']            = array();
while ($row = $data->fetch_assoc()) {
    $fields = array();
    for ($i=0; $i < count($columns); $i++) {
        # code...
        $fields[] = $row["{$columns[$i]}"];
    }
    $datatable['data'][] = $fields;
}
$data->close();
echo json_encode($datatable);

// create json format

$datatable['draw'] = isset($_GET['draw']) ? $_GET['draw'] : 1;

$datatable['recordsTotal'] = $totaldata;

$datatable['recordsFiltered'] = $totaldata;

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

while ($row = $data->fetch_assoc()) {

$fields = array();

for ($i=0; $i < count($columns); $i++) {

# code...

$fields[] = $row["{$columns[$i]}"];

}

$datatable['data'][] = $fields;

}

$data->close();

echo json_encode($datatable);

kode berikut merupakan manipulasi pengurutan data sesuai $columns yang sudah kita buat diatas.

tearkhir adalah json_encode() untuk mengubah array menjadi json.

Sekian Alternative SQL Join untuk Datatables semoga bermanfaat apabila ada error atau ada cara yang lebih baik bisa dishare melalui komentar karena yang onphpid buat masih sangat-sangat sederhana. selamat berekseperimen dan terimakasih.

kode selengkapnya bisa kalian dapatkan disini.