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
When you deal with text in PHP, sometimes you need to break it into parts. This is common when users…
The PHP break statement helps in controlling the flow of loop work. It gives you a way to stop a…
If you start working with PHP, it won't take you that long to figure out that strings are everywhere. Besides…
The array_shift function in PHP takes the first element from an array and returns it. It also moves all other…
PHP static property helps you manage shared data across instances, but can lead to hidden state changes. In this article,…
PHP arrays are lists or maps that contain multiple values grouped by array keys. They may include integers, strings, booleans,…
PHP supports OOP with access modifiers to prevent unintended changes and improve security. In this article, we will cover the…
PHP added the array_map() function to help you apply one function to each item in an array. Understand the array_map…
The PHP range function helps you make sequences. Without it, you would write loops every time you need numbers or…
Connecting PHP to MySQL is not just a technical step—it is the basic part of how data-driven websites work. From…