Simple Pagination with php-mysql

phptutorial

Simple Pagination with php-mysql

Simple pagination code for PHP developers.
Step 1 :Create database name “pagination” in mysql using phpMyadmin.

--
-- Table structure for table `exclusive_news`
--

CREATE TABLE `exclusive_news` (
  `id` int(2) NOT NULL,
  `post_id` int(6) DEFAULT NULL,
  `date` varchar(25) CHARACTER SET latin1 NOT NULL,
  `title` longtext CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci NOT NULL,
  `content` longtext NOT NULL,
  `media` varchar(500) CHARACTER SET latin1 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

or

Step 2 :import the sql file from db folder of project to database with phpMyadmin.

Logic for pagination 

  • find the total number of records in table. suppose you have 20 records in table.
  • set the how many records want to display in page. suppose 5 records per page.
  • now calculate  total page for pagination : total_record/records_per_page. example : 20/5 . so total page will 4.
  • use $_GET[] method to collect page number.use isset() operator to verify page click.
  • now create html table and set limit in SQL query.see the code/comment for help.
  • use pagination hyperlink code .
  • enjoy the simple php-mysql pagination.
  • If you have any query please comment.
    <!DOCTYPE html>
    <?php 
    include_once './Dbi.php';
    
    ?>
    <html>
        <head>
            <meta charset="UTF-8">
            <title>Pagination Demo by learn.iotwebplanet.com </title>
        </head>
        <body>
            <?php
            
            // Create object ob DB class for db connectivity
             $db=new Dbi();
             $db_con=$db->get_conn();
             mysqli_set_charset($db_con, 'utf8');
             //set data to display per page
             $num_rec_per_page=3;
             
             //checking paging by GET
             
             if (isset($_GET["page"])) 
                    { 
                 $page  = $_GET["page"];   //get value of page
                 
                     } 
             else { 
                      $page=1;    //if first page then set 1
                  }
                  
                  
     //pagination trick or logic              
    $start_from = ($page-1) * $num_rec_per_page; 
    
    //create query to check total record 
    $psql = 'SELECT * FROM exclusive_news'; 
    
    //run query and save result 
    $rs_result=mysqli_query($db_con,$psql);
    
    //count total number of record in result
    $total_records = mysqli_num_rows($rs_result);  
    
    //set how many pagination will be visible totalrecord/record per page
    $total_pages = ceil($total_records / $num_rec_per_page); 
    
    
    //code for display pagination 
    
            echo "<a href='index.php?page=1'>".'|<'."</a> "; // Goto 1st page  
    
    for ($i=1; $i<=$total_pages; $i++) 
            { 
                echo "<a href='index.php?page=".$i."'>".$i."</a> "; 
            } 
    echo "<a href='index.php?page=$total_pages'>".'>|'."</a> "; // Goto last page
    
    //end of pagination code
    
    
    ?>
            <table border="2" width="1" cellspacing="1" cellpadding="1" style="width: 500px;">
                <thead>
                    <tr>
                        <th>id</th>
                        <th>date</th>
                        <th>title</th>
                        
                    </tr>
                </thead>
                <tbody>
                     <?php
               
    //this query for loding data in table based on pagination
                     
               $sql = "SELECT  * FROM exclusive_news LIMIT $start_from, $num_rec_per_page"; 
                $table_data=  mysqli_query($db_con,$sql);
               // $sl=$start_from;
                while ($row = mysqli_fetch_array($table_data)) {
                       
    
    
    ?> 
                    <tr>
                        <td><?php  echo $row['id']; ?></td>
                       <td><?php  echo $row['date']; ?></td>
                         <td><?php  echo $row['title']; ?></td>
                    </tr>
                 <?php 
                 
                }
                 ?>
                </tbody>
            </table>
            
            
    <?php
    
    //code for display pagination 
    
            echo "<a href='index.php?page=1'>".'|<'."</a> "; // Goto 1st page  
    
    for ($i=1; $i<=$total_pages; $i++) 
            { 
                echo "<a href='index.php?page=".$i."'>".$i."</a> "; 
            } 
    echo "<a href='index.php?page=$total_pages'>".'>|'."</a> "; // Goto last page
    
    //end of pagination code
    
    ?>
          
        </body>
    </html>

    Step 3 :Copy project folder to your server www/htdocs folder.
    Step 4 : open url http://localhost/PhpPagination/index.php
    Step 5 : Yeah!

Share this post

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.