PHP MySQL Limit Records

In this tutorial, we will learn how to limit records in a MySQL database using PHP. When working with a large database, you may need to retrieve only a limited number of records instead of fetching all the data at once.

In PHP, you can easily retrieve a limited number of records from a MySQL table using the LIMIT clause. So, let’s understand what the LIMIT clause is and how to use it with PHP and MySQL through practical examples.

What is LIMIT Clause in MySQL?

The LIMIT clause in MySQL restricts the number of records returned by a SELECT query. This clause helps you fetch only a specific number of records from a database table. Therefore, the LIMIT clause is very useful on large database tables because retrieving a large number of records at once can negatively impact performance.

Syntax of LIMIT Clause in MySQL

The basic syntax for using the LIMIT clause with a SELECT query in MySQL is:

SELECT column_name(s)
FROM table_name
LIMIT number;

This syntax returns only the first N rows from the result set. In this syntax:

• column_name(s) → Specifies columns you want to retrieve.
• table_name → Represents the name of the table.
• number → Specifies the maximum number of rows to return.

Example 1:

SELECT * FROM students LIMIT 3;

In this example, the clause LIMIT 3 returns the first three records from the database table.

Syntax with Offset

MySQL provides a way to retrieve records from a specific starting position using the OFFSET clause along with LIMIT. It allows you to skip a certain number of rows before returning the result set. The basic syntax is:

SELECT column_name(s)
FROM table_name
LIMIT number, OFFSET offset;

In this syntax, the offset clause specifies the starting position in a 0-based index.
[blocksy-content-block id=”12371″]

What does 0-based index mean?

• offset = 0 → starts from the first row.
• offset = 1 → starts from the second row.
• offset = 2 → starts from the third row.

Example 2:

SELECT * FROM students LIMIT 3, OFFSET 2;

In this example, the OFFSET 2 skips the first two records, and the LIMIT 3 returns the next three records starting from the third row.

Database Table Example

Suppose we have a table called students.

Limit Records Using MySQLi Object-Oriented Method

Inside the htdocs folder, create a new file with the name limit_record.php and paste this code into it.

Example 3: Fetch Limited Records

<?php
// Include the database connection file.
require_once "connect.php";

// SQL query to fetch limited records from the students table.
$sql = "SELECT * FROM students LIMIT 3";

$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)) {
    echo $row['id'] . " - " . $row['name'] . " - " . $row['email'] . " - " . $row['age'] . "<br>";
}
// Closing the connection.
$conn->close();
?>

Now save this file using Ctrl + S.
[blocksy-content-block id=”12121″]
Now open the browser and go to http://localhost/limit_record.php. If the records exist in the table, you will see the following output in your browser.

Output:

1 - John - john@gmail.com - 18
2 - Smith - smith@gmail.com - 20
3 - Saanvi - saanvi@gmail.com - 17

Example 4: Using LIMIT with OFFSET

<?php
// Include the database connection file.
require_once "connect.php";

// SQL query to fetch limited records from the students table.
$sql = "SELECT * FROM students LIMIT 2, 2";

$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)) {
    echo $row['id'] . " - " . $row['name'] . " - " . $row['email'] . " - " . $row['age'] . "<br>";
}
$conn->close();
?>

Output:

3 - Saanvi - saanvi@gmail.com - 17
4 - Mark - mark@gmail.com - 19