Dedetok: My Experience Notes: 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
<?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>
Server Farm Info
<?php
if ($res) {
 echo "Total: $total";
 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 "No Data";
 }
?>
</table>
<?php
 if ($last>1) {
 echo "";
 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 ";
} else {
 echo "Query Fail";
}
?>
</body>
</html>

Wednesday, October 5, 2016

Simple Pagination With PHP and MySQL