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

PHP explode Function: How it Works with Examples

When you deal with text in PHP, sometimes you need to break it into parts. This is common when users…

PHP break Statement: How to Exit a Loop

The PHP break statement helps in controlling the flow of loop work. It gives you a way to stop a…

PHP Strings: Types, Variables & Syntax Tips

If you start working with PHP, it won't take you that long to figure out that strings are everywhere. Besides…

PHP array_shift: Remove the First Array Element with Examples

The array_shift function in PHP takes the first element from an array and returns it. It also moves all other…

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: Accessing and Managing Elements

PHP arrays are lists or maps that contain multiple values grouped by array keys. They may include integers, strings, booleans,…

PHP Access Modifiers: How Public, Private & Protected Work

PHP supports OOP with access modifiers to prevent unintended changes and improve security. In this article, we will cover the…

PHP array_map Function: How to Transform Arrays with Examples

PHP added the array_map() function to help you apply one function to each item in an array. Understand the array_map…

PHP range Function: Create Arrays with Sequence Values

The PHP range function helps you make sequences. Without it, you would write loops every time you need numbers or…

How to Connect a MySQL Database to PHP?

Connecting PHP to MySQL is not just a technical step—it is the basic part of how data-driven websites work. From…

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.