Razvrščanje izpisov podatkovne zbirke - ORDER BY

Ko imamo v tabeli več zapisov, jih pogosto želimo urediti po določenem stolpcu. Tak postopek imenujemo sortiranje oziroma razvrščanje zapisov. V MySQL oziroma MariaDB za to uporabimo ukaz ORDER BY.

Sortiranje je uporabno pri preglednicah, kjer želi uporabnik hitro urediti podatke po naslovu, avtorju, ceni, številu strani ali letu izida. V aplikaciji Knjige so glave stolpcev klikljive, zato lahko uporabnik sam izbere stolpec in smer razvrščanja.

Vsebina strani

Osnovna pravila

Pri sortiranju določimo stolpec, po katerem naj bodo rezultati razvrščeni, in po potrebi še smer razvrščanja.

• Za razvrščanje uporabimo ukaz ORDER BY.
• Naraščajočo smer označimo z ASC.
• Padajočo smer označimo z DESC.
• Če smeri ne navedemo, je privzeta smer običajno naraščajoča.
• Razvrščamo lahko po številskih, besedilnih ali datumskih stolpcih.
• Če uporabnik izbira stolpec za razvrščanje, mora biti ta stolpec na seznamu dovoljenih stolpcev.
• Če uporabnik izbere neveljavno smer, uporabimo privzeto smer, na primer ASC.

SELECT stolpec1, stolpec2
FROM imeTabele
ORDER BY izbraniStolpec;

SELECT *
FROM knjige
ORDER BY Leto DESC;

SELECT *
FROM knjige
ORDER BY Naslov ASC;

<?php
define('DB_SERVER', 'localhost');
define('DB_USER', 'uporabnik');
define('DB_PASS', 'skritoGeslo');
define('DB_NAME', 'knjiznica');

// Povezava do podatkovne zbirke
$connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

// Preverjanje povezave
if (!$connection) {
    die(
        'Povezava s podatkovno zbirko ni vzpostavljena: ' .
        mysqli_connect_error() .
        ' (' . mysqli_connect_errno() . ')'
    );
}

// Dovoljeni stolpci za razvrščanje
$dovoljeniStolpci = ['ID_knjige', 'Priimek_avtorja', 'Ime_avtorja', 'Naslov', 'Strani', 'Cena', 'Leto'];

$stolpec = $_GET['column'] ?? 'ID_knjige';
$vrstniRed = (isset($_GET['order']) && strtolower($_GET['order']) === 'desc') ? 'DESC' : 'ASC';

if (!in_array($stolpec, $dovoljeniStolpci, true)) {
    $stolpec = 'ID_knjige';
}

$sql = "SELECT ID_knjige, Priimek_avtorja, Ime_avtorja, Naslov, Strani, Cena, Leto
        FROM knjige
        ORDER BY $stolpec $vrstniRed";

$result = mysqli_query($connection, $sql);

while ($row = mysqli_fetch_assoc($result)) {
    echo htmlspecialchars($row['Naslov']) . '<br>';
}

mysqli_close($connection);
?>

V primeru z mysqli se stolpec najprej preveri s funkcijo in_array(). Smer razvrščanja je omejena samo na ASC ali DESC. Šele nato se sestavi poizvedba z ORDER BY.

<?php
$streznik = 'localhost';
$baza = 'knjiznica';
$uporabnik = 'uporabnik';
$geslo = 'skritoGeslo';

try {
    $pdo = new PDO("mysql:host=$streznik;dbname=$baza;charset=utf8mb4", $uporabnik, $geslo);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $dovoljeniStolpci = ['ID_knjige', 'Priimek_avtorja', 'Ime_avtorja', 'Naslov', 'Strani', 'Cena', 'Leto'];

    $stolpec = $_GET['column'] ?? 'ID_knjige';
    $vrstniRed = (isset($_GET['order']) && strtolower($_GET['order']) === 'desc') ? 'DESC' : 'ASC';

    if (!in_array($stolpec, $dovoljeniStolpci, true)) {
        $stolpec = 'ID_knjige';
    }

    $sql = "SELECT ID_knjige, Priimek_avtorja, Ime_avtorja, Naslov, Strani, Cena, Leto
            FROM knjige
            ORDER BY $stolpec $vrstniRed";

    $stmt = $pdo->query($sql);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as $row) {
        echo htmlspecialchars($row['Naslov']) . '<br>';
    }
}
catch (PDOException $e) {
    echo 'Napaka pri sortiranju podatkov: ' . $e->getMessage();
}
?>

Pri PDO poizvedbo izvedemo z metodo query(), rezultate pa preberemo z metodo fetchAll(). Tudi pri tem pristopu mora biti stolpec preverjen pred sestavljanjem poizvedbe.

Preverjanje izbranega stolpca in smeri

Pri sortiranju je pomembno, da uporabnik ne more vnesti poljubnega imena stolpca v poizvedbo. Zato pripravimo seznam dovoljenih stolpcev in uporabimo samo tistega, ki je na tem seznamu.

• preberemo izbrani stolpec iz obrazca ali URL parametra,
• preverimo, ali je stolpec na seznamu dovoljenih vrednosti,
• dovolimo le smeri ASC ali DESC,
• če podatek ni veljaven, uporabimo privzeto razvrstitev,
• pri izpisu podatkov uporabimo htmlspecialchars().

$dovoljeniStolpci = ['ID_knjige', 'Priimek_avtorja', 'Ime_avtorja', 'Naslov', 'Strani', 'Cena', 'Leto'];

$stolpec = $_GET['column'] ?? 'ID_knjige';
$vrstniRed = ($_GET['order'] ?? 'asc') === 'desc' ? 'DESC' : 'ASC';

if (!in_array($stolpec, $dovoljeniStolpci, true)) {
    $stolpec = 'ID_knjige';
}

Klikljive glave stolpcev

V aplikaciji lahko uporabniku omogočimo, da razvrščanje spreminja s klikom na naslov stolpca. Glava stolpca je povezava, ki pošlje ime stolpca in smer razvrščanja.

<a href="10_sort.php?column=Naslov&order=asc">Naslov</a>

Če je stolpec že izbran, lahko ob naslednjem kliku smer obrnemo iz ASC v DESC ali obratno.

$novaSmer = ($trenutniStolpec === 'Naslov' && $trenutnaSmer === 'ASC') ? 'desc' : 'asc';

Primerjava razvrščanja z mysqli in PDO

Pri razvrščanju je logika preverjanja stolpca in smeri podobna pri obeh pristopih. Razlikujeta se predvsem v izvedbi poizvedbe in branju rezultatov.

Aplikacija Knjige

Navodila za izdelavo aplikacije Knjige

Priporočila

• Za razvrščanje uporabi ORDER BY.
• Uporabniku ponudi samo smiselne stolpce za razvrščanje.
• Ime stolpca vedno preveri na seznamu dovoljenih vrednosti.
• Smer razvrščanja omeji na ASC in DESC.
• Če uporabnik poda neveljavno vrednost, uporabi privzeto razvrščanje.
• Pri klikljivih glavah označi trenutno aktiven stolpec.
• Pri izpisu podatkov uporabi htmlspecialchars().
• Uporabniku prikaži trenutno razvrstitev in število zapisov.

Pogoste napake

• Ime stolpca iz URL parametra je neposredno vstavljeno v poizvedbo.
• Ni seznama dovoljenih stolpcev za razvrščanje.
• Smer razvrščanja ni omejena na ASC ali DESC.
• Manjka privzeta razvrstitev za neveljavne parametre.
• Uporabnik ne vidi, po katerem stolpcu so podatki trenutno razvrščeni.
• Glave stolpcev niso jasno označene kot povezave za razvrščanje.
• Pri izpisu podatkov manjka htmlspecialchars().
• Pri enakih vrednostih ni dodatnega razvrščanja, zato je vrstni red lahko manj predvidljiv.