OOP - Parte VI Il database | Appunti Digitali

PDO (PHP Data Objects) è un'estensione standard di PHP che fornisce un'interfaccia per connettersi e gestire un database.

Nel file php.ini è possibile specificare i driver abilitati per i vari tipi di database

E' necessario definire quattro parametri fondamentali (meglio metterli nel file config.php):

$host = 'localhost';
$dbname = 'nome_database';
$username = 'nome_utente';
$password = 'password';

<?php

$host = 'localhost';
$dbname = 'nome_database';
$username = 'nome_utente';
$password = 'password';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //si potevano passare nel costruttore come array associativo
    echo "Connessione al db riuscita!"; 
} catch (PDOException $e) {
    echo "Errore di connessione: . $e->getMessage()";
}

?>

@TODO fare approfondimento su SQL injection

Per creare una tabella si usa la funzione exec() e si passano come parametri gli statements per creare le tabelle

<?php

$statements = [
	'CREATE TABLE IF NOT EXISTS authors( 
        author_id   INT AUTO_INCREMENT,
        first_name  VARCHAR(100) NOT NULL, 
        last_name   VARCHAR(100) NULL,
        PRIMARY KEY(author_id)
    );',
	'CREATE TABLE IF NOT EXISTS books (
        book_id   INT AUTO_INCREMENT, 
        title VARCHAR(255) NOT NULL,
        PRIMARY KEY (book_id)
    )'];

$pdo = require 'connect.php';

foreach ($statements as $statement) {
	$pdo->exec($statement); // la exec ritorna il numero di righe se tutto è andato bene oppure false se c'è stato un errore
}

?>

Prepared Statements

Un prepared statement è un template per l'esecuzione di uno stesso statement con diversi valori. L'esecuzione della query avviene in due fasi, la preparazione e l'esecuzione vera e propria.

<?php

$pdo = require 'connect.php';

//primo metodo
$sql = 'insert into authors(first_name, last_name)
        values(?,?)'; // i due ? sono positional placeholders

$statement = $pdo->prepare($sql);
$statement->execute(['Mario', 'Rossi']);

//secondo metodo
$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)'; //named placeholder

$statement = $pdo->prepare($sql);
$statement->execute([
	'first_name' => 'Mario',
	'last_name' => 'Rossi'
]);

?>

E' anche possibile usare la funzione bindValue() per impostare un valore da passare nella query. Il valore sarà quello definito al momento dell'utilizzo di bindValue e non al momento in cui viene chiamata la execute().

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)';

$statement = $pdo->prepare($sql);

$author = [
	'first_name' => 'Mario',
	'last_name' => 'Rossi',
];

$statement->bindValue(':first_name', $author['first_name']);
$statement->bindValue(':last_name', $author['last_name']);

$author['first_name'] = 'Giorgio';
$author['last_name'] = 'Verdi';

// la query verrà eseguita con Mario Rossi, i valori definiti nella bindValue
$statement->execute();

?>

Invece con bindParam i parametri sono passati per riferimento

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)';

$statement = $pdo->prepare($sql);

$author = [
	'first_name' => 'Mario',
	'last_name' => 'Rossi',
];

$statement->bindParam(':first_name', $author['first_name']);
$statement->bindParam(':last_name', $author['last_name']);

$author['first_name'] = 'Giorgio';
$author['last_name'] = 'Verdi';

// La query verrà eseguita con Giorgio Verdi, perché bindParam
// legge i valori delle variabili solo al momento dell'execute().
$statement->execute();

?>

Per creare un prepared statement in cui non sappiamo il numero di parametri possiamo usare la clausola IN

<?php

$pdo = require 'connect.php';

$list = [1, 2, 3];

$placeholder = str_repeat('?,', count($list) - 1) . '?'; //ripete la stringa '?,' per quanti elementi nell'array meno uno, poi aggiunge l'ultimo punto di domanda senza la virgola dopo

$sql = "SELECT book_id, title 
        FROM books 
        WHERE book_id IN ($placeholder)";

$statement = $pdo->prepare($sql);

$statement->execute($list);

//fetchAll ritorna tutti i risultati in una volta, come li ritorna dipende dal parametro, in questo caso un array associativo
//dove le chiavi sono le colonne della tabella. Altrimenti c'è FETCH_BOTH, FETCH_CLASS, FETCH_KEY_PAIR
$books = $statement->fetchAll(PDO::FETCH_ASSOC);

foreach ($books as $book) {
    echo '<h1>Titolo del libro: ' . $book['title'] . '</h1>';
}

//esempio con fetch invece di fetchAll. Fetch restituisce la riga corrente e sposta il cursore in avanti
//così carica in memoria una riga alla volta invece che tutte le righe insieme
//quando non ci sono più righe restituisce false
while ($book = $statement->fetch(PDO::FETCH_ASSOC)) {
    echo '<h1>Titolo del libro: ' . $book['title'] . '</h1>';
}

// esempio con FETCH_KEY_PAIR: richiede che la select ritorni esattamente due colonne
//la prima diventerà la chiave dell'array, la seconda il valore
$books = $statement->fetchAll(PDO::FETCH_KEY_PAIR);

echo "<select name="book" id="book">";
foreach ($books as $id => $title) {
    echo "<option value='$id'>$title</option>";
}
echo "</select>";

?>

Per fare sì che le righe restituite dalla query diventino oggetti della classe corrispondente si usa il metodo fetchObject:

<?php

class Publisher
{
    private $publisher_id;
    private $name;

    public function getName()
    {
        return $this->name;
    }
}

$pdo = require 'connect.php';

$sql = 'SELECT publisher_id, name 
        FROM publishers 
        WHERE publisher_id=:publisher_id';

$statement = $pdo->prepare($sql);
$statement->execute([':publisher_id' => 1]);

// il primo parametro è il nome della classe, se si omette di default è stdClass
//il secondo parametro è un array di valori da passare nel costruttore
$publisher = $statement->fetchObject('Publisher'); 

echo $publisher->name;

//altrimenti si può usare fetch() o fetchAll() settando il mode come FETCH_CLASS
$statement->setFetchMode(PDO::FETCH_CLASS, 'Publisher');

$publisher = $statement->fetch(); // Ritorna un oggetto Publisher
$altri = $statement->fetchAll();  // Ritorna un array di oggetti Publisher

?>

Per fare una select passando come parametro una parte di stringa possiamo usare la clausola LIKE

<?php

$pdo = require 'connect.php';

$keyword = 'es';

$pattern = '%' . $keyword . '%';

$sql = 'SELECT book_id, title 
        FROM books 
        WHERE title LIKE :pattern'; //qui non usiamo il ?

$statement = $pdo->prepare($sql);

$statement->execute([':pattern' => $pattern]);

$books = $statement->fetchAll(PDO::FETCH_ASSOC);

foreach ($books as $book) {
    echo $book['title'] . '<br>';
}

?>

Esempio di select su una sola colonna:

<?php
$pdo = require 'connect.php';

$idDaCercare = 1;

$sql = "SELECT title, author FROM books WHERE book_id = :id";
$statement = $pdo->prepare($sql);

$statement->execute([':id' => $idDaCercare]);

//fetch ritornerebbe un array del tipo Array ( [title] => 'Il nome del libro',  [author] => 'Stephen King')
// fetchColumn ritorna solo il valore della prima colonna nella select come dato grezzo
// se si vuole avere un'altra colonna bisogna passare l'indice come parametro
$title = $statement->fetchColumn();
$author = $statement->fetchColumn(1);

if ($title) {
    echo "Il titolo del libro è: " . $title;
} else {
    echo "Nessun libro trovato con questo ID.";
}
?>

Per fare una select raggruppando i risultati in base al valore della prima colonna:

<?php
$pdo = require 'connect.php';

$publisherId = 1;

// La prima colonna sarà usata come chiave per il raggruppamento
$sql = "SELECT category, title 
        FROM books 
        WHERE publisher_id = :publisher_id";

$statement = $pdo->prepare($sql);
$statement->execute([':publisher_id' => $publisherId]);

// questo ritorna un array dove la chiave è la categoria
// e il valore è un array semplice di titoli
$books = $statement->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);

foreach ($books as $category => $titles) {
    echo "<h2>Categoria: " . $category . "</h2>";
    echo "<ul>";
    foreach ($titles as $title) {
        echo "<li>" . $title . "</li>";
    }
    echo "</ul>";
}

?>

Per eseguire una query di SELECT si può anche usare il metodo query() ma non è sicuro contro attacchi SQL injection.

Esempio di insert:

<?php

$pdo = require_once 'connect.php';

//insert di una riga
$name = 'Mondadori';

//insert di più righe
$names = [
	'Einaudi',
	'Rizzoli'
];
$sql = 'INSERT INTO publishers(name) VALUES(:name)';

$statement = $pdo->prepare($sql);

//caso insert di una riga
$statement->execute([
	':name' => $name
]);

/caso insert di più righe
foreach ($names as $name) {
	$statement->execute([
		':name' => $name
	]);
}

$publisher_id = $pdo->lastInsertId(); //ritorna l'ultimo id inserito

?>

Esempio di update

<?php

$pdo = require_once 'connect.php';

$publisher = [
	'publisher_id' => 1,
	'name' => 'Feltrinelli'
];

$sql = 'UPDATE publishers
        SET name = :name
        WHERE publisher_id = :publisher_id';

$statement = $pdo->prepare($sql);

$statement->bindParam(':publisher_id', $publisher['publisher_id'], PDO::PARAM_INT);
$statement->bindParam(':name', $publisher['name']);

//se qui cambiamo il valore di $publisher['name'] nella execute verrà passato il valore aggiornato

if ($statement->execute()) {
    if ($statement->rowCount() > 0) {
        echo 'Il record con ID ' . $publisher['publisher_id'] . ' è stato aggiornato con successo!';
    } else {
        echo 'Non sono stati eseguiti aggiornamenti. O il record con ID ' . $publisher['publisher_id'] . ' non esiste o il dato era già presente.';
    }
}

?>

Esempio di update con transaction, commit e rollback

<?php

$pdo = require_once 'connect.php';

$publisher = [
    'publisher_id' => 1,
    'name' => 'Feltrinelli'
];

$sql = 'UPDATE publishers
        SET name = :name
        WHERE publisher_id = :publisher_id';

try {
    $pdo->beginTransaction();

    $statement = $pdo->prepare($sql);
    $statement->execute([
        ':name'         => $publisher['name'],
        ':publisher_id' => $publisher['publisher_id']
    ]);

    // Conferma le modifiche
    $pdo->commit();

    if ($statement->rowCount() > 0) {
        echo 'Il record con ID ' . $publisher['publisher_id'] . ' è stato aggiornato con successo!';
    } else {
        echo 'Nessun aggiornamento eseguito.';
    }

} catch (Exception $e) {
    // in caso di errore esegui rollback delle modifiche
    $pdo->rollBack(); //rollback evita che la transazione rimanga appesa
    echo 'Errore: ' . $e->getMessage();
}

?>