Pagination using MySQL LIMIT, OFFSET
Hardcoding page URLs like itempage2.php works for a simple demo, but it breaks down immediately as your dataset grows or if you add more pages later. The core issue in the provided code snippet is that the pagination logic relies on static offsets (LIMIT 4 OFFSET 4) and static links, rather than calculating the current page number and the total number of rows available. To build a robust pagination system, you need to shift from a hardcoded approach to a dynamic one that calculates results at runtime.
The standard MySQL syntax for limiting rows is SELECT * FROM table_name LIMIT offset, count. In your specific case, you are retrieving 4 items per page. If you want the first page, the query is LIMIT 4 OFFSET 0 (or simply LIMIT 4). For the second page, you need to skip the first 4 rows and take the next 4, which results in LIMIT 4 OFFSET 4. The pattern becomes clear: the offset for any given page is calculated by multiplying the number of items per page by the current page number.
However, simply changing the query isn't enough. You also need to know how many pages exist so you can generate links dynamically. This requires a two-query strategy or a subquery approach. The most straightforward method for legacy MySQL setups (pre-8.0) involves running a second query specifically to count the total number of records in the table.
You would first run your main query to fetch the data:
SELECT * FROM menuitem LIMIT 4 OFFSET 0;
Then, you need a separate query to get the grand total:
SELECT COUNT(*) AS total_rows FROM menuitem;
With these two numbersâthe number of items retrieved and the total countâyou can mathematically determine how many pages exist. The formula for total pages is ceil(total_rows / items_per_page). If you have 25 items and display 4 per page, you need 7 pages (since 25 divided by 4 is 6.25).
Once you have the logic to calculate the total rows, you can generate the navigation links dynamically instead of hardcoding them. Instead of linking directly to itempage2.php, construct a URL that includes the page number as a query parameter. This allows your PHP script to accept a variable argument. For example:
$page = isset($_GET['p']) ? (int)$_GET['p'] : 1;
$offset = ($page - 1) * $items_per_page; // e.g., if page is 2, offset is 4
$query = "SELECT * FROM menuitem LIMIT {$items_per_page} OFFSET {$offset}";
$result = mysqli_query($con, $query);
This approach decouples the HTML generation from the specific page number. The "Next" link would simply point to ?p=2, and the "Previous" link (if applicable) would point to ?p=1.
Handling Large Datasets: The Offset Pitfall
While this method is standard and compatible with older MySQL versions, relying on large OFFSET values introduces performance issues. When you request page 100 with an offset of 960, MySQL must scan every single row from the beginning to skip the first 960 before returning the next 4. This effectively turns your query into a linear scan, ignoring indexes until it reaches the desired offset.
As your menuitem table grows beyond 20-30 items, this performance degradation becomes noticeable. If you ever scale this application to handle thousands of records, consider implementing cursor-based pagination or using MySQL's generated columns to map keys to page numbers. However, for small datasets (under 10,000 rows), the OFFSET method remains perfectly acceptable and is significantly easier to implement than complex keyset pagination logic.
If you are maintaining a legacy codebase similar to the snippet provided, sticking with LIMIT and OFFSET is pragmatic. Just ensure you replace the hardcoded file names (itempage2.php) with dynamic query parameters that adjust based on the calculated page count.
