I have some code that LIMITs data to display only 4 items per page. The column I'm using has about 20-30 items, so I need to make those spread out across the pages.
On the first page, I have:
$result = mysqli_query($con,"SELECT * FROM menuitem LIMIT 4");
{
echo "<tr>";
echo "<td align='center'><img src=\"" . $row['picturepath'] . "\" /></td>";
echo "<td align='center'>" . $row['name'] . "</td> <td align='center'> <input type='button' value='More Info'; onclick=\"window.location='more_info.php?';\"> </td>";
echo "<td align='center'>" . $row['price'] . "</td> <td align='center'> <input type='button' value='Add to Order' onclick=''> </td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
<table width="1024" align="center" >
<tr height="50"></tr>
<tr>
<td width="80%" align="right">
<a href="itempage2.php">NEXT</a>
</td>
<td width="20%" align="right">
<a href="">MAIN MENU</a>
</td>
</tr>
</table>
You'll notice towards the bottom of the page my anchor tag within lists the second page, "itempage2.php". In item page 2, I have the same code, except my select statement lists the offset of 4.
$result = mysqli_query($con,"SELECT * FROM menuitem LIMIT 4 offset 4");
This works, this way when there is a pre-determined number of items within my database. But it's not that good. I need to create a new page only if there are more items, not hard-coded into it like it is now.
How can I create multiple pages without having to hard-code each new page, and offset?
Best Answer
First off, don't have a separate server script for each page, that is just madness. Most applications implement pagination via use of a pagination parameter in the URL. Something like:
You can access the requested page number via
$_GET['page']
.This makes your SQL formulation really easy:
So for example if input here was
page=2
, with 4 rows per page, your query would be"So that is the basic problem of pagination. Now, you have the added requirement that you want to understand the total number of pages (so that you can determine if "NEXT PAGE" should be shown or if you wanted to allow direct access to page X via a link).
In order to do this, you must understand the number of rows in the table.
You can simply do this with a DB call before trying to return your actual limited record set (I say BEFORE since you obviously want to validate that the requested page exists).
This is actually quite simple: