'Table sorting and pagination doesn't work together in php

i'm trying to sort a table by different column names ASC or DESC and it works when I press my submit input. I also have a pagination, so I can navigate through pages. I can also define how many entries I want per pages with the same form I use for the sorting.

Everything works, but not together. Meaning that if I want to sort a page, it will only sort that page ! I would like to sort by price ASC for example, and then be able to navigate my pages without that sorting going away.

Also, my pages are empty when I select only the entries wanted. If I chose the sorting pattern and the entries wanted, or the sorting pattern alone, it displays both request, but it goes away when switching pages.

I will really appreciate your help, been smashing my head against my keyboards for a few hours now lol.

Edit : I tried to pass the information of the sorting into the <a>Next<a> but it doesn't work. The main issue here, is that I want that sorting information in the url to stay there (even if I change the page) until I chose another sorting pattern.

My code :

<?php

//On which page am I ?

if (isset($_GET['page']) && !empty($_GET['page'])) {
    $currentPage = (int) strip_tags($_GET['page']);
} else {
    $currentPage = 1;
}

//Database Connection

require_once('db_connect.php');

//How many movies do I have ?

$sqlTotalMovies = "SELECT COUNT(film_id) AS nb_film FROM film_category";
$query = $databaseConnection->prepare($sqlTotalMovies);
$query->execute();
$resultMovies = $query->fetch();
$nbMovies = (int) $resultMovies['nb_film'];

//Sorting

$order = "";
if (isset($_GET['submit'])) {
    $getSorting = $_GET['categories'];
    $order = "ORDER BY" . " " . $getSorting;
}

$moviesPerPage = 10;
if (isset($_GET['moviesPerPage'])) {
    $moviesPerPage = $_GET['moviesPerPage'];
    var_dump($_GET['submit']);
}

//How many pages do I need ?

$pagesNumber = ceil($nbMovies / $moviesPerPage);
$pagesMax = $pagesNumber;

//First movie of the list

$firstMovie = ($currentPage * $moviesPerPage) - $moviesPerPage;
$sqlPages = "SELECT title, category, rating, FID,price FROM film_list $order LIMIT :firstMovie, :moviesPerPage";
$result = $databaseConnection->prepare($sqlPages);
$result->bindValue(':firstMovie', $firstMovie, PDO::PARAM_INT);
$result->bindValue(':moviesPerPage', $moviesPerPage, PDO::PARAM_INT);
$result->execute();

// We get the values in an associative array

$movies = $result->fetchAll(PDO::FETCH_ASSOC);

//Display table

?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SGBDR</title>
</head>

<body>
    <form method="GET">
        <select name="moviesPerPage">
            <option value="10">Afficher <?= $moviesPerPage ?> films :</option>
            <option value="20">Afficher 20 films :</option>
            <option value="30">Afficher 30 films :</option>
            <option value="40">Afficher 40 films :</option>
            <option value="50">Afficher 50 films :</option>
            <option value="100">Afficher 100 films :</option>
        </select>
        <select name="categories">
            <option value="">Trié par :</option>
            <option value="title ASC">Titre ascendant</option>
            <option value="title DESC">Titre descendant</option>
            <option value="category ASC">Categorie ascendante</option>
            <option value="category DESC">Categorie descendante</option>
            <option value="rating ASC">Notation ascendante</option>
            <option value="rating DESC">Notation descendante</option>
            <option value="FID ASC">FID ascendante</option>
            <option value="FID DESC">FID descendante</option>
            <option value="price ASC">Prix ascendant</option>
            <option value="price DESC">Prix descendant</option>
        </select>
        <input type="submit" name="submit" value="Get sorting">
        <a href="./test"></a>
        </input>
    </form>
    <nav>
        <ul class="pagination">
            <li class="page-item <?= ($currentPage == 1) ? "disabled" : "" ?>">
                <a href="./?page=<?= $currentPage - 1 ?>" class="page-link">Previous</a>
            </li>

            <?php for ($pagesNumber = 1; $pagesNumber <= $pagesMax; $pagesNumber++) : ?><?php endfor ?>
            <li class="page-item <?= ($currentPage == $pagesNumber) ? "active" : "" ?>">
                <p><?= $currentPage ?>/<?= $pagesMax ?></p>
            </li>

            <li class="page-item <?= ($currentPage == $pagesMax) ? "disabled" : "" ?>">
                <a href="./?page=<?= $currentPage + 1 ?>" class="page-link">Next</a>
            </li>
        </ul>
    </nav>
    <table>
        <tr>
            <th>Film</th>
            <th>Genre</th>
            <th>Rating</th>
            <th>FID</th>
            <th>Rental Price</th>
        </tr>
        <?php foreach ($movies as $movie) { ?>
            <tr>
                <td><?= $movie['title']; ?></td>
                <td><?= $movie['category']; ?></td>
                <td><?= $movie['rating']; ?></td>
                <td><?= $movie['FID']; ?></td>
                <td><?= $movie['price']; ?></td>
            </tr>
        <?php
        } ?>
    </table>
</body>

</html>

<?php

?>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'Table sorting and pagination doesn't work together in php

Sources

Related Questions