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