PHP: Simple Pagination - Mukesh Chapagain Blog

Facebook Tweet LinkedIn Pin Print EmailShares

This article shows how to create a simple pagination in PHP and MySQL.

In the example code present in this article:

– We will be fetching data from a MySQL database table.
– We will be showing certain rows of data in first page and other rows will be displayed in next pages.
– We will show the page numbers at the bottom of the page.

Let’s suppose our database is test. Let’s create a MySQL database table named users inside database test.


--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

Let’s add/insert some data in table users


--
-- Inserting data for table `users`
--

INSERT INTO `users` (`id`, `name`, `age`, `email`) VALUES
(1, 'Anuradha Koirala', 66, 'anuradha@example.com'),
(2, 'Appa Sherpa', 55, 'apa.sherpa@example.com'),
(3, 'Mahabir Pun', 54, 'mahabir@example.com'),
(4, 'Pushpa Basnet', 25, 'pushpa@example.com'),
(5, 'Laxmi Prasad Devkota', 88, 'laxmi@example.com'),
(6, 'Narayan Gopal', 68, 'gopal@example.com'),
(7, 'Paras Khadka', 29, 'pkhadka@example.com')

Here are the steps to be followed in the PHP code:

1. Connect to the MySQL database table
2. Set a row limit for each page, i.e. number of rows to show on each page
3. Identify page number and offset
4. Count the total number of rows in database table
5. Determine the total number of pages to be displayed
6. Fetch row data from database table using the offset and limit as determined in step 2 and 3 above.
7. Display/Show data
8. Display/Show page numbers using number of pages as determined in step 5 above.

Here’s the full source code:


<?php
// DATABASE CONNECTION
$databaseHost = 'localhost';
$databaseName = 'test';
$databaseUsername = 'root';
$databasePassword = 'root';

$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName); 

// NUMBER OF ROWS TO SHOW PER PAGE
$limit = 3;

// GET PAGE AND OFFSET VALUE
if (isset($_GET['page'])) {
    $page = $_GET['page'] - 1;
    $offset = $page * $limit;
} else {
    $page = 0; 
    $offset = 0;
}

// COUNT TOTAL NUMBER OF ROWS IN TABLE
$sql = "SELECT count(id) FROM users";
$result = mysqli_query($mysqli, $sql);
$row = mysqli_fetch_array($result);
$total_rows = $row[0];

// DETERMINE NUMBER OF PAGES
if ($total_rows > $limit) {
    $number_of_pages = ceil($total_rows / $limit);
} else {
    $pages = 1;
    $number_of_pages = 1;
}

// FETCH DATA USING OFFSET AND LIMIT
$result = mysqli_query($mysqli, "SELECT * FROM users ORDER BY id DESC LIMIT $offset, $limit");
?>

<html>
<head>  
    <title>Homepage</title>
</head>

<body>

    <table width='80%' border=0>

    <tr bgcolor='#CCCCCC'>
        <td>Name</td>
        <td>Age</td>
        <td>Email</td>      
    </tr>
    <?php 
    while($res = mysqli_fetch_array($result)) {         
        echo "<tr>";
        echo "<td>".$res['name']."</td>";
        echo "<td>".$res['age']."</td>";
        echo "<td>".$res['email']."</td>";          
    }
    ?>  
    </table>
    
    <?php
    // SHOW PAGE NUMBERS
    if ($page) {
        echo "<a href='index.php?page=1'>First</a> ";
    }
    for ($i=1;$i<=$number_of_pages;$i++) {
        echo "<a href='index.php?page=$i'>".$i."</a> ";
    }   
    if (($page + 1) != $number_of_pages) {
        echo "<a href='index.php?page=$number_of_pages'>Last</a> ";
    }
    ?>
</body>
</html>

Hope this helps. Thanks.

Related posts: