Zastosowanie paginacji do wyświetlania wielu rekordów przy użyciu MySQL z PHP na Ubuntu 20.04

Shreyas Patil2022-06-27 · 15 min read

Zastosowanie paginacji do wyświetlania wielu rekordów przy użyciu MySQL z PHP na Ubuntu 20.04

Wprowadzenie

Stronicowanie jest ważną koncepcją przy budowaniu aplikacji opartych na danych. Podczas pobierania rekordów z bazy danych może zostać pobrana duża liczba rekordów. Na przykład podczas uruchamiania zapytania o aktywnych użytkowników lub użytkowników należących do określonej lokalizacji może zostać zwrócona duża liczba użytkowników, idąca w setki, a nawet tysiące. Wyświetlanie tak ogromnej liczby rekordów na jednej stronie internetowej nie jest rozwiązaniem przyjaznym dla użytkownika, ponieważ może go przytłoczyć, a także zepsuć płynność korzystania z aplikacji.

Dlatego preferowaną opcją jest ograniczenie liczby rekordów. Jest to koncepcja stronicowania. Innymi słowy, dzielimy liczbę rekordów na strony, a następnie wyświetlamy daną stronę. Pojedyncza strona może zawierać dziesięć, piętnaście lub dowolną inną rozsądną liczbę rekordów, które można wyświetlić na froncie.

Chociaż stronicowanie może poprawić ogólne wrażenia użytkownika z korzystania z aplikacji, może również przyspieszyć jej działanie, ponieważ możemy pobierać mniejszą liczbę rekordów. Dzięki temu przez sieć między klientem a serwerem przesyłanych jest mniej danych, co skraca czas opóźnienia.

W tym samouczku będziemy budować kod PHP, który łączy się z bazą danych, i wdrażać stronicowanie przy użyciu bazy danych MySQL i jej klauzuli LIMIT clause.

Wymagania wstępne

Aby móc śledzić ten samouczek, będziesz potrzebować:

Najnowszej, działającej wersji systemu Ubuntu. Postępuj zgodnie z instrukcjami w samouczku dotyczącym konfiguracji serwera Ubuntu, jeśli potrzebujesz pomocy.
Musisz mieć również zainstalowane PHP i MySQL. Aby skonfigurować PHP i MySQL, zapoznaj się z naszym samouczkiem: Konfiguracja stosu LAMP – Linux Apache MySQL PHP.

Krok 1 — Konfiguracja użytkownika bazy danych i testowej bazy danych

W tym samouczku nawiążemy połączenie z bazą danych MySQL i pobierzemy rekordy. Rekordy te zostaną wyświetlone na stronie HTML za pomocą elementu tabeli. Do nawiązania połączenia i wyświetlenia rekordów użyjemy skryptu PHP. Gdy połączenie z bazą danych będzie gotowe, przetestujemy naszą aplikację ze stronicowaniem i bez niego. Taki sposób testowania pozwoli nam zrozumieć, jak stronicowanie działa w praktyce.

Aby połączyć się z bazą danych MySQL, będziemy potrzebować przykładowej bazy danych MySQL oraz danych uwierzytelniających użytkownika. W tym kroku utworzysz użytkownika innego niż root dla swojej bazy danych MySQL, przykładową bazę danych oraz tabelę do przetestowania skryptu PHP.

Najpierw zaloguj się na swój serwer. Następnie zaloguj się do serwera MySQL za pomocą poniższego polecenia:

mysql> sudo mysql -u root -p

1	mysql> sudo mysql -u root -p

Zostaniesz poproszony o hasło użytkownika root. Wprowadź hasło i naciśnij Enter, aby przejść dalej. Następnie utwórz przykładową bazę danych o nazwie test_db, której będziemy używać w naszym samouczku. Aby utworzyć nową bazę danych, uruchom poniższe polecenie:

mysql> Create database test_db;

1	mysql> Create database test_db;

W odpowiedzi z serwera MySQL zobaczysz informację, że zmiana dotyczy jednego wiersza. Następnie musimy utworzyć nowego użytkownika dla tej bazy danych. Nadamy temu użytkownikowi wszystkie uprawnienia. Jednak podczas pracy z rzeczywistymi aplikacjami pamiętaj o ograniczeniu uprawnień. Naszego użytkownika nazwiemy test_user. W poniższym poleceniu zastąp PASSWORD silnym hasłem:

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 utworzeniu użytkownika odśwież uprawnienia:

mysql> FLUSH PRIVILEGES;

1	mysql> FLUSH PRIVILEGES;

Teraz, gdy zarówno test_user, jak i test_db są gotowe, użyj test_db, aby utworzyć tabele. Aby przełączyć się na test_db, uruchom poniższe polecenie:

mysql> Use test_db;

1	mysql> Use test_db;

Gdy w danych wyjściowych zobaczysz informację o zmianie bazy danych, możemy przystąpić do tworzenia tabeli. Utworzymy nową tabelę o nazwie Products do przechowywania produktów. Na nasze potrzeby będziemy potrzebować tylko dwóch kolumn w tej tabeli: product_id oraz product_name. Ustawimy product_id na AUTO_INCREMENT, dzięki czemu będzie zwiększana za każdym razem, gdy dodamy nowy produkt. Druga kolumna o nazwie product_name będzie służyć do przechowywania nazwy produktu. Kolumna product_name będzie służyć do rozróżniania każdego produktu po nazwie:

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;

Now we will add some produktów do tej nowo utworzonej tabeli. W tym celu uruchom poniższe polecenia:

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

Aby zweryfikować, czy te produkty zostały wprowadzone do tabeli, uruchom poniższe polecenie:

mysql> select * from products;

1	mysql> select * from products;

Jeśli widzisz produkty wprowadzone powyżej, możesz przejść dalej. Wyjdź z bazy danych MySQL za pomocą poniższego polecenia:

mysql> quit;

1	mysql> quit;

Teraz, gdy skonfigurowaliśmy bazę danych MySQL i wprowadziliśmy dane testowe, jesteśmy gotowi do napisania skryptu PHP, który połączy się z tą bazą danych i pobierze rekordy.

Krok 2 – Pobieranie i wyświetlanie rekordów bez stronicowania

Jak omówiono powyżej, najpierw wyświetlimy rekordy bez stronicowania. Zbudujemy skrypt PHP, który łączy się z bazą danych MySQL i pobiera rekordy. Połączymy się z test_db utworzoną powyżej, używając test_user i pobierzemy rekordy.

Nasza tabela products zawiera obecnie tylko dziesięć rekordów. Dla wielu użytkowników może nie być oczywiste, czy stronicowanie jest potrzebne, czy też nie. Jednak przetestowanie aplikacji bez stronicowania wyjaśni, jak pomaga ono w ogólnym odbiorze aplikacji. Zrozumiemy również, jak podział danych pomaga w tworzeniu lepszych doświadczeń użytkownika i zmniejsza obciążenie serwera.

Na swoim serwerze PHP, w katalogu głównym dokumentów folderu witryny, uruchom poniższe polecenie:

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

Teraz dodaj do tego pliku poniższą zawartość. Upewnij się, że zastąpisz PASSWORD wartością hasła swojego użytkownika 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();

}

Zapisz plik. Zanim przejdziemy dalej, podsumujemy to, co zrobiliśmy powyżej:

Najpierw połączyliśmy się z bazą danych MySQL i w tym celu użyliśmy PHP Data Object (PDO) biblioteki. Użyliśmy danych uwierzytelniających użytkownika MySQL, których użyliśmy powyżej, aby połączyć się z bazą danych MySQL. PDO to niezwykle pomocna biblioteka służąca do łączenia się z bazą danych. Sprawia, że warstwa dostępu do danych jest łatwa do zakodowania i pozwala na łączenie się z różnymi bazami danych bez konieczności znacznej refaktoryzacji aplikacji. PDO korzysta z przygotowanych zapytań (prepared statements), co gwarantuje, że bezpieczeństwo nie zostanie naruszone. Dzięki temu zapytania są wykonywane bezpiecznie.

Następnie użyliśmy PDO API do wykonania zapytania select * from products. Instrukcja $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false) zapewnia, że typy danych w PHP są takie same jak w bazie danych. Dzięki temu product_id oraz product_name pojawiają się odpowiednio jako liczba całkowita i ciąg znaków.
Instrukcja $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); nakazuje PDO zgłoszenie wyjątku w przypadku wystąpienia błędu. Dzięki temu możemy łatwo przechwycić wyjątek w bloku try … catch i obsłużyć go w odpowiedni sposób.

Teraz, gdy nasz skrypt jest gotowy, musimy go uruchomić. W tym celu otwórz przeglądarkę i przejdź pod adres URL: /var/www/html/pagination_test.php. Upewnij się, że uzupełnisz adres URL o adres IP swojego serwera. Jeśli uruchamiasz go lokalnie, Twój adres URL będzie wyglądał następująco: http://localhost/var/www/html/pagination_test.php.

Zobaczysz następujący wynik:

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

W tym miejscu nasze produkty są zwracane i wyświetlane na stronie. Teraz wyobraź sobie, że mielibyśmy setki produktów. Skutkowałoby to długą pętlą pobierania danych i wyświetlania ich na stronie. Zwiększy to czas ładowania strony.

W kolejnej sekcji zmodyfikujemy skrypt PHP i dołączymy klauzulę MySQL LIMIT. Rozwiąże to problem wyświetlania dużej liczby rekordów. Dodamy linki stronicowania, aby użytkownik mógł poruszać się między stronami.

Krok 3 – Konfiguracja stronicowania przy użyciu PHP

W tym kroku skonfigurujemy stronicowanie przy użyciu PHP, aby wyświetlić dane za pomocą wielu tabel. Aby dostosować stronicowanie, zmodyfikujemy skrypt, który zakodowaliśmy w poprzednim kroku.

Użyjemy klauzuli LIMIT bazy danych MySQL. Przed dodaniem jej do skryptu zobaczmy przykład składni 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;

W powyższym zapytaniu widzimy, że klauzula LIMIT przyjmuje argumenty offset oraz records. offset określa, ile rekordów należy pominąć, a records określa maksymalną liczbę rekordów do wyświetlenia na stronie lub pobrania z bazy danych.

Skoro już wiemy, jak działa stronicowanie, zobaczmy, jak je zaimplementujemy. Będziemy wyświetlać trzy rekordy na stronę. Jeśli więc mamy dziesięć rekordów w bazie danych, będziemy mieć cztery strony, dzieląc całkowitą liczbę rekordów przez liczbę rekordów na stronę. Ponieważ wynik może nie być liczbą całkowitą, użyjemy funkcji PHP Ceil, aby zaokrąglić wynik w górę do najbliższej liczby całkowitej. Poniżej znajduje się prosty fragment kodu demonstrujący funkcję Ceil:

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

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

Teraz dodamy kod do stronicowania. Aby włączyć stronicowanie i dodać linki nawigacyjne, otwórz plik /var/www/html/pagination_test.php za pomocą poniższego polecenia:

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

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

Następnie zastąp zawartość tego pliku poniższym kodem:

<?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);
    
        /* Begin Paging Info */
        $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;
        /* End Paging Info */
 
        $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>";

        /* Begin Navigation */
        echo "<table border='1' align='center'>";

        echo "<tr>";

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

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

        echo "</tr>";

        echo "</table>";
        /* End Navigation */
    } 
    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);

/* Początek informacji o stronicowaniu */

$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 informacji o stronicowaniu */

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

/* Początek nawigacji */

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

echo "<tr>";

if ($page-1 >= 1) {

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

}

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

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Następna</a></td>";

}

echo "</tr>";

echo "</table>";

/* Koniec nawigacji */

}

catch(PDOException $e) {

echo $e->getMessage();

}

Oto podsumowanie tego kodu:

Musimy przechowywać informację o stronie, na której obecnie się znajdujemy. W tym celu używamy zmiennej $page. Zmienna $page służy do pobrania strony, na której obecnie się znajdujemy, przy użyciu zmiennej $_GET[‘page’].
Następnie musimy określić, ile elementów wyświetlamy na stronie. W tym celu używamy zmiennej $per_page zmienna. Tutaj wyświetlamy tylko trzy produkty na stronę.
Jak omówiono powyżej, musimy znać całkowitą liczbę rekordów istniejących w bazie danych. Jest to potrzebne, abyśmy mogli określić całkowitą liczbę stron. Zmienna $total_records przechowuje te informacje.
Na koniec musimy wiedzieć, ile rekordów należy pominąć. Na przykład, jeśli jesteśmy na drugiej stronie, musimy pominąć pierwsze trzy rekordy w bazie danych. Używamy zmiennej $offset do przechowywania tych informacji. Obliczyliśmy te informacje przy każdym ładowaniu strony za pomocą wzoru $offset=($page-1)*$per_page. Jeśli chcesz wyświetlić inną liczbę elementów, możesz określić tę wartość w zmiennej $per_page.
Aby wyświetlić przyciski nawigacyjne, używamy poniższego kodu:

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

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

. . .

. . .

if( $page-1>=1) {

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

}

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

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Następna</a></td>";

}

. . .

Logika polega na tym, że zmienna $page służy do przechowywania bieżącej strony. Jeśli chcemy wyświetlić poprzednią opcję na stronie, skrypt odejmie jeden od wartości zmiennej $page. Jeśli wynik jest większy lub równy jeden, na stronie zostanie wyświetlona poprzednia opcja.

Podobnie, aby wyświetlić następną opcję, dodajemy jeden do zmiennej $page. W tym miejscu upewniamy się, że wynik dodawania nie przekroczy całkowitej liczby stron w zmiennej $total_pages.

Następnie nadszedł czas na sprawdzenie strony. Otwórz przeglądarkę i przejdź do strony, korzystając z adresu URL użytego wcześniej:

http://your_server_ip/pagination_test.php

1	http://your_server_ip/pagination_test.php

Zobaczysz wynik jak poniżej:

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

table2

table

W ten sposób ostatecznie zaimplementowano stronicowanie przy użyciu klauzuli LIMIT w MySQL w skrypcie PHP. Implementując stronicowanie w swojej aplikacji, zyskasz lepszy sposób nawigacji po wielu rekordach.

Podsumowanie

W tym samouczku przyjrzeliśmy się, jak zaimplementować stronicowanie za pomocą PHP i MySQL na systemie Ubuntu. Korzystając z tych kroków, możesz tworzyć aplikacje, które mogą odpowiednio wyświetlać dużą liczbę rekordów, podzielonych na strony w celu efektywnego zarządzania.

Ponadto mamy więcej pomocnych samouczków dotyczących PHP i MySQL w systemie Ubuntu:

Udanego kodowania!

Shreyas Patil

Autor · CloudSigma

Preslav Dobrev jest projektantem kreatywnym w CloudSigma, skupiającym się na spójnej tożsamości biznesowej przy wykorzystaniu tradycyjnych i innowacyjnych kanałów marketingowych. Biegle łączy wizję artystyczną ze strategicznym marketingiem, tworząc wywierające wpływ narracje marki.

Zastosowanie paginacji do wyświetlania wielu rekordów przy użyciu MySQL z PHP na Ubuntu 20.04

Wprowadzenie

Wymagania wstępne

Krok 1 — Konfiguracja użytkownika bazy danych i testowej bazy danych

Krok 2 – Pobieranie i wyświetlanie rekordów bez stronicowania

Krok 3 – Konfiguracja stronicowania przy użyciu PHP

Podsumowanie

Shreyas Patil

Komentarze