← Back to PHP Course | Chapter 11: Database | Lesson 21 of 21

PHP MySQL Limit Data

A table with a million rows returning all of them at once would overwhelm both the database and whatever is displaying the results -- LIMIT caps a query to return only a specific number of rows, the foundation of pagination (showing 20 results per page) and "top N" style queries (the 5 most recent orders).

In this page:

  1. Basic LIMIT Usage
  2. Pagination with LIMIT and OFFSET
  3. Building a Full Pagination Script
  4. Using LIMIT for Top-N Queries
  5. Performance Considerations with LIMIT
Syntax
php 
SELECT * FROM table_name LIMIT count;
SELECT * FROM table_name LIMIT count OFFSET skip;   // pagination: OFFSET = (page - 1) * count

Basic LIMIT Usage

Adding LIMIT n to the end of a SELECT statement caps the number of rows returned to at most n, regardless of how many rows actually matched the query -- SELECT * FROM products LIMIT 5 returns at most five products, even if the table holds thousands.

Note: Combine LIMIT with ORDER BY whenever the specific rows returned matter, since LIMIT alone offers no guarantee about which rows you get without an explicit sort.
Warning: LIMIT without ORDER BY can return a different set of rows on repeated identical queries, since the database is not obligated to return them in any consistent order.

Example: Basic LIMIT Usage

php 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE products (name TEXT)");
$db->exec("INSERT INTO products VALUES ('A'), ('B'), ('C'), ('D')");
// Declare `$result`, set to `$db->query("SELECT * FROM products LIMIT 2")`
$result = $db->query("SELECT * FROM products LIMIT 2");
// Keep looping while `$row = $result->fetchArray(SQLITE3_ASSOC)` holds
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // Print `$row['name'] . "\n"` to the output
    echo $row['name'] . "\n";
}
?>

Pagination with LIMIT and OFFSET

LIMIT n OFFSET m skips the first m matching rows, then returns up to n rows after that -- LIMIT 10 OFFSET 20 skips the first 20 rows and returns the next 10, which is exactly how page-based pagination is built: page 3 of a 10-per-page list uses OFFSET 20.

Note: Calculate the OFFSET as (pageNumber - 1) * pageSize, so page 1 correctly uses an offset of 0, not pageSize.
Warning: A very large OFFSET value on a large table can become noticeably slow, since the database still has to scan past every skipped row even though it discards them.

Example: Pagination with LIMIT and OFFSET

php 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE items (name TEXT)");
foreach (range(1, 30) as $i) { $db->exec("INSERT INTO items VALUES ('Item $i')"); }
// Declare `$result`, set to `$db->query("SELECT * FROM items LIMIT 10 OFFSET 20")`
$result = $db->query("SELECT * FROM items LIMIT 10 OFFSET 20");
// Keep looping while `$row = $result->fetchArray(SQLITE3_ASSOC)` holds
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // Print `$row['name'] . "\n"` to the output
    echo $row['name'] . "\n";
}
?>

Building a Full Pagination Script

A complete pagination setup needs two pieces of information beyond the page data itself: the total row count (via a separate COUNT query) to calculate the total number of pages, and the current page's slice of results using LIMIT/OFFSET -- together enabling "Page 3 of 12" style navigation controls.

Note: Run a lightweight COUNT(*) query separately from the main data query, rather than fetching every row just to count them in PHP.
Warning: Forgetting the total-count query means you cannot show total pages or disable a "next page" button once the last page has been reached.

Example: Building a Full Pagination Script

php 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE items (name TEXT)");
foreach (range(1, 25) as $i) { $db->exec("INSERT INTO items VALUES ('Item $i')"); }
// Declare `$total`, set to `$db->querySingle("SELECT COUNT(*) FROM items")`
$total = $db->querySingle("SELECT COUNT(*) FROM items");
// Declare `$perPage`, set to `10`
$perPage = 10;
// Declare `$totalPages`, set to `ceil($total / $perPage)`
$totalPages = ceil($total / $perPage);
// Print "Page 1 of $totalPages" to the output
echo "Page 1 of $totalPages";
?>

Using LIMIT for Top-N Queries

Beyond pagination, LIMIT combined with ORDER BY is the standard way to answer "top N" questions -- the 3 highest-paid employees, the 10 best-selling products, the 5 most recent comments -- by sorting in the direction that puts the desired rows first, then limiting to just that count.

Note: For a "top N" query, sort in the direction that puts your desired rows first (DESC for highest/most, ASC for lowest/earliest), then apply LIMIT N.
Warning: Sorting in the wrong direction (ASC instead of DESC, or vice versa) for a "top N" query silently returns the bottom N instead, with no error to signal the mistake.

Example: Using LIMIT for Top-N Queries

php 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE employees (name TEXT, salary INTEGER)");
$db->exec("INSERT INTO employees VALUES ('Alice', 90000), ('Bob', 70000), ('Carol', 85000)");
// Declare `$result`, set to `$db->query("SELECT * FROM employees ORDER BY salary DESC LIMIT 2")`
$result = $db->query("SELECT * FROM employees ORDER BY salary DESC LIMIT 2");
// Keep looping while `$row = $result->fetchArray(SQLITE3_ASSOC)` holds
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // Print `$row['name'] . "\n"` to the output
    echo $row['name'] . "\n";
}
?>

Performance Considerations with LIMIT

LIMIT alone with no OFFSET is efficient since the database can stop scanning as soon as it has found enough matching rows -- but a large OFFSET forces it to still count past every skipped row first, which is worth keeping in mind for deep pagination (like page 500 of a huge table).

Note: For very deep pagination on large tables, consider "cursor-based" pagination (using a WHERE id > lastSeenId LIMIT n pattern) instead of a large OFFSET, which scales much better.
Warning: An index on the column used in ORDER BY makes both the sort and the LIMIT/OFFSET pagination noticeably faster on large tables -- an unindexed sort column can make deep pagination especially slow.

Example: Performance Considerations with LIMIT

php 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE items (name TEXT)");
foreach (range(1, 1000) as $i) { $db->exec("INSERT INTO items VALUES ('Item $i')"); }
// Declare `$start`, set to `microtime(true)`
$start = microtime(true);
$db->query("SELECT * FROM items LIMIT 10")->fetchArray();
// Print "Small offset: " . round(microtime(true) - $start, 5) . "s" to the output
echo "Small offset: " . round(microtime(true) - $start, 5) . "s";
?>
Related Topics

PHP MySQL Update Data · PHP MySQL Delete Data · PHP MySQL Select Data · PHP MySQL Insert Data

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Fetching every row from the database and then slicing the results down to a page's worth in PHP, when LIMIT in the SQL itself is far more efficient and never transfers unneeded rows.
  2. Forgetting to combine LIMIT with ORDER BY, since without an explicit sort, which rows LIMIT happens to return first is not guaranteed to be meaningful or consistent.
  3. Calculating a pagination OFFSET incorrectly, like using the current page number directly instead of (page - 1) * pageSize, producing an off-by-one-page result.
Chapter Summary
  • LIMIT n restricts a query to return at most n rows.
  • LIMIT n OFFSET m skips the first m rows and then returns up to n rows after that, the basis of page-based pagination.
  • LIMIT should almost always be paired with ORDER BY, since the specific rows returned without an explicit sort are not guaranteed to be meaningful.
Browser Support

LIMIT (and OFFSET) are standard SQL and work identically across all MySQL and MariaDB versions PHP supports.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Update Data Next Lesson → PHP MySQLi Connection

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.