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
?>
My Answer:
Sorting and pagination are common features in database applications to help users navigate through large datasets. In MySQL, you can achieve sorting and pagination using the `ORDER BY` and `LIMIT` clauses in your SQL queries.
To sort a table in MySQL, you can use the `ORDER BY` clause followed by the column you want to sort by. For example, to sort a table named `users` by the `name` column in ascending order, you can use the following query:
sql
SELECT * FROM users ORDER BY name ASC;
To sort in descending order, you can use `DESC` instead of `ASC`:
sql
SELECT * FROM users ORDER BY name DESC;
To implement pagination in MySQL, you can use the `LIMIT` clause to specify the number of rows to return and the `OFFSET` clause to specify the starting row. For example, to retrieve the first 10 rows from the `users` table, you can use the following query:
sql
SELECT * FROM users LIMIT 10;
To implement pagination with an offset, you can specify both the `LIMIT` and `OFFSET` clauses. For example, to retrieve rows 11-20 from the `users` table, you can use the following query:
sql
SELECT * FROM users LIMIT 10 OFFSET 10;
By combining sorting and pagination, you can create more user-friendly interfaces for navigating through large datasets in MySQL.
Rate this post
5 of 5 based on 6735 votesComments