个人中心
gpt4 book ai didi

php - 如何在服务器端主动搜索数据表中的内部连接？

转载 作者：行者123 更新时间：2023-12-04 21:09:03 25 4
gpt4 key购买 nike

我在服务器端数据表方面遇到了一些问题。当我使用 inner join 数据时，数据表不起作用，但如果我使用查询 select * from table 这个工作。它的数据表 Controller :

<?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 * ";
$sql.=" FROM hanca";
$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
model.nm_model, hanca.id_hanca, hanca.ukuran, hanca.jumlah_hanca, user.name_usr, vendor.nama_vendor
FROM hanca
INNER JOIN po_detail ON po_detail.id_detail_po = hanca.id_detail_po
INNER JOIN model ON model.id_model = po_detail.id_model
INNER JOIN user ON user.id_usr = hanca.id_user
INNER JOIN vendor ON vendor.id_vendor = hanca.id_vendor WHERE 1=1";
if (!empty($requestData['search']['value'])) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
    $sql.=" AND ( po_detail LIKE '%" . $requestData['search']['value'] . "%' ";
    $sql.=" OR model 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) {
     
    $nestedData = array();
    $nestedData[] = $no++;
    $nestedData[] = $row['nm_model'];
    $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\" onClick=\"detailBelanja('$row[id_hanca]');\" 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

需要更多详细信息的文本虚拟Lorem ipsum dolor sit amet, consectetur adipiscing elit。 Curabitur et arcu aliquet，congue metus eu，congue sapien。 Nam suscipit efficitur elit，ac maximus felis tincidunt eu。 Pellentesque 居民 morbi tristique senectus et netus et malesuada fames ac turpis egestas。 Duis porttitor risus sed erat dignissim varius

最佳答案

试试这个:

HTML 和 JS:

<html>
    <head>
        <link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css" />

        <script type="text/javascript" language="javascript" src="https://code.jquery.com/jquery-3.0.0.min.js"></script>
        <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>

        <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.2.1/js/dataTables.buttons.min.js"></script>
        <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.2.1/js/buttons.flash.min.js"></script>
        <script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
        <script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
        <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.2.1/js/buttons.html5.min.js"></script>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.2.1/css/buttons.dataTables.min.css">

        <script type="text/javascript" language="javascript" class="init">
        $(document).ready(function() {
            var count = 0;
            $('#data_table').dataTable({
                "sServerMethod": "POST", 
                "bProcessing": true,
                "bServerSide": true,
                "sAjaxSource": "get_data.php",

                dom: 'Bfrtip',
                buttons: ['pdf', 'csv'],
            });

            // To edit
            $(document).on('click', '.edit', function(){
                var id = $(this).attr('id');
                alert(id);
                // Editing code here
            });

            // To delete
            $(document).on('click', '.delete', function(){
                var id = $(this).attr('id');
                if(confirm('Are you sure'))
                {
                    alert('Pending');
                    // deleting code here
                }
            });
        });
        </script>

        <style>
        .odd{
            background-color: #FFF8FB !important;
        }
        .even{
            background-color: #DDEBF8 !important;
        }
        </style>
    </head>
    <body>
    <div>
    <table id="data_table">
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Email</th>
                <th>Position</th>
                <th>Department</th>
                <th>Expertise</th>
            </tr>
        </thead>

        <tbody>
        <!-- Dynamic Body -->
        </tbody>

    </table>
    </body>
    </div>
</html>

PHP:

<?php
mysql_connect("localhost", "root", "root") or die('Connection Error');
mysql_select_db("testing_db") or die("Database Connection Error");

$start  = $_REQUEST['iDisplayStart'];
$length = $_REQUEST['iDisplayLength'];
$sSearch = $_REQUEST['sSearch'];

$col = $_REQUEST['iSortCol_0'];

$arr = array(0 => 't1.first_name',1 => 't1.last_name', 2 => 't1.email');

$sort_by = $arr[$col];
$sort_type = $_REQUEST['sSortDir_0'];

$position_filter = '';
$position = substr($_REQUEST['sSearch_3'], 1, -1);
if($position != '')
{
    $position_filter = "and t1.position LIKE '%".$position."%'";
}

$qry = "select t1.id, t1.first_name, t1.last_name, t1.email, t1.position, t1.office, t2.department, t3.expertise from datatables_demo t1 JOIN datatable_dept t2 ON t1.id = t2.emp_id JOIN datatable_expertise t3 ON t1.id = t3.emp_id where (first_name LIKE '%".$sSearch."%' or last_name LIKE '%".$sSearch."%' or email LIKE '%".$sSearch."%') ".$position_filter." ORDER BY ".$sort_by." ".$sort_type." LIMIT ".$start.", ".$length;    // join defined here

$res = mysql_query($qry);
while($row = mysql_fetch_assoc($res))
{
    $data[] = $row;
}

$qry = "select count(id) as count from datatables_demo";
$res = mysql_query($qry);

while($row =  mysql_fetch_assoc($res))
{
    $iTotal = $row['count'];
}

$rec = array(
    'iTotalRecords' => $iTotal,
    'iTotalDisplayRecords' => $iTotal,
    'aaData' => array()
);

$k=0;
if (isset($data) && is_array($data)) {

    foreach ($data as $item) {
        $rec['aaData'][$k] = array(
            0 => ucwords(strtolower($item['first_name'])),
            1 => ucwords(strtolower($item['last_name'])),
            2 => ucwords(strtolower($item['email'])),
            3 => ucwords(strtolower($item['position'])),
            4 => ucwords(strtolower($item['department'])),
            5 => ucwords(strtolower($item['expertise'])),
        );
        $k++;
    }
}

echo json_encode($rec);
?>

关于php - 如何在服务器端主动搜索数据表中的内部连接？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39262480/

25 4 0
文章推荐： docker - 从 docker registry v2 中删除图像
文章推荐： twitter - 如何在我的谷歌浏览器扩展中实现 twitter Oauth
文章推荐： Scala 单线从字符串生成 MD5 哈希
文章推荐： ruby-on-rails - 防止 "options"在 rails 5 模式中创建表
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com