Gestire MySQL in PHP: guida alle funzionalità
PHP e MySQL rappresentano da decenni un binomio fondamentale nello sviluppo web. L'estensione MySQLi (MySQL Improved) e la libreria PDO (PHP Data Objects) offrono strumenti completi per interagire con i database relazionali in modo sicuro ed efficiente. Questa guida esplora in profondità le funzionalità principali, dalle operazioni di base alle tecniche più avanzate.
Connessione al database
Il primo passo per qualsiasi operazione su MySQL è stabilire una connessione. PHP mette a disposizione due approcci principali: l'estensione MySQLi e PDO. Entrambi supportano la programmazione orientata agli oggetti, ma PDO offre il vantaggio di essere indipendente dal motore di database utilizzato.
Connessione con MySQLi
MySQLi può essere usato sia in stile procedurale che ad oggetti. Lo stile ad oggetti è generalmente preferito per la sua leggibilità e manutenibilità.
<?php
// Parametri di connessione al database
$host = 'localhost';
$username = 'root';
$password = 'secret';
$database = 'my_application';
// Creazione della connessione con MySQLi
$connection = new mysqli($host, $username, $password, $database);
// Verifica della connessione
if ($connection->connect_error) {
die('Connessione fallita: ' . $connection->connect_error);
}
// Impostazione del set di caratteri
$connection->set_charset('utf8mb4');
echo 'Connessione riuscita al database.';
Connessione con PDO
PDO utilizza un Data Source Name (DSN) per specificare i parametri di connessione e si avvale delle eccezioni per la gestione degli errori, rendendo il codice più robusto.
<?php
// Parametri di connessione
$host = 'localhost';
$database = 'my_application';
$username = 'root';
$password = 'secret';
try {
// Stringa DSN per MySQL
$dsn = "mysql:host=$host;dbname=$database;charset=utf8mb4";
// Opzioni di configurazione per PDO
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
// Creazione dell'istanza PDO
$pdo = new PDO($dsn, $username, $password, $options);
echo 'Connessione PDO riuscita.';
} catch (PDOException $exception) {
die('Errore di connessione: ' . $exception->getMessage());
}
L'opzione ATTR_ERRMODE => ERRMODE_EXCEPTION è fondamentale: configura PDO per lanciare eccezioni in caso di errore, anziché fallire silenziosamente. L'opzione ATTR_EMULATE_PREPARES => false disabilita l'emulazione dei prepared statement, delegando la preparazione direttamente a MySQL per una maggiore sicurezza.
Esecuzione di query semplici
Una volta stabilita la connessione, è possibile eseguire query SQL. Per le query che non prevedono parametri forniti dall'utente, si può usare il metodo query().
<?php
// Query diretta con MySQLi
$sql = "SELECT id, name, email FROM users WHERE active = 1";
$result = $connection->query($sql);
if ($result->num_rows > 0) {
// Iterazione sui risultati
while ($row = $result->fetch_assoc()) {
echo $row['name'] . ' - ' . $row['email'] . '<br>';
}
} else {
echo 'Nessun utente attivo trovato.';
}
// Liberazione della memoria occupata dal risultato
$result->free();
<?php
// Query diretta con PDO
$sql = "SELECT id, name, email FROM users WHERE active = 1";
$statement = $pdo->query($sql);
// Recupero di tutti i risultati come array associativo
$users = $statement->fetchAll();
foreach ($users as $row) {
echo $row['name'] . ' - ' . $row['email'] . '<br>';
}
Le query dirette sono adatte solo quando il testo SQL è interamente sotto il controllo del programmatore. Ogni volta che un valore proviene dall'esterno (form, URL, API), è obbligatorio ricorrere ai prepared statement.
Prepared statement e parametri vincolati
I prepared statement sono lo strumento principale per prevenire le SQL injection. Separano la struttura della query dai dati, impedendo che input malevoli possano alterare la logica dell'istruzione SQL.
Prepared statement con MySQLi
<?php
// Preparazione della query con segnaposto
$sql = "SELECT id, name, email FROM users WHERE email = ? AND active = ?";
$statement = $connection->prepare($sql);
// Associazione dei parametri: 's' = stringa, 'i' = intero
$email = 'mario@example.com';
$active = 1;
$statement->bind_param('si', $email, $active);
// Esecuzione della query
$statement->execute();
// Recupero dei risultati
$result = $statement->get_result();
while ($row = $result->fetch_assoc()) {
echo 'Trovato: ' . $row['name'];
}
// Chiusura dello statement
$statement->close();
Il metodo bind_param() richiede una stringa di tipi come primo argomento: s per stringhe, i per interi, d per numeri a virgola mobile e b per dati binari (BLOB).
Prepared statement con PDO
<?php
// Prepared statement con segnaposto nominali
$sql = "SELECT id, name, email FROM users WHERE email = :email AND active = :active";
$statement = $pdo->prepare($sql);
// Esecuzione con passaggio diretto dei parametri
$statement->execute([
':email' => 'mario@example.com',
':active' => 1,
]);
// Recupero dei risultati
$users = $statement->fetchAll();
foreach ($users as $row) {
echo 'Trovato: ' . $row['name'];
}
PDO supporta sia i segnaposto posizionali (?) sia quelli nominali (:nome). I segnaposto nominali rendono il codice più leggibile, soprattutto nelle query con molti parametri.
Operazioni CRUD
Le quattro operazioni fondamentali su un database sono la creazione (INSERT), la lettura (SELECT), l'aggiornamento (UPDATE) e la cancellazione (DELETE). Di seguito vengono illustrate usando PDO con prepared statement.
Inserimento di record
<?php
// Inserimento di un nuovo utente
$sql = "INSERT INTO users (name, email, password, created_at) VALUES (:name, :email, :password, NOW())";
$statement = $pdo->prepare($sql);
$statement->execute([
':name' => 'Laura Bianchi',
':email' => 'laura@example.com',
':password' => password_hash('password123', PASSWORD_DEFAULT),
]);
// Recupero dell'ID generato automaticamente
$lastId = $pdo->lastInsertId();
echo "Nuovo utente inserito con ID: $lastId";
Lettura di record
<?php
// Lettura di un singolo record
$sql = "SELECT id, name, email, created_at FROM users WHERE id = :id";
$statement = $pdo->prepare($sql);
$statement->execute([':id' => 5]);
// Recupero di una sola riga
$user = $statement->fetch();
if ($user) {
echo "Nome: {$user['name']}, Email: {$user['email']}";
} else {
echo 'Utente non trovato.';
}
<?php
// Lettura con filtri e ordinamento
$sql = "SELECT id, name, email
FROM users
WHERE active = :active
ORDER BY name ASC
LIMIT :limit OFFSET :offset";
$statement = $pdo->prepare($sql);
// Associazione esplicita con tipo per LIMIT e OFFSET
$statement->bindValue(':active', 1, PDO::PARAM_INT);
$statement->bindValue(':limit', 10, PDO::PARAM_INT);
$statement->bindValue(':offset', 0, PDO::PARAM_INT);
$statement->execute();
$users = $statement->fetchAll();
Per LIMIT e OFFSET è necessario usare bindValue() con il tipo PDO::PARAM_INT, poiché MySQL richiede che questi valori siano interi e non stringhe.
Aggiornamento di record
<?php
// Aggiornamento dell'email di un utente
$sql = "UPDATE users SET email = :email, updated_at = NOW() WHERE id = :id";
$statement = $pdo->prepare($sql);
$statement->execute([
':email' => 'laura.nuova@example.com',
':id' => 5,
]);
// Numero di righe modificate
$rowCount = $statement->rowCount();
echo "Righe aggiornate: $rowCount";
Cancellazione di record
<?php
// Cancellazione di un utente specifico
$sql = "DELETE FROM users WHERE id = :id";
$statement = $pdo->prepare($sql);
$statement->execute([':id' => 5]);
$rowCount = $statement->rowCount();
echo "Righe eliminate: $rowCount";
Transazioni
Le transazioni permettono di raggruppare più operazioni in un'unità atomica: o tutte le operazioni vanno a buon fine, oppure nessuna viene applicata. Questo è essenziale per mantenere la coerenza dei dati in operazioni complesse.
<?php
try {
// Inizio della transazione
$pdo->beginTransaction();
// Addebito dal conto del mittente
$sql = "UPDATE accounts SET balance = balance - :amount WHERE id = :id";
$statement = $pdo->prepare($sql);
$statement->execute([':amount' => 150.00, ':id' => 1]);
// Verifica che il saldo non sia negativo
$sql = "SELECT balance FROM accounts WHERE id = :id";
$checkStatement = $pdo->prepare($sql);
$checkStatement->execute([':id' => 1]);
$account = $checkStatement->fetch();
if ($account['balance'] < 0) {
// Annullamento: saldo insufficiente
throw new Exception('Saldo insufficiente per completare il trasferimento.');
}
// Accredito sul conto del destinatario
$sql = "UPDATE accounts SET balance = balance + :amount WHERE id = :id";
$statement = $pdo->prepare($sql);
$statement->execute([':amount' => 150.00, ':id' => 2]);
// Registrazione del movimento
$sql = "INSERT INTO transactions (sender_id, receiver_id, amount, created_at)
VALUES (:sender, :receiver, :amount, NOW())";
$statement = $pdo->prepare($sql);
$statement->execute([
':sender' => 1,
':receiver' => 2,
':amount' => 150.00,
]);
// Conferma di tutte le operazioni
$pdo->commit();
echo 'Trasferimento completato con successo.';
} catch (Exception $exception) {
// Annullamento di tutte le operazioni in caso di errore
$pdo->rollBack();
echo 'Errore nel trasferimento: ' . $exception->getMessage();
}
Il metodo beginTransaction() disattiva l'auto-commit. Tutte le query successive restano in sospeso fino alla chiamata di commit() o rollBack(). In caso di eccezione, il rollback ripristina lo stato precedente del database.
Inserimenti multipli
Quando occorre inserire molti record, è possibile preparare lo statement una sola volta e rieseguirlo più volte con parametri diversi. Combinando questa tecnica con le transazioni si ottengono prestazioni nettamente superiori.
<?php
// Dati da inserire in blocco
$products = [
['name' => 'Tastiera meccanica', 'price' => 89.99, 'stock' => 50],
['name' => 'Mouse ergonomico', 'price' => 45.50, 'stock' => 120],
['name' => 'Monitor 27 pollici', 'price' => 349.00, 'stock' => 30],
['name' => 'Webcam HD', 'price' => 65.00, 'stock' => 80],
];
$sql = "INSERT INTO products (name, price, stock) VALUES (:name, :price, :stock)";
$statement = $pdo->prepare($sql);
// Uso di una transazione per velocizzare gli inserimenti
$pdo->beginTransaction();
try {
foreach ($products as $product) {
$statement->execute([
':name' => $product['name'],
':price' => $product['price'],
':stock' => $product['stock'],
]);
}
$pdo->commit();
echo count($products) . ' prodotti inseriti con successo.';
} catch (PDOException $exception) {
$pdo->rollBack();
echo 'Errore durante l\'inserimento: ' . $exception->getMessage();
}
Gestione degli errori
Una gestione corretta degli errori è essenziale in qualsiasi applicazione. In ambiente di produzione gli errori di database non devono mai essere mostrati direttamente all'utente finale, poiché potrebbero rivelare informazioni sensibili sulla struttura del sistema.
<?php
// Classe per la gestione centralizzata della connessione
class Database
{
private static ?PDO $instance = null;
// Impedisce l'istanziazione diretta
private function __construct() {}
// Restituisce l'istanza singleton della connessione
public static function getConnection(): PDO
{
if (self::$instance === null) {
try {
$dsn = 'mysql:host=localhost;dbname=my_application;charset=utf8mb4';
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
self::$instance = new PDO($dsn, 'root', 'secret', $options);
} catch (PDOException $exception) {
// Registrazione dell'errore nel log del server
error_log('Errore di connessione al DB: ' . $exception->getMessage());
// Messaggio generico per l'utente
throw new RuntimeException('Servizio temporaneamente non disponibile.');
}
}
return self::$instance;
}
}
// Utilizzo della classe
try {
$pdo = Database::getConnection();
$statement = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$statement->execute([':id' => 1]);
$user = $statement->fetch();
} catch (RuntimeException $exception) {
// Errore di connessione: mostra un messaggio generico
echo $exception->getMessage();
} catch (PDOException $exception) {
// Errore nella query: registra e notifica
error_log('Errore nella query: ' . $exception->getMessage());
echo 'Si è verificato un errore. Riprovare più tardi.';
}
Il pattern Singleton garantisce che venga creata una sola connessione per richiesta, evitando l'apertura di connessioni multiple che consumerebbero risorse inutilmente.
Query dinamiche sicure
In molte applicazioni è necessario costruire query in modo dinamico, ad esempio per gestire filtri di ricerca opzionali. La costruzione sicura di query dinamiche richiede attenzione per evitare vulnerabilità.
<?php
// Costruzione di una query di ricerca con filtri opzionali
function searchUsers(PDO $pdo, array $filters): array
{
$sql = "SELECT id, name, email, role FROM users WHERE 1=1";
$params = [];
// Filtro per nome (ricerca parziale)
if (!empty($filters['name'])) {
$sql .= " AND name LIKE :name";
$params[':name'] = '%' . $filters['name'] . '%';
}
// Filtro per ruolo
if (!empty($filters['role'])) {
$sql .= " AND role = :role";
$params[':role'] = $filters['role'];
}
// Filtro per data di registrazione
if (!empty($filters['registered_after'])) {
$sql .= " AND created_at >= :registered_after";
$params[':registered_after'] = $filters['registered_after'];
}
// Ordinamento sicuro: solo colonne consentite
$allowedColumns = ['name', 'email', 'created_at'];
$orderBy = 'name';
if (!empty($filters['sort']) && in_array($filters['sort'], $allowedColumns, true)) {
$orderBy = $filters['sort'];
}
$sql .= " ORDER BY $orderBy ASC";
// Esecuzione con prepared statement
$statement = $pdo->prepare($sql);
$statement->execute($params);
return $statement->fetchAll();
}
// Esempio di utilizzo con filtri provenienti da un form
$results = searchUsers($pdo, [
'name' => 'Laura',
'role' => 'editor',
'sort' => 'created_at',
]);
La clausola WHERE 1=1 è un idioma comune che permette di aggiungere ogni condizione successiva con AND senza doversi preoccupare della prima. L'ordinamento viene validato contro una whitelist di colonne consentite, poiché i nomi di colonna non possono essere passati come parametri nei prepared statement.
Clausola IN con parametri dinamici
Un caso particolarmente insidioso è l'uso della clausola IN con un numero variabile di valori. Non è possibile passare un array come singolo parametro; occorre generare dinamicamente i segnaposto.
<?php
// Recupero di utenti per una lista di ID
function getUsersByIds(PDO $pdo, array $ids): array
{
if (empty($ids)) {
return [];
}
// Generazione dei segnaposto: ?, ?, ?, ...
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = "SELECT id, name, email FROM users WHERE id IN ($placeholders)";
$statement = $pdo->prepare($sql);
$statement->execute($ids);
return $statement->fetchAll();
}
// Esempio di utilizzo
$selectedIds = [2, 7, 13, 25];
$users = getUsersByIds($pdo, $selectedIds);
Stored procedure
PHP può invocare stored procedure definite in MySQL. Le stored procedure consentono di incapsulare logica complessa direttamente nel database.
<?php
// Chiamata a una stored procedure con parametri
$sql = "CALL get_monthly_report(:year, :month)";
$statement = $pdo->prepare($sql);
$statement->execute([
':year' => 2026,
':month' => 3,
]);
// Recupero dei risultati restituiti dalla procedura
$report = $statement->fetchAll();
foreach ($report as $row) {
echo "Categoria: {$row['category']}, Totale: {$row['total']}<br>";
}
Paginazione dei risultati
La paginazione è una tecnica essenziale per gestire grandi quantità di dati senza sovraccaricare il server o l'interfaccia utente.
<?php
function getPaginatedUsers(PDO $pdo, int $page = 1, int $perPage = 20): array
{
// Calcolo dell'offset a partire dal numero di pagina
$offset = ($page - 1) * $perPage;
// Conteggio totale per calcolare le pagine
$countStatement = $pdo->query("SELECT COUNT(*) FROM users WHERE active = 1");
$totalRows = (int) $countStatement->fetchColumn();
$totalPages = (int) ceil($totalRows / $perPage);
// Query paginata
$sql = "SELECT id, name, email
FROM users
WHERE active = 1
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset";
$statement = $pdo->prepare($sql);
$statement->bindValue(':limit', $perPage, PDO::PARAM_INT);
$statement->bindValue(':offset', $offset, PDO::PARAM_INT);
$statement->execute();
return [
'data' => $statement->fetchAll(),
'total_rows' => $totalRows,
'total_pages' => $totalPages,
'current_page' => $page,
'per_page' => $perPage,
];
}
// Esempio: recupero della terza pagina con 15 elementi per pagina
$result = getPaginatedUsers($pdo, 3, 15);
echo "Pagina {$result['current_page']} di {$result['total_pages']}<br>";
foreach ($result['data'] as $user) {
echo "{$user['name']} - {$user['email']}<br>";
}
Gestione di date e orari
Il trattamento corretto di date e orari è un aspetto spesso sottovalutato. MySQL memorizza le date in formato YYYY-MM-DD e i timestamp in formato YYYY-MM-DD HH:MM:SS. PHP offre la classe DateTime per la manipolazione e la formattazione.
<?php
// Inserimento con data corrente e fuso orario esplicito
$now = new DateTime('now', new DateTimeZone('Europe/Rome'));
$sql = "INSERT INTO events (title, event_date, created_at) VALUES (:title, :event_date, :created_at)";
$statement = $pdo->prepare($sql);
$statement->execute([
':title' => 'Conferenza PHP',
':event_date' => $now->format('Y-m-d'),
':created_at' => $now->format('Y-m-d H:i:s'),
]);
// Recupero e formattazione di una data dal database
$sql = "SELECT title, event_date FROM events WHERE id = :id";
$statement = $pdo->prepare($sql);
$statement->execute([':id' => $pdo->lastInsertId()]);
$event = $statement->fetch();
if ($event) {
$eventDate = new DateTime($event['event_date']);
echo $event['title'] . ': ' . $eventDate->format('d/m/Y');
}
Operazioni con JOIN
Le operazioni di JOIN sono fondamentali per lavorare con database relazionali, consentendo di combinare dati provenienti da più tabelle in un'unica query.
<?php
// Query con JOIN fra ordini, utenti e prodotti
$sql = "SELECT
orders.id AS order_id,
users.name AS customer_name,
products.name AS product_name,
order_items.quantity,
order_items.unit_price,
(order_items.quantity * order_items.unit_price) AS subtotal
FROM orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id
WHERE orders.created_at >= :start_date
ORDER BY orders.created_at DESC";
$statement = $pdo->prepare($sql);
$statement->execute([':start_date' => '2026-01-01']);
$orderDetails = $statement->fetchAll();
// Raggruppamento dei risultati per ordine
$groupedOrders = [];
foreach ($orderDetails as $row) {
$orderId = $row['order_id'];
if (!isset($groupedOrders[$orderId])) {
$groupedOrders[$orderId] = [
'customer' => $row['customer_name'],
'items' => [],
'total' => 0,
];
}
$groupedOrders[$orderId]['items'][] = [
'product' => $row['product_name'],
'quantity' => $row['quantity'],
'subtotal' => $row['subtotal'],
];
$groupedOrders[$orderId]['total'] += $row['subtotal'];
}
Connessioni persistenti e pool
Le connessioni persistenti mantengono aperta la connessione al database fra una richiesta HTTP e la successiva, evitando il costo di apertura e chiusura ripetute. Questa tecnica è utile nelle applicazioni ad alto traffico.
<?php
// Abilitazione delle connessioni persistenti con PDO
$options = [
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO(
'mysql:host=localhost;dbname=my_application;charset=utf8mb4',
'root',
'secret',
$options
);
Le connessioni persistenti vanno usate con cautela: se non gestite correttamente, possono saturare il numero massimo di connessioni consentite dal server MySQL. In ambienti con molti processi PHP simultanei, è consigliabile configurare adeguatamente il parametro max_connections di MySQL.
Chiusura della connessione
In PHP la connessione al database viene chiusa automaticamente al termine dello script. Tuttavia, nelle applicazioni di lunga durata o nei daemon, è buona pratica chiuderla esplicitamente per liberare risorse.
<?php
// Chiusura esplicita con MySQLi
$connection->close();
// Chiusura con PDO: si annulla il riferimento all'oggetto
$pdo = null;
Considerazioni sulla sicurezza
La sicurezza nell'interazione con il database non si esaurisce con l'uso dei prepared statement. Esistono diverse pratiche complementari che contribuiscono a rafforzare la protezione dell'applicazione.
Le password devono sempre essere memorizzate come hash, mai in chiaro. PHP mette a disposizione le funzioni password_hash() e password_verify() che utilizzano algoritmi robusti come bcrypt.
<?php
// Registrazione: creazione dell'hash della password
$plainPassword = 'La_Mia_Password_Sicura!';
$hashedPassword = password_hash($plainPassword, PASSWORD_DEFAULT);
$sql = "INSERT INTO users (email, password) VALUES (:email, :password)";
$statement = $pdo->prepare($sql);
$statement->execute([
':email' => 'utente@example.com',
':password' => $hashedPassword,
]);
// Login: verifica della password
$sql = "SELECT id, email, password FROM users WHERE email = :email";
$statement = $pdo->prepare($sql);
$statement->execute([':email' => 'utente@example.com']);
$user = $statement->fetch();
if ($user && password_verify($plainPassword, $user['password'])) {
echo 'Accesso consentito.';
} else {
echo 'Credenziali non valide.';
}
Altre pratiche essenziali includono: utilizzare un utente MySQL con privilegi minimi (solo quelli necessari all'applicazione), validare e sanitizzare ogni input prima di usarlo in una query, non esporre mai i messaggi di errore del database all'utente finale e mantenere aggiornati sia PHP che MySQL per beneficiare delle patch di sicurezza.
Conclusione
PHP offre strumenti maturi e potenti per la gestione di MySQL. L'estensione PDO, in particolare, fornisce un'interfaccia coerente, sicura e flessibile che si adatta a progetti di qualsiasi dimensione. I prepared statement, le transazioni, la gestione strutturata degli errori e le buone pratiche di sicurezza costituiscono i pilastri su cui costruire applicazioni affidabili. La padronanza di queste funzionalità consente di scrivere codice che non solo funziona correttamente, ma resiste nel tempo e si mantiene con facilità.