Saturday, May 5, 2018

Simple Pagination in PHP

Today I am going to show you how to paginate mysql data in php. This is going to be very simple basic pagination. We will extract recrods from our employees table and paginate them.

Simple Pagination in PHP
Lets Start with creating our table and inserting records for pagination. employees.sql contains the data for employees table, index.php is our main file which is going to show paginated data and style.css will be handling all the styles for webpage and pagination.

employees.sql

DROP TABLE IF EXISTS `employees`;
CREATE TABLE IF NOT EXISTS `employees` (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` text,
  `email` varchar(255) DEFAULT NULL,
  `gender` tinytext,
  `designation` varchar(100) DEFAULT NULL,
  `image` text,
  PRIMARY KEY (`employee_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

INSERT INTO `employees` (`employee_id`, `fullname`, `email`, `gender`, `designation`, `image`) VALUES
(1, 'Art Venere', '[email protected]', 'Male', 'Salesman', NULL),
(2, 'Anderson Thomas', '[email protected]', 'Male', 'Salesman', NULL),
(3, 'Darell Garcia', '[email protected]', 'Male', 'Data Entry Operator', NULL),
(4, 'Alisha Watson', '[email protected]', 'Female', 'Data Entry Operator', NULL),
(5, 'Catherine Wilson', '[email protected]', 'Female', 'Data Entry Operator', NULL),
(6, 'Andrew Willis', '[email protected]', 'Male', 'Human Resource Manager', NULL),
(7, 'Jenna Gibson', '[email protected]', 'Female', 'Customer Relationship Officer', NULL),
(8, 'Stephen Dorf', '[email protected]', 'Female', 'Store Keeper', NULL),
(9, 'Angela Parsons', '[email protected]', 'Female', 'Accountant', NULL),
(10, 'George Orwell', '[email protected]', 'Male', 'Accounts Manager', NULL),
(11, 'Graciela Motley', '[email protected]', 'Female', 'Assistant Manager', NULL);

define.php

<?php
define("SITE_URL", "http://" . $_SERVER['SERVER_NAME']);
define("DB_HOST", "DATABASE HOST");  // Your database host
define("DB_NAME", "DATABASE NAME");  // Your database name
define("DB_USER", "USERNAME");  // Username for database 
define("DB_PASSWORD", "PASSWORD"); // Password for database 
?>

index.php

<?php
include("define.php");

$dbcon = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die(mysqli_connect_error());
$current_page = isset($_GET['page']) && intval($_GET['page']) ? $_GET['page'] : 1;
$query = mysqli_query($dbcon, "SELECT * FROM employees");
$total = mysqli_num_rows($query);
$items_per_page = 3; //Records to show per page
$num_of_pages = ceil($total / $items_per_page); // Number of pages to show in pagination
$page_url = SITE_URL . '/simple-pagination-in-php/?page=';

if ($current_page >= $num_of_pages) {
    $next = $num_of_pages;
    $current_page = $num_of_pages;
} else {
    $next = $current_page + 1;
}

if ($current_page <= 1) {
    $prev = 1;
    $current_page = 1;
} else {
    $prev = $current_page - 1;
}
$limit_start = ($current_page - 1) * $items_per_page; // Where to start in database records
$query = mysqli_query($dbcon, "SELECT * FROM employees LIMIT $limit_start,$items_per_page");

while ($row = mysqli_fetch_assoc($query)) {
    $employees[] = $row;
}
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Simple Pagination in PHP</title>
        <meta content='text/html; charset=UTF-8' http-equiv='Content-Type'/>
        <link rel="stylesheet" href="css/style.css" />
    </head>
    <body>
        <div class="main-container">
            <?php
            if (!empty($employees)) {
                foreach ($employees as $employee) {
                    $image = empty($employee['employee_image']) ? 'no-image.png' : $employee['employee_image'];
                    ?>
                    <div class="employee-row">
                        <div class="employee-image">
                            <img src="images/<?php echo $image ?>" class="image-responsive" />
                        </div>
                        <div class="employee-details">
                            <div><?php echo $employee['fullname'] ?></div>
                            <div><?php echo $employee['email'] ?></div>
                            <div><?php echo $employee['designation'] ?></div>
                            <div><?php echo $employee['gender'] ?></div>
                        </div>
                    </div>
                    <?php }
                ?>
                <div class="pagination-container">
                    <ul class="pagination">
                        <li><a href="<?php echo $num_of_pages > 1 ? $page_url . $prev : 'javascript:;' ?>" class="btn btn-default pager-prev">Prev</a></li>
                        <?php
                        for($i = 1; $i <= $num_of_pages; $i++) {
                            $href = $i== $current_page ? 'javascript:;' : $page_url . $i;
                            ?>
                            <li class="<?php echo $i== $current_page ? 'active' : ''; ?>">
                                <a href="<?php echo $href; ?>" class="btn btn-default"><?php echo $i; ?></a>
                            </li>
                            <?php
                        }
                        ?>

                        <li><a href="<?php echo $num_of_pages > 1 ? $page_url . $next : 'javascript:;' ?>" class="btn btn-default pager-next">Next</a></li>
                    </ul>
                </div>
            <?php
            } else {
                echo 'No Records Found';
            }
            ?>
        </div>
    </body>
</html>

style.css

*{
    box-sizing: border-box;
}
html,body{
    margin: 0px;
    padding: 0px;
}
body{
    background: #f0f0f0;
    font: normal normal 14px Open Sans,Verdana, Arial;
}
a:link {
    text-decoration: none;
    color: #3778cd;
}
.main-container{
    max-width: 1024px;
    margin: 0px auto;
}
.extract-wrapper{
    margin: 20px 0px;
}
ul.pagination{
    padding: 0px;
    margin: 0px;
    list-style: none;
    float: left;
}
ul.pagination li{
    background: #fff;
    color: #3778cd;
    display: inline;
    margin: 5px auto;
}
ul.pagination > li.active > a{
    background: #3778cd;
    color: #fff;
}
ul.pagination > li > a,
ul.pagination > li > span{
    float: left;
    margin-left: -1px;
}
.employee-row:after{
    content:"";
    display: table;
    clear: both;
}
.employee-row{
    background: #fff;
    color: #333;
    padding: 10px;
    margin-bottom: 15px;
    box-shadow: 0px 0px 2px rgba(100,100,100,0.5);
    clear:both;
}
.image-responsive{
    width: 100%;
    height: auto;
}
.employee-image, .employee-details{
    float:left;
}
.employee-image{
    width: 100px;
    height:100px;
    overflow:hidden;
}
.employee-details{
    padding-left: 10px;
}
.btn{
    display: inline-block;
    padding: 5px 10px;
    border: 1px solid #ddd;
}
.btn-default{
    background: #fff;
}