ZGLED

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.

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.

Sortiranje zapisov v tabeli

Sortiranje pomeni, da zapise uredimo po izbranem stolpcu, na primer po priimku avtorja, naslovu knjige, ceni ali letu izida.

Osnovna sintaksa za sortiranje zapisov je:

SELECT stolpec1, stolpec2
FROM imeTabele
ORDER BY izbraniStolpec;

Privzeto se zapisi uredijo naraščajoče. Če želimo padajoče razvrščanje, uporabimo DESC:

SELECT *
FROM knjige
ORDER BY Leto DESC;

Za naraščajoče razvrščanje lahko po želji uporabimo oznako ASC:

SELECT *
FROM knjige
ORDER BY Naslov ASC;

V spletni aplikaciji lahko uporabniku omogočimo, da izbere tako stolpec kot tudi smer razvrščanja.

Osnovni primer z mysqli

Spodnji zgled prikaže razvrščene zapise tabele knjige. Ker imena stolpcev ne moremo varno vezati kot običajni parameter, moramo dovoljene stolpce predhodno preveriti.

<?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.

Osnovni primer s PDO

Tudi z vmesnikom PDO sortiranje izvedemo z ukazom ORDER BY. Tudi tu moramo ime stolpca najprej preveriti na seznamu dovoljenih vrednosti.

<?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.

KorakmysqliPDO
Izvedba poizvedbemysqli_query()$pdo->query()
Branje vrsticemysqli_fetch_assoc()$stmt->fetchAll() ali $stmt->fetch()
Preverjanje stolpcain_array()in_array()
Dovoljeni smeriASC, DESCASC, DESC
Zaščita izpisahtmlspecialchars()htmlspecialchars()

📘Aplikacija Knjige

V priloženi aplikaciji Knjige je sortiranje izvedeno v datoteki 10_sort.php. Aplikacija najprej določi seznam dovoljenih stolpcev za razvrščanje, med katerimi so ID_knjige, Priimek_avtorja, Ime_avtorja, Naslov, Strani, Cena in Leto.

Nato iz URL parametrov prebere izbrani stolpec in vrstni red. Če stolpec ni dovoljen, uporabi privzeti stolpec ID_knjige; za smer pa dovoli le ASC ali DESC. Nato sestavi poizvedbo SELECT ... FROM knjige ORDER BY ....

Na strani so glave stolpcev klikljive. Ob kliku se stran znova naloži z novima parametroma column in order. Aktivni stolpec je posebej označen, uporabniku pa se izpišeta tudi trenutna razvrstitev in število zapisov.

Primer: aplikacija Knjige – 10_sort.php
  1. Najprej določimo, po katerih stolpcih sme uporabnik razvrščati zapise.
  2. Pripravimo uporabniku prijazne oznake stolpcev za prikaz v tabeli.
  3. Iz URL parametrov ali obrazca preberemo izbrani stolpec in smer razvrščanja.
  4. Preverimo, ali je stolpec dovoljen.
  5. Preverimo, ali je smer razvrščanja enaka ASC ali DESC.
  6. Izvedemo poizvedbo z ukazom ORDER BY.
  7. Glave stolpcev pripravimo kot povezave, ki ob kliku spremenijo smer razvrščanja.
  8. Uporabniku prikažemo trenutno razvrstitev, število zapisov in preglednico rezultatov.

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.

Vprašanja in komentarji

Vprašanje je lahko zasebno ali vidno vsem, ki imajo dostop do strani.

0
Za pošiljanje vprašanja se prijavite.

Za to stran še ni vprašanj.

MySQL Zgledi