使用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的<table>标签，以及定义列头的<thead>部分。<tbody>部分通常由DataTables在服务器端数据加载后动态填充。

<table id="masterscheduleTEST" class="display" style="width:100%">
    <thead>
        <tr>
            <th>CRN</th>
            <th>Course ID</th>
            <th>Course Name</th>
            <th>Professor</th>
            <th>Section</th>
            <th>Building</th>
            <th>Room</th>
            <th>Start Time</th>
            <th>End Time</th>
            <th>Day</th>
            <th>Seats</th>
        </tr>
    </thead>
    <tbody>
        <!-- DataTables will populate this tbody -->
    </tbody>
</table>

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格式返回。

Imagine By Magic Studio

AI图片生成器，用文字制作图片

下载

1. 数据库连接

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

<?php
// database.php (示例，实际应用中应更安全地管理凭据)
// 确保只连接一次
if (!isset($connection)) {
    $servername = "localhost";
    $username = "phpmyadmin";
    $password = "*****"; // 请替换为您的数据库密码
    $dbname = "System Designs";

    try {
        $connection = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8", $username, $password);
        $connection->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）。

<?php
include("database.php"); // 包含数据库连接文件

$output = array();

// 基础SELECT语句，为所有列设置别名
$query = "
    SELECT
        class.CRN AS CRN_val,
        course.courseID AS CourseID_val,
        course.courseTitle AS CourseTitle_val,
        user.lastName AS LastName_val,
        class.section AS Section_val,
        building.buildingName AS BuildingName_val,
        room.roomNumber AS RoomNumber_val,
        period.startTime AS StartTime_val,
        period.endTime AS EndTime_val,
        day.weekday AS Weekday_val,
        class.seatsAvailable AS SeatsAvailable_val
    FROM
        course
    INNER JOIN class ON course.courseID = class.courseID
    INNER JOIN faculty ON class.facultyID = faculty.facultyID
    INNER JOIN user ON faculty.userID = user.userID
    INNER JOIN room ON class.roomNo = room.roomID
    INNER JOIN building ON room.buildingID = building.buildingID
    INNER JOIN timeSlot ON class.timeSlotID = timeSlot.timeSlotID
    INNER JOIN period ON timeSlot.period = period.periodID
    INNER JOIN day ON timeSlot.days = day.dayID
";

$baseQuery = $query; // 保存不带WHERE、ORDER BY和LIMIT的基础查询
$whereClause = []; // 用于存储WHERE条件
$searchParams = []; // 用于存储预处理语句的参数

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"]