使用PHP PDO实现DataTables服务器端处理：从数据检索到高效分页

DDD

发布时间：2025-10-12 11:39:19

171人浏览过

来源于php中文网

原创

使用PHP PDO实现DataTables服务器端处理：从数据检索到高效分页

本教程将指导您如何利用php pdo和datatables实现高效的服务器端数据处理。我们将详细讲解html、javascript和php后端配置，解决常见的查询构建、数据过滤、排序、分页及数据格式化问题，并提供优化后的代码示例，确保您的datatables应用具备良好的性能和数据安全性。

DataTables服务器端处理概述

DataTables是一个功能强大的JavaScript库，用于增强HTML表格的功能。当处理大量数据时，客户端加载所有数据会导致性能问题。服务器端处理（Server-Side Processing）允许DataTables将数据请求（如排序、搜索、分页）发送到服务器，由服务器处理后返回所需的数据子集，从而显著提升大型数据集的性能。

PHP的PDO（PHP Data Objects）扩展提供了一个轻量级、一致的接口来访问数据库，支持预处理语句，有助于防止SQL注入攻击，是构建安全高效后端服务的理想选择。

本教程将结合DataTables的服务器端模式和PHP PDO，构建一个健壮的数据表格解决方案。

前端（HTML & JavaScript）配置

首先，我们需要一个基本的HTML表格结构和DataTables的JavaScript初始化代码。

立即学习“PHP免费学习笔记（深入）”；

HTML 结构

您的HTML文件应包含一个带有唯一ID的

标签，以及定义列头的部分。部分通常由DataTables在服务器端数据加载后动态填充。

CRN	Course ID	Course Name	Professor	Section	Building	Room	Start Time	End Time	Day	Seats

JavaScript 初始化

在JavaScript中，通过设置serverSide: true来启用服务器端处理，并通过ajax选项指定PHP后端脚本的URL和请求类型。

$(document).ready(function() {
    var masterScheduleTable = $('#masterscheduleTEST').DataTable({
        "processing": true, // 显示“处理中”提示
        "serverSide": true, // 启用服务器端处理
        "order": [],        // 初始不排序，或指定默认排序
        "ajax": {
            url: "http://ec2-13-59-215-177.us-east-2.compute.amazonaws.com/panel/scripts/fetch.php", // 您的PHP后端脚本URL
            type: "POST"    // 使用POST方法发送请求
        },
        // 可选：定义列，如果您的数据字段名与HTML列顺序不完全匹配，或者需要自定义渲染
        "columns": [
            { "data": "CRN_val" }, // 这里的"CRN_val"应与PHP返回的JSON数据中的键名匹配
            { "data": "CourseID_val" },
            { "data": "CourseTitle_val" },
            { "data": "LastName_val" },
            { "data": "Section_val" },
            { "data": "BuildingName_val" },
            { "data": "RoomNumber_val" },
            { "data": "StartTime_val" },
            { "data": "EndTime_val" },
            { "data": "Weekday_val" },
            { "data": "SeatsAvailable_val" }
        ]
    });
});

注意事项：

columns选项非常重要，它告诉DataTables如何将服务器返回的数据映射到表格的每一列。data属性的值应与PHP脚本返回的JSON对象中每个数据行（数组或对象）的键名一致。为了避免SQL中的table.column格式导致的问题，我们将在PHP中为列设置别名。
确保ajax.url指向正确的后端脚本地址。

后端（PHP PDO）实现 fetch.php

PHP后端脚本负责接收DataTables发送的请求参数，执行相应的数据库查询（包括搜索、排序和分页），并将结果以DataTables期望的JSON格式返回。

拍我AI

AI视频生成平台PixVerse的国内版本

下载

1. 数据库连接

首先，建立一个安全的PDO数据库连接。建议将连接信息放在单独的配置文件中。

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        // 默认获取关联数组，键名与列名（或别名）一致
        $connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        die("数据库连接失败: " . $e->getMessage());
    }
}
?>

2. 构建基础查询

在fetch.php中，我们将逐步构建SQL查询。为了便于在PHP中访问，强烈建议为所有选定的列设置别名（AS）。

3. 处理全局搜索/过滤
DataTables会将全局搜索值通过$_POST["search"]["value"]发送过来。我们需要将其应用于所有可搜索的列。为了防止SQL注入，务必使用预处理语句。
if (isset($_POST["search"]["value"]) && $_POST["search"]["value"] != '') {
    $searchValue = '%' . $_POST["search"]["value"] . '%';
    $searchConditions = [];

    // 为每个可搜索的列添加LIKE条件
    $searchConditions[] = "class.CRN LIKE :search_val_0";
    $searchParams[':search_val_0'] = $searchValue;

    $searchConditions[] = "course.courseID LIKE :search_val_1";
    $searchParams[':search_val_1'] = $searchValue;

    $searchConditions[] = "course.courseTitle LIKE :search_val_2";
    $searchParams[':search_val_2'] = $searchValue;

    $searchConditions[] = "user.lastName LIKE :search_val_3";
    $searchParams[':search_val_3'] = $searchValue;

    $searchConditions[] = "class.section LIKE :search_val_4";
    $searchParams[':search_val_4'] = $searchValue;

    $searchConditions[] = "building.buildingName LIKE :search_val_5";
    $searchParams[':search_val_5'] = $searchValue;

    $searchConditions[] = "room.roomNumber LIKE :search_val_6";
    $searchParams[':search_val_6'] = $searchValue;

    $searchConditions[] = "period.startTime LIKE :search_val_7";
    $searchParams[':search_val_7'] = $searchValue;

    $searchConditions[] = "period.endTime LIKE :search_val_8";
    $searchParams[':search_val_8'] = $searchValue;

    $searchConditions[] = "day.weekday LIKE :search_val_9";
    $searchParams[':search_val_9'] = $searchValue;

    $searchConditions[] = "class.seatsAvailable LIKE :search_val_10";
    $searchParams[':search_val_10'] = $searchValue;

    $whereClause[] = "(" . implode(" OR ", $searchConditions) . ")";
}

if (!empty($whereClause)) {
    $query .= " WHERE " . implode(" AND ", $whereClause);
}

// 保存带WHERE条件但没有ORDER BY和LIMIT的查询，用于计算过滤后的总记录数
$queryFiltered = $query;
重要更正： 原始代码中OR course.courseTitle "%'.$_POST["search"]["value"].'%"缺少LIKE关键字，已在此处修复并使用预处理语句。
4. 处理排序
DataTables通过$_POST["order"]数组发送排序信息，包含列索引和排序方向。我们需要将列索引映射到实际的数据库列名。
$columnMap = [
    0 => 'class.CRN',
    1 => 'course.courseID',
    2 => 'course.courseTitle',
    3 => 'user.lastName',
    4 => 'class.section',
    5 => 'building.buildingName',
    6 => 'room.roomNumber',
    7 => 'period.startTime',
    8 => 'period.endTime',
    9 => 'day.weekday',
    10 => 'class.seatsAvailable'
];

if (isset($_POST["order"])) {
    $orderBy = [];
    foreach ($_POST['order'] as $order) {
        $columnIdx = intval($order['column']);
        $dir = strtoupper($order['dir']); // ASC或DESC
        if (isset($columnMap[$columnIdx])) {
            $orderBy[] = $columnMap[$columnIdx] . ' ' . $dir;
        }
    }
    if (!empty($orderBy)) {
        $query .= ' ORDER BY ' . implode(', ', $orderBy);
    }
} else {
    // 默认排序
    $query .= ' ORDER BY course.courseTitle ASC, class.section ASC';
}
5. 处理分页
DataTables通过$_POST["start"]（起始记录索引）和$_POST["length"]（每页记录数）发送分页信息。
$recordsTotal = 0; // 用于存储未过滤的总记录数
$recordsFiltered = 0; // 用于存储过滤后的总记录数

// 1. 获取未过滤的总记录数 (recordsTotal)
$stmtTotal = $connection->prepare("SELECT COUNT(*) FROM (" . $baseQuery . ") AS total_count");
$stmtTotal->execute();
$recordsTotal = $stmtTotal->fetchColumn();

// 2. 获取过滤后的总记录数 (recordsFiltered)
// 如果有搜索条件，则计算过滤后的总数
if (!empty($whereClause)) {
    $stmtFiltered = $connection->prepare("SELECT COUNT(*) FROM (" . $queryFiltered . ") AS filtered_count");
    $stmtFiltered->execute($searchParams); // 绑定搜索参数
    $recordsFiltered = $stmtFiltered->fetchColumn();
} else {
    $recordsFiltered = $recordsTotal; // 没有过滤时，过滤数等于总数
}


if ($_POST["length"] != -1) {
    $query .= ' LIMIT :start_limit, :length_limit';
    $searchParams[':start_limit'] = intval($_POST['start']);
    $searchParams[':length_limit'] = intval($_POST['length']);
}
关键修正：

recordsTotal应在没有任何WHERE、ORDER BY或LIMIT子句的情况下计算。
recordsFiltered应在应用了WHERE子句（搜索过滤）但没有LIMIT子句的情况下计算。
$statement->rowCount()在SELECT查询后通常返回受影响的行数，对于LIMIT查询，它返回的是实际返回的行数，而不是总数。因此，需要单独的COUNT(*)查询来获取recordsTotal和recordsFiltered。
LIMIT子句的参数也应使用预处理。

6. 执行最终查询并获取数据
现在，执行包含所有条件（搜索、排序、分页）的最终查询。
$statement = $connection->prepare($query);

// 绑定所有参数
foreach ($searchParams as $paramName => $paramValue) {
    if (strpos($paramName, ':search_val_') === 0) {
        $statement->bindValue($paramName, $paramValue, PDO::PARAM_STR);
    } elseif ($paramName == ':start_limit' || $paramName == ':length_limit') {
        $statement->bindValue($paramName, $paramValue, PDO::PARAM_INT);
    }
}

$statement->execute();
$result = $statement->fetchAll();

$data = array();
foreach ($result as $row) {
    // 使用别名访问数据
    $sub_array = array(
        "CRN_val" => $row["CRN_val"],
        "CourseID_val" => $row["CourseID_val"],
        "CourseTitle_val" => $row["CourseTitle_val"],
        "LastName_val" => $row["LastName_val"],
        "Section_val" => $row["Section_val"],
        "BuildingName_val" => $row["BuildingName_val"],
        "RoomNumber_val" => $row["RoomNumber_val"],
        "StartTime_val" => $row["StartTime_val"],
        "EndTime_val" => $row["EndTime_val"],
        "Weekday_val" => $row["Weekday_val"],
        "SeatsAvailable_val" => $row["SeatsAvailable_val"]