ORDER BY in php with paging ordering only the first page

Asked

Viewed 146 times

0

I wrote the following code in php, I would like it to show the items in order according to the form combobox, but in it it sorts only the first page’s items. Thank you!

<html lang="pt-br">
<head>
    <title>Livros</title>
    <meta charset="utf-8">
    <link rel="stylesheet" type="text/css" href="_css/style.css">
</head>
<body>
<header>
    <form method="get">
        <select name="ordem">
            <option>Selecione</option>
            <option value="ano_public">Ano</option>
            <option value="autor">Autor</option>
            <option value="codigo">Codigo</option>
            <option value="titulo">Titulo</option>
        </select>
        <input type="text" name="filtro">
        <input type="submit" name="Enviar">     
    </form>
</header>
<?php
@$filtro = isset($_GET["filtro"]) ? $_GET["filtro"] : "codigo";
$ordem = isset($_GET["ordem"]) ? $_GET["ordem"] : "codigo";
$link = mysqli_connect("127.0.0.1","root","","db_aula");
$sql = "SELECT COUNT(*) qtde FROM tb_livro";
$rs = mysqli_query($link, $sql);
$r = mysqli_fetch_array($rs);
$total = $r['qtde'];
$qtde = 4;
$num_pags = ceil($total/$qtde);
$pagina = empty($_GET['pagina']) ? 1 : $_GET['pagina'];

$sql = "SELECT codigo, titulo, autor, ano_public FROM tb_livro ORDER BY $ordem LIMIT ".($pagina-1)*$qtde.", $qtde";

$rs = mysqli_query($link, $sql);

    while($r = mysqli_fetch_array($rs)){
        $r = array_map('utf8_encode', $r);
        $foto = "_img/". $r["codigo"].".jpg";
    if(!file_exists($foto)) $foto = "./fotos/nophoto.jpg";
        echo '<div class="livro">' . 
             '<img src="'.$foto.'" class="foto">' . 
             '<span class="info">Codigo: '.$r["codigo"] .
             '</span><br><br>Titulo: ' . $r["titulo"] . 
             '<br />Autor: ' . $r["autor"] . 
             '<br />Ano de publicação: ' . $r["ano_public"] . '<br/>' . 
             '</div>';
    }

echo "<ul align='center'>";
    for($i=1; $i<=$num_pags; $i++) {
        if($i!=$pagina)
            echo "<li><a href=\"?pagina=$i\">$i</a></li>";
        else
            echo "<li>$i</li>";
    }
echo "</ul>";

mysqli_free_result($rs);
mysqli_close($link);
?>
</body>
</html>

1 answer

1

Nor would it be otherwise, after all you are only passing the page number, you are not passing the filter or ordering:

echo "<li><a href=\"?pagina=$i\">$i</a></li>";

You’d have to wear something like that on the links:

echo "<li><a href=\"?pagina=$i&ordem=$ordem&filtro=$filtro\">$i</a></li>";


There are other problems with your code, such as unnecessary deletion (@) and serious SQL injection problems, any rogue erases his entire DB with a mere manipulation of URL parameters.

It is important you read this post (and apply):

How to prevent SQL code injection into my PHP code?

Browser other questions tagged

You are not signed in. Login or sign up in order to post.