PHP MySQL Limit Data

LIMIT Usage Patterns

SELECT * FROM users LIMIT 10

SELECT * FROM users LIMIT 5, 10

LIMIT $start, $per_page

ORDER BY score DESC LIMIT 3

🔹 Basic LIMIT Clause

The simplest form of LIMIT returns a specified number of records from the beginning of the result set:

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

// Get first 5 users
$sql = "SELECT id, name, email FROM users LIMIT 5";
$result = mysqli_query($conn, $sql);

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

mysqli_close($conn);
?>

🔹 LIMIT with OFFSET

Use OFFSET to skip a certain number of records before limiting. Syntax: LIMIT offset, count or LIMIT count OFFSET offset:

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

// Skip first 3 records, then get 5 records
$sql = "SELECT id, name FROM users LIMIT 3, 5";
// Alternative syntax: LIMIT 5 OFFSET 3

$result = mysqli_query($conn, $sql);

echo "Records 4-8:<br>";
while($row = mysqli_fetch_assoc($result)) {
    echo "ID: " . $row["id"] . " - " . $row["name"] . "<br>";
}

mysqli_close($conn);
?>

🔹 Pagination Example

LIMIT is commonly used for pagination, dividing large result sets into manageable pages for better user experience:

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

// Pagination settings
$records_per_page = 10;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$start_from = ($page - 1) * $records_per_page;

// Get records for current page
$sql = "SELECT id, name, email FROM users LIMIT $start_from, $records_per_page";
$result = mysqli_query($conn, $sql);

// Display records
while($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . " - " . $row['email'] . "<br>";
}

// Get total records for pagination links
$sql_count = "SELECT COUNT(*) as total FROM users";
$result_count = mysqli_query($conn, $sql_count);
$row_count = mysqli_fetch_assoc($result_count);
$total_pages = ceil($row_count['total'] / $records_per_page);

// Display pagination links
echo "<br>Pages: ";
for($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='?page=$i'>$i</a> ";
}

mysqli_close($conn);
?>

🔹 Top N Results with ORDER BY

Combine LIMIT with ORDER BY to get top or bottom records based on specific criteria:

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

// Get top 3 highest scores
$sql = "SELECT name, score FROM students 
        ORDER BY score DESC 
        LIMIT 3";

$result = mysqli_query($conn, $sql);

echo "Top 3 Students:<br>";
$rank = 1;
while($row = mysqli_fetch_assoc($result)) {
    echo $rank . ". " . $row['name'] . " - Score: " . $row['score'] . "<br>";
    $rank++;
}

mysqli_close($conn);
?>

🔹 Dynamic LIMIT with Variables

Use variables to make LIMIT values dynamic and flexible:

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

// Dynamic limit values
$limit = 5;
$offset = 10;

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

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

$stmt->close();
mysqli_close($conn);
?>

🔹 Important Tips