How to make pagination with PHP & MYSQL

What is pagination ?

Pagination is a faster and easiest way to navigate website data with numbering system. Generally we use to navigate our data like following:

But Now-a-days we use the smart navigation system like following:

Which is friendly and easy to access any page instantly for your user.

Steps for pagination

1. Connect to your database.
2. Grab your page id into a variable.
3. Make a limit variable for how many data you wanted to show per page.
4. Create a offset variable for starting data from mysql query.
5. Now make your query and get results in a while loop.
6. Count your total data in the mysql table.
7. Calculate total pagination number and making a loop for showing pagination after data.

Now, create a mysql database named pagination with a table named results, and insert some data in it and make a filename connectdb.php to make a connection to our database. A demo database is given below you may follow:

CREATE TABLE IF NOT EXISTS `results` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `position` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

--
-- Dumping data for table `results`
--

INSERT INTO `results` (`id`, `name`, `position`) VALUES
(1, 'MD. Anisur Rahman Bhuyan', 'Web Developer'),
(2, 'John Niloy', 'C.T.O'),
(3, 'Maeen Uddin', 'Software Developer'),
(4, 'Najim Uddin', 'Management'),
(5, 'MD. Maruf', 'Q.M.O'),
(6, 'Raj chy', 'Server Manager'),
(7, 'Monir Ahmed', 'Intern'),
(8, 'Jiya hasan', 'Intern'),
(9, 'Meghla manus', 'H.R.M'),
(10, 'Rakib johan', 'intern'),
(11, 'alex gia', 'Accountant');

Let’s make index.php

<?php
# connect to database
include 'connectdb.php';

# limit data per page
$limit = 5;

# if page_id set, change $start_from data
if(isset($_GET['page_id'])){
   $page_id 	= (int)$_GET['page_id'];
   $start_from = ($page_id-1)*$limit;
} else {
   $page_id = 0;
   $start_from = 0;
}

# make results query
$sql = "SELECT * FROM results LIMIT $start_from, $limit";
$result = $db->query($sql);

if ($result->num_rows > 0) {
    // make output results
    while($row = $result->fetch_assoc()) {
        $name = $row['name'];
        $position = $row['position'];

        echo 'Name: '.$name;
        echo '<br>';
        echo 'Position: '.$position;
        echo'<hr>';
    }
} else {
    echo "No results found";
}

# count total data in results table
$sql2 = "SELECT * FROM results";
$row_query = $db->query($sql2);
$total_row = $row_query->num_rows;

# estimate total pagination number
$total = ceil($total_row/$limit);

# making a loop for pagination
for( $i=1; $i <= $total; $i++ )
{
   if( $i == $page_id ) { echo "<span style='current'>".$i."</span>"; }

   else { echo "<span><a href='?page_id=".$i."'>".$i."</a></span>"; }
}

# close db connection
$db->close();

Now, visit your page and see like this result:

Thank you…
MD. Anisur Rahman Bhuyan
Date: 29/12/2015