There are situations where you only need a specific number of rows returned. This is where the “LIMIT” clause in MySQL comes. It allows you to control the number of data rows retrieved, making your queries more manageable in PHP.
You will learn how to use LIMIT in MySQL queries with PHP, along with examples. Let’s get started.
What is the LIMIT Clause?
The LIMIT clause in MySQL specifies the maximum number of data rows to return. You can also define an offset to indicate where the results should start.
Here is the general syntax:
SELECT column_name(s) FROM table_name LIMIT offset, row_count;
offset: Indicates the starting point for the result set.row_count: Specifies the number of rows to retrieve.
In the following sections, you will see more examples in PHP and MySQL.
Examples of PHP MySQL Limit Data
Suppose you have a table named employees and you want to fetch the first five entries. Here is how you can achieve that using PHP and MySQL:
$sqlconne = new mysqli("localhost", "root", "", "company");
if ($sqlconne->connect_error) {
die("Connection failed: " . $sqlconne->connect_error);
}
$qrsql = "SELECT id, name, department FROM employees LIMIT 5";
$qryresult = $sqlconne->query($qrsql);
if ($qryresult->num_rows > 0) {
while ($row = $qryresult->fetch_assoc()) {
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Department: " . $row['department'] . "<br>";
}
} else {
echo "No records found.";
}
$sqlconne->close();
The LIMIT 5 statement ensures only five rows are retrieved, regardless of the total number of rows in the employees table.
Pagination Example
Here is another example for pagination. This helps us to see data in chunks rather than all at once.This is an example shows you how it works:
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$items_per_page = 10;
$offset = ($page - 1) * $items_per_page;
$qrsql = "SELECT id, name FROM employees LIMIT $offset, $items_per_page";
$qryresult= $sqlconne->query($qrsql );
if ($qryresult->num_rows > 0) {
while ($row = $qryresult->fetch_assoc()) {
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . "<br>";
}
} else {
echo "No records found.";
}
echo "<a href='?page=" . ($page - 1) . "'>Previous</a> | <a href='?page=" . ($page + 1) . "'>Next</a>";
$sqlconne->close();
$offsetdetermines the starting row for each page.$items_per_pagespecifies how many rows to display per page.
This way dynamically adjusts the query based on the current page.
Skip a Few Rows
Sometimes, you may need to skip a few rows and fetch the next set. For instance, to fetch rows 6 through 10:
$qrsql = "SELECT id, name FROM employees LIMIT 5, 5";
$qryresult= $sqlconne->query($qrsql );
if ($qryresult->num_rows > 0) {
while ($row = $qryresult->fetch_assoc()) {
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . "<br>";
}
} else {
echo "No records found.";
}
$sqlconne->close();
- The
LIMIT 5, 5retrieves five rows starting from the sixth row.
This method is useful when you know the exact range of data you want.
Retrieve Data in a Specific Order
You can use LIMIT with ORDER BY to retrieve data in a specific order. For instance, fetching the top three highest-paid employees:
$qrsql = "SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3";
$qryresult= $sqlconne->query($qrsql );
if ($qryresult->num_rows > 0) {
while ($row = $qryresult->fetch_assoc()) {
echo "Name: " . $row['name'] . " - Salary: $" . $row['salary'] . "<br>";
}
} else {
echo "No records found.";
}
$sqlconne->close();
The ORDER BY salary DESC ensures the results are sorted in descending order before applying the LIMIT clause.
Let’s summarize it.
Wrapping Up
The MySQL LIMIT data in PHP is a useful tool for managing data retrieval in MySQL. So if you are implementing pagination, fetching specific rows, or sorting results, combining LIMIT with PHP ensures your queries remain scalable.
Similar Reads
PHP 5 added support for classes and interfaces. This made object-oriented code possible with one feature being polymorphism. That means…
The PHP associative array is a structured collection wherein data elements are organized into lists or groups. Each element is…
PHP static property helps you manage shared data across instances, but can lead to hidden state changes. In this article,…
You have two or more arrays. You want one. That is the problem array_merge solves in PHP. This function lets…
PHP gives us the ability to work with MongoDB, highlighting the importance of CRUD (Create, Read, Update, Delete) operations for…
The substr() function in PHP returns part of a string. You give it a string, a starting point, and optionally…
Sometimes, when you run a script, you may encounter an error because the directory you're trying to access doesn't exist.…
The anonymous function in PHP lets you define a small task that doesn’t require a function name. Understand the Anonymous…
In web development, managing and manipulating data is one of the most common tasks. When it comes to storing complex…
Use strtoupper() function when you want to change all letters in a string to uppercase in PHP. It works with…