Použitie stránkovania na zobrazenie viacerých záznamov pomocou MySQL s PHP na Ubuntu 20.04

Shreyas Patil2022-06-27 · 15 min read

Použitie stránkovania na zobrazenie viacerých záznamov pomocou MySQL s PHP na Ubuntu 20.04

Úvod

Stránkovanie je dôležitým konceptom pri budovaní aplikácií riadených dátami. Pri vyťahovaní záznamov z databázy sa môže načítať veľké množstvo záznamov. Napríklad pri spustení dopytu na aktívnych používateľov alebo používateľov patriacich do určitej konkrétnej lokality sa môže vrátiť veľký počet používateľov v stovkách alebo dokonca tisícoch. Zobrazenie tohto obrovského množstva záznamov na jednej webovej stránke nie je používateľsky prívetivé riešenie, pretože môže používateľa zahltiť a zároveň narušiť plynulosť používateľského zážitku.

Preto je preferovanou možnosťou obmedziť počet záznamov. Toto je koncept stránkovania. Inými slovami, rozdelíme počet záznamov na stránky a potom stránku zobrazíme. Jedna stránka môže mať desať alebo dokonca pätnásť alebo akékoľvek iné množstvo realizovateľných záznamov, ktoré je možné zobraziť na front-ende.

Hoci stránkovanie môže zlepšiť celkový používateľský zážitok z aplikácie, môže ju tiež zrýchliť, keďže môžeme získavať menší počet záznamov. Vďaka tomu sa cez sieť medzi klientom a serverom prenáša menej dát, čo znižuje čas latencie.

V tomto návode budeme vytvárať PHP kód, ktorý sa pripája k databáze, a implementovať stránkovanie pomocou databázy MySQL a jej LIMIT klauzuly.

Požiadavky

Ak chcete postupovať podľa tohto návodu, budete potrebovať:

Najnovšiu verziu Ubuntu, nainštalovanú a spustenú. Postupujte podľa návodu Ako nastaviť server Ubuntu, ak potrebujete pomoc.
Budete tiež musieť mať nainštalované PHP a MySQL. Na nastavenie PHP a MySQL máme návod: Inštalácia LAMP Stacku – Linux Apache MySQL PHP.

Krok 1 — Nastavenie databázového používateľa a testovacej databázy

V tomto návode nadviažeme databázové pripojenie s databázou MySQL a načítame záznamy. Tieto záznamy sa zobrazia na HTML stránke pomocou prvku tabuľky. Na nadviazanie pripojenia a zobrazenie záznamov použijeme PHP skript. Keď budeme mať databázové pripojenie pripravené, otestujeme našu aplikáciu so stránkovaním a bez stránkovania. Testovanie týmto spôsobom nám umožní pochopiť, ako stránkovanie funguje v praxi.

Na pripojenie k databáze MySQL budeme potrebovať ukážkovú databázu MySQL a overovacie údaje používateľa na pripojenie k databáze MySQL. V tomto kroku vytvoríte používateľa bez oprávnení root pre vašu databázu MySQL, ukážkovú databázu a tabuľku na otestovanie PHP skriptu.

Najprv sa prihláste na svoj server. Potom sa prihláste do MySQL servera pomocou príkazu nižšie:

mysql> sudo mysql -u root -p

1	mysql> sudo mysql -u root -p

Budete vyzvaní na zadanie hesla pre používateľa root. Zadajte heslo a stlačte Enter, aby ste pokračovali. Ďalej vytvorte ukážkovú databázu s názvom test_db, ktorú budeme používať v našom návode. Ak chcete vytvoriť novú databázu, spustite príkaz nižšie:

mysql> Create database test_db;

1	mysql> Create database test_db;

Na výstupe z MySQL servera uvidíte, že bol ovplyvnený jeden riadok. Ďalej budeme musieť pre túto databázu vytvoriť nového používateľa. Tomuto používateľovi udelíme všetky privilégiá. Pri práci s reálnymi aplikáciami sa však uistite, že privilégiá obmedzíte. Nášho používateľa nazveme test_user. V príkaze nižšie nahraďte PASSWORD silným heslom:

mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';

1	mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';

Po vytvorení používateľa obnovte privilégiá (flush):

mysql> FLUSH PRIVILEGES;

1	mysql> FLUSH PRIVILEGES;

Teraz, keď sú test_user aj test_db pripravené, použite test_db na vytvorenie tabuliek. Ak chcete prepnúť na test_db, spustite príkaz nižšie:

mysql> Use test_db;

1	mysql> Use test_db;

Keď na výstupe uvidíme, že sa databáza zmenila, sme pripravení vytvoriť našu tabuľku. Vytvoríme novú tabuľku s názvom Products na uchovávanie produktov. Pre naše účely budeme v tejto tabuľke potrebovať iba dva stĺpce: product_id a product_name. Nastavíme product_id na AUTO_INCREMENT, aby sa zvyšovala zakaždým, keď pridáme nový produkt. Druhý stĺpec s názvom product_name sa použije na uloženie názvu produktu. Stĺpec product_name sa použije na odlíšenie jednotlivých produktov podľa názvu:

mysql> Create table products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50) NOT NULL ) Engine = InnoDB;

1	mysql> Create table products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50) NOT NULL ) Engine = InnoDB;

Teraz pridáme niekoľko produktov do tejto novovytvorenej tabuľky. Na to spustite nasledujúce príkazy:

mysql> Insert into products(product_name) values ('WIRELESS MOUSE');
mysql> Insert into products(product_name) values ('BLUETOOTH SPEAKER');
mysql> Insert into products(product_name) values ('GAMING KEYBOARD');
mysql> Insert into products(product_name) values ('320GB FAST SSD');
mysql> Insert into products(product_name) values ('17 INCHES TFT');
mysql> Insert into products(product_name) values ('SPECIAL HEADPHONES');
mysql> Insert into products(product_name) values ('HD GRAPHIC CARD');
mysql> Insert into products(product_name) values ('80MM THERMAL PRINTER');
mysql> Insert into products(product_name) values ('HDMI TO VGA CONVERTER');
mysql> Insert into products(product_name) values ('FINGERPRINT SCANNER');

mysql> Insert into products(product_name) values ('WIRELESS MOUSE');

mysql> Insert into products(product_name) values ('BLUETOOTH SPEAKER');

mysql> Insert into products(product_name) values ('GAMING KEYBOARD');

mysql> Insert into products(product_name) values ('320GB FAST SSD');

mysql> Insert into products(product_name) values ('17 INCHES TFT');

mysql> Insert into products(product_name) values ('SPECIAL HEADPHONES');

mysql> Insert into products(product_name) values ('HD GRAPHIC CARD');

mysql> Insert into products(product_name) values ('80MM THERMAL PRINTER');

mysql> Insert into products(product_name) values ('HDMI TO VGA CONVERTER');

mysql> Insert into products(product_name) values ('FINGERPRINT SCANNER');

Ak chcete overiť, či boli tieto produkty vložené do tabuľky, spustite nasledujúci príkaz:

mysql> select * from products;

1	mysql> select * from products;

Ak vidíte produkty, ktoré ste zadali vyššie, môžete pokračovať. Ukončite databázu MySQL pomocou nasledujúceho príkazu:

mysql> quit;

1	mysql> quit;

Teraz, keď sme nastavili databázu MySQL a zadali testovacie dáta, sme pripravení napísať PHP skript na pripojenie k tejto databáze a načítanie záznamov.

Krok 2 – Načítanie a zobrazenie záznamov bez stránkovania

Ako bolo uvedené vyššie, najprv zobrazíme záznamy bez stránkovania. Vytvoríme PHP skript, ktorý sa pripojí k databáze MySQL a načíta záznamy. Pripojíme sa k test_db, ktorú sme vytvorili vyššie, pomocou test_user a načítame záznamy.

Naša tabuľka products má momentálne iba desať záznamov. Pre mnohých používateľov nemusí byť zrejmé, či je stránkovanie potrebné alebo nie. Testovanie aplikácie bez stránkovania však podrobnejšie ukáže, ako pomáha celkovému dojmu z aplikácie. Pochopíme tiež, ako rozdelenie údajov pomáha vytvárať lepšiu používateľskú skúsenosť a znižuje zaťaženie servera.

Na vašom PHP serveri v koreňovom adresári dokumentov (document root) priečinka webovej lokality spustite nasledujúci príkaz:

sudo nano /var/www/html/pagination_test.php

Teraz do tohto súboru pridajte nasledujúci obsah. Uistite sa, že nahradíte PASSWORD hodnotou hesla vášho test_user:

<?php
    try {
        $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
        $sql = "select * from products";
        $stmt = $pdo->prepare($sql);
        $stmt -> execute();
        echo "<table border='1' align='center'>";
        while (($row = $stmt -> fetch(PDO::FETCH_ASSOC)) !== false) {
            echo "<tr>";
            echo "<td>".$row['product_id']."</td>";
            echo "<td>".$row['product_name']."</td>";
            echo "</tr>";
        }
        echo "</table>";
    } 

    catch(PDOException $e) {
        echo $e->getMessage();
    }
?>

<?php

try {

$pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

$sql = "select * from products";

$stmt = $pdo->prepare($sql);

$stmt -> execute();

echo "<table border='1' align='center'>";

while (($row = $stmt -> fetch(PDO::FETCH_ASSOC)) !== false) {

echo "<tr>";

echo "<td>".$row['product_id']."</td>";

echo "<td>".$row['product_name']."</td>";

echo "</tr>";

}

echo "</table>";

}

catch(PDOException $e) {

echo $e->getMessage();

}

Uložte súbor. Predtým, ako sa pohneme ďalej, zhrnieme si, čo sme urobili vyššie:

Najprv sme sa pripojili k databáze MySQL a na to sme použili PHP Data Object (PDO) knižnicu. Na pripojenie k databáze MySQL sme použili prihlasovacie údaje používateľa MySQL, ktorého sme uviedli vyššie. PDO je mimoriadne užitočná knižnica, ktorá sa používa na pripojenie k databáze. Zjednodušuje kódovanie vrstvy prístupu k údajom a umožňuje pripojenie k rôznym databázam bez nutnosti rozsiahleho refaktorovania aplikácie. PDO používa pripravené príkazy (prepared statements), čo zaisťuje, že bezpečnosť nie je ohrozená. Dopyty sa preto vykonávajú bezpečne.

Ďalej sme použili PDO API na vykonanie príkazu select * from products. Príkaz $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false) zabezpečuje, že dátové typy v PHP sú rovnaké ako v databáze. Preto sa product_id a product_name zobrazujú ako celé číslo a reťazec.
Príkaz $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); hovorí PDO, aby v prípade chyby vyvolalo výnimku. Vďaka tomu môžeme výnimku ľahko zachytiť v bloku try … catch a spracovať ju podľa potreby.

Teraz, keď máme náš skript pripravený, musíme ho spustiť. Otvorte prehliadač a prejdite na URL adresu: /var/www/html/pagination_test.php. Uistite sa, že k URL adrese pripojíte IP adresu vášho servera. Ak skript spúšťate lokálne, vaša URL adresa bude http://localhost/var/www/html/pagination_test.php.

Uvidíte nasledujúci výstup:

Applying Pagination to Display Multiple Records using MySQL with PHP image 1

Tu sa vrátia naše produkty a zobrazia sa na stránke. Teraz si predstavte, že by sme mali stovky produktov. Výsledkom by bol dlhý cyklus na načítanie údajov a ich zobrazenie na stránke. To by predĺžilo čas načítania stránky.

V nasledujúcej časti upravíme PHP skript a zahrnieme MySQL LIMIT klauzulu. Tým sa vyrieši problém so zobrazovaním veľkého počtu záznamov. Pridáme odkazy na stránkovanie, aby používateľ mohol prechádzať medzi stránkami.

Krok 3 – Nastavenie stránkovania pomocou PHP

V tomto kroku nastavíme stránkovanie pomocou PHP na zobrazenie údajov pomocou viacerých tabuliek. Aby sme prispôsobili stránkovanie, upravíme skript, ktorý sme napísali v predchádzajúcom kroku.

Budeme používať LIMIT klauzulu databázy MySQL. Predtým, ako ju pridáme do skriptu, pozrime sa na príklad syntaxe MySQL LIMIT :

mysql> Select [column1, column2, column n...] from [table name] LIMIT offset, records;

1	mysql> Select [column1, column2, column n...] from [table name] LIMIT offset, records;

Vo vyššie uvedenom príkaze môžeme vidieť, že LIMIT klauzula má offset a records argumenty. offset hovorí, koľko záznamov sa má preskočiť a records určuje maximálny počet záznamov, ktoré sa majú zobraziť na stránke alebo načítať z databázy.

Teraz, keď už vieme, ako stránkovanie funguje, pozrime sa na to, ako ho budeme implementovať. Na stránke zobrazíme tri záznamy. Ak teda máme v databáze desať záznamov, budeme mať štyri stránky vydelením celkového počtu záznamov počtom záznamov na stránku. Keďže výsledok nemusí byť celé číslo, použijeme PHP funkciu Ceil na zaokrúhlenie na najbližšie celé číslo, aby sme dostali celočíselný výsledok. Nižšie je uvedený jednoduchý úryvok kódu na demonštráciu funkcie Ceil :

$total_pages=ceil($total_records/$per_page);

1	$total_pages=ceil($total_records/$per_page);

Teraz pridáme kód pre stránkovanie. Ak chcete povoliť stránkovanie a pridať navigačné odkazy, otvorte súbor /var/www/html/pagination_test.php pomocou príkazu nižšie:

$ sudo nano /var/www/html/pagination_test.php

1	$ sudo nano /var/www/html/pagination_test.php

Ďalej nahraďte obsah tohto súboru nižšie uvedeným kódom:

<?php
    try {
        $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
    
        /* Začiatok informácií o stránkovaní */
        $page = 1;

        if (isset($_GET['page'])) {
            $page = filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);
        }
        $per_page = 3;
        $sqlcount = "select count(*) as total_records from products";
        $stmt = $pdo->prepare($sqlcount);
        $stmt->execute();
        $row = $stmt->fetch();
        $total_records = $row['total_records'];
        $total_pages = ceil($total_records / $per_page);
        $offset = ($page-1) * $per_page;
        /* Koniec informácií o stránkovaní */
 
        $sql = "select * from products limit :offset, :per_page";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(['offset'=>$offset, 'per_page'=>$per_page]);

        echo "<table border='1' align='center'>";

        while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {
            echo "<tr>";
            echo "<td>".$row['product_id']."</td>";
            echo "<td>".$row['product_name']."</td>";
            echo "</tr>";
        }

        echo "</table>";

        /* Začiatok navigácie */
        echo "<table border='1' align='center'>";

        echo "<tr>";

        if ($page-1 >= 1) {
            echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page - 1).">Predchádzajúce</a></td>";
            }

        if ($page+1 <= $total_pages) {
            echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Nasledujúce</a></td>";
        }

        echo "</tr>";

        echo "</table>";
        /* Koniec navigácie */
    } 
    catch(PDOException $e) {
        echo $e->getMessage();
    }
?>

<?php

try {

$pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

/* Začiatok informácií o stránkovaní */

$page = 1;

if (isset($_GET['page'])) {

$page = filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);

}

$per_page = 3;

$sqlcount = "select count(*) as total_records from products";

$stmt = $pdo->prepare($sqlcount);

$stmt->execute();

$row = $stmt->fetch();

$total_records = $row['total_records'];

$total_pages = ceil($total_records / $per_page);

$offset = ($page-1) * $per_page;

/* Koniec informácií o stránkovaní */

$sql = "select * from products limit :offset, :per_page";

$stmt = $pdo->prepare($sql);

$stmt->execute(['offset'=>$offset, 'per_page'=>$per_page]);

echo "<table border='1' align='center'>";

while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {

echo "<tr>";

echo "<td>".$row['product_id']."</td>";

echo "<td>".$row['product_name']."</td>";

echo "</tr>";

}

echo "</table>";

/* Začiatok navigácie */

echo "<table border='1' align='center'>";

echo "<tr>";

if ($page-1 >= 1) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page - 1).">Predchádzajúce</a></td>";

}

if ($page+1 <= $total_pages) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Ďalšie</a></td>";

}

echo "</tr>";

echo "</table>";

/* Koniec navigácie */

}

catch(PDOException $e) {

echo $e->getMessage();

}

Tu je zhrnutie tohto kódu:

Musíme uchovávať informácie o stránke, na ktorej sa práve nachádzame. Na to používame $page premennú. Premenná $page sa používa na získanie stránky, na ktorej sa práve nachádzame, pomocou $_GET[‘page’] premennej.
Ďalej musíme určiť, koľko položiek zobrazujeme na stránke. Na to používame $per_page premenná. Tu zobrazujeme iba tri produkty na stránku.
Ako bolo uvedené vyššie, potrebujeme mať celkový počet záznamov, ktoré existujú v databáze. Je to potrebné na to, aby sme mohli určiť celkový počet stránok. $total_records premenná uchováva tieto informácie.
Nakoniec potrebujeme vedieť, koľko záznamov máme preskočiť. Napríklad, ak sme na druhej stránke, musíme preskočiť prvé tri záznamy v databáze. Používame $offset premennú na uchovanie tejto informácie. Túto informáciu sme vypočítali pri každom načítaní stránky pomocou vzorca $offset=($page-1)*$per_page. Ak potrebujete zobraziť akýkoľvek iný počet položiek, môžete hodnotu zadať v $per_page premennej.
Na zobrazenie navigačných tlačidiel používame nižšie uvedený kód:

. . .
if( $page-1>=1) {
    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Predchádzajúca</a></td>";
    }

if( $page+1<=$total_pages) {
    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Ďalšia</a></td>";
}

. . .

. . .

if( $page-1>=1) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Predchádzajúca</a></td>";

}

if( $page+1<=$total_pages) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Ďalšia</a></td>";

}

. . .

Logika je taká, že $page sa používa na uchovanie aktuálnej stránky. Ak chceme na stránke zobraziť predchádzajúcu možnosť, skript odpočíta jednotku od hodnoty $page premennej. Ak je výsledok väčší alebo rovný jednej, na stránke sa zobrazí predchádzajúca možnosť.

Podobne, pre zobrazenie nasledujúcej možnosti, pripočítame jednotku k $page premennej. Tu zabezpečujeme, aby výsledok sčítania neprekročil celkový počet stránok v $total_pages premennej.

Potom je čas skontrolovať stránku. Otvorte prehliadač a prejdite na stránku pomocou URL adresy, ktorú ste použili predtým:

http://your_server_ip/pagination_test.php

1	http://your_server_ip/pagination_test.php

Uvidíte nasledujúci výstup:

Applying Pagination to Display Multiple Records using MySQL with PHP image 2

table2

table

Tu ste nakoniec implementovali stránkovanie pomocou klauzuly LIMIT v MySQL v PHP skripte. Implementáciou stránkovania vo vašej aplikácii získate lepší spôsob navigácie v rámci viacerých záznamov.

Záver

V tomto návode sme sa pozreli na to, ako implementovať stránkovanie pomocou PHP a MySQL na Ubuntu. Pomocou týchto krokov môžete vytvárať aplikácie, ktoré dokážu adekvátne zobraziť veľké množstvo záznamov rozdelených na stránky pre efektívnu manipuláciu.

Okrem toho máme ďalšie užitočné návody pre PHP a MySQL na Ubuntu:

Príjemnú prácu!

Shreyas Patil

Autor · CloudSigma

Preslav Dobrev je kreatívny dizajnér v spoločnosti CloudSigma, ktorý sa zameriava na konzistentnú firemnú identitu prostredníctvom tradičných a inovatívnych marketingových kanálov. Dokáže brilantne spájať umeleckú víziu so strategickým marketingom, čím vytvára pôsobivé príbehy značky.

Použitie stránkovania na zobrazenie viacerých záznamov pomocou MySQL s PHP na Ubuntu 20.04

Úvod

Požiadavky

Krok 1 — Nastavenie databázového používateľa a testovacej databázy

Krok 2 – Načítanie a zobrazenie záznamov bez stránkovania

Krok 3 – Nastavenie stránkovania pomocou PHP

Záver

Shreyas Patil

Komentáre