Pagination using PHP

In this tutorial, we’re going to make pagination using PHP.

Pagination allows us to show an only limited number of records if we had the large collection of data. By this, we not to fetch all data from Tables.

Pagination using PHP


The Data Source

Here, is our data that we going to use in our pagination.

id emp_name Salary
1 Yogesh 30000
2 Vishal 28000
3 Vijay 35000
4 Rahul 25000
5 Sonarika 50000
6 Jitentendre 48000
7 Aditya 36000
8 Anil 32000
9 Sunil 48000
10 Akilesh 52000
11 Raj 48000
12 Mayank 54000

In this example, we going to use MySQL , and our name of the database is tutorial and table name is an employee.

 


Contents

  1. HTML
  2. CSS
  3. PHP

 


1.HTML

First, we’re going to create our HTML structure. We created a <div> with id="content" inside it, we created a table and a form.

On the table, we only created a single row, which contains columns heading (S.no, Name, Salary).

<table width="100%" id="emp_table" border="0">
    <tr class="tr_header">
       <th>S.no</th>
       <th>Name</th>
       <th>Salary</th>
    </tr>

</table>

We set form method="post" and inside form we created a <div id="div_pagination">. <div > contains two hidden fields and Next and Previous Button. First, hidden field name="row" we the set default value to 0 and second, hidden field name="allcount" we also set its default value to 0 ( it will contain the total number of rows which we take out using PHP) .

<form method="post" action="">
    <div id="div_pagination">
        <input type="hidden" name="row" value="0">
        <input type="hidden" name="allcount" value="0">
        <input type="submit" class="button" name="but_prev" value="Previous">
        <input type="submit" class="button" name="but_next" value="Next">
    </div>
</form>

Completed Code

<div id="content">
    <table width="100%" id="emp_table" border="0">
        <tr class="tr_header">
            <th>S.no</th>
            <th>Name</th>
            <th>Salary</th>
        </tr>

    </table>
    <form method="post" action="">
        <div id="div_pagination">
            <input type="hidden" id="row" name="row" value="0">
            <input type="hidden" id="allcount" name="allcount" value="0">
            <input type="submit" class="button" name="but_prev" value="Previous">
            <input type="submit" class="button" name="but_next" value="Next">
        </div>
    </form>
</div>

2.CSS

Let’s add some styling over our HTML structure.

Add border: 3px solid lavender to the table and set its border-radius:3px. Set background-color: dodgerblue to header row. Change color of <th> tag text to white, added padding:10px 0px.

/* Table */
#emp_table {
    border:3px solid lavender;
    border-radius:3px;
}

/* Table header */
.tr_header{
    background-color:dodgerblue;
}
.tr_header th{
    color:white;
    padding:10px 0px;
    letter-spacing: 1px;
}

Changing even row background-color:lavender and text color to black.

#emp_table tr:nth-child(even){
    background-color:lavender;
    color:black;
}

We set buttons border:0px, added background-color:mediumpurple and color:white.

.button{
    border-radius:3px;
    border:0px;
    background-color:mediumpurple;
    color:white;
    padding:10px 20px;
    letter-spacing: 1px;
}

3.PHP

Connection

First, we need to create a connection and selecting Database. In this tutorial, we are going to use the tutorial database. I created the separate file and name it config.php and save connection code on it.

$host = "localhost";    /* Host name */
$user = "root";         /* User */
$password = "";         /* Password */
$dbname = "tutorial";   /* Database name */
$con = mysql_connect($host, $user, $password) or die("Unable to connect");

$db = mysql_select_db($dbname, $con) or die("Database not found");

On index.php we include "config.php" file in <head> section.

Getting data

We created a connection to our database and included config file successfully. Now we are fetching data from Table "employee".

We declare two PHP variables $rowperpage and $row. Here, we are using $rowperpage for how much rows we are going to fetch at a time. In this example, we are going to show 5 rows on the page (we can change its value to 10,20, etc). And we set $row=0.

$rowperpage = 5;
$row = 0;

Now we have decided how much rows we are going to fetch and starting position. We use both of these values in our SQL query. First, we count the total number of rows in the table and storing it in $allcount variable.

$sql = "SELECT COUNT(*) AS cntrows FROM employee";
$result = mysql_query($sql);
$fetchresult = mysql_fetch_array($result);
$allcount = $fetchresult['cntrows'];

Storing $allcount and $row to hidden fields.

<input type="hidden" name="row" value="<?php echo $row; ?>">
<input type="hidden" name="allcount" value="<?php echo $allcount; ?>">

Selecting rows from empoyee table using LIMIT in SELECT query. On LIMIT we use $row and $rowperpage which we have defined in <head>. We change the value of $row when Button gets clicked. We execute our select query and we are using while for looping. Inside the while loop, we fetch emp_name and salary and creating a new row and use those values in <td>.

// selecting rows
$sql = "SELECT * FROM employee  ORDER BY ID ASC LIMIT $row,".$rowperpage;
$result = mysql_query($sql);
$sno = $row + 1;
while($fetch = mysql_fetch_array($result)){
    $name = $fetch['emp_name'];
    $salary = $fetch['salary'];
    ?>
    <tr>
        <td align='center'><?php echo $sno; ?></td>
        <td align='center'><?php echo $name; ?></td>
        <td align='center'><?php echo $salary; ?></td>
    </tr>
<?php
    $sno ++;
}
?>

On Button Click

When Next Button is getting clicked, we need to fetch next 5 rows from the table. We are going to add $row with $rowperpage and check that it’s less that $allcount or not. If its less than $allcount then change the value of $row.

// Next Button
if(isset($_POST['but_next'])){
    $row = $_POST['row'];
    $allcount = $_POST['allcount'];

    $val = $row + $rowperpage;
    if( $val < $allcount ){
        $row = $val;
    }
}

When previous Button is getting clicked, then $row we subtract $row with $rowperpage and check if $row is less than 0 or not. If it is less than 0 then we set $row value to 0

// Previous Button
if(isset($_POST['but_prev'])){
    $row = $_POST['row'];
    $row -= $rowperpage;
    if( $row < 0 ){
        $row = 0;
    }
}

Complete Code

<!doctype html>
<html>
    <head>
        <link href="style.css" type="text/css" rel="stylesheet">
        <?php
            include("config.php");

            $rowperpage = 5;
            $row = 0;

            // Previous Button
            if(isset($_POST['but_prev'])){
                $row = $_POST['row'];
                $row -= $rowperpage;
                if( $row < 0 ){
                    $row = 0;
                }
            }

            // Next Button
            if(isset($_POST['but_next'])){
                $row = $_POST['row'];
                $allcount = $_POST['allcount'];

                $val = $row + $rowperpage;
                if( $val < $allcount ){
                    $row = $val;
                }
            }
        ?>
    </head>
    <body>
    <div id="content">
        <table width="100%" id="emp_table" border="0">
            <tr class="tr_header">
                <th>S.no</th>
                <th>Name</th>
                <th>Salary</th>
            </tr>
            <?php
            // count total number of rows
            $sql = "SELECT COUNT(*) AS cntrows FROM employee";
            $result = mysql_query($sql);
            $fetchresult = mysql_fetch_array($result);
            $allcount = $fetchresult['cntrows'];

            // selecting rows
            $sql = "SELECT * FROM employee  ORDER BY ID ASC limit $row,".$rowperpage;
            $result = mysql_query($sql);
            $sno = $row + 1;
            while($fetch = mysql_fetch_array($result)){
                $name = $fetch['emp_name'];
                $salary = $fetch['salary'];
                ?>
                <tr>
                    <td align='center'><?php echo $sno; ?></td>
                    <td align='center'><?php echo $name; ?></td>
                    <td align='center'><?php echo $salary; ?></td>
                </tr>
            <?php
                $sno ++;
            }
            ?>
        </table>
        <form method="post" action="">
            <div id="div_pagination">
                <input type="hidden" name="row" value="<?php echo $row; ?>">
                <input type="hidden" name="allcount" value="<?php echo $allcount; ?>">
                <input type="submit" class="button" name="but_prev" value="Previous">
                <input type="submit" class="button" name="but_next" value="Next">
            </div>
        </form>
    </div>
    </body>
</html>
Spread the love

Related Post

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *