PHP MySQL Select with Limit: A Comprehensive Guide

In a database you often want only a slice of a table — the newest ten posts, the first page of search results, a single "top scorer" — rather than every row. The LIMIT clause in a MySQL SELECT statement caps how many rows the query returns. Combined with an OFFSET, it is the foundation of pagination: serving large result sets one page at a time instead of loading thousands of rows into memory.

This chapter covers the LIMIT syntax, the row-skipping OFFSET, how to build pagination safely with prepared statements, and the gotchas that trip people up. It assumes you can already connect to MySQL and run a basic SELECT.

Syntax of SELECT with LIMIT

The basic form sets a maximum number of rows to return:

SELECT column1, column2, ... FROM table_name LIMIT row_count;

To also skip rows from the start of the result set, add an offset. MySQL supports two equivalent spellings:

SELECT ... FROM table_name LIMIT offset, row_count;   -- offset first
SELECT ... FROM table_name LIMIT row_count OFFSET offset;  -- explicit OFFSET

What each part means:

• row_count — the maximum number of rows to return.
• offset — how many rows to skip before starting to return rows. The offset is zero-based, so OFFSET 0 starts at the first row.
• LIMIT 10, 5 returns 5 rows starting after the first 10 (i.e. rows 11–15). The same query reads more naturally as LIMIT 5 OFFSET 10.

LIMIT without ORDER BY is non-deterministic. Without an explicit ORDER BY, MySQL is free to return the rows in any order, so "the first 2 rows" can differ between runs. Always pair LIMIT with ORDER BY when row order matters.

Example: retrieving the first rows

Consider a table named students:

+----+---------+--------+-------+
| id | name    | class  | marks |
+----+---------+--------+-------+
|  1 | John    | 10     | 90    |
|  2 | Michael | 9      | 85    |
|  3 | Jessica | 8      | 80    |
|  4 | Sarah   | 10     | 88    |
|  5 | David   | 9      | 72    |
+----+---------+--------+-------+

To fetch the first two students (ordered by id so the result is stable):

<?php
$conn = mysqli_connect("localhost", "username", "password", "database");

$query = "SELECT id, name, class, marks FROM students ORDER BY id LIMIT 2";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo "ID: {$row['id']} Name: {$row['name']} Class: {$row['class']} Marks: {$row['marks']}<br>";
}

mysqli_close($conn);
?>

Output:

ID: 1 Name: John Class: 10 Marks: 90
ID: 2 Name: Michael Class: 9 Marks: 85

Note the use of mysqli_fetch_assoc() (associative array only) rather than mysqli_fetch_array(), which returns both numeric and string keys and so allocates twice the memory for the same data.

Skipping rows with OFFSET

To get the second page of two students — rows 3 and 4 — skip the first two:

<?php
$conn = mysqli_connect("localhost", "username", "password", "database");

$query = "SELECT id, name, marks FROM students ORDER BY id LIMIT 2 OFFSET 2";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo "ID: {$row['id']} Name: {$row['name']} Marks: {$row['marks']}<br>";
}

mysqli_close($conn);
?>

Output:

ID: 3 Name: Jessica Marks: 80
ID: 4 Name: Sarah Marks: 88

Building pagination safely

For real pagination the page number comes from user input (a URL like ?page=3), so the offset must never be concatenated directly into SQL. Use a prepared statement with bound integer parameters:

<?php
$conn = mysqli_connect("localhost", "username", "password", "database");

$perPage = 2;
$page    = max(1, (int) ($_GET['page'] ?? 1)); // force a positive integer
$offset  = ($page - 1) * $perPage;

$stmt = mysqli_prepare(
    $conn,
    "SELECT id, name, marks FROM students ORDER BY id LIMIT ? OFFSET ?"
);
mysqli_stmt_bind_param($stmt, "ii", $perPage, $offset); // "ii" = two integers
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

while ($row = mysqli_fetch_assoc($result)) {
    echo "ID: {$row['id']} Name: {$row['name']} Marks: {$row['marks']}<br>";
}

mysqli_close($conn);
?>

Casting the page to (int) and binding it as an integer ("ii") prevents SQL injection, since LIMIT/OFFSET accept only numbers anyway.

To show "Page 3 of N", you also need the total row count. Run a separate COUNT(*) query and divide by the page size:

$total = (int) mysqli_fetch_row(
    mysqli_query($conn, "SELECT COUNT(*) FROM students")
)[0];
$pageCount = (int) ceil($total / $perPage); // 5 rows / 2 per page = 3 pages

Common gotchas

• No ORDER BY, no guarantee. As noted above, LIMIT only returns a stable slice when paired with an ORDER BY on a unique (or tie-broken) column.
• Large offsets are slow. LIMIT 20 OFFSET 100000 still makes MySQL scan and discard 100,000 rows. For deep pagination, prefer keyset pagination — WHERE id > :lastSeenId ORDER BY id LIMIT 20 — which jumps straight to the right place using the index. See WHERE.
• OFFSET cannot be used alone. MySQL requires a LIMIT whenever you use OFFSET. To skip rows and return "all the rest", use a huge limit: LIMIT 18446744073709551615 OFFSET 10.
• Pages are zero-indexed internally. A common off-by-one bug is forgetting that page 1 maps to OFFSET 0, hence ($page - 1) * $perPage above.

Practice