Simple Pagination With PHP and MySQL

This is my personal note to create simple pagination using php and mysql. I used mysqli object oriented style for future php support. Version MySQL and PHP are:

  • MySQL Version 5.5
  • PHP Version 5.6

To create your own demo, create your database and populate it.
CREATE TABLE `kci_logipv4` (
  `logdate` datetime DEFAULT NULL,
  `logipv4` int(11) unsigned DEFAULT NULL,
  `logmsg` varchar(1000) DEFAULT NULL,
  `logfrom` int(11) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `kci_logipv4` VALUES ('2016-09-28 23:22:11',3104583205,'SSH',1),('2016-09-28 23:22:11',3104583205,'SSH',1),('2016-09-29 09:07:50',1347119510,'SSH',1),('2016-09-29 09:07:51',1347119510,'SSH',1),('2016-09-29 20:28:27',3743330746,'SSH',1),('2016-09-29 20:28:28',3743330746,'SSH',1),('2016-09-29 20:59:51',1033072143,'SSH',1),('2016-09-29 20:59:53',1033072143,'SSH',1),('2016-09-29 21:06:55',1907273049,'SSH',1),('2016-09-29 21:06:57',1907273049,'SSH',1),('2016-09-30 03:20:08',3075559489,'SSH',1),('2016-09-30 03:20:09',3075559489,'SSH',1),('2016-09-30 11:47:19',1323303364,'SSH',1),('2016-09-30 11:47:21',1323303364,'SSH',1),('2016-09-30 21:59:33',3565240261,'SSH',1),('2016-09-30 21:59:35',3565240261,'SSH',1),('2016-10-01 03:09:18',1794631394,'SSH',1),('2016-10-01 03:09:18',1794631394,'SSH',1),('2016-10-01 10:02:35',2746017717,'SSH',1),('2016-10-01 10:02:36',2746017717,'SSH',1),('2016-10-01 10:29:18',1963359914,'SSH',1),('2016-10-01 10:29:19',1963359914,'SSH',1),('2016-10-01 10:57:30',2000423175,'SSH',1),('2016-10-01 10:57:31',2000423175,'SSH',1),('2016-10-01 21:33:06',1757971329,'SSH',1),('2016-10-01 21:33:07',1757971329,'SSH',1),('2016-10-02 21:00:31',1033072141,'SSH',1),('2016-10-02 21:00:34',1033072141,'SSH',1),('2016-10-03 02:35:10',2065638212,'SSH',1),('2016-10-03 02:35:11',2065638212,'SSH',1),('2016-10-03 06:18:41',3754290014,'SSH',1),('2016-10-03 06:18:43',3754290014,'SSH',1),('2016-10-03 15:14:53',1912501632,'SSH',1),('2016-10-03 15:14:53',1912501632,'SSH',1),('2016-10-04 00:04:44',3281659836,'SSH',1),('2016-10-04 00:04:45',3281659836,'SSH',1),('2016-10-04 01:45:32',1168426137,'SSH',1),('2016-10-04 01:45:33',1168426137,'SSH',1),('2016-10-04 12:04:05',2065638165,'SSH',1),('2016-10-04 12:04:05',2065638165,'SSH',1),('2016-10-04 13:11:16',457715326,'SSH',1),('2016-10-04 13:11:16',457715326,'SSH',1),('2016-10-04 16:50:40',1839072489,'SSH',1),('2016-10-04 16:50:40',1839072489,'SSH',1),('2016-10-05 09:20:48',1760484871,'',1),('2016-10-05 09:20:48',1760484871,'',1);
Here is the kci_logread.php code you can view it live
<?php

/*
database: database
table: kci_logipv4
logdate datetime
logipv4 int(11)
logmsg varchar(1000)
logfrom int
*/

// host, user, password, database, [port]
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
    die('Internal Server Error maybe database');
}

//This is the page number want to display
//retrieve from user navigation via method get or post
$page = 1;
if(isset($_GET["page"])){
    $page = intval($_GET["page"]); // sanitize input
}
if(isset($_POST["page"])){
    $page = intval($_POST["page"]); // sanitize input
}
//This is the number of results displayed per page
$page_rows = 15;

//count all result
//must equal to query to display data
$res = $mysqli->query("select Count(*) as total from kci_logipv4");
$rows = $res->num_rows;
//number of data available
$total=0;
if($rows) {
    $row = $res->fetch_assoc();
    $total = $row['total'];
}

//this is which first row should be retrieve
$start = ($page_rows * $page) - $page_rows;
//query to display data
//read $page_row data start from row $start 
$res = $mysqli->query("SELECT * FROM kci_logipv4 order by logdate desc Limit $start, $page_rows ");
//number of data to display
$rows = $res->num_rows;

//This tells us the page number of our last page
$last = ceil($total/$page_rows);

?>
<html>
<body>
<p>Server Farm Info</p>
<?php
if ($res) {
    echo "<p>Total: $total</p>";
    if ($rows>0) {
?>
<table border="1">
    <tr>
        <th>From</th>
        <th>Datetime</th>
        <th>IP</th>
        <th>Comment</th>
    </tr>
<?php
        while ($row = $res->fetch_assoc()) {
?>
    <tr>
        <td>Server farm <?php //echo $row['logfrom']; ?></td>
        <td><?php echo $row['logdate']; ?></td>
        <td align="center"><?php echo long2ip($row['logipv4']); ?></td>
        <td><?php echo $row['logmsg']; ?></td>
    </tr>
<?php
        }
    } else {
        echo "<p>No Data</p>";
    }
?>
</table>
<?php
    if ($last>1) {
        echo "<P>";
        if ($page>1) {
            echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], ENT_QUOTES, "utf-8")."?page=1\">First</a> ";
        }
        if ($page>2 && $page<=$last) {
            echo "... ";
        }
        if ($page>1) {
            echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], ENT_QUOTES, "utf-8")."?page=".($page-1)."\">".($page-1)."</a> ";
        }
        echo "$page ";
        if ($page+1<=$last) {
            echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], ENT_QUOTES, "utf-8")."?page=".($page+1)."\">".($page+1)."</a> ";
        }
        if ($page+1<$last) {
            echo "... ";
        }
        if ($page<$last) {
            echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], ENT_QUOTES, "utf-8")."?page=$last\">Last</a> ";
        }
        echo "| Number of pages: $last </p>";
} else {
    echo "<p>Query Fail</p>";
}
?>
</body>
</html>