PHP MySQL Using the LIMIT Clause

⏱️ PHP MySQL – Using the LIMIT Clause

The LIMIT clause in MySQL is used to restrict the number of records returned in a SELECT query. It’s very useful for pagination or displaying a subset of data.

1️⃣ Using `mysqli` (Procedural)

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

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Select only 3 records
$sql = "SELECT id, name, email, age FROM users 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"]."<br>";
}
} else {
echo "0 results";
}

mysqli_close($conn);
?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

// Select only 3 records

$sql = "SELECT id, name, email, age FROM users 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"]."<br>";

}

} else {

echo "0 results";

}

mysqli_close($conn);

✅ Returns first 3 rows from the table.

2️⃣ Using `mysqli` Object-Oriented

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

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

// Limit 2 records, skip the first 1
$sql = "SELECT * FROM users LIMIT 1, 2"; // OFFSET, COUNT
$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();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// Limit 2 records, skip the first 1

$sql = "SELECT * FROM users LIMIT 1, 2"; // OFFSET, COUNT

$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();

✅ LIMIT offset, count → skip offset rows and return count rows.

3️⃣ Using PDO

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

$limit = 2;
$offset = 1;
$stmt = $conn->prepare("SELECT * FROM users LIMIT :offset, :limit");
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
echo "ID: ".$user['id']." - Name: ".$user['name']."<br>";
}

} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

$conn = null;
?>

<?php

try {

$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$limit = 2;

$offset = 1;

$stmt = $conn->prepare("SELECT * FROM users LIMIT :offset, :limit");

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

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

$stmt->execute();

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {

echo "ID: ".$user['id']." - Name: ".$user['name']."<br>";

}

} catch(PDOException $e) {

echo "Error: " . $e->getMessage();

}

$conn = null;

✅ In PDO, bind parameters as PDO::PARAM_INT when using LIMIT.

4️⃣ Key Points

LIMIT restricts the number of rows returned.
Syntax:
- LIMIT count → returns the first count rows
- LIMIT offset, count → skip offset rows, return count rows
Useful for pagination, e.g., showing 10 records per page.
Works with mysqli procedural, object-oriented, and PDO.
Always bind LIMIT parameters as integers in PDO to avoid errors.

PHP MySQL Using the LIMIT Clause