MySQL Limit Data

PHP MySQL Limit Data (Pagination)

MySQL provides a LIMIT clause that is used to specify the number of records to return.

The LIMIT clause makes it easy to code multi-page results or pagination with SQL, and is very useful on large tables. Returning a large number of records can severely impact performance.

Using LIMIT and OFFSET

If your table contains thousands of rows, you probably don't want to load them all at once.

Assume we wish to select the first 30 records from "Orders": $sql = "SELECT * FROM Orders LIMIT 30";

But what if you want to select records 16 through 30? To do this, you combine LIMIT with an OFFSET. The offset tells MySQL how many rows to skip before starting to return data.

<?php
// Select 10 records, but skip the first 15.
// This will return records 16 through 25!
$sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";

$result = mysqli_query($conn, $sql);
// Display loop goes here...
?>

(Note: You can also write this using a shorter syntax: LIMIT 15, 10 where 15 is the offset and 10 is the limit).

Pagination is an essential feature for blogs, search results, and admin dashboards!