• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Limit Data

← Previous Next →

When working with databases, you often deal with large datasets. Retrieving all rows at once can be inefficient and slow down your application. MySQL’s LIMIT clause allows you to restrict the number of rows returned by a query.

In PHP applications, using LIMIT is crucial for pagination, performance optimization, and user-friendly displays. This tutorial explains how to use LIMIT with MySQLi and PDO, including offset handling, best practices, and real-world examples.

What is the LIMIT Clause?

The LIMIT clause specifies the maximum number of rows a query should return. It is typically used with SELECT but can also be applied to DELETE and UPDATE in some scenarios.

Basic Syntax

SELECT * FROM table_name LIMIT number_of_rows;

Example Table: students

id name age email
1 Neha 16 neha@example.com
2 Anika 17 anika@example.com
3 Riya 16 riya@example.com
4 Sanya 18 sanya@example.com
5 Priya 17 priya@example.com 
SELECT * FROM students LIMIT 3;

This query fetches the first three rows from the table.

Using OFFSET

You can skip a number of rows before retrieving data using an offset:

SELECT * FROM students LIMIT 2, 3;

  • The first number (2) is the offset, i.e., the number of rows to skip.

  • The second number (3) is the number of rows to return.

This query skips the first two rows and fetches the next three.

Using LIMIT with ORDER BY

LIMIT is often combined with ORDER BY to get top results or specific ranges.

SELECT * FROM students ORDER BY age DESC LIMIT 2;

This query retrieves the two oldest students.

Using LIMIT in PHP MySQLi

Procedural Style

<?php
$conn = mysqli_connect("localhost", "root", "", "school_db");
if (!$conn) die("Connection failed: " . mysqli_connect_error());

$sql = "SELECT * FROM students ORDER BY id ASC LIMIT 3";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "ID: {$row['id']}, Name: {$row['name']}, Age: {$row['age']}, Email: {$row['email']}<br>";
    }
} else {
    echo "No records found.";
}

mysqli_close($conn);
?>

Object-Oriented Style

<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

$sql = "SELECT * FROM students ORDER BY age DESC LIMIT 2, 2";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: {$row['id']}, Name: {$row['name']}, Age: {$row['age']}, Email: {$row['email']}<br>";
    }
} else {
    echo "No records found.";
}

$conn->close();
?>

Using LIMIT with PDO

<?php
try {
    $conn = new PDO("mysql:host=localhost;dbname=school_db", "root", "");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->prepare("SELECT * FROM students ORDER BY id ASC LIMIT :limit OFFSET :offset");
    $limit = 3;
    $offset = 1;

    $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if ($students) {
        foreach ($students as $student) {
            echo "ID: {$student['id']}, Name: {$student['name']}, Age: {$student['age']}, Email: {$student['email']}<br>";
        }
    } else {
        echo "No records found.";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>

Explanation:

  • LIMIT :limit OFFSET :offset allows dynamic row selection.

  • bindParam() with PDO::PARAM_INT is necessary for numeric values.

  • ORDER BY ensures a consistent ordering when using limits.

Pagination Using LIMIT

Pagination is a common use case for LIMIT. For example, to display 5 records per page:

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

$sql = "SELECT * FROM students ORDER BY id ASC LIMIT $records_per_page OFFSET $offset";

  • Page 1: offset 0

  • Page 2: offset 5

  • Page 3: offset 10

This technique improves performance and user experience when displaying large datasets.

Best Practices

  1. Always combine LIMIT with ORDER BY for consistent results.

  2. Use OFFSET carefully on very large datasets; consider keyset pagination for better performance.

  3. Sanitize any dynamic page or limit values to avoid injection risks.

  4. Use LIMIT in DELETE or UPDATE cautiously when modifying many rows.

  5. Consider indexes on ordered columns to improve query speed.

Common Errors

Error Cause Solution
Syntax error Using LIMIT without proper placement Ensure LIMIT comes after ORDER BY
Unexpected results No ORDER BY Use ORDER BY to define row order
Injection risk Using user input directly Always validate and sanitize
Performance issues Large OFFSET on big tables Use indexed columns or keyset pagination

Real-World Examples

  1. Pagination in dashboards: Displaying 10 users per page with next/previous links.

  2. Top results: Fetching top 5 highest-scoring students.

  3. Data previews: Showing a few recent posts or products on a homepage.

Example:

SELECT * FROM products ORDER BY created_at DESC LIMIT 5;

This retrieves the 5 most recently added products.

Summary of the Tutorial

  • LIMIT restricts the number of rows returned by a query.

  • Combine with ORDER BY to control which rows are retrieved first.

  • Use OFFSET to skip rows for pagination.

  • Implement LIMIT with PHP MySQLi or PDO for efficient data retrieval.

  • Proper use of LIMIT improves performance, usability, and scalability for applications handling large datasets.

Using LIMIT effectively allows developers to build responsive, user-friendly interfaces while keeping server load low, making it an essential tool for PHP and MySQL applications.

Practice Questions

  1. Write a SQL query to select the first 5 students from the students table.

  2. Write a SQL query to fetch 3 students, skipping the first 2 rows.

  3. Write a PHP MySQLi procedural script to select the top 3 oldest students using LIMIT.

  4. Write a PHP MySQLi object-oriented script to retrieve students with age greater than 16, limited to 4 rows.

  5. Write a PDO PHP script to fetch 5 students starting from the 6th row using LIMIT and OFFSET.

  6. Explain why combining LIMIT with ORDER BY is important for predictable results.

  7. Write a SQL query to fetch the last 5 students added to the table, assuming id is auto-incremented.

  8. Write a PHP script to implement pagination displaying 10 students per page based on a page GET parameter.

  9. Describe a real-world scenario where using LIMIT improves performance in a web application.

  10. Write a SQL query to select 3 students ordered by name ascending, skipping the first row.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top