Ricerca, paginazione, prestiti e statistiche

In questa lezione l'applicazione diventa completa. Riprendiamo le query delle lezioni 5 e 6 e le portiamo nelle pagine: il catalogo con ricerca e pagine, i prestiti e le statistiche.

Il catalogo completo

Il catalogo deve permettere di:

• cercare un testo nel titolo o nel cognome dell'autore;
• filtrare per genere;
• mostrare 5 libri per pagina, con i pulsanti "Precedente" e "Successiva";
• indicare quante copie di ogni libro sono disponibili in questo momento.

Ecco la logica di index.php, divisa in quattro passi.

1. Leggere i filtri

<?php
require 'funzioni.php';

// ---- 1. Leggo i filtri dall'indirizzo (GET): index.php?cerca=levi&genere=Romanzo&pagina=2
$cerca  = trim($_GET['cerca'] ?? '');
$genere = $_GET['genere'] ?? '';
$pagina = max(1, (int) ($_GET['pagina'] ?? 1));

Ricerca e filtri sono letture, quindi usano GET: l'indirizzo con la ricerca si può salvare o mandare a un compagno. max(1, ...) impedisce pagine zero o negative.

2. La WHERE dinamica

Con un filtro solo basterebbe una query fissa. Per la sola ricerca:

<?php
$cerca_like = "%$cerca%";      // con la ricerca vuota diventa '%%', che trova tutto
$stmt = mysqli_prepare($conn, 'SELECT l.titolo, a.cognome FROM libri l JOIN autori a ON a.id = l.autore_id
                               WHERE l.titolo LIKE ? OR a.cognome LIKE ?');
mysqli_stmt_bind_param($stmt, 'ss', $cerca_like, $cerca_like);

Con due filtri i casi diventano quattro: nessun filtro, solo la ricerca, solo il genere, tutti e due. Si potrebbero scrivere quattro query diverse dentro degli if, ma con un terzo filtro diventerebbero otto, con un quarto sedici. Conviene invece costruire la WHERE un pezzo alla volta, mettendo in un array i pezzi di SQL (scritti da noi), in un altro i valori (dell'utente) e in una stringa le lettere dei loro tipi:

<?php
// ---- 2. Costruisco la condizione WHERE solo con i filtri usati (sempre con parametri!)
$condizioni = [];
$valori = [];
$tipi = '';
if ($cerca !== '') {
    $condizioni[] = '(l.titolo LIKE ? OR a.cognome LIKE ?)';
    $valori[] = "%$cerca%";
    $valori[] = "%$cerca%";
    $tipi .= 'ss';
}
if ($genere !== '') {
    $condizioni[] = 'l.genere = ?';
    $valori[] = $genere;
    $tipi .= 's';
}
$where = $condizioni ? 'WHERE ' . implode(' AND ', $condizioni) : '';

Con tutti e due i filtri, $where diventa:

WHERE (l.titolo LIKE ? OR a.cognome LIKE ?) AND l.genere = ?

e $tipi vale 'sss'. Nella stringa SQL entrano solo pezzi scritti da noi, con i segnaposto; i valori dell'utente stanno in $valori e arriveranno a MySQL separati. È la tecnica che permette query flessibili senza SQL injection.

Le parentesi intorno all'OR sono indispensabili: senza, AND verrebbe valutato prima (lezione 5) e il filtro per genere si applicherebbe solo alla ricerca per cognome.

3. Contare e calcolare le pagine

Per sapere quante pagine ci sono, serve il numero totale dei libri trovati, con gli stessi filtri:

<?php
// ---- 3. Quanti libri in tutto? Serve per sapere quante pagine ci sono
$stmt = mysqli_prepare($conn, "SELECT COUNT(*) FROM libri l JOIN autori a ON a.id = l.autore_id $where");
if ($valori) {
    mysqli_stmt_bind_param($stmt, $tipi, ...$valori);   // i ... "spargono" l'array: un valore per ogni ?
}
mysqli_stmt_execute($stmt);
$totale = mysqli_fetch_row(mysqli_stmt_get_result($stmt))[0];
$pagine = max(1, (int) ceil($totale / PER_PAGINA));
$pagina = min($pagina, $pagine);

...$valori è l'operatore spread: passa a mysqli_stmt_bind_param gli elementi dell'array come se li avessimo scritti uno per uno, quanti che siano. Se non c'è nessun filtro la query non ha ?, e il bind va saltato: per questo c'è l'if.

Con 14 libri e 5 per pagina: ceil(14 / 5) = 3 pagine. min riporta all'ultima pagina chi scrive a mano ?pagina=99. PER_PAGINA è una costante di config.php.

4. I libri della pagina, con la disponibilità

<?php
// ---- 4. I libri di questa pagina, con le copie disponibili (copie meno prestiti non restituiti)
$sql = "SELECT l.id, l.titolo, l.anno, l.genere, l.copie, a.nome, a.cognome,
               l.copie - COUNT(p.id) AS disponibili
        FROM libri l
        JOIN autori a ON a.id = l.autore_id
        LEFT JOIN prestiti p ON p.libro_id = l.id AND p.data_restituzione IS NULL
        $where
        GROUP BY l.id
        ORDER BY l.titolo
        LIMIT ? OFFSET ?";
// ai valori dei filtri si aggiungono i due interi di LIMIT e OFFSET
$valori[] = PER_PAGINA;
$valori[] = ($pagina - 1) * PER_PAGINA;
$tipi .= 'ii';

$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, $tipi, ...$valori);
mysqli_stmt_execute($stmt);
$libri = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);

$risultato = mysqli_query($conn, 'SELECT DISTINCT genere FROM libri ORDER BY genere');
$generi = array_column(mysqli_fetch_all($risultato, MYSQLI_ASSOC), 'genere');

• È la query della disponibilità della lezione 6, con la LEFT JOIN che aggancia solo i prestiti aperti, più la WHERE dinamica e la paginazione.
• OFFSET: alla pagina 1 si saltano 0 libri, alla 2 se ne saltano 5, alla 3 dieci. In generale (pagina - 1) × libri per pagina.
• LIMIT e OFFSET vogliono numeri interi: alle lettere dei filtri si aggiungono due i, e ai valori i due numeri. Qui il bind c'è sempre, perché i due ? finali ci sono sempre.
• GROUP BY l.id basta, anche se nella SELECT ci sono altre colonne di libri e di autori: dipendono tutte dall'id del libro, e MySQL lo riconosce.
• I generi per il menu a tendina arrivano dal database, così un genere nuovo compare da solo.

<?php
$stmt = $pdo->prepare($sql);   // con LIMIT :limite OFFSET :salta
foreach ($parametri as $nome => $valore) {
    $stmt->bindValue($nome, $valore);
}
$stmt->bindValue(':limite', PER_PAGINA, PDO::PARAM_INT);
$stmt->bindValue(':salta', ($pagina - 1) * PER_PAGINA, PDO::PARAM_INT);
$stmt->execute();
$libri = $stmt->fetchAll(PDO::FETCH_ASSOC);

La pagina: filtri e link che li conservano

Quando si passa alla pagina 2, i filtri non devono andare persi. La funzione http_build_query costruisce la parte dopo il ? a partire da un array, e codifica correttamente spazi, apostrofi e caratteri speciali:

<?php
// link a un'altra pagina conservando i filtri
function link_pagina(int $n): string
{
    return '?' . http_build_query(['cerca' => $_GET['cerca'] ?? '', 'genere' => $_GET['genere'] ?? '', 'pagina' => $n]);
}

$titolo = 'Catalogo';
require 'header.php';
?>
<h1>Catalogo</h1>

<form class="filtri" method="get">
    <input type="search" name="cerca" value="<?= h($cerca) ?>" placeholder="Titolo o cognome dell'autore">
    <select name="genere">
        <option value="">Tutti i generi</option>
        <?php foreach ($generi as $g): ?>
            <option <?= $g === $genere ? 'selected' : '' ?>><?= h($g) ?></option>
        <?php endforeach; ?>
    </select>
    <button>Cerca</button>
</form>

<p><?= $totale ?> libri trovati</p>

<?php if ($libri): ?>
<table>
    <tr><th>Titolo</th><th>Autore</th><th>Anno</th><th>Genere</th><th>Disponibili</th></tr>
    <?php foreach ($libri as $l): ?>
    <tr>
        <td><a href="libro.php?id=<?= $l['id'] ?>"><?= h($l['titolo']) ?></a></td>
        <td><?= h($l['nome'] . ' ' . $l['cognome']) ?></td>
        <td><?= h((string) $l['anno']) ?></td>
        <td><?= h($l['genere']) ?></td>
        <td class="<?= $l['disponibili'] > 0 ? 'si' : 'no' ?>"><?= $l['disponibili'] ?> su <?= $l['copie'] ?></td>
    </tr>
    <?php endforeach; ?>
</table>
<?php else: ?>
    <p>Nessun libro corrisponde alla ricerca.</p>
<?php endif; ?>

<?php if ($pagine > 1): ?>
<nav class="paginazione">
    <?php if ($pagina > 1): ?><a href="<?= h(link_pagina($pagina - 1)) ?>">← Precedente</a><?php endif; ?>
    <span>Pagina <?= $pagina ?> di <?= $pagine ?></span>
    <?php if ($pagina < $pagine): ?><a href="<?= h(link_pagina($pagina + 1)) ?>">Successiva →</a><?php endif; ?>
</nav>
<?php endif; ?>

<?php require 'footer.php'; ?>

Il form di ricerca non ha il campo pagina: una nuova ricerca riparte giustamente dalla prima pagina.

Prova: cerca levi (3 libri, una pagina sola), poi scegli il genere Fantascienza senza testo (4 libri), poi nessun filtro (3 pagine). Cerca anche l'isola: l'apostrofo non crea problemi.

I prestiti

L'elenco e la restituzione

prestiti.php è riservata al bibliotecario. Mostra i prestiti aperti con la JOIN su tre tabelle e, accanto a ciascuno, un pulsante per registrare la restituzione. La stessa pagina riceve anche il POST del pulsante:

<?php
require 'funzioni.php';
richiedi_login();

// Restituzione di un libro (modulo POST in fondo a ogni riga)
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    csrf_controlla();
    $id = (int) ($_POST['id'] ?? 0);
    $stmt = mysqli_prepare($conn, 'UPDATE prestiti SET data_restituzione = CURDATE() WHERE id = ? AND data_restituzione IS NULL');
    mysqli_stmt_bind_param($stmt, 'i', $id);
    mysqli_stmt_execute($stmt);
    messaggio(mysqli_stmt_affected_rows($stmt) > 0 ? 'Restituzione registrata.' : 'Prestito già chiuso.');
    vai('prestiti.php');
}

// Prestiti ancora aperti: JOIN fra tre tabelle, e da quanti giorni il libro è fuori
$risultato = mysqli_query($conn,
    'SELECT p.id, l.titolo, s.nome, s.cognome, s.classe, p.data_prestito,
            DATEDIFF(CURDATE(), p.data_prestito) AS giorni
     FROM prestiti p
     JOIN libri l    ON l.id = p.libro_id
     JOIN studenti s ON s.id = p.studente_id
     WHERE p.data_restituzione IS NULL
     ORDER BY p.data_prestito'
);
$prestiti = mysqli_fetch_all($risultato, MYSQLI_ASSOC);

$titolo = 'Prestiti';
require 'header.php';
?>
<h1>Prestiti in corso</h1>
<p><a class="bottone" href="prestito_nuovo.php">+ Nuovo prestito</a></p>

<?php if ($prestiti): ?>
<table>
    <tr><th>Libro</th><th>Studente</th><th>Classe</th><th>Dal</th><th>Giorni</th><th></th></tr>
    <?php foreach ($prestiti as $p): ?>
    <tr class="<?= $p['giorni'] > GIORNI_PRESTITO ? 'ritardo' : '' ?>">
        <td><?= h($p['titolo']) ?></td>
        <td><?= h($p['nome'] . ' ' . $p['cognome']) ?></td>
        <td><?= h($p['classe']) ?></td>
        <td><?= date('d/m/Y', strtotime($p['data_prestito'])) ?></td>
        <td><?= $p['giorni'] ?><?= $p['giorni'] > GIORNI_PRESTITO ? ' ⚠️' : '' ?></td>
        <td>
            <form method="post">
                <?= csrf_campo() ?>
                <input type="hidden" name="id" value="<?= $p['id'] ?>">
                <button>Restituito</button>
            </form>
        </td>
    </tr>
    <?php endforeach; ?>
</table>
<?php else: ?>
    <p>Nessun libro in prestito.</p>
<?php endif; ?>
<?php require 'footer.php'; ?>

• DATEDIFF calcola i giorni direttamente in SQL; la riga dei prestiti oltre i 30 giorni (GIORNI_PRESTITO) prende la classe CSS ritardo e un ⚠️.
• Nell'UPDATE c'è anche AND data_restituzione IS NULL: se il bibliotecario preme due volte (o ha due schede aperte), la seconda volta non cambia la data della prima restituzione. mysqli_stmt_affected_rows() dice quale dei due casi è successo.
• Qui la SELECT usa mysqli_query(), perché non contiene nessun dato dell'utente.

Il nuovo prestito: il controllo va fatto sul server

prestito_nuovo.php mostra due menu, libro e studente. Il menu dei libri contiene solo quelli con copie libere, grazie a una HAVING. Ma il menu si può manomettere: il controllo vero si ripete sul server, al momento del salvataggio.

<?php
require 'funzioni.php';
richiedi_login();
$errori = [];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    csrf_controlla();
    $libro_id = (int) ($_POST['libro_id'] ?? 0);
    $studente_id = (int) ($_POST['studente_id'] ?? 0);

    // Il libro ha ancora copie libere? Lo controlla il server, anche se il menu mostra solo quelli disponibili
    $stmt = mysqli_prepare($conn, 'SELECT l.copie - (SELECT COUNT(*) FROM prestiti
                                                     WHERE libro_id = l.id AND data_restituzione IS NULL) AS libere
                                   FROM libri l WHERE l.id = ?');
    mysqli_stmt_bind_param($stmt, 'i', $libro_id);
    mysqli_stmt_execute($stmt);
    $riga = mysqli_fetch_row(mysqli_stmt_get_result($stmt));   // null se il libro non esiste
    $libere = $riga ? $riga[0] : null;

    if ($libere === null) $errori[] = 'Scegli un libro.';
    elseif ($libere < 1) $errori[] = 'Di questo libro non ci sono copie disponibili.';
    if (!$studente_id) $errori[] = 'Scegli lo studente.';

    if (!$errori) {
        $stmt = mysqli_prepare($conn, 'INSERT INTO prestiti (libro_id, studente_id, data_prestito) VALUES (?, ?, CURDATE())');
        mysqli_stmt_bind_param($stmt, 'ii', $libro_id, $studente_id);
        mysqli_stmt_execute($stmt);
        messaggio('Prestito registrato.');
        vai('prestiti.php');
    }
}

$risultato = mysqli_query($conn, 'SELECT l.id, l.titolo, l.copie - COUNT(p.id) AS libere
                                 FROM libri l LEFT JOIN prestiti p ON p.libro_id = l.id AND p.data_restituzione IS NULL
                                 GROUP BY l.id HAVING libere > 0 ORDER BY l.titolo');
$libri = mysqli_fetch_all($risultato, MYSQLI_ASSOC);
$risultato = mysqli_query($conn, 'SELECT id, nome, cognome, classe FROM studenti ORDER BY classe, cognome');
$studenti = mysqli_fetch_all($risultato, MYSQLI_ASSOC);

(Il form HTML ha due <select>, libro_id e studente_id, costruiti con foreach come quello degli autori in libro_form.php.)

• La copie libere si calcolano con una sottoquery nella SELECT (lezione 6). Se il libro non esiste, mysqli_fetch_row() restituisce null.
• Uno studente inesistente viene comunque respinto: la chiave esterna studente_id fa fallire l'INSERT. Ancora una volta il database è l'ultima linea di difesa.

Le statistiche

statistiche.php è pubblica e mostra tre riepiloghi. Ognuno è una query della lezione 6, eseguita e stampata in una tabella o in un elenco:

<?php
// Pagina pubblica: tre query con funzioni di aggregazione e GROUP BY
require 'funzioni.php';

$risultato = mysqli_query($conn, 'SELECT genere, COUNT(*) AS titoli, SUM(copie) AS copie
                                 FROM libri GROUP BY genere ORDER BY titoli DESC, genere');
$per_genere = mysqli_fetch_all($risultato, MYSQLI_ASSOC);

$risultato = mysqli_query($conn, 'SELECT l.titolo, a.cognome, COUNT(p.id) AS prestiti
                                 FROM libri l
                                 JOIN autori a ON a.id = l.autore_id
                                 JOIN prestiti p ON p.libro_id = l.id
                                 GROUP BY l.id
                                 ORDER BY prestiti DESC, l.titolo
                                 LIMIT 5');
$piu_letti = mysqli_fetch_all($risultato, MYSQLI_ASSOC);

$risultato = mysqli_query($conn, 'SELECT s.classe, COUNT(*) AS prestiti
                                 FROM prestiti p JOIN studenti s ON s.id = p.studente_id
                                 GROUP BY s.classe
                                 HAVING prestiti >= 1
                                 ORDER BY prestiti DESC, s.classe');
$per_classe = mysqli_fetch_all($risultato, MYSQLI_ASSOC);

$titolo = 'Statistiche';
require 'header.php';
?>
<h1>Statistiche</h1>

<h2>I 5 libri più letti</h2>
<ol>
    <?php foreach ($piu_letti as $r): ?>
    <li><?= h($r['titolo']) ?> (<?= h($r['cognome']) ?>): <?= $r['prestiti'] ?> <?= $r['prestiti'] == 1 ? 'prestito' : 'prestiti' ?></li>
    <?php endforeach; ?>
</ol>

<h2>Prestiti per classe</h2>
<table>
    <tr><th>Classe</th><th>Prestiti</th></tr>
    <?php foreach ($per_classe as $r): ?>
    <tr><td><?= h($r['classe']) ?></td><td><?= $r['prestiti'] ?></td></tr>
    <?php endforeach; ?>
</table>
<?php require 'footer.php'; ?>

(La tabella dei libri per genere si stampa allo stesso modo.) MySQL permette di usare l'alias prestiti anche nella HAVING; altri database richiedono di riscrivere COUNT(*).

Esercizi

<?php
$solo_disponibili = isset($_GET['disponibili']);
$having = $solo_disponibili ? 'HAVING disponibili > 0' : '';
// nella query dei libri, dopo GROUP BY l.id:
//     GROUP BY l.id $having ORDER BY l.titolo LIMIT ...

<?php
$stmt = mysqli_prepare($conn, "SELECT COUNT(*) FROM (
                          SELECT l.id, l.copie - COUNT(p.id) AS disponibili
                          FROM libri l
                          JOIN autori a ON a.id = l.autore_id
                          LEFT JOIN prestiti p ON p.libro_id = l.id AND p.data_restituzione IS NULL
                          $where
                          GROUP BY l.id $having
                        ) AS t");

<?php
$stmt = mysqli_prepare($conn, 'SELECT l.titolo, p.data_prestito, p.data_restituzione
                               FROM prestiti p JOIN libri l ON l.id = p.libro_id
                               WHERE p.studente_id = ?
                               ORDER BY p.data_restituzione IS NULL DESC, p.data_prestito DESC');
mysqli_stmt_bind_param($stmt, 'i', $id);
mysqli_stmt_execute($stmt);
$prestiti = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);

$aperti = array_filter($prestiti, fn($p) => $p['data_restituzione'] === null);
$chiusi = array_filter($prestiti, fn($p) => $p['data_restituzione'] !== null);

<?php $massimo = max(array_column($per_classe, 'prestiti')); ?>
<div class="grafico">
  <?php foreach ($per_classe as $r): ?>
    <div class="riga">
      <span><?= h($r['classe']) ?></span>
      <div class="barra" style="width: <?= round($r['prestiti'] / $massimo * 100) ?>%"><?= $r['prestiti'] ?></div>
    </div>
  <?php endforeach; ?>
</div>

.grafico .riga { display: flex; align-items: center; gap: .5rem; margin: .3rem 0; }
.grafico .riga span { width: 3rem; }
.grafico .barra { background: #2453a6; color: #fff; padding: .2rem .5rem; border-radius: 4px; text-align: right; }