Use array_chunk to process SQL query results in pagination query

M66 2025-04-28

When conducting database pagination query, we usually encounter scenarios where we need to display a large amount of data. If we load all the data to the page at once, it may cause the page to load slowly or even timeout. To solve this problem, we can use paging technology to load data in batches, and PHP's array_chunk function is a very effective tool that can help us effectively process SQL query results in paging queries.

This article will introduce how to use PHP's array_chunk function to optimize the implementation process of paging query.

1. Introduction to array_chunk function

array_chunk is a built-in function in PHP that is used to split a large array into multiple small arrays. The basic usage is as follows:

 array_chunk(array $array, int $length, bool $preserve_keys = false): array

$array : The array to be divided.
$length : The length of each small array.
$preserve_keys : Whether to preserve the key name of the original array (default is false ).

Through this function, we can divide a large data set into arrays of multiple pages, returning one page of data at a time.

2. Requirements for pagination query

Suppose we have a user table ( users ) in the database that contains a large amount of user data. We want to display this data on the page. The usual practice is to query all records from the database at once and process the paging logic on the backend. However, doing so can cause performance problems, especially when the data volume is very large.

Therefore, we can use the array_chunk function to divide the query results into multiple pages to optimize performance. We divide the SQL query results into multiple arrays through array_chunk , and then display the data by page.

3. Use array_chunk to implement the pagination function

Here is a simple example showing how to use the array_chunk function to paginate SQL query results:

 <?php

// Connect to the database
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check the connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Set the number of records displayed per page
$items_per_page = 10;

// Get the current page number，Default is 1
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $items_per_page;

// Query data
$query = "SELECT * FROM users LIMIT $offset, $items_per_page";
$result = $mysqli->query($query);

// If the query is successful
if ($result->num_rows > 0) {
    // Save query results into an array
    $data = [];
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }

    // use array_chunk Split the query results by the amount of data per page
    $chunks = array_chunk($data, $items_per_page);

    // Get the data display of the current page
    $current_page_data = $chunks[$page - 1];

    // Display the data of the current page
    foreach ($current_page_data as $user) {
        echo "username: " . $user['username'] . "<br>";
        echo "Mail: " . $user['email'] . "<br>";
        echo "<hr>";
    }
} else {
    echo "No user data found。";
}

// Pagination link
$total_pages = ceil(count($data) / $items_per_page);
echo "<div>common " . $total_pages . " Page</div>";

for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='?page=$i'>1. $i Page</a> ";
}

$mysqli->close();
?>

4. Optimize data display

By using array_chunk we are able to cut the query results by page and display only the data of the current page. This is very useful for processing large amounts of data and can effectively reduce the burden on each page load.

In addition, we can further optimize the paging function. For example, data can be loaded dynamically via AJAX instead of refreshing the page every time. In this way, the user experience will be smoother.

5. URL parameter optimization

In pagination, the page parameter is usually passed in the URL to indicate the current page number. If our paging link uses the URL structure as follows:

 http://example.com/users?page=2

We can replace the domain part of the URL with m66.net to ensure that the format we require is:

array_chunk