Create paginated data tables using PHP and DataTables

In web development, data tables are components we often need to use. Paging is one of the common requirements when processing large amounts of data. If you want to implement a paging data table with a large amount of data, many people may choose to use a front-end framework such as Vue, React or Angular. However, paginated data tables can also be easily created using the back-end language PHP and the front-end plug-in DataTables, and are more flexible and customizable. Let's take a look at how to use PHP and DataTables to create paginated data tables.

First, we need a data source for rendering data. Since PHP is a very powerful server-side language, data can be obtained by connecting to a database or reading files. In this article, we will use PHP to connect to the MySQL database to obtain data, and output it in JSON format for DataTables to render data tables. For the convenience of demonstration, we use a fake data table. You can create a data table named "users" through the following code.

CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    age INT(3) NOT NULL,
    email VARCHAR(50),
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Next, we will write the data source interface in the PHP file. The code is as follows:

<?php
header('Content-Type: application/json');

$servername = "localhost"; // 数据库服务器名
$username = "username"; // 数据库用户名
$password = "password"; // 数据库密码
$dbname = "myDB"; // 数据库名

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
} 

// 查询数据总数
$sql = "SELECT COUNT(*) FROM users";
$result = $conn->query($sql);
$row = $result->fetch_row();
$total_count = $row[0];

// 分页参数
$limit = $_GET["length"];
$offset = $_GET["start"];

// 查询当前页数据
$sql = "SELECT id, name, age, email, registration_date FROM users LIMIT $offset, $limit";
$result = $conn->query($sql);

// 构造JSON数据
$data = [];
while($row = $result->fetch_assoc()) {
    $data[] = $row;
}
echo json_encode(array(
    "draw" => $_GET["draw"], 
    "recordsTotal" => $total_count, 
    "recordsFiltered" => $total_count, 
    "data" => $data
));

$conn->close();
?>

In the above code, we first connect to the database and query the total number of data. Next, get the paging parameters from the GET parameters, and query the current page data based on the paging parameters. Finally, the data is structured into JSON format and output to DataTables.

Next, we need to introduce the DataTables plug-in, configure and initialize it. DataTables and jQuery can be introduced through the following code.

<!-- 引入 jQuery -->
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>

<!-- 引入 DataTables -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css">
<script type="text/javascript" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>

Next, create a table DOM node in HTML and add the following code to initialize DataTables.

// 初始化 DataTables
$(document).ready(function() {
    $('#example').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "data.php"
    });
});

Here, we set the processing parameter of DataTables to true, which means turning on the loading prompt. The serverSide parameter is also set to true, which means the server-side mode is enabled. Finally, the ajax parameter is set to "data source interface URL", pointing to the PHP file we just wrote. In this way, when the user browses the data table, DataTables will obtain data from the server in a paging query and present it in the table.

At this point, a data table containing paging function has been successfully created. The above is the basic idea and code demonstration of using PHP and DataTables to create paging data tables. Readers can adjust and improve it according to their specific needs.

The above is the detailed content of Create paginated data tables using PHP and DataTables. For more information, please follow other related articles on the PHP Chinese website!