PHP/MySQLi :How to Make a Simple Pagination

PHP/MySQLi :How to Make a Simple Pagination สำหรับ
แบ่งหน้าการแสดงข้อมูลออกเป็นหน้าๆ ดัง ตย. แสดงหน้าละ 5 record เพื่อให้ง่ายต่อการเรียกดู

database

-- phpMyAdmin SQL Dump
-- version 4.8.0.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Aug 16, 2018 at 06:11 AM
-- Server version: 10.1.32-MariaDB
-- PHP Version: 5.6.36

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `pagination`
--

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `userid` int(11) NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `username` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`userid`, `firstname`, `lastname`, `username`) VALUES
(1, 'neovic', 'devierte', 'nurhodelta'),
(2, 'julyn', 'divinagracia', 'julyn'),
(3, 'lee', 'ann', 'lee09'),
(4, 'tintin', 'demapanag', 'tin45'),
(5, 'dee', 'tolentino', 'deedee'),
(6, 'jaira', 'jacinto', 'jjacinto'),
(7, 'tetai', 'devi', 'tdevi'),
(8, 'tintin', 'hermosa', 'tinhermosa'),
(9, 'piolo', 'pascual', 'ppascual'),
(10, 'lee', 'bagun', 'faker'),
(11, 'barny', 'dino', 'bdino');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `user`
--
ALTER TABLE `user`
  ADD PRIMARY KEY (`userid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
  MODIFY `userid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

-- phpMyAdmin SQL Dump

-- version 4.8.0.1

-- https://www.phpmyadmin.net/

-- Host: 127.0.0.1

-- Generation Time: Aug 16, 2018 at 06:11 AM

-- Server version: 10.1.32-MariaDB

-- PHP Version: 5.6.36

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET AUTOCOMMIT = 0;

START TRANSACTION;

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;

-- Database: `pagination`

-- --------------------------------------------------------

-- Table structure for table `user`

CREATE TABLE `user` (

`userid` int(11) NOT NULL,

`firstname` varchar(30) NOT NULL,

`lastname` varchar(30) NOT NULL,

`username` varchar(30) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table `user`

INSERT INTO `user` (`userid`, `firstname`, `lastname`, `username`) VALUES

(1, 'neovic', 'devierte', 'nurhodelta'),

(2, 'julyn', 'divinagracia', 'julyn'),

(3, 'lee', 'ann', 'lee09'),

(4, 'tintin', 'demapanag', 'tin45'),

(5, 'dee', 'tolentino', 'deedee'),

(6, 'jaira', 'jacinto', 'jjacinto'),

(7, 'tetai', 'devi', 'tdevi'),

(8, 'tintin', 'hermosa', 'tinhermosa'),

(9, 'piolo', 'pascual', 'ppascual'),

(10, 'lee', 'bagun', 'faker'),

(11, 'barny', 'dino', 'bdino');

-- Indexes for dumped tables

-- Indexes for table `user`

ALTER TABLE `user`

ADD PRIMARY KEY (`userid`);

-- AUTO_INCREMENT for dumped tables

-- AUTO_INCREMENT for table `user`

ALTER TABLE `user`

MODIFY `userid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Full Code

<?php
$conn= mysqli_connect("localhost","root","password_devbanban.com","pagination") 
or die("Error: " . mysqli_error($conn));
mysqli_query($conn, "SET NAMES 'utf8' ");
//query
$query=mysqli_query($conn,"SELECT COUNT(userid) FROM `user`");
	$row = mysqli_fetch_row($query);

	$rows = $row[0];

	$page_rows = 5;  //จำนวนข้อมูลที่ต้องการให้แสดงใน 1 หน้า  ตย. 5 record / หน้า 

	$last = ceil($rows/$page_rows);

	if($last < 1){
		$last = 1;
	}

	$pagenum = 1;

	if(isset($_GET['pn'])){
		$pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);
	}

	if ($pagenum < 1) {
		$pagenum = 1;
	}
	else if ($pagenum > $last) {
		$pagenum = $last;
	}

	$limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;

	$nquery=mysqli_query($conn,"SELECT * from  user $limit");

	$paginationCtrls = '';

	if($last != 1){

	if ($pagenum > 1) {
$previous = $pagenum - 1;
		$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'" class="btn btn-info">Previous</a> &nbsp; &nbsp; ';

		for($i = $pagenum-4; $i < $pagenum; $i++){
			if($i > 0){
		$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'" class="btn btn-primary">'.$i.'</a> &nbsp; ';
			}
	}
}

	$paginationCtrls .= ''.$pagenum.' &nbsp; ';

	for($i = $pagenum+1; $i <= $last; $i++){
		$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'" class="btn btn-primary">'.$i.'</a> &nbsp; ';
		if($i >= $pagenum+4){
			break;
		}
	}

if ($pagenum != $last) {
$next = $pagenum + 1;
$paginationCtrls .= ' &nbsp; &nbsp; <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'" class="btn btn-info">Next</a> ';
}
	}
?>
<!DOCTYPE html>
<html>
	<head>
		<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"" rel="nofollow">
		<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
	</head>
	<body>
		<div" rel="nofollow">
			<div style="height: 20px;"></div>
			<div class="row">
				<div class="col-lg-2">
				</div>
				<div class="col-lg-6">
					<h4>Simple Pagination using PHP/MySQLi</h4>
					<table width="80%" class="table table-striped table-bordered table-hover">
						
						<thead>
							<tr class="info">
							<th>UserID</th>
							<th>Firstname</th>
							<th>Lastname</th>
							<th>Username</th>
							</tr>
						</thead>
					
						<tbody>
							<?php
								while($crow = mysqli_fetch_array($nquery)){
							?>
							<tr>
								<td><?php echo $crow['userid']; ?></td>
								<td><?php echo $crow['firstname']; ?></td>
								<td><?php echo $crow['lastname']; ?></td>
								<td><?php echo $crow['username']; ?></td>
							</tr>
							<?php
									}
							?>
						</tbody>
					</table>
					<div id="pagination_controls"><?php echo $paginationCtrls; ?></div>
				</div>
				<div class="col-lg-2">
				</div>
			</div>
		</div>
	</body>
</html>

<!-- Ref : 

	https://www.sourcecodester.com/tutorials/php/11606/simple-pagination-using-phpmysqli.html

	-->

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

<?php

$conn= mysqli_connect("localhost","root","password_devbanban.com","pagination")

or die("Error: " . mysqli_error($conn));

mysqli_query($conn, "SET NAMES 'utf8' ");

//query

$query=mysqli_query($conn,"SELECT COUNT(userid) FROM `user`");

$row = mysqli_fetch_row($query);

$rows = $row[0];

$page_rows = 5; //จำนวนข้อมูลที่ต้องการให้แสดงใน 1 หน้า ตย. 5 record / หน้า

$last = ceil($rows/$page_rows);

if($last < 1){

$last = 1;

}

$pagenum = 1;

if(isset($_GET['pn'])){

$pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);

}

if ($pagenum < 1) {

$pagenum = 1;

}

else if ($pagenum > $last) {

$pagenum = $last;

}

$limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;

$nquery=mysqli_query($conn,"SELECT * from user $limit");

$paginationCtrls = '';

if($last != 1){

if ($pagenum > 1) {

$previous = $pagenum - 1;

$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'" class="btn btn-info">Previous</a>     ';

for($i = $pagenum-4; $i < $pagenum; $i++){

if($i > 0){

$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'" class="btn btn-primary">'.$i.'</a>   ';

}

$paginationCtrls .= ''.$pagenum.'   ';

for($i = $pagenum+1; $i <= $last; $i++){

$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'" class="btn btn-primary">'.$i.'</a>   ';

if($i >= $pagenum+4){

break;

}

if ($pagenum != $last) {

$next = $pagenum + 1;

$paginationCtrls .= '     <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'" class="btn btn-info">Next</a> ';

}

<!DOCTYPE html>

<html>

<head>

</head>

<body>

<div" rel="nofollow">

</div>

<h4>Simple Pagination using PHP/MySQLi</h4>

<thead>

<th>UserID</th>

<th>Firstname</th>

<th>Lastname</th>

<th>Username</th>

</tr>

</thead>

<tbody>

<?php

while($crow = mysqli_fetch_array($nquery)){

<tr>

</tr>

<?php

}

</tbody>

</table>

</div>

</div>

</body>

</html>

<!-- Ref :

https://www.sourcecodester.com/tutorials/php/11606/simple-pagination-using-phpmysqli.html

-->

ผลการทำงาน

Ref : https://www.sourcecodester.com/tutorials/php/11606/simple-pagination-using-phpmysqli.html

ร่วมสนับสนุน ค่ากาแฟ ค่าโฮส devbanban.com และทีมงานได้ที่

ธนาคารกรุงไทย สาขาเดอะมอลล์ท่าพระ
ชื่อบัญชี นายพิศิษฐ์ บวรเลิศสุธี เลขที่ 878-0-17747-6
————————————————————————————
ธนาคารไทยพาณิชย์ สาขามหาวิทยาลัยราชภัฏธนบุรี
ชื่อบัญชี นายพิศิษฐ์ บวรเลิศสุธี เลขที่ 406-359094-1

โทร. 094 861 6709 , LineID: disketjet1 , Email: devbanban@gmail.com (พิศิษฐ์)