Paginering toepassen om meerdere records weer te geven met MySQL met PHP op Ubuntu 20.04

Shreyas Patil2022-06-27 · 17 min read

Paginering toepassen om meerdere records weer te geven met MySQL met PHP op Ubuntu 20.04

Introductie

Paginering is een belangrijk concept bij het bouwen van datagestuurde applicaties. Bij het ophalen van records uit de database kan een groot aantal records worden opgehaald. Bijvoorbeeld, bij het uitvoeren van een query voor actieve gebruikers of gebruikers die tot een specifieke locatie behoren, kan een groot aantal gebruikers in de honderden of zelfs duizenden worden geretourneerd. Het weergeven van dit enorme aantal records op een enkele webpagina is geen gebruiksvriendelijke oplossing, omdat het de gebruiker kan overweldigen en de soepelheid van de gebruikerservaring kan verpesten.

Daarom is een betere optie om het aantal records te beperken. Dit is het concept van paginering. Met andere woorden, we verdelen het aantal records in pagina's en tonen vervolgens de pagina. Een enkele pagina kan tien of zelfs vijftien of een ander haalbaar aantal records bevatten dat op de front-end kan worden weergegeven.

Hoewel paginering de algehele gebruikerservaring van de applicatie kan verbeteren, kan het de applicatie ook sneller maken omdat we een kleiner aantal records kunnen ophalen. Hierdoor wordt er minder data over het netwerk tussen client en server verzonden, wat de latentietijd verkort.

In deze handleiding zullen we een PHP-code bouwen die verbinding maakt met de database en paginering implementeert met behulp van de MySQL-database en de LIMIT-clausule.

Vereisten

Om deze handleiding te kunnen volgen, heb je het volgende nodig:

De nieuwste versie van Ubuntu, geïnstalleerd en werkend. Volg de handleiding 'Hoe stel je een Ubuntu-server in' als je hulp nodig hebt.
Je moet ook PHP en MySQL geïnstalleerd hebben. Om PHP en MySQL in te stellen, hebben we een handleiding: LAMP Stack installatie – Linux Apache MySQL PHP.

Stap 1 — Een databasegebruiker en een testdatabase instellen

In deze handleiding zullen we de databaseverbinding met de MySQL-database tot stand brengen en de records ophalen. Deze records worden weergegeven op een HTML-pagina met het tabelelement. Om de verbinding tot stand te brengen en records weer te geven, gebruiken we een PHP-script. Zodra we klaar zijn met de databaseverbinding, testen we onze applicatie met en zonder paginering. Door op deze manier te testen, kunnen we begrijpen hoe paginering in de praktijk werkt.

Om verbinding te maken met de MySQL-database hebben we een voorbeeld-MySQL-database en de inloggegevens van de gebruiker nodig om verbinding te maken met de MySQL-database. In deze stap maak je een niet-rootgebruiker aan voor je MySQL-database, een voorbeeld-database en een tabel om het PHP-script te testen.

Log eerst in op je server. Log vervolgens in op de MySQL-server met het onderstaande commando:

mysql> sudo mysql -u root -p

1	mysql> sudo mysql -u root -p

Er wordt gevraagd om het wachtwoord van de root-gebruiker. Voer het wachtwoord in en druk op Enter om verder te gaan. Maak vervolgens een voorbeeld-database aan, genaamd test_db, die we in onze handleiding zullen gebruiken. Voer het onderstaande commando uit om een nieuwe database aan te maken:

mysql> Create database test_db;

1	mysql> Create database test_db;

Je ziet de uitvoer van de MySQL-server dat er één rij is beïnvloed. Vervolgens moeten we een nieuwe gebruiker voor deze database aanmaken. We zullen deze gebruiker alle rechten verlenen. Zorg er echter bij praktische toepassingen voor dat je de rechten beperkt. We noemen onze gebruiker test_user. Vervang in het onderstaande commando PASSWORD door een sterk wachtwoord:

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

Zodra de gebruiker is aangemaakt, vernieuw je de rechten:

mysql> FLUSH PRIVILEGES;

1	mysql> FLUSH PRIVILEGES;

Nu zowel test_user als test_db gereed zijn, gebruik je test_db om tabellen te maken. Om over te schakelen naar test_db, voer je het onderstaande commando uit:

mysql> Use test_db;

1	mysql> Use test_db;

Zodra we in de uitvoer zien dat de database is gewijzigd, zijn we klaar om onze tabel te maken. We maken een nieuwe tabel genaamd Products om de producten in op te slaan. Voor onze doeleinden hebben we slechts twee kolommen in deze tabel nodig: product_id en product_name. We stellen de product_id in op AUTO_INCREMENT, zodat deze telkens wordt verhoogd wanneer we een nieuw product toevoegen. De andere kolom genaamd product_name zal worden gebruikt om de naam van het product op te slaan. De product_name kolom zal worden gebruikt om elk product op naam te onderscheiden:

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;

Nu zullen we enkele producten toevoegen aan deze nieuw gemaakte tabel. Voer hiervoor de onderstaande commando's uit:

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

Om te controleren of deze producten in de tabel zijn ingevoerd, voert u het onderstaande commando uit:

mysql> select * from products;

1	mysql> select * from products;

Als u de producten ziet die u hierboven hebt ingevoerd, bent u klaar om verder te gaan. Sluit de MySQL-database af met het onderstaande commando:

mysql> quit;

1	mysql> quit;

Nu we de MySQL-database hebben ingesteld en testgegevens hebben ingevoerd, zijn we klaar om het PHP-script te schrijven om verbinding te maken met deze database en records op te halen.

Stap 2 – Records ophalen en weergeven zonder paginering

Zoals hierboven besproken, zullen we eerst de records weergeven zonder paginering. We gaan een PHP-script bouwen dat verbinding maakt met de MySQL-database en de records ophaalt. We maken verbinding met de test_db die we hierboven hebben gemaakt met behulp van de test_user en halen de records op.

Onze products tabel heeft momenteel slechts tien records. Het is voor veel gebruikers misschien niet meteen duidelijk of paginering wel of niet nodig is. Het testen van de applicatie zonder paginering zal echter verduidelijken hoe dit bijdraagt aan de algehele ervaring van de applicatie. We zullen ook begrijpen hoe het splitsen van de gegevens helpt bij het creëren van een betere gebruikerservaring en de belasting van de server vermindert.

Voer op uw PHP-server, in de document root van de websitemap, het onderstaande commando uit:

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

Voeg nu in dit bestand de onderstaande inhoud toe. Zorg ervoor dat u PASSWORD vervangt door de wachtwoordwaarde van uw 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();

}

Sla het bestand op. Voordat we verder gaan, zullen we samenvatten wat we hierboven hebben gedaan:

Eerst hebben we verbinding gemaakt met de MySQL-database, en hiervoor hebben we de PHP Data Object (PDO)-bibliotheek gebruikt. We hebben de inloggegevens gebruikt van de MySQL-gebruiker die we hierboven hadden gebruikt om verbinding te maken met de MySQL-database. PDO is een uiterst nuttige bibliotheek die wordt gebruikt om verbinding te maken met de database. Het maakt de gegevenstoegangslaag eenvoudig te coderen en maakt het mogelijk om verbinding te maken met diverse databases zonder de applicatie uitgebreid te hoeven herstructureren. PDO maakt gebruik van prepared statements, wat ervoor zorgt dat de beveiliging niet in het gedrang komt. Hierdoor worden query's veilig uitgevoerd.

Vervolgens hebben we de PDO API gebruikt om de select * from products-instructie uit te voeren. De $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false) zorgt ervoor dat de datatypen in PHP hetzelfde zijn als in de database. Hierdoor verschijnen product_id en product_name respectievelijk als integer en string.
De $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); vertelt PDO om een uitzondering te genereren als er een fout optreedt. Hierdoor kunnen we de uitzondering eenvoudig opvangen in het try … catch-blok en deze naar behoefte afhandelen.

Nu we ons script klaar hebben, moeten we dit uitvoeren. Open hiervoor je browser en bezoek de URL: /var/www/html/pagination_test.php. Zorg ervoor dat je het IP-adres van je server aan de URL toevoegt. Als je lokaal draait, wordt je URL http://localhost/var/www/html/pagination_test.php.

Je ziet de onderstaande uitvoer:

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

Hier worden onze producten geretourneerd en op de pagina weergegeven. Stel je nu voor dat we honderden producten hadden. Dit zou resulteren in een lange lus om de gegevens op te halen en op de pagina weer te geven. Dit zal de laadtijd van de pagina verlengen.

In de volgende sectie zullen we het PHP-script aanpassen en de MySQL LIMIT-clausule opnemen. Dit zal het probleem van het weergeven van een groot aantal records oplossen. We zullen de pagineringslinks toevoegen zodat de gebruiker door de pagina's kan navigeren.

Stap 3 – Paginering instellen met PHP

In deze stap gaan we paginering instellen met PHP om de gegevens in meerdere tabellen weer te geven. Om paginering mogelijk te maken, zullen we het script aanpassen dat we in de vorige stap hebben gecodeerd.

We zullen gebruikmaken van de LIMIT-clausule van de MySQL-database. Voordat we dit aan het script toevoegen, kijken we naar een voorbeeld van de MySQL LIMIT-syntaxis:

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;

In de bovenstaande instructie kunnen we zien dat de LIMIT-clausule offset en records-argumenten heeft. offset geeft aan hoeveel records moeten worden overgeslagen en records geeft het maximale aantal records aan dat per pagina moet worden weergegeven of uit de database moet worden opgehaald.

Nu we weten hoe paginering werkt, gaan we kijken hoe we dit gaan implementeren. We tonen drie records per pagina. Dus als we tien records in onze database hebben, hebben we vier pagina's door het totale aantal records te delen door het aantal records per pagina. Omdat het resultaat mogelijk geen heel getal is, gebruiken we de PHP Ceil-functie om af te ronden naar het dichtstbijzijnde gehele getal om een heel getal als resultaat te krijgen. Hieronder staat een eenvoudig codefragment om de Ceil-functie te demonstreren:

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

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

Nu voegen we de code voor de paginering toe. Om paginering in te schakelen en de navigatielinks toe te voegen, open je het bestand /var/www/html/pagination_test.php-bestand met het onderstaande commando:

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

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

Vervang vervolgens de inhoud van dit bestand door de onderstaande code:

<?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).">Vorige</a></td>";
            }

        if ($page+1 <= $total_pages) {
            echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Volgende</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", "WACHTWOORD");

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

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

/* Begin pagineringsinfo */

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

/* Einde pagineringsinfo */

$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 navigatie */

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

echo "<tr>";

if ($page-1 >= 1) {

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

}

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

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

}

echo "</tr>";

echo "</table>";

/* Einde navigatie */

}

catch(PDOException $e) {

echo $e->getMessage();

}

Hier is een samenvatting van deze code:

We moeten de informatie bewaren over de pagina waar we ons momenteel op bevinden. Hiervoor gebruiken we de $page variabele. De $page variabele wordt gebruikt om de pagina te bepalen waar we ons momenteel op bevinden met behulp van de $_GET[‘page’] variabele.
Vervolgens moeten we specificeren hoeveel pagina's we op een pagina weergeven. Hiervoor gebruiken we de $per_page variabele. Hier tonen we slechts drie producten per pagina.
Zoals hierboven besproken, moeten we het totale aantal records hebben dat in de database bestaat. Dit is nodig zodat we het totale aantal pagina's kunnen identificeren. De $total_records variabele bevat deze informatie.
Ten slotte moeten we weten hoeveel records we moeten overslaan. Als we bijvoorbeeld op de tweede pagina zijn, moeten we de eerste drie records van de database overslaan. We gebruiken de $offset variabele om deze informatie te bewaren. We hebben deze informatie bij elke paginalading berekend met behulp van de formule $offset=($page-1)*$per_page. Als u een ander aantal items wilt weergeven, kunt u de waarde opgeven in de $per_page variabele.
Om de navigatieknoppen weer te geven, gebruiken we de onderstaande code:

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

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

. . .

. . .

if( $page-1>=1) {

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

}

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

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

}

. . .

De logica is dat de $page wordt gebruikt om de huidige pagina te bevatten. Als we de vorige optie op de pagina willen weergeven, trekt het script één af van de waarde van de $page variabele. Als het resultaat groter of gelijk is aan één, wordt de vorige optie op de pagina weergegeven.

Op dezelfde manier tellen we, om de volgende optie weer te geven, één op bij de $page variabele. Hier zorgen we ervoor dat het resultaat van de optelling niet groter is dan het totale aantal pagina's in de $total_pages variabele.

Daarna is het tijd om de pagina te controleren. Open de browser en navigeer naar de pagina met de URL die u eerder hebt gebruikt:

http://your_server_ip/pagination_test.php

1	http://your_server_ip/pagination_test.php

U ziet de uitvoer zoals hieronder:

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

table2

table

Hier hebt u eindelijk de paginering geïmplementeerd met behulp van de LIMIT clausule van MySQL in het PHP-script. Door de paginering in uw applicatie te implementeren, hebt u een betere manier om door meerdere records te navigeren.

Conclusie

In deze handleiding hebben we gekeken naar het implementeren van paginering met behulp van PHP en MySQL op Ubuntu. Met behulp van deze stappen kunt u applicaties bouwen die een groot aantal records adequaat kunnen weergeven, verdeeld over de pagina's voor effectieve manipulatie.

Daarnaast hebben we nog meer nuttige handleidingen over PHP en MySQL met Ubuntu:

Veel computerplezier!

Shreyas Patil

Auteur · CloudSigma

Preslav Dobrev is een creatief ontwerper bij CloudSigma, met de nadruk op een consistente bedrijfsidentiteit door middel van traditionele en innovatieve marketingkanalen. Hij is bedreven in het samenvoegen van artistieke visie met strategische marketing om impactvolle merkverhalen te creëren.

Paginering toepassen om meerdere records weer te geven met MySQL met PHP op Ubuntu 20.04

Introductie

Vereisten

Stap 1 — Een databasegebruiker en een testdatabase instellen

Stap 2 – Records ophalen en weergeven zonder paginering

Stap 3 – Paginering instellen met PHP

Conclusie

Shreyas Patil

Reacties