Milind Daraniya

When working with large datasets in PHP and MySQL, it's often necessary to split the data into multiple pages for better performance and user experience. In this tutorial, we'll guide you through the process of selecting data from a MySQL database and splitting it into pages using PHP. Let's get started!

Step 1: Connect to the Database 
First, establish a connection to your MySQL database using PHP's mysqli extension. Here's an example of connecting to the database:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

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

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

Step 2: Retrieve Data from the Database 
To retrieve the data from the database, you'll need to construct a SQL query and execute it using PHP. Here's an example of selecting data from a table named users:

<?php
// Construct SQL query
$sql = "SELECT * FROM users";

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

// Check if there are any results
if ($result->num_rows > 0) {
    // Process the data
    while ($row = $result->fetch_assoc()) {
        // Perform actions with the data
        // Example: Display the user's name
        echo "User: " . $row["name"] . "<br>";
    }
} else {
    echo "No results found.";
}

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

Step 3: Implement Pagination 
To split the retrieved data into pages, you'll need to implement pagination. Here's an example of how you can achieve pagination using PHP:

<?php
// Determine the current page
$current_page = isset($_GET['page']) ? $_GET['page'] : 1;

// Set the number of results per page
$results_per_page = 10;

// Calculate the offset for the query
$offset = ($current_page - 1) * $results_per_page;

// Construct the SQL query with pagination
$sql = "SELECT * FROM users LIMIT $offset, $results_per_page";

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

// Process and display the data
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "User: " . $row["name"] . "<br>";
    }
} else {
    echo "No results found.";
}

// Generate pagination links
$sql = "SELECT COUNT(*) AS total FROM users";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$total_pages = ceil($row["total"] / $results_per_page);

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

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

Feel free to customize the code according to your specific database structure and requirements.

Thanks