To limit the number of rows retrieved from a MySQL database using MySQLi in PHP, you can use the LIMIT clause in your SQL query. The LIMIT clause allows you to specify the maximum number of records you want to retrieve.

Here's how you can do it in both procedural and object-oriented approaches.

1. Limit Data Using MySQLi (Procedural Approach)

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// SQL query to limit the number of records (e.g., 5 records)$sql = "SELECT id, name, email FROM users LIMIT 5"; // Change 5 to your desired limit// Execute the query$result = mysqli_query($conn, $sql);// Check if the query was successful and if there are rows to fetchif (mysqli_num_rows($result) > 0) {    // Fetch and display the records    while ($row = mysqli_fetch_assoc($result)) {        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";    }} else {    echo "No records found.";}// Close the connectionmysqli_close($conn);?>

2. Limit Data Using MySQLi (Object-Oriented Approach)

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// SQL query to limit the number of records (e.g., 5 records)$sql = "SELECT id, name, email FROM users LIMIT 5"; // Change 5 to your desired limit// Execute the query$result = $conn->query($sql);// Check if there are rows to fetchif ($result->num_rows > 0) {    // Fetch and display the records    while ($row = $result->fetch_assoc()) {        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";    }} else {    echo "No records found.";}// Close the connection$conn->close();?>

3. Limit Data with Offset (Pagination)

If you want to paginate the data (e.g., display a certain number of records per page), you can combine the LIMIT clause with the OFFSET to specify where to start retrieving the data.

Here's an example of paginated data (e.g., 5 records per page):

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// Get the current page number (default is 1 if not set)$page = isset($_GET['page']) ? $_GET['page'] : 1;$records_per_page = 5; // Number of records per page$offset = ($page - 1) * $records_per_page; // Calculate the offset// SQL query with LIMIT and OFFSET$sql = "SELECT id, name, email FROM users LIMIT $records_per_page OFFSET $offset";// Execute the query$result = mysqli_query($conn, $sql);// Check if the query was successful and if there are rows to fetchif (mysqli_num_rows($result) > 0) {    // Fetch and display the records    while ($row = mysqli_fetch_assoc($result)) {        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";    }} else {    echo "No records found.";}// Display pagination linksecho "<br><a href='?page=" . ($page - 1) . "'>Previous</a> | <a href='?page=" . ($page + 1) . "'>Next</a>";// Close the connectionmysqli_close($conn);?>

Explanation:

1. Basic Limiting:

   • LIMIT 5: Fetches the first 5 records from the users table. Change the number 5 to any number to control how many records are returned.

2. Pagination:

   • The LIMIT clause is used to fetch records in batches (pages).

   • OFFSET specifies where the query should start fetching records from. For example, if you are on page 2, the OFFSET will be 5 ((page - 1) * records_per_page).

3. Handling Pages:

   • We get the page number from the URL using $_GET['page'] (e.g., ?page=1).

   • Based on the page number, we calculate the correct OFFSET.

4. Displaying Results:

   • The records are displayed in a loop (mysqli_fetch_assoc()), and pagination links are shown below the results.

Using Prepared Statements with LIMIT

For better security (to prevent SQL injection), you can use prepared statements for limiting and paginating the results as well.

Example with Prepared Statement (Object-Oriented)

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// Get the current page number (default is 1 if not set)$page = isset($_GET['page']) ? $_GET['page'] : 1;$records_per_page = 5; // Number of records per page$offset = ($page - 1) * $records_per_page; // Calculate the offset// Prepare the SQL query with LIMIT and OFFSET$stmt = $conn->prepare("SELECT id, name, email FROM users LIMIT ? OFFSET ?");$stmt->bind_param("ii", $records_per_page, $offset); // Bind parameters// Execute the query$stmt->execute();$result = $stmt->get_result();// Check if there are rows to fetchif ($result->num_rows > 0) {    // Fetch and display the records    while ($row = $result->fetch_assoc()) {        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";    }} else {    echo "No records found.";}// Display pagination linksecho "<br><a href='?page=" . ($page - 1) . "'>Previous</a> | <a href='?page=" . ($page + 1) . "'>Next</a>";// Close the statement and connection$stmt->close();$conn->close();?>

Summary:

• Use LIMIT to restrict the number of records returned.

• Use LIMIT with OFFSET for pagination, where OFFSET determines where the results start.

• Always consider using prepared statements for security, especially when accepting user input like page numbers.

Let me know if you need more details or assistance!