Estensione MySQLi

Estensione MySQLi: come utilizzarla per gestire database MYSQL

Per memorizzare o accedere ai dati archiviati all'interno di un database MySQL è necessario effettuare prima la connessione allo stesso. PHP consente di svolgere queste operazioni tramite due estensioni: MySQLi (MySQL improved) e PDO (PHP Data Objects).

Questa guida è dedicata a MySQLi e le sue funzioni, vedremo esempi di codice per creare un database ed effettuare le cosiddette CRUD (Create, Read, Update, Delete) ossia le 4 operazioni fondamentali che possiamo effettuare su una base dati.

Cos'è MySQLi

MySQLi (la i sta per improved, che significa migliorata) è un'estensione PHP per la gestione di un database MySQL. È stata sviluppata per sfruttare le nuove funzionalità presenti nella versione di MySQL 4.1.3 e successive. MySQLi è disponibile a partire da PHP 5 e fornisce un modo semplice per connettersi ed eseguire query su un database MySQL.

L'estensione supporta sia il paradigma di programmazione procedurale che quello orientato agli oggetti. Poiché non abbiamo ancora affrontato la programmazione ad oggetti in questa guida useremo l'approccio di tipo procedurale che va bene per applicazioni semplici che non necessitano di continue modifiche.

Per approfondire https://www.php.net/manual/en/mysqli.quickstart.dual-interface.php

Connessione al database

È possibile effettuare la connessione ad un database MySQL usando la funzione PHP mysqli_connect(), la cui sintassi è la seguente

mysqli_connect($host, $username, $password, $dbname);

dove:

• $host è l’indirizzo della macchina dove è installato MySQL. Può essere il nome (in locale sarà "localhost") o l’indirizzo IP.
• $username è il nome dell'utente a cui sono stati concessi i privilegi per eseguire i comandi SQL.
• $password è la password associata all'utente $username.
• $dbname: è il nome del database con il quale vogliamo interagire.

La funzione restituisce false se la connessione non viene stabilita, altrimenti restituirà un oggetto di tipo mysqli. Una volta instaurata una connessione tutte le successive comunicazioni tra PHP e MySQL avverranno tramite questa connessione.

Supponiamo di aver installato MySQL in locale (sul nostro computer) e aver creato l'utente test con password test

Nota: tra le opzioni disponibili per installare MySQL c'è XAMPP, un'ambiente di lavoro che oltre Apache e PHP include anche il DBMS.

Di seguito è mostrato un esempio di utilizzo della funzione mysqli_connect()

<?php
$conn = mysqli_connect("localhost", "test", "test");

if(false === $conn){
    exit("Errore: impossibile stabilire una connessione " . mysqli_connect_error());
}

echo "Connesso: " . mysqli_get_host_info($conn);

Se la connessione va a buon fine dovremmo ottenere il seguente output

Connesso a localhost via TCP/IP

Nota: se si verifica un errore, la funzione mysqli_connect_error() restituisce un messaggio di errore relativo all'ultimo tentativo di connessione.

Chiusura della connessione

La connessione al database verrà chiusa automaticamente non appena termina l'esecuzione dello script. Tuttavia, una volta che abbiamo terminato di lavorare coi dati è buona prassi chiudere la connessione al database per rilasciare le risorse. È possibile farlo tramite la funzione mysqli_close().

Adesso vediamo come creare un database con relative tabelle e dati e come effettuare operazioni su di essi.

La funzione mysqli_query

La funzione mysqli_query() viene utilizzata per eseguire query su un database. La funzione accetta come parametri la connessione al database e una query SQL

mysql_query($conn, $query);

In caso di successo la funzione restituisce true se eseguiamo istruzioni di tipo CREATE, INSERT, DELETE, UPDATE, oppure un oggetto mysqli se eseguiamo una SELECT.

Vediamo adesso come utilizzare la funzione mysqli_query() per creare database e tabelle, inserire dati e operare su di essi.

Creazione database - CREATE DATABASE

Per creare un nuovo database MySQL abbiamo bisogno dell'istruzione SQL CREATE DATABASE, quindi dobbiamo eseguire tale istruzione passandola alla funzione mysqli_query() di PHP per creare il nostro database.

L'esempio seguente crea un database di nome clienti

<?php
$conn = mysqli_connect("localhost", "test", "test");
$sql = "CREATE DATABASE clienti";

if(false === mysqli_query($conn, $sql)) {
    echo "Errore: impossibile eseguire la query. " . mysqli_error($conn);
}
mysqli_close($conn);

Creazione tabelle - CREATE TABLE

Come già sappiamo dalla guida sull'introduzione ai database, i dati sono memorizzati in tabelle con un nome univoco, ciascuna delle quali è composta da righe che rappresentano i record e colonne che sono i campi della tabella.

Ora che abbiamo creato il nostro database non ci resta che creare le tabelle che memorizzeranno i dati. L'istruzione CREATE TABLE viene utilizzata per creare una tabella nel database. È possibile specificare i campi della tabella e il tipo di dato.

Vediamo un esempio di codice PHP per creare una tabella di nome anagrafica per il database clienti

<?php
$conn = mysqli_connect("localhost", "test", "test", "clienti");
$sql = "
CREATE TABLE anagrafica (
    id INT(11) NOT NULL AUTO_INCREMENT,
    nome VARCHAR(255) NOT NULL,
    cognome VARCHAR(255) NOT NULL,
    eta SMALLINT(3) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
";

if(false === mysqli_query($conn, $sql)) {
    exit("Errore: impossibile eseguire la query. " . mysqli_error($conn));
}
echo "Tabella creata con successo";

mysqli_close($conn);

Il codice PHP nell'esempio precedente crea la tabella anagrafica contenente quattro campi: id, nome, cognome, eta.

Possiamo notare che ciascun campo è seguito da una dichiarazione del tipo di dati, la quale specifica quale tipo di dati può contenere la colonna. Il tipo VARCHAR indica un campo di tipo stringa, INT e SMALLINT di tipo intero.

Inoltre abbiamo aggiunto alcuni vincoli specificati dopo il nome della colonna. I vincoli definiscono le regole relative ai valori consentiti nelle colonne.

Ad esempio:

• PRIMARY KEY o chiave primaria è un campo che identifica i record della tabella in modo univoco.
• AUTOINCREMENT indica a MySQL che il campo deve incrementarsi di una unità dopo ogni nuovo inserimento.
• NOT NULL indica che il campo non può essere vuoto.

Per maggiori informazioni fare riferimento alla documentazione MySQL.

Adesso vediamo come è possibile popolare la tabella con alcuni dati.

Inserimento dati - INSERT INTO

L'istruzione INSERT INTO viene utilizzata per inserire dati nella tabella MySQL. Di seguito è mostrata la sintassi:

INSERT INTO nome_tabella (campo1, campo2, ... campoN) VALUES (valore1, valore2, ... valoreN);

Adesso facciamo un esempio concreto di codice PHP utilizzando l'istruzione INSERT INTO con i valori appropriati per la nostra tabella anagrafica, precedentemente creata.

Anche in questo caso ci serviremo della funzione mysqli_query() di PHP per inserire i dati nella tabella.

<?php
$conn = mysqli_connect("localhost", "test", "test", "clienti");
$sql = "INSERT INTO anagrafica (nome, cognome, eta) VALUES ('Mario', 'Rossi', 40)";

if(false === mysqli_query($conn, $sql)) {
    exit("Errore: impossibile eseguire la query. " . mysqli_error($conn));
}
echo "Dati inseriti con successo";

mysqli_close($conn);

Nell'esempio appena visto viene inserita una nuova riga nella tabella anagrafica specificando dei valori per i campi nome, cognome e eta. I valori per i campi nome e cognome vengono inseriti tra singoli apici in quanto il loro tipo è VARCHAR, mentre il campo eta viene inserito come numero intero.

Nota: non abbiamo specificato nessun valore per il campo id in quanto è autoincrementante, ciò vuol dire che MySQL assegnerà automaticamente un valore a questo campo se non viene specificato, aumentando il valore precedente di 1.

Cosa succede se vogliamo inserire un valore contenente un apice? Possiamo racchiuderlo tra doppi apici oppure aggiungere la sequenza di escape \'.

Facciamo un esempio: se proviamo ad eseguire la query

$sql = "INSERT INTO anagrafica (nome, cognome, eta) VALUES ('John', 'O'Really', 40)";

otteniamo il seguente errore

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Really', 40)' at line 1

poiché la stringa "O'Really" contiene un apice.

Facendo l'escape di questo carattere, ossia anteponendogli il carattere \ risolviamo

$sql = "INSERT INTO anagrafica (nome, cognome, eta) VALUES ('John', 'O\'Really', 40)";

In questo esempio abbiamo utilizzato una stringa predefinita, ma se il valore provenisse da un input utente, ossia da un modulo contatti dove l'utente può inserire qualsiasi cosa?

In generale l'approccio migliore è l'utilizzo delle Prepared Statement (letteralmente istruzioni preparate), una funzionalità integrata in MySQL 4.1 e versioni successive.

Tramite questa funzionalità è possibile inserire nel database in maniera sicura i dati prelevati da un modulo HTML, proteggendo l'applicazione da una vulnerabilità nota come SQL Injection. Affronteremo questo argomento in maniera più approfondita in una guida dedicata.

Estrazione dati - SELECT

L'istruzione SELECT di MySQL viene utilizzata per selezionare i dati da una o più tabelle del database.

Prima di vedere un esempio concreto in PHP, descriviamo brevemente i passaggi necessari per recuperare i dati da una tabella e stamparli a video:

• Creiamo una connessione al database usando mysqli_connect().
• Utilizziamo mysqli_query() per eseguire una SELECT sul database.
• In caso di successo la funzione mysqli_query() restituisce una risorsa di tipo mysqli_result.
• La risorsa restituita viene passata alla funzione mysqli_fetch_array().
• La funzione mysqli_fetch_array() restituisce un array di stringhe per ogni record della tabella oppure NULL se non ci sono record.
• Tramite un ciclo while stampiamo i dati di ciascuna record.
• Liberiamo le risorse con le funzioni mysqli_free_result() e chiudiamo la connessione.

<?php
$conn = mysqli_connect("localhost", "test", "test", "clienti");
$sql = "SELECT nome, cognome, eta FROM anagrafica";
$result = mysqli_query($conn, $sql);

if($result === false) {
    exit("Errore: impossibile eseguire la query. " . mysqli_error($conn));
}

while ($row = mysqli_fetch_array($result)) {
    echo $row['nome'].' '.$row['cognome'].' '.$row['eta'].'<br>';
}

mysqli_free_result($result);
mysqli_close($conn);

Nell'esempio precedente abbiamo selezionato i campi di tutti i record della tabella. Se volessimo estrarre solamente i record che rispondono a una precisa condizione dobbiamo aggiungere la clausola WHERE nella query.

Ad esempio la seguente query estrae tutti gli utenti con età maggiore di 40

SELECT nome, cognome
FROM anagrafica
WHERE eta > 40

Aggiornamento dati - UPDATE

Un'altra operazione utilizzata molto frequentementi sui record di un database è l'aggiornamento (o modifica).

L'istruzione UPDATE consente di modificare i record esistenti in una tabella del database. In genere è usata in combinazione con la clausola WHERE per applicare le modifiche solo ai record che soddisfano specifiche condizioni.

La sintassi è la seguente

UPDATE nome_tabella SET campo1 = valore1, campo2 = valore2, ... WHERE condizione;

Di regola:

• È possibile aggiornare uno o più campi con una sola query.
• È possibile specificare una o più condizioni tramite la clausola WHERE.
• È possibile aggiornare i valori di una tabella alla volta.

Consideriamo adesso i record della nostra tabella anagrafica di esempio

+----+------------+-----------+------+
| id | nome       | cognome   | eta  |
+----+------------+-----------+------+
|  1 | Mario      | Rossi     |  40  |
|  2 | John       | Smith     |  32  |
|  3 | Pippo      | Bianchi   |  35  |
+----+------------+-----------+------+

Supponendo di voler modificare il nome dell'utente con id uguale a 3, possiamo scrivere il seguente codice PHP

<?php
$conn = mysqli_connect("localhost", "test", "test", "clienti");
$sql = "UPDATE anagrafica SET nome = 'Carlo' WHERE id = 3";
$result = mysqli_query($conn, $sql);

if(mysqli_affected_rows($conn) > 0) {
    echo "Utente aggiornato con successo";
}

mysqli_close($conn);

In questo esempio abbiamo introdotto la funzione mysql_afftected_rows() che restituisce il numero di righe coinvolte nell'aggiornamento tramite UPDATE. Se il numero restituito è maggiore di zero, verrà eseguito l'echo del messaggio presente dentro la condizione if.

Nota: mysql_affected_rows() può essere utilizzata dopo l'esecuzione delle seguenti istruzioni SQL: UPDATE, INSERT, REPLACE, DELETE.

Cancellazione dati - DELETE

Una volta popolata una tabella, è possibile eliminare i record utilizzando l'istruzione DELETE. Anche in questa caso, l'istruzione SQL viene utilizzata in combinazione con WHERE per eliminare solo i record che soddisfano una determinata condizione.

La sintassi di base dell'istruzione DELETE è la seguente:

DELETE FROM nome_tabella WHERE condizione;

Vediamo un esempio di utilizzo

<?php
$conn = mysqli_connect("localhost", "test", "test", "clienti");
$sql = "DELETE FROM anagrafica WHERE id = 1";
$result = mysqli_query($conn, $sql);

if(mysqli_affected_rows($conn) > 0) {
    echo "Utente eliminato con successo";
}

mysqli_close($conn);

Anche in questo caso tramite la funzione mysql_affected_rows() verifichiamo se ci sono state righe coinvolte nella cancellazione.

Se dopo aver cancellato il record eseguiamo una SELECT sulla tabella anagrafica otterremo un risultato simile al seguente

+----+------------+-----------+------+
| id | nome       | cognome   | eta  |
+----+------------+-----------+------+
|  2 | John       | Smith     |  32  |
|  3 | Pippo      | Bianchi   |  35  |
+----+------------+-----------+------+

dove l'utente con id uguale a 1 è stato cancellato.

Nota: è importante specificare sempre la clausola WHERE in combinazione di DELETE, in quanto la sua omissione porterebbe all'eliminazione di tutti i record presenti della tabella.

Conclusioni

In questa guida abbiamo analizzato l'estensione MySQLi, ossia uno dei modi che PHP mette a disposizione per lavorare con un database MySQL. Abbiamo visto come connetterci ad una database ed effettuare semplici query per inserire, selezionare, modificare e cancellare i dati.

Per una panoramica completa delle funzioni disponibili per l'estensione MySQLi facciamo sempre riferimento alla documentazione ufficiale https://www.php.net/manual/en/mysqli.summary.php

Una volta presa confidenza con le funzioni di MySQLi sarà possibile costruire una piccola applicazione che preleva i dati dell'utente da un modulo contatto e li salva in un datatabase. Vedremo questa e altre funzionalità interessanti in un successivo tutorial dedicato.