Create Pagination Using PHP and MySQL
Pagination is basically dividing the data into different views. Suppose there is a list of 100 news data where you just want to show 10 news per page, then we will have to add and design pagination for that. It can be used to show information in the minimum page section. It retrieves limited data from the DB. So that it also improves the response time of the webpage. In this tutorial, So I will explain to you the creation Pagination process using PHP and MySQL We will cover this tutorial in easy steps with a live demo of the Create Pagination Using PHP and MySQL.
So let’s implement Create Pagination Using PHP and MySQL. Look following folder and file structure:
- pagination-php-mysql
- assets
- css
- style.css
- css
- templates
- header.php
- footer.php
- class
- DBConnection.php
- News.php
- index.php
- assets
Step 1: Create MySQL Database Table
First, we will create database with the following table.
CREATE TABLE `news` ( `id` int(11) NOT NULL, `title` varchar(255) NOT NULL, `content` text NOT NULL, `publish_date` date NOT NULL DEFAULT current_timestamp(), `user` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `news` (`id`, `title`, `content`, `publish_date`, `user`) VALUES (1, '1-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (2, '2-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (3, '3-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (4, '4-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (5, '5-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (6, '6-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (7, '7-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (8, '8-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (9, '9-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (10, '10-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (11, '11-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'), (12, '12-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'); ALTER TABLE `news` ADD PRIMARY KEY (`id`); ALTER TABLE `news` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13; COMMIT;
Step 3: Database Connection class
Create a class file named
DBConnection.php inside “class/” folder.
_conn = new PDO("mysql:host=$this->_dbHostName;dbname=$this->_dbName", $this->_dbUserName, $this->_dbPassword, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
// set the PDO error mode to exception
$this->_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Error connecting to MySQL server: " . $e->getMessage();
}
}
// return Connection
public function returnConnection()
{
return $this->_conn;
}
}
?>
Step 4: Create a class file named
News.php inside “class/” folder.
_page = $page;
}
public function setRowPerPage($rowPerPage)
{
$this->_rowPerPage = $rowPerPage;
}
public function setStart($start)
{
$this->_start = $start;
}
// __construct - Database Config File
public function __construct()
{
$this->_db = new DBConnection();
$this->_db = $this->_db->returnConnection();
}
// get News list from mysql databse
public function getNewsList()
{
try {
$sql = "SELECT * FROM news ";
$limit = " limit " . $this->_start . "," . $this->_rowPerPage;
$query = $sql . $limit;
$stmt = $this->_db->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
return $result;
} catch (Exception $error) {
die("Exception Caught!: " . $error->getMessage());
}
}
// count Total News form database
public function countTotalNews()
{
try {
$sql = "SELECT * FROM news";
$stmt = $this->_db->prepare($sql);
$stmt->execute();
$count = $stmt->rowCount();
return $count;
} catch (Exception $error) {
die("Exception Caught!: " . $error->getMessage());
}
}
}
?>
Step 5: Create action file named index.php
We need news layout the structure for our main index page index.php
setRowPerPage($rowperpage);
$newsInfo->setStart($start);
$newsInfo->setPage($page);
$countTotalNews = $newsInfo->countTotalNews();
$newsList = $newsInfo->getNewsList();
include('templates/header.php');
$per_page_html = '';
?>
Create Pagination Using PHP and MySQL
Title
Content
Publish Date
User
';
$pageCount = ceil($countTotalNews / $rowperpage);
if ($pageCount > 1) {
for ($i = 1; $i <= $pageCount; $i++) {
if ($i == $page) {
$per_page_html .= '' . $i . ' ';
} else {
$per_page_html .= '' . $i . ' ';
}
}
}
$per_page_html .= "";
}
echo $per_page_html;
?>
Create header.php and footer.php section of the webpage. The Bootstrap library is used to provide a better UI, so, include it in the header and footer section.
header.php
Create Pagination Using PHP and MySQL | WebHaunt
footer.php