PHP MySQL LIMIT Data: How to Optimize PHP Queries?

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();
  • $offset determines the starting row for each page.
  • $items_per_page specifies 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, 5 retrieves 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

Polymorphism in PHP: How It Works with Examples

PHP 5 added support for classes and interfaces. This made object-oriented code possible with one feature being polymorphism. That means…

PHP Associative Array: Optimal Data Organization

The PHP associative array is a structured collection wherein data elements are organized into lists or groups. Each element is…

PHP Static Property: How It Works & Examples

PHP static property helps you manage shared data across instances, but can lead to hidden state changes. In this article,…

PHP array_merge: Combine Multiple Arrays into One

You have two or more arrays. You want one. That is the problem array_merge solves in PHP. This function lets…

MongoDB CRUD with PHP: Create, Read, Update, and Delete

PHP gives us the ability to work with MongoDB, highlighting the importance of CRUD (Create, Read, Update, Delete) operations for…

PHP substr Function: How to Extract and Manipulate Strings

The substr() function in PHP returns part of a string. You give it a string, a starting point, and optionally…

PHP is_dir Function: Check if a Directory Exists

Sometimes, when you run a script, you may encounter an error because the directory you're trying to access doesn't exist.…

PHP Anonymous Function: How It Works with Examples

The anonymous function in PHP lets you define a small task that doesn’t require a function name. Understand the Anonymous…

PHP Multidimensional Arrays: Data Manipulation

In web development, managing and manipulating data is one of the most common tasks. When it comes to storing complex…

PHP strtoupper Function: Convert Strings to Uppercase

Use strtoupper() function when you want to change all letters in a string to uppercase in PHP. It works with…

Previous Article

How to Update MySQL Data with PHP?

Next Article

PHP MySQL CRUD: Understanding Database Operations

Write a Comment

Leave a Comment

Your email address will not be published. Required fields are marked *


Subscribe to Get Updates

Get the latest updates on Coding, Database, and Algorithms straight to your inbox.
No spam. Unsubscribe anytime.