Limit Data Selections in PHP and MySQL – Complete Beginner Tutorial

When working with large databases, displaying all records at once is inefficient and slow. To improve performance and user experience, PHP and MySQL provide the LIMIT clause, which allows you to fetch only a specific number of records.

This tutorial explains how to limit data selections in PHP and MySQL, including offset, pagination, and real-world examples with output.

What Is LIMIT in PHP MySQL?

The LIMIT clause is used in a SELECT query to restrict the number of rows returned from a database table.

Basic syntax:

SELECT * FROM table_name LIMIT number;

Why Use LIMIT in PHP MySQL?

Using LIMIT helps to:

• Improve page load speed
• Reduce server load
• Create pagination systems
• Display recent posts or latest users
• Control large data output

Prerequisites for Using LIMIT Clause

Before proceeding, ensure you have:

• PHP installed
• MySQL database
• A working database connection
• A table with existing records

Sample MySQL Table Used in Examples

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

PHP MySQL Database Connection Code

<?php
$conn = mysqli_connect("localhost", "root", "", "testdb");

if (!$conn) {
    die("Database connection failed");
}
?>

Select Limited Records Using LIMIT in PHP MySQL

<?php
$query = "SELECT * FROM users LIMIT 5";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}
?>

Output:

Rahul
Amit
Neha
Suresh
Pooja

Limit Data Selection with OFFSET in PHP MySQL

OFFSET is used to skip records before fetching results.

SELECT * FROM users LIMIT offset, count;

Example with OFFSET

<?php
$query = "SELECT * FROM users LIMIT 5, 5";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}
?>

Output:

Ravi
Anil
Kiran
Manoj
Deepa

Use LIMIT with ORDER BY in PHP MySQL

LIMIT is commonly combined with ORDER BY.

<?php
$query = "SELECT * FROM users ORDER BY id DESC LIMIT 3";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}
?>

Output:

Deepa
Manoj
Kiran

Dynamic LIMIT Value Using PHP Variable

<?php
$limit = 4;
$query = "SELECT * FROM users LIMIT $limit";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['email'] . "<br>";
}
?>

Output:

rahul@example.com
amit@gmail.com
neha@gmail.com
suresh@gmail.com

Pagination Example Using LIMIT in PHP MySQL

Pagination is the most common real-world use of LIMIT.

<?php
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$records_per_page = 5;
$offset = ($page - 1) * $records_per_page;

$query = "SELECT * FROM users LIMIT $offset, $records_per_page";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}
?>

Output (Page 2):

Ravi
Anil
Kiran
Manoj
Deepa

Secure LIMIT Usage with Prepared Statements

<?php
$stmt = mysqli_prepare($conn,
    "SELECT * FROM users LIMIT ?, ?");
mysqli_stmt_bind_param($stmt, "ii", $offset, $limit);

$offset = 0;
$limit = 3;

mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}
?>

Output:

Rahul
Amit
Neha

Common Mistakes When Using LIMIT Clause

• Using negative values
• Forgetting OFFSET logic in pagination
• Allowing unsanitized user input
• Mixing LIMIT with incorrect ORDER BY

PHP MySQL LIMIT, limit data selection PHP MySQL, MySQL LIMIT OFFSET PHP, PHP pagination MySQL, fetch limited records PHP, PHP MySQL tutorial

Best Practices for Limiting Data in PHP MySQL

• Always use ORDER BY with LIMIT
• Validate LIMIT and OFFSET values
• Use pagination for large datasets
• Combine LIMIT with indexed columns
• Use prepared statements for dynamic limits

Related tutorial:

• PHP MySQL Insert Data
• PHP MySQL Select Data
• PHP MySQL Update Data
• Limit Data Selections in PHP and MySQL
• PHP MySQL Pagination Full Tutorial
• PHP MySQL Use WHERE

Frequently Asked Questions (FAQ)

1. What does LIMIT do in MySQL?

It restricts the number of rows returned.

2. Can LIMIT be used without ORDER BY?

Yes, but results may be unpredictable.

3. What is OFFSET in LIMIT?

It skips a specified number of rows.

4. Is LIMIT important for pagination?

Yes, it is essential.

5. Can LIMIT improve performance?

Yes, significantly for large tables.