基于PHP+DataTable实现Query Builder风格的自定义网格搜索需求

我之前正好做过类似的需求，结合你用PHP+Ajax+DataTable的技术栈，给你一套可落地的解决方案，分前端集成和后端解析两部分来实现：

一、前端部分：集成QueryBuilder组件并对接DataTable #

1. 引入依赖资源 #

首先在页面里加载必要的库（注意版本兼容，jQuery是基础依赖）：

<!-- jQuery -->
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<!-- DataTables CSS & JS -->
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.min.css">
<script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
<!-- QueryBuilder CSS & JS -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jQuery-QueryBuilder@2.6.4/dist/css/query-builder.default.min.css">
<script src="https://cdn.jsdelivr.net/npm/jQuery-QueryBuilder@2.6.4/dist/js/query-builder.min.js"></script>

2. 构建页面结构 #

添加QueryBuilder容器、搜索按钮和DataTable容器：

<div id="queryBuilder"></div>
<button id="btnSearch" class="btn btn-primary mt-3">执行搜索</button>
<table id="dataTable" class="display" width="100%"></table>

3. 初始化QueryBuilder和DataTable #

在JS里配置QueryBuilder的字段规则，同时初始化DataTable的Ajax加载逻辑：

$(document).ready(function() {
    // 1. 初始化QueryBuilder
    $('#queryBuilder').queryBuilder({
        plugins: ['bt-tooltip-errors'],
        filters: [
            {
                id: 'name',
                label: 'Name',
                type: 'string',
                operators: ['equal', 'not_equal', 'contains', 'not_contains', 'begins_with', 'ends_with']
            },
            {
                id: 'email',
                label: 'Email',
                type: 'string',
                operators: ['equal', 'not_equal', 'contains', 'not_contains']
            },
            {
                id: 'phone',
                label: 'Phone',
                type: 'string',
                operators: ['equal', 'contains']
            },
            {
                id: 'gender',
                label: 'Gender',
                type: 'string',
                operators: ['equal', 'not_equal'],
                values: {
                    'male': 'Male',
                    'female': 'Female'
                }
            },
            {
                id: 'zipcode',
                label: 'Zipcode',
                type: 'integer',
                operators: ['equal', 'not_equal', 'less', 'less_or_equal', 'greater', 'greater_or_equal']
            }
        ]
    });

    // 2. 初始化DataTable
    var table = $('#dataTable').DataTable({
        processing: true,
        serverSide: true,
        ajax: {
            url: 'fetch_data.php',
            type: 'POST',
            data: function(d) {
                // 把QueryBuilder的规则传给后端
                d.queryRules = $('#queryBuilder').queryBuilder('getRules');
            }
        },
        columns: [
            { data: 'name', title: 'Name' },
            { data: 'email', title: 'Email' },
            { data: 'phone', title: 'Phone' },
            { data: 'gender', title: 'Gender' },
            { data: 'zipcode', title: 'Zipcode' }
        ]
    });

    // 3. 绑定搜索按钮事件
    $('#btnSearch').on('click', function() {
        table.draw(); // 触发DataTable重新加载数据
    });
});

二、后端部分：PHP解析查询规则并生成SQL #

1. 核心函数：解析QueryBuilder规则为SQL WHERE子句 #

创建一个函数来把前端传来的规则转换成安全的SQL片段，这里一定要用预处理语句来防止SQL注入：

function parseQueryRules($rules, &$params) {
    $sqlParts = [];
    $operatorsMap = [
        'equal' => '=',
        'not_equal' => '!=',
        'contains' => 'LIKE',
        'not_contains' => 'NOT LIKE',
        'begins_with' => 'LIKE',
        'ends_with' => 'LIKE',
        'less' => '<',
        'less_or_equal' => '<=',
        'greater' => '>',
        'greater_or_equal' => '>='
    ];

    foreach ($rules as $rule) {
        if (isset($rule['rules'])) {
            // 处理分组规则（AND/OR）
            $groupSql = parseQueryRules($rule['rules'], $params);
            if (!empty($groupSql)) {
                $sqlParts[] = "($groupSql) " . strtoupper($rule['condition']);
            }
        } else {
            // 处理单个规则
            $operator = $operatorsMap[$rule['operator']];
            $field = $rule['id'];
            $value = $rule['value'];

            // 根据操作符处理值的格式
            switch ($rule['operator']) {
                case 'contains':
                case 'not_contains':
                    $value = "%$value%";
                    break;
                case 'begins_with':
                    $value = "$value%";
                    break;
                case 'ends_with':
                    $value = "%$value";
                    break;
            }

            $paramKey = ":param_" . count($params);
            $params[$paramKey] = $value;
            $sqlParts[] = "$field $operator $paramKey";
        }
    }

    // 处理分组后的条件连接
    if (!empty($sqlParts)) {
        // 移除最后一个多余的AND/OR（分组时会多带一个）
        $lastPart = array_pop($sqlParts);
        if (strpos($lastPart, 'AND') !== false || strpos($lastPart, 'OR') !== false) {
            array_push($sqlParts, trim(substr($lastPart, 0, strrpos($lastPart, ' '))));
        } else {
            array_push($sqlParts, $lastPart);
        }
        return implode(' ', $sqlParts);
    }
    return '';
}

2. 主逻辑：处理DataTable请求并返回数据 #

在fetch_data.php里处理前端的Ajax请求，结合DataTable的分页、排序参数和QueryBuilder的查询规则：

<?php
// 连接数据库（根据你的实际配置修改）
$pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 获取DataTable的参数
$start = $_POST['start'] ?? 0;
$length = $_POST['length'] ?? 10;
$orderColumn = $_POST['columns'][$_POST['order'][0]['column']]['data'] ?? 'name';
$orderDir = $_POST['order'][0]['dir'] ?? 'asc';

// 获取QueryBuilder的规则
$queryRules = $_POST['queryRules'] ?? null;
$params = [];
$whereClause = '';

if ($queryRules && !empty($queryRules['rules'])) {
    $whereClause = parseQueryRules($queryRules['rules'], $params);
    if (!empty($whereClause)) {
        $whereClause = "WHERE $whereClause";
    }
}

// 计算总记录数
$totalStmt = $pdo->query("SELECT COUNT(*) FROM your_table");
$totalRecords = $totalStmt->fetchColumn();

// 计算过滤后的记录数
$filteredStmt = $pdo->prepare("SELECT COUNT(*) FROM your_table $whereClause");
foreach ($params as $key => $value) {
    $filteredStmt->bindValue($key, $value);
}
$filteredStmt->execute();
$filteredRecords = $filteredStmt->fetchColumn();

// 查询数据
$dataStmt = $pdo->prepare("SELECT name, email, phone, gender, zipcode FROM your_table $whereClause ORDER BY $orderColumn $orderDir LIMIT $start, $length");
foreach ($params as $key => $value) {
    $dataStmt->bindValue($key, $value);
}
$dataStmt->execute();
$data = $dataStmt->fetchAll(PDO::FETCH_ASSOC);

// 返回DataTable需要的JSON格式
echo json_encode([
    'draw' => intval($_POST['draw']),
    'recordsTotal' => $totalRecords,
    'recordsFiltered' => $filteredRecords,
    'data' => $data
]);
?>

三、关键注意事项 #

• SQL注入防护：全程使用PDO预处理语句，绝对不要直接拼接用户输入到SQL里，上面的代码已经做了处理。
• 字段类型匹配：确保QueryBuilder里的type和数据库字段类型一致，比如Zipcode设为integer，后端处理时不用加通配符。
• 分组规则支持：代码已经支持AND/OR分组查询，和你参考的Demo逻辑一致。
• 版本兼容：如果你的DataTable或QueryBuilder版本较旧，可能需要调整API调用方式，比如QueryBuilder的getRules方法是否有变化。

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