How to use the mysqli_result function to combine LIMIT to implement the paging function?

In website development, paging function is a very common requirement. Pagination can effectively reduce the time for page loading and make the user experience smoother. This article will introduce how to use the mysqli_result function in PHP to implement the paging function with LIMIT .

1. Basic principles of pagination

The basic idea of ​​paging is that returning all data at once may cause performance problems, especially when the amount of data is large. Therefore, we implement paging by limiting the number of records in the query, thus avoiding excessive data loading.

LIMIT is a keyword in SQL that limits the number of returned records. Combined with LIMIT , you can specify the starting position of the query result and the number of records returned to achieve the pagination effect.

2. Database connection and query

First, we need to establish a connection to the MySQL database and write SQL query statements. To implement paging, we use LIMIT to limit the number of records displayed per page, and use OFFSET to set which record to start returning.

Sample code:

 <?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "your_database";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

// Number of records displayed per page
$records_per_page = 10;

// Get the current page number
if (isset($_GET['page']) && is_numeric($_GET['page'])) {
    $current_page = $_GET['page'];
} else {
    $current_page = 1;
}

// Calculate offset
$offset = ($current_page - 1) * $records_per_page;

// writeSQLQuery statement
$sql = "SELECT * FROM your_table LIMIT $offset, $records_per_page";

// Execute a query
$result = $conn->query($sql);

// Get query results
if ($result->num_rows > 0) {
    // Output data
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "No data";
}

// Calculate the total number of records
$total_sql = "SELECT COUNT(*) AS total FROM your_table";
$total_result = $conn->query($total_sql);
$total_row = $total_result->fetch_assoc();
$total_records = $total_row['total'];

// Calculate the total number of pages
$total_pages = ceil($total_records / $records_per_page);

// Output paging link
for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='m66.net?page=$i'>$i</a> ";
}

// Close the connection
$conn->close();
?>

3. Code parsing

• Database connection : First, we connect to the MySQL database through the mysqli extension. After the connection is successful, check for errors.

• Paging logic : Determine the current page number by obtaining page parameters. If no page number is provided, the default is the first page. We then determine the starting position of the query result by calculating the offset.

• SQL query : Use LIMIT and OFFSET to limit the number of records per query. The LIMIT $offset, $records_per_page statement implements the function of paging.

• Total records and total page count : Calculate the total number of records in the table through another query, and calculate the total number of pages using the ceil() function to ensure that the last page can be displayed even if the number of records is not an integer multiple of records_per_page .

• Pagination link : Generate paging links based on the total number of pages. Each link points to a different page number, and users can click to jump to the corresponding page.

4. Summary

Using the mysqli_result function combined with LIMIT to implement the paging function is a very common database query technique. By rationally designing the paging logic, we can improve the loading speed of the website and improve the user experience. In actual development, the paging function is often used in combination with search, filtering and other functions to meet the needs of different scenarios.