PHP/SQL实现DataTable跨两张表通过ID解析对应客户名称

问题现象 #

• 基于jQuery DataTable开发的报价列表功能，基础渲染、排序、搜索均可正常运行
• 客户列仅输出数字格式的client_id（如值为5），无法展示对应客户名称
• 数据存储规则：报价数据存放在quotes表，客户名称字段client_name存放在独立的clients表，两表关联关系为quotes.client_id = clients.id
• 附clients表结构：
  

原有代码 #

前端页面（Tablefile.html） #

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>JQuery Datatable</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
    <script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            $('#jquery-datatable-ajax-php').DataTable({
                  'processing': true,
                  'serverSide': true,
                  'serverMethod': 'post',
                  'order': [[0, 'desc']],
                  'ajax': {
                      'url':'datatable.php'
                  },
                  'columns': [
                    { data: 'id', 'name': 'id', fnCreatedCell: function (nTd, sData, oData, iRow, iCol) {$(nTd).html("<a href='/quotes/view/"+oData.id+"'>"+oData.id+"</a>");}},
                    { data: 'client_id' },
                    { data: 'quote_number' },
                    { data: 'project' },
                    { data: 'quote_total', render: $.fn.dataTable.render.number(',', '.', 2, '$') }
                  ]
           });
        } );
    </script>
</head>
<body>
    <div class="container mt-5">
        <h2 style="margin-bottom: 30px;">jQuery Datatable</h2>
        <table id="jquery-datatable-ajax-php" class="display" style="width:100%">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Client Name</th>
                    <th>Quote Number</th>
                    <th>Project Name</th>
                    <th data-orderable="false">Price</th>
                </tr>
            </thead>
        </table>
    </div>
</body>
</html>

后端接口（datatable.php） #

<?php
   include 'connection.php';
   $draw = $_POST['draw'];
   $row = $_POST['start'];
   $rowperpage = $_POST['length']; // 单页显示行数
   $columnIndex = $_POST['order'][0]['column']; // 排序列索引
   $columnName = $_POST['columns'][$columnIndex]['data']; // 排序列字段名
   $columnSortOrder = $_POST['order'][0]['dir']; // 排序规则 asc/desc
   $searchValue = $_POST['search']['value']; // 搜索关键词
   $searchArray = array();
   // 搜索条件
   $searchQuery = " ";
   if($searchValue != ''){
      $searchQuery = " AND (quote_number LIKE :quote_number OR project LIKE :project OR quote_total LIKE :quote_total ) ";
      $searchArray = array( 
           'quote_number'=>"%$searchValue%",
           'project'=>"%$searchValue%",
           'quote_total'=>"%$searchValue%"
      );
   }
   // 无过滤总记录数
   $stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes ");
   $stmt->execute();
   $records = $stmt->fetch();
   $totalRecords = $records['allcount'];
   // 带过滤总记录数
   $stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes WHERE 1 ".$searchQuery);
   $stmt->execute($searchArray);
   $records = $stmt->fetch();
   $totalRecordwithFilter = $records['allcount'];
   // 查询列表数据
   $stmt = $conn->prepare("SELECT * FROM quotes WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");
   // 绑定参数
   foreach ($searchArray as $key=>$search) {
      $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
   }
   $stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
   $stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
   $stmt->execute();
   $empRecords = $stmt->fetchAll();
   $data = array();
   foreach ($empRecords as $row) {
      $data[] = array(
         "id"=>$row['id'],
         "client_id"=>$row['client_id'],
         "quote_number"=>$row['quote_number'],
         "project"=>$row['project'],
         "quote_total"=>$row['quote_total']
      );
   }
   // 返回响应
   $response = array(
      "draw" => intval($draw),
      "iTotalRecords" => $totalRecords,
      "iTotalDisplayRecords" => $totalRecordwithFilter,
      "aaData" => $data
   );
   echo json_encode($response);

修复步骤 #

核心逻辑是通过SQL JOIN关联两张表，拉取客户名称字段后同步修改前后端字段绑定。

1. 修改列表查询SQL，关联客户表
   将原单表查询语句替换为关联查询，使用INNER JOIN匹配有对应客户信息的报价数据（如果需要保留无对应客户的异常报价，可替换为LEFT JOIN）：

$stmt = $conn->prepare("SELECT quotes.*, clients.client_name FROM quotes INNER JOIN clients ON quotes.client_id = clients.id WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");

2. 修改后端返回数据结构
   在组装返回给前端的$data数组时，将原client_id字段替换为查询到的client_name：

foreach ($empRecords as $row) {
   $data[] = array(
      "id"=>$row['id'],
      "client_name"=>$row['client_name'],
      "quote_number"=>$row['quote_number'],
      "project"=>$row['project'],
      "quote_total"=>$row['quote_total']
   );
}

3. 修改前端列绑定配置
   将DataTable第二列的绑定字段从client_id改为client_name，匹配后端返回的字段：

'columns': [
  { data: 'id', 'name': 'id', fnCreatedCell: function (nTd, sData, oData, iRow, iCol) {$(nTd).html("<a href='/quotes/view/"+oData.id+"'>"+oData.id+"</a>");}},
  { data: 'client_name' },
  { data: 'quote_number' },
  { data: 'project' },
  { data: 'quote_total', render: $.fn.dataTable.render.number(',', '.', 2, '$') }
]

4. （可选）扩展支持客户名称搜索
   如果需要全局搜索支持匹配客户名称，需要同步修改搜索逻辑：

• 给搜索条件增加client_name匹配规则
• 带过滤条件的总数查询SQL也需要增加JOIN逻辑，避免报错
  示例修改：

// 搜索条件修改
if($searchValue != ''){
   $searchQuery = " AND (quote_number LIKE :quote_number OR project LIKE :project OR quote_total LIKE :quote_total OR client_name LIKE :client_name ) ";
   $searchArray = array( 
        'quote_number'=>"%$searchValue%",
        'project'=>"%$searchValue%",
        'quote_total'=>"%$searchValue%",
        'client_name'=>"%$searchValue%"
   );
}
// 带过滤的总数查询修改
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes INNER JOIN clients ON quotes.client_id = clients.id WHERE 1 ".$searchQuery);

内容的提问来源于stack exchange，提问作者dss