Click here to Skip to main content
15,886,137 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a table that displays the records stored in a database. I have customized my pagination differently from what bootstrap uses to look like phpMyAdmin table pagination. What I have done so far works perfectly but refreshes the page on each user selection. I am stuck on how to use ajax to make the pagination work fine without refreshing the page.

Here's the HTML code to display the table with checkbox, select input and pagination links

            <!--DISPLAY TABLE-->
    <form class="" name="frmDisplay" id="frmDisplay" method="POST" action="">
        <div id="display_table"></div>
            <input type="checkbox" name="check" id="check" onchange=""></input> <label for="check">Show All</label>

                    <label class="clabel">|</label>

                    <label class="clabel" for="rowno">Number of Rows:</label>
                    <select class="" id="rowno" name="rowno" style="width:50px;height:25px;margin-bottom:3px;">
                        <option value='all' hidden disabled>All</option>
                        <?php
                        //set the value of $rowno
                            $rowno = isset($_POST['rowno'])?$_POST['rowno']:5;
                            if($rowno == 5)
                            {
                                $rowno = isset($_GET['limit'])?$_GET['limit']:5;
                            }
                        ?>
                        <option value='5' <?=$rowno==5?'selected':''?>>5</option>
                        <option value='10' <?=$rowno==10?'selected':''?>>10</option>
                        <option value='15' <?=$rowno==15?'selected':''?>>15</option>
                        <option value='20' <?=$rowno==20?'selected':''?>>20</option>
                        <option value='25' <?=$rowno==25?'selected':''?>>25</option>
                        <option value='30' <?=$rowno==30?'selected':''?>>30</option>
                    </select>
                            
                    <label class="clabel">|</label>

                    <label class="clabel" for="filter">Filter Rows:</label>
                    <input class="" style="width:50%;height:25px;margin-bottom:10px;" id="filter" name="filter" placeholder="Filter this table" onkeyup="filtertbl();"></input>
            <div class='table-responsive-sm' style='width:100%;margin-top:10px;'>
                        <?php
                        //code to fetch all records from database on checkbox checked
                        if(isset($_POST['check']))
                            {
                                $sql = "SELECT *FROM evatbl WHERE RegNo = ?";
                                if($stmt = $con->prepare($sql))
                                {
                                    $stmt->bind_param("s", $pregno);
                                    $pregno = $_SESSION['regno'];
                                    $stmt->execute();
                                    $result = $stmt->get_result();
                                    $num_rows = $result->num_rows;
                                    if($num_rows>0)
                                    {
                                        $count = 1;
                                        echo "<table id='t01'' class='table'>
                                        <tr id='tblhead'>
                                        <th>SN</th>
                                        <th>Course Title</th>
                                        <th>Course Code</th>
                                        <th>Credit Unit</th>
                                        <th>Course Lecturer</th>
                                        <th>Rating(%)</th>
                                        </tr>";
                                        while($row = $result->fetch_assoc())
                                        {
                                            $ccode = $row['CourseCode'];
                                            $ctitle = $row['CourseTitle'];
                                            $cunit = $row['CreditUnit'];
                                            $clec = $row['CourseLecturer'];
                                            $crate = $row['Rating'];
                                            echo "
                                            <tr>
                                            <td>$count</td>
                                            <td>$ctitle</td>
                                            <td>$ccode</td>
                                            <td>$cunit</td>
                                            <td>$clec</td>
                                            <td>$crate</td>
                                            </tr>";
                                            $count++;
                                        }
                                    }
                                    else{
                                        echo "<p style='color:darkblue;margin-bottom:0;'>Oops! No records found.</p>";
                                    }
                                }
                            }
                            else
                            {
                                //code for pagination
                                //get current page
                                $currentpage = isset($_GET['currentpage']) ? $_GET['currentpage'] : 1;

                                $no_of_records_per_page = $rowno;
                                $setoff = ($currentpage - 1) * $no_of_records_per_page;

                                //get total number of records in database
                                $sqlcount = "SELECT *FROM evatbl WHERE RegNo = ?";
                                $stmt = $con->prepare($sqlcount);
                                $stmt->bind_param("s", $pregno);
                                $pregno = $_SESSION['regno'];
                                $stmt->execute();
                                $result = $stmt->get_result();
                                $num_rows = $result->num_rows;
                                $totalpages = ceil($num_rows/$no_of_records_per_page);
                                
                                //query for pagination
                                $sqllimit = "SELECT *FROM evatbl WHERE RegNo = ? ORDER BY CourseTitle LIMIT $setoff, $no_of_records_per_page";
                                if ($stmt = $con->prepare($sqllimit))
                                {
                                    $stmt = $con->prepare($sqllimit);
                                    $stmt->bind_param("s", $pregno);
                                    $pregno = $_SESSION['regno'];
                                    $stmt->execute();
                                    $result = $stmt->get_result();
                                    $num_rows = $result->num_rows;
                                    if ($num_rows>0)
                                    {
                                        $count = 1;
                                        echo "<table id='t01' class='table' width='100%'>
                                        <tr id='tblhead'>
                                        <th>SN</th>
                                        <th>Course Title</th>
                                        <th>Course Code</th>
                                        <th>Credit Unit</th>
                                        <th>Course Lecturer</th>
                                        <th>Rating(%)</th>
                                        </tr>";
                                        while($row = $result->fetch_assoc())
                                        {
                                            $ccode = $row['CourseCode'];
                                            $ctitle = $row['CourseTitle'];
                                            $cunit = $row['CreditUnit'];
                                            $clec = $row['CourseLecturer'];
                                            $crate = $row['Rating'];
                                            echo "
                                            <tr>
                                            <td>$count</td>
                                            <td>$ctitle</td>
                                            <td>$ccode</td>
                                            <td>$cunit</td>
                                            <td>$clec</td>
                                            <td>$crate</td>
                                            </tr>";
                                            $count++;
                                        }
                                    }
                                    else{
                                        echo "<p style='color:darkblue;margin-bottom:0;'>Oops! No records found.</p>";
                                    }
                                        echo "</table>";
                                        ?><br>
                                        <div class="nav_div">
                                            <?php
                                        //First Page Button
                                        if($currentpage > 1)
                                        {
                                            echo "<a class='nav_a' href='view_eva.php?limit=".$rowno."¤tpage=".(1)."' title='First'><<</a>";
                                        }
                                        //Previous Page Button
                                        if($currentpage >= 2)
                                        {
                                            echo "<a class='nav_a' href='view_eva.php?limit=".$rowno."¤tpage=".($currentpage - 1)."' title='Previous'><</a>";
                                        }
                                        ?>
                                            
                                        <select class='navno' name='navno' id='navno' onchange="pageNav(this)">
                                        <?php
                                        //Link to available number of pages with select drop-down
                                            for($i = 1; $i <= $totalpages; $i++)
                                            {
                                                echo "<option class='bold'";
                                                if ($currentpage==$i) 
                                                { 
                                                    echo "selected"; 
                                                } 
                                                echo " value='view_eva.php?limit=".$rowno."¤tpage=".$i."'>".$i."</option>";
                                            }
                                        ?>
                                        </select>
                                        
                                        <?php
                                        //Next Page Button
                                        if($currentpage < $totalpages)
                                        {
                                            echo "<a class='nav_a' href='view_eva.php?limit=".$rowno."¤tpage=".($currentpage + 1)."' title='Next'>></a>";
                                        }
                                        //Last Page Button
                                        if($currentpage <= $totalpages - 1)
                                        {
                                            echo "<a class='nav_a' href='view_eva.php?limit=".$rowno."¤tpage=".($currentpage = $totalpages)."' title='Last'>>></a>";
                                        }
                                        ?>
                                        </div>
                                    <?php
                                    }
                                }
                        ?>
                    </form>
                </div>
            </div>


What I have tried:

Here's the javascript that controls form submission on each user selection. I know this is where I'll need to use ajax requests but I can't figure out how. I've been on this for some days now. 

    <script type="text/javascript">
            <?php
                /*Using PHP to create a javascript variable that can be used to
                  add the `selected` attribute to the respective option*/
                printf("let rownum='%s'", empty($_POST['rowno']) ? 0 : $_POST['rowno']);
                ?>
    
                let myForm=document.forms.frmDisplay;
                let mySelect=myForm.rowno;
                let myCheck=myForm.check;
                let myNavSelect=myForm.nav_no;
                
                
                // find all options and if the POSTed value matches - add the selected attribute
                // establish initial display conditions following page load / form submission
                if(rownum)
                {
                    if(rownum=='all') myCheck.checked=true;
                    Array.from(mySelect.querySelectorAll('option')).some(option=>
                    {
                        if(rownum==Number(option.value) || rownum=='all')
                        {
                            option.selected=true;
                            return true;
                        }
                    });
                }
                        
                // listen for changes on the checkbox
                myCheck.addEventListener('click',function(e)
                {
                    if(myCheck.checked)
                    {
    
                        var msg = confirm('Do you really want to see all of the \nrows? For a big table this could crash \nthe browser.');
                        if(!msg)
                        {
                            myCheck.checked=false;
                            return false;
                        }
                    }
    
                    if(mySelect.firstElementChild.value=='all')
                    {
                        mySelect.firstElementChild.selected=this.checked;
                        mySelect.firstElementChild.disabled=!this.checked;
                    }
                    myForm.submit();
                });
    
                // listen for changes on the select  
                mySelect.addEventListener('change',function(e)
                {
                    if(myCheck.checked) myCheck.checked=false;
                    
                    myForm.submit();
                });
    
                //load url on the navigate select
                function pageNav(option)
                {
                    location.href = option.value;
                }
    </script>

Please, I need all the help I can get to complete this project. Thank you. 
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900