服务器端数据表中的内部连接4表 [英] inner join 4 table in datatables server-side

问题描述

我有代码php用于在数据表中显示数据，但是此php（服务器端）无法正常工作。什么数据表不能使用内部联接？如果可以，如何修复我的代码？再说一遍，如何通过内部联接在数据表中主动搜索数据。非常感谢：

i had code php for showing data in datatables, but this php (server-side) not working . whats the datatables cannot use inner join ? if it can, how to fix my code ?. and one more again, how to active searching data in datatables with inner join. thanks very much:

<?php

require_once '../config/config.php'; // Use require. Can not use INCLUDE function
// storing  request (ie, get/post) global array to a variable  
$requestData = $_REQUEST;

$columns = array(
// datatable column index  => database column name
    0 => 'id_hanca',
    1 => 'id_detail_po',
    2 => 'ukuran',
    3 => 'jumlah_hanca',
    4 => 'status_hanca',
    5 => 'id_user',
    6 => 'id_vendor'
);


// getting total number records without any search


$sql = "SELECT po_detail.id_detail_po, hanca.id_hanca, hanca.ukuran, hanca.jumlah_hanca, user.name_usr, vendor.nama_vendor";
$sql.= "FROM hanca";
$sql.= "INNER JOIN po_detail ON po_detail.id_detail_po = hanca.id_detail_po";
$sql.= "INNER JOIN user ON user.name_usr = hanca.id_user";
$sql.= "INNER JOIN vendor ON vendor.id_vendor = hanca.id_vendor";
$query = $db->query($sql);
$totalData = $query->num_rows;
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.

$sql = "SELECT * ";
$sql.=" FROM hanca WHERE 1=1";
if (!empty($requestData['search']['value'])) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
    $sql.=" AND ( id_hanca LIKE '%" . $requestData['search']['value'] . "%' ";
    $sql.=" OR id_detail_po LIKE '%" . $requestData['search']['value'] . "%' ";
    $sql.=" OR ukuran LIKE '%" . $requestData['search']['value'] . "%' )";
}
$query = $db->query($sql);
$totalFiltered = $query->num_rows; // when there is a search parameter then we have to modify total number filtered rows as per search result. 
$sql.=" ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . "  LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";
$query = $db->query($sql);

$data = array();
$no = 1;
foreach ($query as $row) {
    
    
    
    //Nama User
//    $qry = $db->query("SELECT name_usr FROM user WHERE id_usr='" . $row['id_user'] . "'");
//    $nm_user = $qry->fetch_assoc();
    
    $nestedData = array();
    $nestedData[] = $no++;
    $nestedData[] = $row['id_detail_po'];
    $nestedData[] = $row['ukuran'];
    $nestedData[] = $row['jumlah_hanca'];
    $nestedData[] = $row['name_usr'];    
    $nestedData[] = $row['nama_vendor'];
    // Input Hiddden to include value for update cart
    // Add html button for action
    $nestedData[] = "<a href='#' class=\" btn btn-info btn-xs btn-flat\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Detail Belanja\"><span class=\"glyphicon glyphicon-search\"></span> Detail</a>";
    $data[] = $nestedData;
}

$json_data = array(
    "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw. 
    "recordsTotal" => intval($totalData), // total number of records
    "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
    "data" => $data   // total data array
);

echo json_encode($json_data);  // send data as json format


                       

推荐答案

您没有在选择语句中选择 id_user 和 id_vendor 列...尝试将它们选择为

You aren't selecting id_user and id_vendor colums in your select statement... try selecting them as below:

$ sql = SELECT po_detail.id_detail_po，hanca.id_hanca，hanca.ukuran，hanca.jumlah_hanca，user.name_usr，供应商。 nama_vendor，hanca.id_user，hanca.id_vendor;
$ sql。= FROM hanca;
//代码的其余部分.....

这篇关于服务器端数据表中的内部连接4表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！