Adding Pagination (Keep post data) with jQuery

Question

I am looking for a good way to setup pagination with my search form, I want it to display the data based on the amount of entries per page the user selects. This value they input under their search result and it is grabbed via $_POST['searchamount']. I then tried to setup a simple pagination following some tutorials, but I have been stuck with issues as the following pages won't work for showing any data, I believe its because my search script requires the $_POST['search'] value to be true meaning somebudy submit the form, and when clicking on the numbers to scroll through the pages of results, it will just redirect to search.php?page=1 for example. How do I add an additional way so that it can continue the search, while making it secure?

I was reading online there are some cool ways to do it with jQuery, I would prefer to have it like that where it is automatic to display the following results without leaving the page.

Here is an example of what I tried

<?php while($row = mysqli_fetch_array($result)): ?>
    <tr>
        <td><?php echo $row['FactionName'];?></td>
        <td><?php echo $row['MoneyBalanceFormatted'];?></td>
        <td><?php echo $row['SpawnerWorthFormatted'];?></td>
        <td><?php echo $row['BlockWorthFormatted'];?></td>
        <td><?php echo $row['ItemWorthFormatted'];?></td>
        <td><?php echo $row['TotalWorthFormatted'];?></td>
        <td><?php echo $row['RichestMember'];?></td>
    </tr>
<?php endwhile;?>
<?php
    $page_query = "SELECT * FROM  ORDER BY TotalWorth DESC";
    $page_result = mysqli_query($con, $page_query);
    $total_records = mysqli_num_rows($page_result);
    $total_pages = ceil($total_records/$record_per_page);
    $start_loop = $page;
    $difference = $total_pages - $page;
    if($difference <= 5)
    {
     $start_loop = $total_pages - 5;
    }
    $end_loop = $start_loop + 4;
    if($page > 1)
    {
     echo "<a href='search.php?page=1'>First</a>";
     echo "<a href='search.php?page=".($page - 1)."'><<</a>";
    }
    for($i=$start_loop; $i<=$end_loop; $i++)
    {     
     echo "<a href='search.php?page=".$i."'>".$i."</a>";
    }
    if($page <= $end_loop)
    {
     echo "<a href='search.php?page=".($page + 1)."'>>></a>";
     echo "<a href='search.php?page=".$total_pages."'>Last</a>";
    }


    ?>

Although my example doesn't work properly at all, it even displays an error with mysqli_num_rows returning a boolean error.

"it even displays an error with mysqli_num_rows returning a boolean error" That's because you didn't put a table name in your query — Patrick Q, Oct 02 '17 at 21:24
See my answer here https://stackoverflow.com/questions/46055722/is-it-possible-to-do-this-pagination-without-backgrid/46244355#46244355 and adapt what you need accordingly — Mohammed Elhag, Oct 02 '17 at 21:44
how embarassing...I wrote it but deleted it by accident. Thankyou @PatrickQ — user3646954, Oct 02 '17 at 22:12

kharhys · Accepted Answer · 2017-10-02T22:43:30.477

You may be interested in datatables a jquery plugin for tables. Basically, you'd have to remove the pagination logic in you snippet so that only the table rendering bits remain. Then use javascript to enable pagination and entries per page selection on the table thanks to datatables. Check out their website for guides and examples.

I often have this sort of structure in my code. First a loop to populate table header, footer and body with data from SQL query.

    <?php if( isset($report['rows']) && count($report['rows']) > 0 ): ?>
        <div id="report-table-container">
            <table id="report-table" class="uk-table uk-table-hover uk-table-striped" cellspacing="0" width="100%">
                <thead>
                    <tr>
                    <?php foreach( $report['cols'] as $k => $v ): ?>
                        <th> <?= $v ?> </th> 
                    <?php endforeach ?>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                    <?php foreach( $report['cols'] as $k => $v ): ?>
                        <th> <?= $v ?> </th> 
                    <?php endforeach ?>
                    </tr>
                </tfoot> 
                <tbody>
                    <?php foreach($report['rows'] as $index => $entry): ?>
                    <tr>
                        <?php foreach( $report['cols'] as $k => $v ): ?>
                        <td> <?= $entry[$k] ?> </td> 
                        <?php endforeach ?>
                    </tr> 
                    <?php endforeach ?>
                </tbody>        
            </table>
        </div>  
        <?php endif ?>

Note the table has attribute id='report-table'. Use this in your javascript to enable pagination on that table. Like so

<script>
$(document).ready(function() {
    $('#report-table').DataTable({
        dom: 'tipl'
    })
</script>

The parameter dom specifies elements to be added to your table. l for instance specifies a dropdown for selecting number of items per page. Ensure you have imported jQuery and DataTable js and css files.

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. — Jay Blanchard, Oct 02 '17 at 21:42
Thankyou, it looks like a very effective way to do what I was looking for. You matched it spot on what I was looking for. Please tell me though, would this example be what I was looking for with the pagination? https://datatables.net/examples/basic_init/alt_pagination.html Also how will I implement this into my current search script? Should I just start fresh and delete my script and follow this tutorial then recreate the query's after to display the table data in a loop? — user3646954, Oct 02 '17 at 22:16
I have edited the answer to include code snippets. Hope they shed more light on the procedure — kharhys, Oct 02 '17 at 22:45

Adding Pagination (Keep post data) with jQuery

1 Answers1