How to fix pagination problem with filter in PHP and SQL?

Question

PHP enthusiast here..

I have a script where it does a query and brings the entire database rows into table cells and it's paginated.

The problem is when a user types something in the search box to filter the pagination, it only works in page 1 showing the filtered results but then it stops working on page 2, on page 2 is goes back to all the paginated results and ignores the filter.

How do I bring the filter value into the url so it stays throughout the pagination?

Sorry if it's obvious I just can't figure it out

<?php require('includes/config.php');

//if not logged in redirect to login page
if(!$user->is_logged_in()){ header('Location: index.php'); exit(); }

//include header template
require('layout/header.php'); 

 $stmt = $db->query("SELECT name, last_name FROM `designers` WHERE `username` = '" . $_SESSION['username'] . "'");
 $result1 = $stmt->fetch(PDO::FETCH_ASSOC); 
 $name = $result1['name'];
 $last_name = $result1['last_name'];

      if (isset($_GET['pageno'])) {
        $pageno = $_GET['pageno'];
    } else {
        $pageno = 1;
    }
    $no_of_records_per_page = 15;
    $offset = ($pageno-1) * $no_of_records_per_page;
    $conn=mysqli_connect("xxxx", "xxxx", "xxxx", "xxxx");
    $total_pages_sql = "SELECT COUNT(*) FROM protonumbers WHERE designer LIKE '".$name.'_'.$last_name."'";
    $result = mysqli_query($conn,$total_pages_sql);
    $total_rows = mysqli_fetch_array($result)[0];
    $total_pages = ceil($total_rows / $no_of_records_per_page);

?>
<style>
table {
border-collapse: collapse;
width: 100%;
}

th, td {
text-align: center;
padding: 8px;
}

tr:nth-child(even){background-color: #f2f2f2}

th {
background-color: #428bca;
color: white;
}
</style>
<div class="container">

<div class="row">
    <div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">
        <form action="search.php" autocomplete="off" method="post">
            <h3>My Proto Numbers</h3><hr><br>
                            <?php
            //check for any errors
            if(isset($error)){
                foreach($error as $error){
                    echo '<p class="bg-danger">'.$error.'</p>';
                }
            }
            ?>
            </div>
            </div>
     </div>
            <?php

if(isset($_POST['search']))
{
$valueToSearch = $_POST['valueToSearch'];
// search in all table columns
// using concat mysql function
$query = "SELECT * FROM `protonumbers` WHERE UPPER(CONCAT(`brand`, `protoID`, `season`, `program`, `protonumber`)) LIKE UPPER('%".$valueToSearch."%') AND designer LIKE '".$name.'_'.$last_name."' LIMIT $offset, $no_of_records_per_page";
$search_result = filterTable($query);
}
else {
$query = "SELECT * FROM `protonumbers` WHERE designer LIKE '".$name.'_'.$last_name."' LIMIT $offset, $no_of_records_per_page";
$search_result = filterTable($query);
}

// function to connect and execute the query
function filterTable($query)
{
$connect = mysqli_connect("xxxx", "xxxx", "xxxx", "xxxx");
$filter_Result = mysqli_query($connect, $query);
return $filter_Result;
}

?>
<div class="container">
<div class="row">
    <div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">
            <div class="form-group">
                <input type="text" name="valueToSearch" class="form-control input-lg" placeholder="Search by Proto number, Season, Brand, Program" tabindex="1">

            <div class="row"><br />
                <div class="col-xs-6 col-md-6">
                    <input type="submit" name="search" value="Search" class="btn btn-primary btn-block btn-lg" tabindex="2"> . 
  </form></div></div></div></div>
                     <div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">
                                            <a href="index.php">Back</a><br><br>        <ul class="pagination">
    <li><a href="?pageno=1">First</a></li>
    <li class="<?php if($pageno <= 1){ echo 'disabled'; } ?>">
        <a href="<?php if($pageno <= 1){ echo '#'; } else { echo "?pageno=".($pageno - 1); } ?>">Prev</a>
    </li>
    <li class="<?php if($pageno >= $total_pages){ echo 'disabled'; } ?>">
        <a href="<?php if($pageno >= $total_pages){ echo '#'; } 
else { echo "?pageno=".($pageno + 1); } ?>">Next</a>
    </li>
    <li><a href="?pageno=<?php echo $total_pages; ?>">Last</a></li>
</ul> 
            <table>
            <tr>
                <th>ProtoID</th>
                <th>Brand</th>
                <th>Season</th>
                <th>Program</th>
                <th>Proto Number</th>
            </tr>

  <!-- populate table from mysql database -->
            <?php while($row = 
mysqli_fetch_array($search_result)):?>
            <tr>
                <td><?php echo $row['protoID'];?></td>
                <td><?php echo $row['brand'];?></td>
                <td><?php echo $row['season'];?></td>
                <td><?php echo $row['program'];?></td>
                <td><?php echo $row['protonumber'];?></td>
            </tr>
            <?php endwhile;?>
        </table>

                </div></div>

<?php
//include header template
require('layout/footer.php');
?>

HTTP is stateless. You need to persist the filters. Keep them on the URI as parameters or store w/ session. — ficuscr, Aug 07 '19 at 20:57
So, for example if a constraint is set on `last_name`, when rendering the page link for say page 2, you would append the `pageno` and `last_name`... could be something like `href="= "?last_name={$last_name}&page=2"...` Extra benefit of your links being sharable.. i.e. I can email you a link to the same results I am seeing. — ficuscr, Aug 07 '19 at 21:02
@ficuscr Yes please that'd be really helpful. I'm just stuck in how to append them. I would like to show you the entire script so you see the HTML too — Gabe, Aug 07 '19 at 21:11
got the rest of the code? Snippet where you render the pagination links? Otherwise I can pseudo code it. This something like [DataTables](https://www.datatables.net/) or something home made? — ficuscr, Aug 07 '19 at 21:13
@ficuscr It's home made hehe. I edited the original post and just added the remaining of the code — Gabe, Aug 07 '19 at 21:20
Shared an answer, I think it should be enough to get you going. There is opportunity for clean up / consolidation. Think of this maybe in a more OOP fashion, the table, has results, that are derived from properties (filters, source, current page, etc). All together creates a "thing" a "table". Holler if anything still not clear. — ficuscr, Aug 07 '19 at 21:36

score 0 · Accepted Answer · answered Aug 07 '19 at 21:32

0

Ok, so, as stated in the comments this boils down to HTTP not being stateful, you'll need to persist those filters values across requests. So, storing things in say session, or simply keeping the URIs query string in state.

So building out that query string...

<?php
    $assignedFilters = [];
    if (!empty($name)) $assignedFilters['name'] = $name;
    if (!empty($last_name)) $assignedFilters['last_name'] = $last_name;
    $filterParams = http_build_query($assignedFilters);
?>

And then getting it on the href for you pagination links... Here is one example

  <a href="<?php if($pageno <= 1) { echo '#'; 
} else { 
  echo "?pageno=".($pageno - 1) . '&' . $filterParams; } ?>">Prev</a>

In other words append . '&' . $filterParams to your existing pagination links.

answered Aug 07 '19 at 21:32

ficuscr

6,975
2
32
52

I want to filter using the input in the section . Whatever the user writes in the search box would filter the pagination through out the pages. – Gabe Aug 07 '19 at 21:36
Hmm. First I am hearing of that. Maybe that should have been in your question then? – ficuscr Aug 07 '19 at 21:38
I should've! I thought I wrote it lol, my bad. Currently it filters automatically by name and last name that's not an issue, the issue starts when the user types in the text box the filter and clicks search then it shows the results fine on the first page but when you click the other pages it erases that query and shows everything – Gabe Aug 07 '19 at 21:42
Same concept I've tried to illustrate with my answer. Set the `value` of the text `` with what is submitted prior to rendering - that way it will "persist". FWIW: https://stackoverflow.com/questions/3105296/if-rest-applications-are-supposed-to-be-stateless-how-do-you-manage-sessions – ficuscr Aug 07 '19 at 21:52

How to fix pagination problem with filter in PHP and SQL?

1 Answers1