<html>
<head>
<title>Pagination Example: PHP and SQL (MySQL)</title>
</head>
<body>
<div style="width:770px">
<?php
define('MAX_REC_PER_PAGE', 15);
# set up db or die trying
$db = mysql_connect("localhost", "", "") or die("Couldn't connect to db!");
mysql_select_db("test") or die("Couldn't select db!");
# calculate total records
$rs = mysql_query("SELECT COUNT(*) FROM states") or die("Count query error!");
list($total) = mysql_fetch_row($rs);
$total_pages = ceil($total / MAX_REC_PER_PAGE);
# calculate our starting point and range...
$page = intval(@$_GET["page"]);
# intval() forces to a numeric value to protect against garbage or malicious entries; intval("string") is ZER0
# @ in front of a variable suppresses warnings if it isn't there, an empy string intval() will be ZERO
if (0 == $page)
$page = 1; # 1-based
$start = MAX_REC_PER_PAGE * ($page - 1); # first record we will display
$max = MAX_REC_PER_PAGE; # maximum number to show
# query
$rs = mysql_query("
SELECT id, short, name
FROM states
ORDER BY id ASC
LIMIT $start, $max -- where the magic happens
") or die("State query error!");
?>
<h4><a href="http://www.parseerror.com/">parseerror.com</a> / <a href="../">pizza</a> / <a href="./">php</a> / paginate.php</h4>
<h1>Pizza's PHP Pagination</h1>
<h2>The 50 States (and D.C.)</h2>
<h3>Page <?= $page ?></h3>
<table border="1" width="100%">
<tr>
<th>#</th>
<th>Abbrev.</th>
<th>Full Name</th>
</tr>
<?php
while (list($id, $short, $name) = mysql_fetch_row($rs)) {
?>
<tr>
<td width="10%"><?= htmlspecialchars($id) ?></td>
<td width="30%"><?= htmlspecialchars($short) ?></td>
<td><?= htmlspecialchars($name) ?></td>
</tr>
<?php
}
?>
</table>
<table border="0" cellpadding="5" align="center">
<tr>
<td>Goto Page:</td>
<?php
for ($i = 1; $i <= $total_pages; $i++) {
$txt = $i;
if ($page != $i) # don't link to current page
$txt = "<a href=\"" . $_SERVER["PHP_SELF"] . "?page=$i\">$txt</a>";
?>
<td align="center"><?= $txt ?></td>
<?php
}
?>
</tr>
</table>
<hr>
<p><center><a href="paginate.phps">View The Source</a></center></p>
</div>
</body>
</html>