PHP MySQL LIMIT Data, Easy To Learn MySQL LIMIT Data Tutorial in PHP

Here's a clear and concise explanation with code examples for "PHP MySQL LIMIT Data" — ideal for blog posts, tutorials, or documentation.

PHP MySQL LIMIT Data

When working with large datasets in MySQL, it's inefficient to fetch all records at once. The LIMIT clause in MySQL allows you to retrieve a specific number of records, making your applications faster and more responsive.

What is the LIMIT Clause?

The LIMIT clause is used in a SQL query to restrict the number of rows returned by the database. It's commonly used with SELECT statements for pagination or performance.

Syntax:

SELECT column1, column2 FROM table_name LIMIT offset, count;

offset: Number of records to skip.
count: Number of records to return.

Using LIMIT in PHP with MySQLi

1. Connect to the Database

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

2. Fetch Limited Records

Let’s say we want to display only the first 5 rows:

$sql = "SELECT id, name FROM users LIMIT 5";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();

3. Add Pagination with LIMIT and OFFSET

Here’s how to add pagination:

$limit = 5; // Number of records per page
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page - 1) * $limit;

$sql = "SELECT id, name FROM users LIMIT $offset, $limit";
$result = $conn->query($sql);

Add navigation links:

$total_sql = "SELECT COUNT(*) FROM users";
$total_result = $conn->query($total_sql);
$total_row = $total_result->fetch_row();
$total_records = $total_row[0];
$total_pages = ceil($total_records / $limit);

for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='?page=$i'>Page $i</a> ";
}

Security Tip

Always use prepared statements when working with dynamic values to avoid SQL injection:

$stmt = $conn->prepare("SELECT id, name FROM users LIMIT ?, ?");
$stmt->bind_param("ii", $offset, $limit);
$stmt->execute();
$result = $stmt->get_result();

Summary

LIMIT helps you control the number of results from a MySQL query.
It is very useful for pagination and performance optimization.
Use LIMIT offset, count format.
Always sanitize input or use prepared statements when using dynamic LIMIT values.

PHP Tutorial