Question 72332 in Mysql Question posted in
The official MySQL documentation can be found here.

How do I limit the numbers shown in the pagination? – Mysql

This is for a school project this page displays a table which is split into sections of 15 per page, however at the bottom of the table their are the numbers on which page to go to.

My table has 862450 rows meaning there is 57408 numbers shown at the bottom. How would do i limit this to 5 numbers so it looks like this

<< 12345 >>

Any help is greatly appreciated thanks!

<div class="container mt-4">

      

        <div class="row">
            <div class="col-md-12">
                <div class="card">
                    <div class="card-header">
                        <h4>List of Regulated Pools
                      
                        </h4>
                        <div class="btn-group">
  <a href="Databaseone.php" class="btn btn-primary active" aria-current="page"> Regulated Pool Register</a>
  <a href="Databasetwo.php" class="btn btn-primary">Certified Safety Pools</a>
  <a href="Databasethree.php" class="btn btn-primary">Registered Pool Inspectors</a>
</div>
                    </div>
                    <div class="card-body">

                        <table class="table table-bordered table-striped">
                            <thead>
                                <tr>
                                    <th>ID</th>
                                    <th>Site Name</th>
                                    <th>Unit Number</th>
                                    <th>Street Number</th>
                                    <th>Street Name</th>
                                    <th>Street Type</th>
                                    <th>Suburb</th> 
                                     <th>Post Code</th> 
                                    <th>Nubmer of Pools</th>
                                    <th>Local Goverment Authority</th>
                                    <th>Shared Pool</th>
                                  
                                </tr>
                            </thead>
                            <tbody>
                            
                                <?php
       
                                require('conn.php');
                                $num_per_page=15;
                                
                                if(isset($_GET["page"]))
                                {
                                    $page=$_GET["page"];
                                }
                                else
                                {
                                    $page=1;
                                }
                                
                                $start_from=($page-1)*15;
                                
                               
                                $query="select * from norm_pool limit $start_from,$num_per_page";
                                $result= mysqli_query($conn, $query);
                                    
                                  
                                    
                                    if(mysqli_num_rows($result) > 1 )
                                    {
                                            while($row = $result->fetch_assoc()) 
                                        {
                                            ?>
                                            <tr>
                                                 <td><?= $row['ID']; ?></td>
                                                <td><?= $row['sitename']; ?></td>
                                                <td><?= $row['unitno']; ?></td>
                                                <td><?= $row['streetno']; ?></td>
                                                <td><?= $row['streetname']; ?></td>
                                                <td><?= $row['streettype']; ?></td>
                                                <td><?= $row['suburb']; ?></td>
                                                <td><?= $row['postcode']; ?></td>
                                                <td><?= $row['noofpools']; ?></td>
                                                <td><?= $row['localgovauth']; ?></td>
                                                <td><?= $row['sharedpool']; ?></td>
                                        
                                               
                                            </tr>
                                            <?php
                                        }
                                    }
                                    else
                                    {
                                        echo "<h5> No Users Found </h5>";
                                    }
                                 
                                ?>
                                
                                
                            </tbody>
                        </table>
                        <?php 
                        require('conn.php');
                                $query='select * from norm_pool';
                                $result= mysqli_query($conn, $query);
                        $total_records=mysqli_num_rows($result);
                        $total_pages=ceil($total_records/$num_per_page);
                        
                        for($i=1;$i<=$total_pages;$i++)
                        {
                            echo "<a href='Databaseone.php?page=".$i."'>".$i."</a>";
                        }
                        ?>
                    </div>
                </div>
            </div>
        </div>
    </div>

I have tried multiple solutions and none have had any success as all the solutions have very different code to mine

Tags:

2

Answers


  1. 0

    here is one way to do it

    please have a look at the code below:

    //get the current page
$pagenumber = $_GET['page'];


                        $query='select * from norm_pool';
                        $result= mysqli_query($conn, $query);

                      
                        $total_records=mysqli_num_rows($result);
                       
                        $total_pages=ceil($total_records/$num_per_page);
                         
                        //link for the first page
                        echo "<a class='btn btn-warning m-2' href='Databaseone.php?page=1'> < First Page > </a>";

                        //As we want only 5 link in pagination hence we can itrate it only 5 times
                        for($i=1;$i<=5;$i++)
                        {
                            //incrementing page number before because we do not want to include current page.
                            $pagenumber++;
                            echo "<a class='btn btn-primary m-2' href='Databaseone.php?page=".$pagenumber."'>".$pagenumber."   </a>";

                        }
    //link for the last page
                        echo "<a class='btn btn-danger m-2' href='Databaseone.php?page=$total_pages'> < Last Page > </a>";

    Result:(I believe as you are using bootstrap hence i added some )
    enter image description here

    Login or Signup to reply.
  2. 0

    To limit the numbers shown in the pagination, you can use the concept of "ellipsis" or "dots" to indicate that there are more pages available beyond what is currently displayed. Here are some steps you can follow:

    1. Determine the maximum number of pages you want to display at any given time.
    2. Calculate the current page’s position relative to the total number of pages.
    3. If the current page is near the beginning or end of the pagination, display the maximum number of pages allowed.
    4. If the current page is somewhere in the middle, display the maximum number of pages allowed, with ellipsis or dots to indicate there are more pages available.
    5. When the user clicks on the ellipsis, update the pagination to display the next set of pages.

    For example, let’s say you want to display a maximum of 5 pages at any given time, and you’re currently on page 10 out of a total of 20 pages. The pagination would display something like this:

    1 2 … 9 10 11 … 19 20

    The ellipsis indicates that there are more pages available beyond what is currently displayed, and when the user clicks on it, the pagination updates to display the next set of pages.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search