Use stmt_init to implement the pagination query function

In PHP, the mysqli extension is a powerful tool for interacting with a MySQL database. When we process large amounts of data, paging queries are a very common and effective technique to avoid loading all data at once. Today we will explore how to use the mysqli::stmt_init function to implement the pagination query function.

1. What is pagination query?

Pagination query refers to displaying data in batches according to a certain number of data, and displaying part of data on each page, rather than loading all data at once. Through paging, we can not only improve the data processing efficiency, but also optimize the front-end display effect and improve the user experience.

2. The role of mysqli::stmt_init

The mysqli::stmt_init function is used to initialize a new mysqli_stmt object, and then preprocessing statements can be executed through the object. Preprocessing statements can improve query efficiency while avoiding the risk of SQL injection.

3. How to implement pagination query using mysqli::stmt_init ?

Suppose we have a users table that contains a large amount of user information and we want to paginate the data of these users. First, we need to calculate the total number of data, and then calculate the offset of the query based on the page number and the number of displayed pieces per page. The following are the steps to implement paging query.

4. Sample code

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

// 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 entries displayed per page
$records_per_page = 10;

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

// Get the total number of data
$sql_count = "SELECT COUNT(*) AS total_records FROM users";
$result_count = $conn->query($sql_count);
$row = $result_count->fetch_assoc();
$total_records = $row['total_records'];

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

// use stmt_init Function initialization preprocessing statement
$stmt = $conn->stmt_init();
if ($stmt->prepare("SELECT * FROM users LIMIT ?, ?")) {
    // Bind parameters
    $stmt->bind_param("ii", $start_from, $records_per_page);
    
    // Execute a query
    $stmt->execute();
    
    // Get results
    $result = $stmt->get_result();
    
    // Output data
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row['id'] . " - Name: " . $row['name'] . "<br>";
    }
    
    // Release the result set
    $stmt->free_result();
    
    // Close the preprocessing statement
    $stmt->close();
}

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

<!-- Show paging links -->
<div>
    <p>Page: 
        <?php
        // Pagination Navigation
        for ($i = 1; $i <= $total_pages; $i++) {
            echo "<a href='m66.net?page=$i'>$i</a> ";
        }
        ?>
    </p>
</div>

5. Explain the code

• Database connection: Use new mysqli() to create a connection to the database. If the connection fails, the code displays an error message and terminates execution.

• Calculate the paging offset: The number of records displayed on each page is records_per_page , and calculate which record to start query based on the current page number $page . $start_from = ($page - 1) * $records_per_page calculates the offset.

• Get the total number of records: We execute a query to get the total number of records in the users table, thereby calculating the total number of pages.

• Execute pagination query: Use stmt_init() to initialize a preprocessing statement, write a query through prepare() method, bind_param() method to bind the query parameters, and then execute the query and get the result.

• Display paging navigation: Through a loop, generate paging links. Clicking on each page number will jump to the corresponding page and display it in m66.net?page=$i format.

6. Summary

Using the mysqli::stmt_init function for pagination query can not only improve the efficiency of data query, but also effectively prevent SQL injection. In actual development, paging queries are often used in scenarios where large amounts of data are needed. Through reasonable paging design, system performance and user experience can be significantly improved.

Hope this article can help you understand how to use the mysqli::stmt_init function to implement the pagination query function. If you have any questions or need further discussion, please leave a message to discuss!