How to add pagination to existing code?

Question

I finish my code with listing some items from DB but now i must pagination them because will overload the page. so here is part of my code

 <!--elements to display-->
                                  <form method="get" action="?">
                            <div class="underlineInput">
                                <span>Display</span>

                                <select class="pageSelect" name="per_page" onchange="this.form.submit()">
                                    <option>25</option>
                                    <option>50</option>
                                    <option>100</option>
                                    <option>500</option>
                                    <option>all</option>
                                </select>

                                <span>  items per page</span>
                            </div>
         </form>
                        </div>
                    </div>
                    <!--table-->

                    <table class="table">
                        <thead>
                            <tr>
                                <th>
                                    <div class="handle" style="border-color:#0fb175;"></div>
                                    <div class="thContent"> <a href="" class="">ID</a></div>
                                </th>
                                <th>
                                    <div class="handle" style="border-color:#b10f46;"></div>
                                    <div class="thContent"> <a href="" class="">Username</a></div>
                                </th>
                                <th>
                                    <div class="handle" style="border-color:#0fb175;"></div>
                                    <div class="thContent"> <a href="" class="">Cod</a></div>
                                </th>
                                <th>
                                    <div class="handle" style="border-color:#0f53b1;"></div>
                                    <div class="thContent"> <a href="" class="">Email</a></div>

                                </th>
                                <th>
                                    <div class="handle" style="border-color:#0fb175;"></div>
                                    <div class="thContent"> <a href="" class="">Roles</a></div>
                                </th>
                                <th>
                                    <div class="handle" style="border-color:#0fb175;"></div>
                                    <div class="thContent"> <a href="" class="">Activated</a></div>
                                </th>
                                <th class="underlineInput">
                                    <div class="handle" style="border-color:#cfb01d;"></div>
                                    <div class="thContent">Actiuni</div>
                                </th>
                            </tr>
                        </thead>
                        <tbody>
                        <form id="delete_alll" method="post">
                        <?php 





                        $sql = "SELECT * FROM `admins` WHERE `username` = '".$_SESSION['logged_in']."'";
                        $adimin_logat = mysqli_query($db_connect, $sql);
                        $row = mysqli_fetch_assoc($adimin_logat);
                        //afisteaza rolurile mai mare decat admin care e 1 in functie de userul logat
                        $sql = "SELECT * FROM `admins` WHERE `roles` > ".$row['roles']." "; 

                        if(isset($_POST['search_user'])){
                            $sql="SELECT * FROM `admins` WHERE `roles` > ".$row['roles']." AND `username` = '".$_POST['srch_usr']."'";  
                        }


                        $connect = mysqli_query($db_connect, $sql);

            $counter = 0;



            if(isset($_GET['per_page'])){
                $resultPerPage = $_GET['per_page'];
            }
            else{
                $resultPerPage = 6;
            }
            $count = 0;

            while (($item = mysqli_fetch_array($connect)))  
        {
            if ($counter % 2) {
                $bgcolor = '#D3D3D3';
            } else {
                $bgcolor = '#A8A8A8';
            }

            $counter++;
            $count++;

                        ?>


                            <tr>

                                <td bgcolor="<?= $bgcolor ?>" ><input type="checkbox" name="check_dell_all[]" value="<?= $item['id'] ?>" /><?= $item['id'] ?></td>

                                <td bgcolor="<?= $bgcolor ?>" ><?= $item['username'] ?></td>
                                <td bgcolor="<?= $bgcolor ?>"><?= $item['code'] ?></td>
                                <td bgcolor="<?= $bgcolor ?>"><?= $item['email'] ?></td>
                                <td bgcolor="<?= $bgcolor ?>"><?php

if ($item['roles'] == 0)
{
    echo "Root";
}
if ($item['roles'] == 1)
{
    echo "Admin";
}
if ($item['roles'] == 2)
{
    echo "Editor";
}
if ($item['roles'] == 3)
{
    echo "Something_else";
}
                                ?></td>
                                <td bgcolor="<?= $bgcolor ?>">

                                <?php

if ($item['activated'] == '0')
{
    echo "No";
}
if ($item['activated'] == '1')
{
    echo "Yes";
}
                        ?>
</td>
                                <td bgcolor="<?= $bgcolor ?>" class="underlineInput">
                                    <select class="focusSelect" onChange="window.document.location.href=this.options[this.selectedIndex].value;" value="GO">

                                        <option selected="selected"></option>
                                        <option value="admin.php?action=edit&admin_id=<?= $item['id'] ?>">Edit</option>
                                        <option value="admin.php?action=delete&admin_id=<?= $item['id'] ?>">Sterge</option>
                                        <option value="admin.php?action=activate&admin_id=<?= $item['id'] ?>">Activare</option>   
                                    </select>
                                </td>
                            </tr>
                            <?php


    }
                            ?>
                        </tbody>
                        </form>

and is display like this !

My question is how i can paginate this, whn i click on the nr of page (wich will be generated by row number split to how many to show) with simple get times ?page=2&items_toshow=25 and must show the 25 to 50 items. Can you help me ? Thanks.

Answer 1

Applying pagination to existing listing is quite simple.

I can explain you the pseudo logic for this:

1) Get the total count of records. You need 2 sqls, one for count and one for actual records explained in last some points.

2) Add a variable number of pages. (For example 20)

3) Divide total records by records per page.

Add ceil() function to it.

Loop over starting from 1 to numbe of pages.

Put anchor link in it pointing it to current page with number.

Get page number from url and filter records by applying limit.