Skip to main content

Simple Pagination Using PHP and MySQLi

 

Simple Pagination Using PHP and MySQLi

Database
CREATE TABLE `users` (
  `name` varchar(250) NOT NULL,
  `age` varchar(250) NOT NULL,
  `department` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

db.php

<?php

$con = mysqli_connect("localhost","root","","users");
	if (mysqli_connect_errno()){
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
		die();
		}
?>

index.php

<html>
<head>
<title>Create Simple Pagination Using PHP and MySQLi </title>
<link rel="stylesheet" href="css/bootstrap.min.css">
</head>
<body>
<div style="width:700px; margin:0 auto;">

<h3>Simple Pagination Using PHP and MySQLi</h3>
<table class="table table-striped table-bordered">
<thead>
<tr>
<th style='width:50px;'>ID</th>
<th style='width:150px;'>Name</th>
<th style='width:50px;'>Age</th>
<th style='width:150px;'>Department</th>
</tr>
</thead>
<tbody>
<?php
include('db.php');

if (isset($_GET['page_no']) && $_GET['page_no']!="") {
	$page_no = $_GET['page_no'];
	} else {
		$page_no = 1;
        }

	$total_records_per_page = 3;
    $offset = ($page_no-1) * $total_records_per_page;
	$previous_page = $page_no - 1;
	$next_page = $page_no + 1;
	$adjacents = "2"; 

	$result_count = mysqli_query($con,"SELECT COUNT(*) As total_records FROM `pagination_table`");
	$total_records = mysqli_fetch_array($result_count);
	$total_records = $total_records['total_records'];
    $total_no_of_pages = ceil($total_records / $total_records_per_page);
	$second_last = $total_no_of_pages - 1; // total page minus 1

    $result = mysqli_query($con,"SELECT * FROM `pagination_table` LIMIT $offset, $total_records_per_page");
    while($row = mysqli_fetch_array($result)){
		echo "<tr>
			  <td>".$row['id']."</td>
			  <td>".$row['name']."</td>
	 		  <td>".$row['age']."</td>
		   	  <td>".$row['dept']."</td>
		   	  </tr>";
        }
	mysqli_close($con);
    ?>
</tbody>
</table>

<div style='padding: 10px 20px 0px; border-top: dotted 1px #CCC;'>
<strong>Page <?php echo $page_no." of ".$total_no_of_pages; ?></strong>
</div>

<ul class="pagination">
	<?php // if($page_no > 1){ echo "<li><a href='?page_no=1'>First Page</a></li>"; } ?>
    
	<li <?php if($page_no <= 1){ echo "class='disabled'"; } ?>>
	<a <?php if($page_no > 1){ echo "href='?page_no=$previous_page'"; } ?>>Previous</a>
	</li>
       
    <?php 
	if ($total_no_of_pages <= 10){  	 
		for ($counter = 1; $counter <= $total_no_of_pages; $counter++){
			if ($counter == $page_no) {
		   echo "<li class='active'><a>$counter</a></li>";	
				}else{
           echo "<li><a href='?page_no=$counter'>$counter</a></li>";
				}
        }
	}
	elseif($total_no_of_pages > 10){
		
	if($page_no <= 4) {			
	 for ($counter = 1; $counter < 8; $counter++){		 
			if ($counter == $page_no) {
		   echo "<li class='active'><a>$counter</a></li>";	
				}else{
           echo "<li><a href='?page_no=$counter'>$counter</a></li>";
				}
        }
		echo "<li><a>...</a></li>";
		echo "<li><a href='?page_no=$second_last'>$second_last</a></li>";
		echo "<li><a href='?page_no=$total_no_of_pages'>$total_no_of_pages</a></li>";
		}

	 elseif($page_no > 4 && $page_no < $total_no_of_pages - 4) {		 
		echo "<li><a href='?page_no=1'>1</a></li>";
		echo "<li><a href='?page_no=2'>2</a></li>";
        echo "<li><a>...</a></li>";
        for ($counter = $page_no - $adjacents; $counter <= $page_no + $adjacents; $counter++) {			
           if ($counter == $page_no) {
		   echo "<li class='active'><a>$counter</a></li>";	
				}else{
           echo "<li><a href='?page_no=$counter'>$counter</a></li>";
				}                  
       }
       echo "<li><a>...</a></li>";
	   echo "<li><a href='?page_no=$second_last'>$second_last</a></li>";
	   echo "<li><a href='?page_no=$total_no_of_pages'>$total_no_of_pages</a></li>";      
            }
		
		else {
        echo "<li><a href='?page_no=1'>1</a></li>";
		echo "<li><a href='?page_no=2'>2</a></li>";
        echo "<li><a>...</a></li>";

        for ($counter = $total_no_of_pages - 6; $counter <= $total_no_of_pages; $counter++) {
          if ($counter == $page_no) {
		   echo "<li class='active'><a>$counter</a></li>";	
				}else{
           echo "<li><a href='?page_no=$counter'>$counter</a></li>";
				}                   
                }
            }
	}
?>
    
	<li <?php if($page_no >= $total_no_of_pages){ echo "class='disabled'"; } ?>>
	<a <?php if($page_no < $total_no_of_pages) { echo "href='?page_no=$next_page'"; } ?>>Next</a>
	</li>
    <?php if($page_no < $total_no_of_pages){
		echo "<li><a href='?page_no=$total_no_of_pages'>Last &rsaquo;&rsaquo;</a></li>";
		} ?>
</ul>



</div>
</body>
</html>


Labels:

Comments

Post a Comment

Popular posts from this blog

Insert Fetch Update Delete Mysql data in Codeigniter

Database -- -- Table structure for table `tbl_user` -- CREATE TABLE IF NOT EXISTS `tbl_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(200) NOT NULL, `last_name` varchar(200) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=95 ; -- -- Dumping data for table `tbl_user` -- INSERT INTO `tbl_user` (`id`, `first_name`, `last_name`) VALUES (91, 'Harold', 'Jones'), (89, 'Christine', 'Smith'), (88, 'Marker', 'Angela'), (87, 'Romeo', 'Mary'), (86, 'Smith', 'John'); Controller - main.php <?php defined('BASEPATH') OR exit('No direct script access allowed'); class Main extends CI_Controller { //functions public function index(){ $this->load->model("main_model"); $data["fetch_data"] = $this->main_model->...
Post a Comment
Read more

How to Generate Excel File in Codeigniter using PHPExcel

Libraries/Excel.php <?php if (!defined('BASEPATH')) exit('No direct script access allowed'); require_once('PHPExcel.php'); class Excel extends PHPExcel { public function __construct() { parent::__construct(); } } ?> Libraries/IOFactory.php <?php if (!defined('BASEPATH')) exit('No direct script access allowed'); require_once('PHPExcel/IOFactory.php'); class IOFactory extends PHPExcel_IOFactory { public function __construct() { parent::__construct(); } } ?> Controllers/Excel_export.php <?php defined('BASEPATH') OR exit('No direct script access allowed'); class Excel_export extends CI_Controller { function index() { $this->load->model("excel_export_model"); $data["employee_data"] = $this->excel_export_model->fetch_data(); $this->load->view("excel_export_view", $data); } function action() { $this->load->model(...
Post a Comment
Read more

Core php login logout and register view delete

Core php login logout and register view delete Database.php CREATE TABLE IF NOT EXISTS register.`users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `email` varchar(50) NOT NULL, `password` varchar(50) NOT NULL, `trn_date` datetime NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE IF NOT EXISTS register.`new_record` ( `id` int(11) NOT NULL AUTO_INCREMENT, `trn_date` datetime NOT NULL, `name` varchar(50) NOT NULL, `age`int(11) NOT NULL, `submittedby` varchar(50) NOT NULL, PRIMARY KEY (`id`) ); Auth.php <?php session_start(); if(!isset($_SESSION["username"])){ header("Location: login.php"); exit(); } ?> Dashbaord .php <!DOCTYPE html> <html lang="en"> <head> <title>Dashboard</title> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="https://maxcdn.boo...
Post a Comment
Read more