PHP - PDO

Questo post fa parte di una serie preparata qualche anno fa per delle lezioni su PHP.

Cos'è PDO

PDO (Php Data Objects) è un'estensione che fornisce  un'interfaccia coerente per l'accesso a diversi tipi di basi di dati. Si usa specificando quale driver usare per l'accesso alla particolare base di dati che si intende utilizzare.

Driver disponibili

L'elenco dei driver disponibili si può ottenere con:

<?php
print_r(PDO::getAvailableDrivers());

Sul mio calcolatore ottengo:

Array
(
    [0] => mysql
    [1] => odbc
    [2] => pgsql
    [3] => sqlite
    [4] => sqlite2
)

Gli esempi che seguono fanno uso, per semplicità, del driver sqlite2, ma a parte la specificazione iniziale della sorgente dati dovrebbero essere applicabili con qualsiasi tipo di base di dati supportata.

Inserimento di righe

Per inserire righe in una tabella dovremo fare una connessione e poi eseguire una query di tipo insert:

<?php
try
{
    /*** connect to SQLite database and get a handler ***/
    $dbh = new PDO("sqlite2:pictures.sql.db");
    // $dbh = new PDO("mysql:host=localhost; dbname=mydb", $user, $password); // for MySql
    
    $count = $dbh->exec("INSERT INTO Category(Description, Rank) VALUES ('Libri', 10)");
    echo 'Affected rows: ' . $count . "\n";
}
catch(PDOException $e)
{
    echo 'Error: ' . $e->getMessage() . "\n";
}

Risultato:

Affected rows: 1

Selezione

<?php
try
{
    /*** connect to SQLite database ***/
    $dbh = new PDO("sqlite2:pictures.sql.db");
    
    $sql = "SELECT * FROM Category";
        
    foreach ($dbh->query($sql, PDO::FETCH_ASSOC) as $row)
    {
      print_r($row);
    }

}
catch(PDOException $e)
{
    echo 'Error: ' . $e->getMessage() . "\n";
}

Risultato:

Array
(
    [id] => 1
    [Description] => Luoghi
    [Rank] => 9
)
Array
(
    [id] => 2
    [Description] => Persone
    [Rank] => 2
)
Array
(
    [id] => 3
    [Description] => Oggetti
    [Rank] => 3
)
Array
(
    [id] => 4
    [Description] => Libri
    [Rank] => 10
)

Inserimenti con Prepared Statement

Supponiamo di dover fare diverse operazioni di inserimento in un'unica tabella. A tale scopo è possibile usare i cosiddetti Prepared Statement, con i seguenti vantaggi:

1. protezione da SQL injection
2. maggiore velocità di esecuzione
3. maggiore comodità di esecuzione (non dobbiamo preoccuparci di fare l'escape degli apici, ad esempio)

<?php
try
{
  /*** connect to SQLite database ***/
  $dbh = new PDO("sqlite2:pictures.sql.db");
  
  /*** we prepare the generic query, using placeholders ***/
  $stmt = $dbh->prepare(
    "INSERT INTO Category (Description, Rank) VALUES (:description, :rank)"
    );
  $stmt->bindParam(':description', $description);
  $stmt->bindParam(':rank', $rank);

  // insert one row
  $description = 'Feste';
  $rank = 13;
  $stmt->execute();

  // insert a second row
  $description = 'Sport';
  $rank = 14;
  $stmt->execute();
  
  // insert a third row
  $description = '€urozona';
  $rank = 15;
  $stmt->execute();

  // insert a fourth row
  $description = "Prove d'orchestra";
  $rank = 16;
  $stmt->execute();

}
catch(PDOException $e)
{
    echo 'Error: ' . $e->getMessage() . "\n";
}

Dopo l'esecuzione, possiamo verificare la presenza in tabella delle righe inserite:

$ echo "select * from Category;" | sqlite pictures.sql.db 
1|Luoghi|9
2|Persone|2
3|Oggetti|3
4|Libri|10
5|Feste|13
6|Sport|14
7|€urozona|15
8|Prove d'orchestra|16

Analogamente, avremmo potuto fare un collegamento basato sulla posizione anziché sul nome del segnaposto (pratica sconsigliabile):

<?php
try
{
  /*** connect to SQLite database ***/
  $dbh = new PDO("sqlite2:pictures.sql.db");
  
  $stmt = $dbh->prepare(
    "INSERT INTO Category (Description, Rank) VALUES (?, ?)"
    );
  $stmt->bindParam(1, $description);
  $stmt->bindParam(2, $rank);

  // insert one row
  $description = "Fuochi d'artificio";
  $rank = 17;
  $stmt->execute();

  // insert another row
  $description = "Monumenti";
  $rank = 18;
  $stmt->execute();
}
catch(PDOException $e)
{
    echo 'Error: ' . $e->getMessage() . "\n";
}

Controlliamo l'esito:

$ echo "select * from Category where id>8;" | sqlite pictures.sql.db 
9|Fuochi d'artificio|17
10|Monumenti|18

Selezione tramite Prepared Statement

Anche per le operazioni di selezione possiamo tranquillamente usare delle istruzioni preparate:

<?php
try
{
  /*** connect to SQLite database ***/
  $dbh = new PDO("sqlite2:pictures.sql.db");
  
  $stmt = $dbh->prepare("SELECT * FROM Category where Rank <= ?");

  $stmt->bindParam(1, $max_rank);
  $max_rank = 10;

  if ($stmt->execute())
  {
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
      print_r($row);
    }
  }
  
}
catch(PDOException $e)
{
    echo 'Error: ' . $e->getMessage() . "\n";
}

Risultato:

Array
(
    [id] => 1
    [Description] => Luoghi
    [Rank] => 9
)
Array
(
    [id] => 2
    [Description] => Persone
    [Rank] => 2
)
Array
(
    [id] => 3
    [Description] => Oggetti
    [Rank] => 3
)
Array
(
    [id] => 4
    [Description] => Libri
    [Rank] => 10
)

E anche per la selezione vale ovviamente la possibilità di usare dei segnaposto con nomi sensati:

<?php
try
{
  /*** connect to SQLite database ***/
  $dbh = new PDO("sqlite2:pictures.sql.db");
  
  $stmt = $dbh->prepare(
    "SELECT * FROM Category WHERE Description LIKE :description AND Rank <= :max_rank"
    );
    
  $stmt->bindParam(':description', $description);
  $stmt->bindParam(':max_rank', $max_rank);

  $max_rank = 9;
  $description = 'L%';

  if ($stmt->execute())
  {
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
      print_r($row);
    }
  }
}
catch(PDOException $e)
{
    echo 'Error: ' . $e->getMessage() . "\n";
}

Risultato:

Array
(
    [id] => 1
    [Description] => Luoghi
    [Rank] => 9
)

Aggiornamenti e cancellazioni

Istruzioni di tipo update e di tipo delete possono essere preparate in maniera analoga.

  $stmt = $dbh->prepare(
    "UPDATE Picture SET Path=:path WHERE id=:id;"
    );

oppure

  $stmt = $dbh->prepare(
    "DELETE FROM Picture WHERE id=:id;"
    );

Gestione degli errori

Nel caso di esecuzione di inserimenti, possiamo controllare l'esito dell'operazione e verificare cosa è andato storto. Si consideri il seguente esempio, in cui tentiamo di inserire una riga in cui il valore per una chiave esterna non è definito:

<?php
try
{
  /*** connect to SQLite database ***/
  $dbh = new PDO("sqlite2:pictures.sql.db");

  $count = $dbh->exec(
    "INSERT INTO Picture(Path, Description, Type) VALUES
      ('pictures/first.jpg', 'A tree', 'jpeg');"
    );
    
  if ($count)
  {
    echo "Elemento inserito\n";
  }
  else
  {
    print_r($dbh->errorInfo());
  }
}
catch(PDOException $e)
{
    echo 'Error: ' . $e->getMessage() . "\n";
}

L'esecuzione non effettua nessun inserimento, ma possiamo avere notizie sul motivo:

Array
(
    [0] => 23000
    [1] => 19
    [2] => Picture.Category_id may not be NULL
)

Transazioni

Nel caso si debbano eseguire più query dipendenti l'una dall'altra, è consigliabile racchiuderle in una transazione, in modo che vengano eseguite tutte o nessuna. Si consideri questo esempio, in cui prima viene inserita una categoria e successivamente una immagine con un id duplicato e per questo non valido. Ciò che si vuole ottenere è che se il secondo inserimento fallisce, anche il primo deve essere annullato.

<?php
try
{
  /*** connect to SQLite database ***/
  $dbh = new PDO("sqlite2:pictures.sql.db");

  $dbh->beginTransaction();
  
  // we insert a new category
  $dbh->exec(
    "INSERT INTO Category(Description, Rank) VALUES
      ('Alberi', 18);"
    );
  
  // we get the id, since it comes from an auto-increment field
  $category_id = $dbh->lastInsertId();  
  echo 'Categoria inserita, id=' . $category_id . "\n";
  
  // we insert the picture, using the category_id we got
  $count = $dbh->exec(
    "INSERT INTO Picture(id, Path, Description, Type, Category_id) VALUES
      (1, 'pictures/first.jpg', 'A tree', 'jpeg', $category_id);"
    );
    
  if ($count)
  {
    echo "Elemento inserito\n";
    $dbh->commit();
  }
  else
  {
    print_r($dbh->errorInfo());
    // since we did not insert the picture, we don't want the category either
    $dbh->rollBack();
  }

}
catch(PDOException $e)
{
    echo 'Error: ' . $e->getMessage() . "\n";
}

L'esecuzione ci dice che la categoria è stata inserita, ma poi in effetti non ne troviamo traccia nel database (visto che è stato eseguito il RollBack):

Categoria inserita, id=11
Array
(
    [0] => 23000
    [1] => 19
    [2] => PRIMARY KEY must be unique
)

Controlliamo il database:

$ echo "select * from Category;" | sqlite pictures.sql.db 
...
10|Monumenti|18

Oggetti come risultato

Con fetchObject si possono ottenere i dati direttamente come istanza di una classe:

<?php

class Picture

{

  // settings, variables, functions...

}

try

{

  /*** connect to SQLite database ***/

  $dbh = new PDO("sqlite2:pictures.sql.db");

  $query = "SELECT * FROM Picture";

    

  $result = $dbh->query($query);

  

  while ($picture = $result->fetchObject('Picture'))

    {

      print_r($picture);

    }

}

catch(PDOException $e)

{

    echo 'Error: ' . $e->getMessage() . "\n";

}

Risultato:

Picture Object

(

    [id] => 1

    [Path] => pictures/first.jpg

    [Description] => A tree

    [Type] => jpeg

    [Width] => 

    [Height] => 

    [Category_id] => 12

)

Picture Object

(

    [id] => 2

    [Path] => pictures/first.jpg

    [Description] => A tree

    [Type] => jpeg

    [Width] => 

    [Height] => 

    [Category_id] => 13

)

Questo approccio è però sconsigliato quando la classe contiene un costruttore che imposta dei valori per alcune proprietà dell'oggetto. Il costruttore dovrebbe essere il primo metodo eseguito dopo la creazione dell'istanza, ma si può facilmente verificare che esso viene invece eseguito dopo che le proprietà lette con la query nel database sono state importate.

Se si usano i prepared statement, si può ovviare forzando il caricamento delle proprietà dopo l'esecuzione del costruttore, come nel seguente esempio:

class Category

{

  private $id;

  private $Description;

  private $Rank;

  private $_status;

  public function __construct($status)

  {

    $this->_status = $status;

  }

}

try

{

    /*** connect to SQLite database and get a handler ***/

    $dbh = new PDO("sqlite2:pictures.sql.db");

}

catch(PDOException $e)

{

    echo 'Error: ' . $e->getMessage() . "\n";

    $dbh = false;

}

if($dbh)

{

  $stmt = $dbh->prepare("SELECT * FROM Category");

  $stmt->setFetchMode(
     PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 

     'Category', 

     array(1)

     );

  if ($stmt->execute())

  {

    while ($row = $stmt->fetch())

    {

      print_r($row);

    }

  }

}

Risultato:

Category Object

(

    [id:Category:private] => 1

    [Description:Category:private] => Luoghi

    [Rank:Category:private] => 9

    [_status:Category:private] => 1

)

Category Object

(

    [id:Category:private] => 2

    [Description:Category:private] => Persone

    [Rank:Category:private] => 2

    [_status:Category:private] => 1

)

Category Object

(

    [id:Category:private] => 3

    [Description:Category:private] => Oggetti

    [Rank:Category:private] => 3

    [_status:Category:private] => 1

)

Category Object

(

    [id:Category:private] => 4

    [Description:Category:private] => Libri

    [Rank:Category:private] => 10

    [_status:Category:private] => 1

)

Nel codice, si può notare che viene impostata la modalità di recupero dei dati (metodo setFetchMode) indicando di usare sia la modalità FETCH_CLASS sia quella FETCH_PROPS_LATE. È anche possibile impostare un quarto parametro, dopo il nome della classe da istanziare, in forma di array di valori da passare al costruttore.