Použití stránkování k zobrazení více záznamů pomocí MySQL s PHP na Ubuntu 20.04

Shreyas Patil2022-06-27 · 15 min read

Použití stránkování k zobrazení více záznamů pomocí MySQL s PHP na Ubuntu 20.04

Úvod

Stránkování je důležitým konceptem při vytváření aplikací řízených daty. Při načítání záznamů z databáze může dojít k načtení velkého množství záznamů. Například při spuštění dotazu na aktivní uživatele nebo uživatele patřící do určité lokality může být vrácen velký počet uživatelů v řádu stovek nebo dokonce tisíců. Zobrazení tohoto obrovského množství záznamů na jediné webové stránce není uživatelsky přívětivé řešení, protože může uživatele zahltit a také narušit plynulost uživatelského zážitku.

Proto je lepším řešením omezit počet záznamů. To je koncept stránkování. Jinými slovy, rozdělíme počet záznamů na stránky a ty pak zobrazíme. Jedna stránka může obsahovat deset, patnáct nebo jakékoli jiné proveditelné množství záznamů, které lze zobrazit na front-endu.

Zatímco stránkování může zlepšit celkový uživatelský zážitek z aplikace, může ji také zrychlit, protože můžeme načítat menší počet záznamů. Tím se přenáší méně dat po síti mezi klientem a serverem, což snižuje latenci.

V tomto návodu budeme vytvářet PHP kód, který se připojí k databázi, a implementovat stránkování pomocí databáze MySQL a její LIMIT klauzule.

Požadavky

Chcete-li postupovat podle tohoto návodu, budete potřebovat:

Nejnovější verzi Ubuntu, spuštěnou a funkční. Postupujte podle návodu Jak nastavit server Ubuntu, pokud potřebujete pomoc.
Budete také muset mít nainstalované PHP a MySQL. Pro nastavení PHP a MySQL máme návod: Instalace LAMP Stacku – Linux Apache MySQL PHP.

Krok 1 — Nastavení databázového uživatele a testovací databáze

V tomto návodu navážeme připojení k databázi MySQL a načteme záznamy. Tyto záznamy se zobrazí na HTML stránce pomocí elementu tabulky. K navázání připojení a zobrazení záznamů použijeme PHP skript. Jakmile budeme mít připojení k databázi připravené, otestujeme naši aplikaci se stránkováním i bez něj. Testování tímto způsobem nám umožní pochopit, jak stránkování funguje v praxi.

K připojení k databázi MySQL budeme potřebovat ukázkovou databázi MySQL a autentizační údaje uživatele pro připojení k databázi MySQL. V tomto kroku vytvoříte uživatele bez oprávnění root pro vaši databázi MySQL, ukázkovou databázi a tabulku pro otestování PHP skriptu.

Nejprve se přihlaste ke svému serveru. Poté se přihlaste k serveru MySQL pomocí níže uvedeného příkazu:

mysql> sudo mysql -u root -p

1	mysql> sudo mysql -u root -p

Budete vyzváni k zadání hesla uživatele root. Zadejte heslo a stiskněte Enter, abyste se posunuli dále. Dále vytvořte ukázkovou databázi s názvem test_db, kterou budeme v našem návodu používat. Chcete-li vytvořit novou databázi, spusťte níže uvedený příkaz:

mysql> Create database test_db;

1	mysql> Create database test_db;

Uvidíte výstup ze serveru MySQL, že byl ovlivněn jeden řádek. Dále budeme muset pro tuto databázi vytvořit nového uživatele. Tomuto uživateli udělíme všechna oprávnění. Při práci s reálnými aplikacemi se však ujistěte, že oprávnění omezíte. Našeho uživatele pojmenujeme test_user. V níže uvedeném příkazu nahraďte PASSWORD silným heslem:

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';

Jakmile je uživatel vytvořen, obnovte oprávnění (flush privileges):

mysql> FLUSH PRIVILEGES;

1	mysql> FLUSH PRIVILEGES;

Nyní, když jsou jak test_user, tak test_db připraveny, použijte test_db k vytvoření tabulek. Chcete-li přepnout na test_db, spusťte níže uvedený příkaz:

mysql> Use test_db;

1	mysql> Use test_db;

Jakmile ve výstupu uvidíme, že se databáze změnila, jsme připraveni vytvořit naši tabulku. Vytvoříme novou tabulku s názvem Products pro uložení produktů. Pro naše účely budeme v této tabulce potřebovat pouze dva sloupce: product_id a product_name. Nastavíme product_id na AUTO_INCREMENT, aby se zvýšil pokaždé, když přidáme nový produkt. Druhý sloupec s názvem product_name bude sloužit k uložení názvu produktu. Sloupec product_name bude sloužit k rozlišení jednotlivých produktů podle 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;

Nyní do této nově vytvořené tabulky přidáme nějaké produkty do této nově vytvořené tabulky. K tomu spusťte níže uvedené pří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');

Chcete-li ověřit, zda byly tyto produkty vloženy do tabulky, spusťte níže uvedený příkaz:

mysql> select * from products;

1	mysql> select * from products;

Pokud vidíte produkty, které jste zadali výše, můžete pokračovat. Ukončete databázi MySQL pomocí níže uvedeného příkazu:

mysql> quit;

1	mysql> quit;

Nyní, když jsme nastavili databázi MySQL a zadali testovací data, jsme připraveni napsat PHP skript pro připojení k této databázi a načtení záznamů.

Step 2 – Načtení a zobrazení záznamů bez stránkování

Jak bylo uvedeno výše, nejprve zobrazíme záznamy bez stránkování. Vytvoříme PHP skript, který se připojí k databázi MySQL a načte záznamy. Připojíme se k test_db vytvořené výše pomocí test_user a načteme záznamy.

Our products má v současné době pouze deset záznamů. Mnoha uživatelům nemusí být zřejmé, zda je stránkování potřeba, či nikoli. Otestování aplikace bez stránkování však podrobněji ukáže, jak pomáhá celkovému dojmu z aplikace. Pochopíme také, jak rozdělení dat pomáhá vytvářet lepší uživatelskou zkušenost a snižuje zatížení serveru.

Na svém PHP serveru v kořenovém adresáři webu spusťte níže uvedený příkaz:

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

Nyní do tohoto souboru přidejte níže uvedený obsah. Ujistěte se, že nahradíte PASSWORD hodnotou hesla vašeho 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();

}

Soubor uložte. Než se posuneme dál, shrneme si, co jsme udělali výše:

Nejprve jsme se připojili k databázi MySQL a k tomu jsme použili PHP Data Object (PDO) knihovnu. K připojení k databázi MySQL jsme použili přihlašovací údaje uživatele MySQL, které jsme použili výše. PDO je mimořádně užitečná knihovna, která se používá k připojení k databázi. Usnadňuje kódování vrstvy přístupu k datům a umožňuje připojení k různým databázím bez nutnosti rozsáhlého refaktorování aplikace. PDO používá připravené dotazy (prepared statements), což zajišťuje, že není ohrožena bezpečnost. Dotazy jsou proto prováděny bezpečně.

Dále jsme použili PDO API k provedení příkazu select * from products . Nastavení $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false) zajišťuje, že datové typy v PHP jsou stejné jako v databázi. Proto se product_id a product_name zobrazují jako celé číslo, respektive řetězec.
Nastavení $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); říká PDO, aby v případě chyby vyhodilo výjimku. Díky tomu můžeme výjimku snadno zachytit v bloku try … catch a zpracovat ji podle potřeby.

Nyní, když máme náš skript připravený, musíme ho spustit. Za tímto účelem otevřete prohlížeč a přejděte na URL: /var/www/html/pagination_test.php. Ujistěte se, že k URL připojíte IP adresu vašeho serveru. Pokud skript spouštíte lokálně, vaše URL bude http://localhost/var/www/html/pagination_test.php.

Uvidíte následující výstup:

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

Zde se vrátí naše produkty a zobrazí se na stránce. Nyní si představte, že bychom měli stovky produktů. Výsledkem by byl dlouhý cyklus pro načtení dat a jejich zobrazení na stránce. To by prodloužilo dobu načítání stránky.

V další části upravíme PHP skript a zahrneme MySQL LIMIT klauzuli. Tím se vyřeší problém se zobrazováním velkého počtu záznamů. Přidáme odkazy na stránkování, aby uživatel mohl procházet stránkami.

Krok 3 – Nastavení stránkování pomocí PHP

V tomto kroku nastavíme stránkování pomocí PHP pro zobrazení dat pomocí více tabulek. Abychom stránkování přizpůsobili, upravíme skript, který jsme naprogramovali v předchozím kroku.

Budeme používat LIMIT klauzuli databáze MySQL. Před jejím přidáním do skriptu se podívejme na pří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;

Ve výše uvedeném příkazu vidíme, že klauzule LIMIT obsahuje argumenty offset a records. offset určuje, kolik záznamů se má přeskočit, a records určuje maximální počet záznamů, které se mají zobrazit na stránce nebo načíst z databáze.

Nyní, když víme, jak stránkování funguje, se podívejme na to, jak ho budeme implementovat. Na každé stránce zobrazíme tři záznamy. Pokud tedy máme v databázi deset záznamů, budeme mít čtyři stránky, což zjistíme vydělením celkového počtu záznamů počtem záznamů na stránku. Vzhledem k tomu, že výsledek nemusí být celé číslo, použijeme PHP funkci Ceil pro zaokrouhlení na nejbližší celé číslo nahoru, abychom získali celočíselný výsledek. Níže je jednoduchý fragment kódu pro demonstraci funkce Ceil :

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

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

Nyní přidáme kód pro stránkování. Chcete-li povolit stránkování a přidat navigační odkazy, otevřete soubor /var/www/html/pagination_test.php pomocí níže uvedeného příkazu:

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

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

Dále nahraďte obsah tohoto souboru níže uvedeným kódem:

<?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čátek informací o stránkování */
        $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;
        /* Konec informací o stránkování */
 
        $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čátek navigace */
        echo "<table border='1' align='center'>";

        echo "<tr>";

        if ($page-1 >= 1) {
            echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page - 1).">Předchozí</a></td>";
            }

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

        echo "</tr>";

        echo "</table>";
        /* Konec navigace */
    } 
    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čátek informací o stránkování */

$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;

/* Konec informací o stránkování */

$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čátek navigace */

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

echo "<tr>";

if ($page-1 >= 1) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page - 1).">Předchozí</a></td>";

}

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

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

}

echo "</tr>";

echo "</table>";

/* Konec navigace */

}

catch(PDOException $e) {

echo $e->getMessage();

}

Zde je shrnutí tohoto kódu:

Musíme uchovávat informace o stránce, na které se právě nacházíme. K tomu používáme $page proměnnou. Proměnná $page se používá k získání stránky, na které se právě nacházíme, pomocí $_GET[‘page’] proměnné.
Dále musíme určit, kolik stránek zobrazujeme na stránce. K tomu používáme $per_page proměnná. Zde zobrazujeme pouze tři produkty na stránku.
Jak bylo diskutováno výše, potřebujeme mít celkový počet záznamů, které v databázi existují. To je nutné k tomu, abychom mohli určit celkový počet stránek. $total_records proměnná uchovává tyto informace.
Nakonec potřebujeme vědět, kolik záznamů máme přeskočit. Pokud jsme například na druhé stránce, musíme přeskočit první tři záznamy v databázi. Používáme k tomu $offset proměnnou pro uchování této informace. Tuto informaci jsme vypočítali při každém načtení stránky pomocí vzorce $offset=($page-1)*$per_page. Pokud potřebujete zobrazit jakýkoli jiný počet položek, můžete hodnotu určit v $per_page proměnné.
Pro zobrazení navigačních tlačítek použijeme níže uvedený kód:

. . .
if( $page-1>=1) {
    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Předchozí</a></td>";
    }

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

. . .

. . .

if( $page-1>=1) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Předchozí</a></td>";

}

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

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

}

. . .

Logika je taková, že $page se používá k uchování aktuální stránky. Pokud chceme na stránce zobrazit předchozí možnost, skript odečte jedničku od hodnoty $page proměnné. Pokud je výsledek větší nebo roven jedné, zobrazí se na stránce předchozí možnost.

Podobně pro zobrazení další možnosti přičteme jedničku k $page proměnné. Zde zajišťujeme, aby výsledek sčítání nepřekročil celkový počet stránek v $total_pages proměnné.

Poté je čas stránku zkontrolovat. Otevřete prohlížeč a přejděte na stránku pomocí adresy URL, kterou jste použili dříve:

http://your_server_ip/pagination_test.php

1	http://your_server_ip/pagination_test.php

Uvidíte výstup jako níže:

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

table2

table

Zde jste konečně implementovali stránkování pomocí klauzule LIMIT v MySQL v PHP skriptu. Implementací stránkování ve vaší aplikaci získáte lepší způsob navigace mezi více záznamy.

Závěr

V tomto návodu jsme se podívali na to, jak implementovat stránkování pomocí PHP a MySQL na Ubuntu. Pomocí těchto kroků můžete vytvářet aplikace, které dokážou adekvátně zobrazit velké množství záznamů rozdělených na stránky pro efektivní manipulaci.

Kromě toho máme další užitečné návody pro PHP a MySQL na Ubuntu:

Příjemnou práci s počítačem!

Shreyas Patil

Autor · CloudSigma

Preslav Dobrev je kreativní designér ve společnosti CloudSigma, který se zaměřuje na konzistentní firemní identitu prostřednictvím tradičních i inovativních marketingových kanálů. Je zdatný v propojování umělecké vize se strategickým marketingem za účelem vytváření působivých příběhů značky.

Použití stránkování k zobrazení více záznamů pomocí MySQL s PHP na Ubuntu 20.04

Úvod

Požadavky

Krok 1 — Nastavení databázového uživatele a testovací databáze

Step 2 – Načtení a zobrazení záznamů bez stránkování

Krok 3 – Nastavení stránkování pomocí PHP

Závěr

Shreyas Patil

Komentáře