Join Tables with mysqli

wong89

how join tables,,my scripts not work

<?php
include_once "session.php";
/**
* Script: DataTables server-side script for PHP 5.2+ and MySQL 4.1+
* Notes: Based on a script by Allan Jardine that used the old PHP mysql_* functions.
* Rewritten to use the newer object oriented mysqli extension.
* Copyright: 2010 - Allan Jardine (original script)
* 2012 - Kari Söderholm, aka Haprog (updates)
* License: GPL v2 or BSD (3-point)
*/
mb_internal_encoding('UTF-8');

/**
* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'ref_alat_jenis.nama_jenis_alat', 'ref_alat_daftar.kd_daftar_alat', 'ref_alat_daftar.merk_alat', 'ref_alat_daftar.type_alat',
'ref_alat_daftar.jumlah_alat', 'ref_alat_daftar.tarif_sewa_alat' );

// Indexed column (used for fast and accurate table cardinality)
$sIndexColumn = 'ref_alat_daftar.kd_daftar_alat';

// DB table to use
$sTable = 'ref_alat_jenis,ref_alat_daftar';

// Database connection information
include "../../../../include/koneksi.php";

// Input method (use $_GET, $_POST or $_REQUEST)
$input =& $_POST;

/** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/**
* Character set to use for the MySQL connection.
* MySQL will return all strings in this charset to PHP (if the data is stored correctly in the database).
*/
$gaSql['charset'] = 'utf8';

/**
* MySQL connection
*/
$db = new mysqli($gaSql['server'], $gaSql['user'], $gaSql['password'], $gaSql['db']);
if (mysqli_connect_error()) {
die( 'Error connecting to MySQL server (' . mysqli_connect_errno() .') '. mysqli_connect_error() );
}

if (!$db->set_charset($gaSql['charset'])) {
die( 'Error loading character set "'.$gaSql['charset'].'": '.$db->error );
}

/**
* Paging
*/
$sLimit = "";
if ( isset( $input['iDisplayStart'] ) && $input['iDisplayLength'] != '-1' ) {
$sLimit = " LIMIT ".intval( $input['iDisplayStart'] ).", ".intval( $input['iDisplayLength'] );
}

/**
* Ordering
*/
$aOrderingRules = array();
if ( isset( $input['iSortCol_0'] ) ) {
$iSortingCols = intval( $input['iSortingCols'] );
for ( $i=0 ; $i<$iSortingCols ; $i++ ) {
if ( $input[ 'bSortable_'.intval($input['iSortCol_'.$i]) ] == 'true' ) {
$aOrderingRules[] =
"".$aColumns[ intval( $input['iSortCol_'.$i] ) ]." "
.($input['sSortDir_'.$i]==='asc' ? 'asc' : 'desc');
}
}
}

if (!empty($aOrderingRules)) {
$sOrder = " ORDER BY ".implode(", ", $aOrderingRules);
} else {
$sOrder = "ORDER BY ref_alat_daftar.kd_daftar_alat ASC"; // Ubah disini jika ada order clause jika tidak di kosongkan
}

/**
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$iColumnCount = count($aColumns);

if ( isset($input['sSearch']) && $input['sSearch'] != "" ) {
$aFilteringRules = array();
for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
if ( isset($input['bSearchable_'.$i]) && $input['bSearchable_'.$i] == 'true' ) {
$aFilteringRules[] = "".$aColumns[$i]." LIKE '%".$db->real_escape_string( $input['sSearch'] )."%'";
}
}
if (!empty($aFilteringRules)) {
$aFilteringRules = array('('.implode(" OR ", $aFilteringRules).')');
}
}

// Individual column filtering
for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
if ( isset($input['bSearchable_'.$i]) && $input['bSearchable_'.$i] == 'true' && $input['sSearch_'.$i] != '' ) {
$aFilteringRules[] = "".$aColumns[$i]." LIKE '%".$db->real_escape_string($input['sSearch_'.$i])."%'";
}
}

if (!empty($aFilteringRules)) {
$sWhere = " WHERE ".implode(" AND ", $aFilteringRules);
} else {
$sWhere = "WHERE ref_alat_jenis.kd_jenis_alat=ref_alat_daftar.kd_jenis_alat"; // Ubah disini jika ada where clause jika tidak di kosongkan
}

/**
* SQL queries
* Get data to display
*/
$aQueryColumns = array();
foreach ($aColumns as $col) {
if ($col != ' ') {
$aQueryColumns[] = $col;
}
}

$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".implode(", ", $aQueryColumns)."
FROM ".$sTable."".$sWhere.$sOrder.$sLimit;

$rResult = $db->query( $sQuery ) or die($db->error);

// Data set length after filtering
$sQuery = "SELECT FOUND_ROWS()";
$rResultFilterTotal = $db->query( $sQuery ) or die($db->error);
list($iFilteredTotal) = $rResultFilterTotal->fetch_row();

// Total data set length
$sQuery = "SELECT COUNT(".$sIndexColumn.") FROM ".$sTable."";
$rResultTotal = $db->query( $sQuery ) or die($db->error);
list($iTotal) = $rResultTotal->fetch_row();

/**
* Output
*/
$output = array(
"sEcho" => intval($input['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array(),
);

// Membuat Nomor Urut
$no=$input['iDisplayStart'];

while ( $aRow = $rResult->fetch_assoc() ) {
$row = array();

// Menampilkan Tombol Action
$tombolaction = '<div id="atas"><a href="?ref=daftar-alat-edit&amp;changedata='.$aRow["kd_daftar_alat"].'" class="btn btn-secondary btn-sm btn-icon" data-toggle="tooltip" data-placement="top" data-original-title="Edit"> Edit</a> <a href="?ref=daftar-alat-act&amp;act=hapus&amp;dropdata='.$aRow["kd_daftar_alat"].'" class="btn btn-danger btn-sm btn-icon konfirmasihapus" data-toggle="tooltip" data-placement="top" data-original-title="Hapus"> Hapus</a></div>';

// Menampilkan array
for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
        $row[] = $aRow[ $aColumns[$i] ];
}
$no++;
$row = array( '<center>'.$no.'.</center>', ucwords($aRow['nama_jenis_alat']), ucwords($aRow['merk_alat']).' / '.$aRow['type_alat'], 
               $aRow['jumlah_alat'], $aRow['tarif_sewa_alat'], $tombolaction );
$output['aaData'][] = $row;

}

echo json_encode( $output );