Applicazione della paginazione per visualizzare più record utilizzando MySQL con PHP su Ubuntu 20.04

Shreyas Patil2022-06-27 · 16 min read

Applicazione della paginazione per visualizzare più record utilizzando MySQL con PHP su Ubuntu 20.04

Introduzione

Paginazione è un concetto importante nella creazione di applicazioni basate sui dati. Quando si estraggono i record dal database, potrebbe essere recuperato un gran numero di record. Ad esempio, quando si esegue la query per gli utenti attivi o per gli utenti appartenenti a una posizione specifica, può essere restituito un gran numero di utenti, nell'ordine di centinaia o addirittura migliaia. Visualizzare questo enorme numero di record su una singola pagina web non è una soluzione user-friendly, in quanto può sopraffare l'utente e rovinare la fluidità dell'esperienza utente.

Pertanto, un'opzione preferibile è limitare il numero di record. Questo è il concetto di paginazione. In altre parole, dividiamo il numero di record in pagine e poi visualizziamo la pagina. Una singola pagina può contenere dieci o anche quindici o qualsiasi altra quantità di record fattibili che possono essere visualizzati sul front-end.

Sebbene la paginazione possa migliorare l'esperienza utente complessiva dell'applicazione, può anche renderla più veloce poiché possiamo recuperare un numero ridotto di record. Di conseguenza, vengono trasferiti meno dati sulla rete tra client e server, riducendo il tempo di latenza.

In questo tutorial, andremo a creare un codice PHP che si connette al database e implementa la paginazione utilizzando il MySQL database e la sua clausola LIMIT clause.

Prerequisiti

Per seguire questo tutorial, avrai bisogno di:

L'ultima versione di Ubuntu, attiva e funzionante. Segui il tutorial su come configurare il server Ubuntu se hai bisogno di assistenza.
Dovrai anche avere installato PHP e MySQL. Per configurare PHP e MySQL, abbiamo un tutorial: Configurazione dello stack LAMP – Linux Apache MySQL PHP.

Passo 1 — Configurare un utente del database e un database di test

In questo tutorial, stabiliremo la connessione al database con il database MySQL e recupereremo i record. Questi record verranno visualizzati su una pagina HTML con l'elemento table. Per stabilire la connessione e visualizzare i record utilizzeremo uno script PHP. Una volta pronti con la connessione al database, testeremo la nostra applicazione con e senza paginazione. Testare in questo modo ci permetterà di capire come funziona praticamente la paginazione.

Per connetterci al database MySQL avremo bisogno di un database MySQL di esempio e delle informazioni di autenticazione dell'utente per connettersi al database MySQL. In questo passaggio, creerai un utente non root per il tuo database MySQL, un database di esempio e una tabella per testare lo script PHP.

Prima di tutto, accedi al tuo server. Quindi, accedi al server MySQL utilizzando il comando seguente:

mysql> sudo mysql -u root -p

1	mysql> sudo mysql -u root -p

Ti verrà richiesta la password dell'utente root. Inserisci la password e premi Invio per andare avanti. Successivamente, crea un database di esempio, chiamato test_db, che utilizzeremo nel nostro tutorial. Per creare un nuovo database, esegui il comando seguente:

mysql> Create database test_db;

1	mysql> Create database test_db;

Vedrai l'output dal server MySQL che indica che una riga è stata interessata. Successivamente, dovremo creare un nuovo utente per questo database. Concederemo a questo utente tutti i privilegi. Tuttavia, quando si lavora con applicazioni reali, assicurati di limitare i privilegi. Chiameremo il nostro utente test_user. Nel comando seguente, sostituisci PASSWORD con una password sicura:

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

Una volta creato l'utente, esegui il flush dei privilegi:

mysql> FLUSH PRIVILEGES;

1	mysql> FLUSH PRIVILEGES;

Now that both test_user and test_db are ready, use the test_db to make tables. To switch to test_db, run the below command:

mysql> Use test_db;

1	mysql> Use test_db;

Una volta visualizzato il database modificato nell'output, siamo pronti per creare la nostra tabella. Creeremo una nuova tabella chiamata Products per contenere i prodotti. Per i nostri scopi, avremo bisogno di solo due colonne in questa tabella: product_id e product_name. Imposteremo product_id su AUTO_INCREMENT, in modo che venga incrementato ogni volta che aggiungiamo un nuovo prodotto. L'altra colonna chiamata product_name verrà utilizzata per memorizzare il nome del prodotto. La colonna product_name verrà utilizzata per differenziare ciascun prodotto in base al nome:

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;

Ora aggiungeremo alcuni prodotti a questa tabella appena creata. Per fare questo, esegui i comandi seguenti:

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

Per verificare se questi prodotti sono stati inseriti nella tabella, esegui il comando seguente:

mysql> select * from products;

1	mysql> select * from products;

Se vedi i prodotti che hai inserito sopra, sei pronto per procedere. Esci dal database MySQL utilizzando il comando seguente:

mysql> quit;

1	mysql> quit;

Ora che abbiamo configurato il database MySQL e inserito i dati di test, siamo pronti a scrivere il codice dello script PHP per connetterci a questo database e recuperare i record.

Passo 2 – Recupero e visualizzazione dei record senza paginazione

Come discusso sopra, mostreremo prima i record senza paginazione. Creeremo uno script PHP che si connette al database MySQL e recupera i record. Ci connetteremo al test_db che abbiamo creato sopra utilizzando l'utente test_user e recupereremo i record.

La nostra tabella products ha attualmente solo dieci record. Potrebbe non essere ovvio per molti utenti se la paginazione sia necessaria o meno. Tuttavia, testare l'applicazione senza la paginazione spiegherà in dettaglio come questa aiuti nell'esperienza complessiva dell'applicazione. Capiremo anche come la suddivisione dei dati aiuti a creare una migliore esperienza utente e riduca il carico sul server.

Nel tuo server PHP, nella document root della cartella del sito web, esegui il comando seguente:

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

Ora in questo file, aggiungi il contenuto seguente. Assicurati di sostituire PASSWORD con il valore della password del tuo 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();

}

Salva il file. Prima di procedere, riassumeremo ciò che abbiamo fatto sopra:

In primo luogo, ci siamo connessi al database MySQL, e per questo abbiamo utilizzato la PHP Data Object (PDO) libreria. Abbiamo utilizzato le credenziali dell'utente MySQL che avevamo usato sopra per connetterci al database MySQL. PDO è una libreria estremamente utile che viene utilizzata per connettersi al database. Rende lo strato di accesso ai dati facile da codificare e consente di connettersi a diversi database senza dover rifattorizzare ampiamente l'applicazione. PDO utilizza prepared statement che garantiscono che la sicurezza non venga compromessa. Di conseguenza, le query vengono eseguite in modo sicuro.

Successivamente, abbiamo utilizzato l' PDO API per eseguire l'istruzione select * from products . L'istruzione $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false) garantisce che i tipi di dati in PHP siano gli stessi del database. Di conseguenza, product_id e product_name appaiono rispettivamente come intero e stringa.
L'istruzione $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); indica a PDO di lanciare un'eccezione in caso di errore. In questo modo, possiamo facilmente intercettare l'eccezione nel blocco try … catch e gestirla come necessario.

Ora che il nostro script è pronto, dobbiamo eseguirlo. Per fare ciò, apri il browser e visita l'URL: /var/www/html/pagination_test.php. Assicurati di aggiungere l'IP del tuo server all'URL. Se lo stai eseguendo localmente, il tuo URL diventerà http://localhost/var/www/html/pagination_test.php.

Vedrai il seguente output:

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

Qui, i nostri prodotti vengono restituiti e visualizzati sulla pagina. Ora, immagina se avessimo centinaia di prodotti. Ciò comporterebbe un lungo ciclo per recuperare i dati e visualizzarli sulla pagina. Questo aumenterà il tempo di caricamento della pagina.

Nella sezione successiva, modificheremo lo script PHP e includeremo il MySQL LIMIT clausola. Questo risolverà il problema della visualizzazione di un gran numero di record. Aggiungeremo i link di paginazione in modo che l'utente possa navigare tra le pagine.

Passo 3 – Configurazione della paginazione tramite PHP

In questo passo, configureremo la paginazione utilizzando PHP per visualizzare i dati utilizzando più tabelle. Per adattare la paginazione, modificheremo lo script che avevamo codificato nel passo precedente.

Utilizzeremo la LIMIT clausola del database MySQL. Prima di aggiungere questo allo script, vediamo un esempio della sintassi MySQL LIMIT sintassi:

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;

Nell'istruzione sopra possiamo vedere che la LIMIT clausola ha gli argomenti offset e records. offset indica quanti record saltare e records indica il numero massimo di record da visualizzare per pagina o da recuperare dal database.

Ora che sappiamo come funziona la paginazione, vediamo come la implementeremo. Visualizzeremo tre record per pagina. Quindi, se abbiamo dieci record nel nostro database, avremo quattro pagine dividendo il numero totale di record per il numero di record per pagina. Poiché il risultato potrebbe non essere un numero intero, utilizzeremo la funzione PHP Ceil per arrotondare all'intero superiore più vicino per ottenere il risultato come numero intero. Di seguito è riportato un semplice frammento di codice per dimostrare la funzione Ceil funzione:

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

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

Ora aggiungeremo il codice per la paginazione. Per abilitare la paginazione e aggiungere i link di navigazione, apri il file /var/www/html/pagination_test.php utilizzando il comando seguente:

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

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

Successivamente, sostituisci il contenuto di questo file con il codice seguente:

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

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

/* Inizio info paginazione */

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

/* Fine info paginazione */

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

/* Inizio navigazione */

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

echo "<tr>";

if ($page-1 >= 1) {

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

}

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

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

}

echo "</tr>";

echo "</table>";

/* Fine navigazione */

}

catch(PDOException $e) {

echo $e->getMessage();

}

Ecco un riepilogo di questo codice:

Dobbiamo conservare le informazioni sulla pagina in cui ci troviamo attualmente. Per questo stiamo utilizzando la $page variabile. La $page variabile viene utilizzata per estrarre la pagina in cui ci troviamo attualmente utilizzando la $_GET[‘page’] variabile.
Successivamente, dobbiamo specificare quante pagine stiamo visualizzando su una pagina. Per questo stiamo utilizzando la $per_page variabile. Qui stiamo visualizzando solo tre prodotti per pagina.
Come discusso sopra, dobbiamo avere il numero totale di record esistenti nel database. Questo è necessario per poter identificare il numero totale di pagine. La $total_records variabile contiene questa informazione.
Infine, dobbiamo sapere quanti record saltare. Ad esempio, se ci troviamo sulla seconda pagina, dobbiamo saltare i primi tre record del database. Stiamo utilizzando la $offset variabile per contenere questa informazione. Abbiamo calcolato questa informazione a ogni caricamento della pagina utilizzando la formula $offset=($page-1)*$per_page. Se hai bisogno di visualizzare un qualsiasi altro numero di elementi, puoi specificare il valore nella $per_page variabile.
Per visualizzare i pulsanti di navigazione, utilizziamo il codice seguente:

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

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

. . .

. . .

if( $page-1>=1) {

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

}

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

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

}

. . .

La logica è che $page viene utilizzata per contenere la pagina corrente. Se vogliamo visualizzare l'opzione precedente sulla pagina, lo script sottrarrà uno dal valore della $page variabile. Se il risultato è maggiore o uguale a uno, l'opzione precedente viene visualizzata sulla pagina.

Allo stesso modo, per visualizzare l'opzione successiva, aggiungiamo uno alla $page variabile. Qui ci assicuriamo che il risultato dell'addizione non superi il numero totale di pagine nella $total_pages variabile.

Dopodiché, è il momento di controllare la pagina. Apri il browser e naviga alla pagina utilizzando l'URL che hai usato in precedenza:

http://your_server_ip/pagination_test.php

1	http://your_server_ip/pagination_test.php

Vedrai l'output come segue:

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

table2

table

Qui hai finalmente implementato la paginazione utilizzando la clausola LIMIT di MySQL nello script PHP. Implementando la paginazione nella tua applicazione, avrai un modo migliore per navigare tra più record.

Conclusione

In questo tutorial, abbiamo visto come implementare la paginazione utilizzando PHP e MySQL su Ubuntu. Utilizzando questi passaggi, puoi creare applicazioni in grado di visualizzare adeguatamente un gran numero di record, suddivisi tra le pagine per una manipolazione efficace.

Inoltre, abbiamo altri tutorial utili su PHP e MySQL utilizzando Ubuntu:

Buona programmazione!

Shreyas Patil

Autore · CloudSigma

Preslav Dobrev è un designer creativo presso CloudSigma, con un focus su un'identità aziendale coerente attraverso l'uso di canali di marketing tradizionali e innovativi. È abile nel fondere la visione artistica con il marketing strategico per creare narrazioni di brand di grande impatto.

Applicazione della paginazione per visualizzare più record utilizzando MySQL con PHP su Ubuntu 20.04

Introduzione

Prerequisiti

Passo 1 — Configurare un utente del database e un database di test

Passo 2 – Recupero e visualizzazione dei record senza paginazione

Passo 3 – Configurazione della paginazione tramite PHP

Conclusione

Shreyas Patil

Commenti