PHP - MySQL

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

Creazione di un utente e di un database

Per poter accedere a un database MySQL è necessario godere di adeguati privilegi.

Se vogliamo creare un utente sym con privilegi completi per tutti i database il cui nome inizia con sym_, dovremo utilizzare (ad esempio con l'interfaccia web phpMyAdmin):

-- Codice SQL
CREATE USER 'sym'@'localhost' IDENTIFIED BY 'ALpBtawuKws6fSbG';

GRANT USAGE ON * . * TO 'sym'@'localhost' IDENTIFIED BY 'ALpBtawuKws6fSbG' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `sym\_%` . * TO 'sym'@'localhost';

Un nuovo database, ad esempio sym_mediamanager, potrà poi essere creato con:

CREATE DATABASE `sym_mediamanager` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

A questo punto abbiamo a disposizione le informazioni necessarie per l'accesso al database, che potremo scrivere in un file di configurazione. Ad esempio, potremmo preparare un file db.ini simile al seguente:

USER=sym
PASSWORD=ALpBtawuKws6fSbG
SERVER=localhost
DB=sym_mediamanager

Creazione delle tabelle

Possiamo creare le tabelle mediante phpMyAdmin o con altri strumenti. Immaginiamo di voler creare due tabelle come le seguenti:

Schema relazionale (realizzato con wwwsqldesigner)

Le istruzioni necessarie saranno simili alle seguenti:

-- Codice SQL-- ---
-- Globals
-- ---

-- SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
-- SET FOREIGN_KEY_CHECKS=0;

-- ---
-- Table 'Category'
-- 
-- ---

DROP TABLE IF EXISTS `Category`;
        
CREATE TABLE `Category` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `Description` VARCHAR(255) NOT NULL,
  `Rank` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);

-- ---
-- Table 'Picture'
-- 
-- ---

DROP TABLE IF EXISTS `Picture`;
        
CREATE TABLE `Picture` (
  `id` INTEGER AUTO_INCREMENT,
  `Path` VARCHAR(255) NOT NULL,
  `Description` VARCHAR(255) DEFAULT NULL,
  `Type` VARCHAR(10) DEFAULT NULL,
  `Width` INTEGER DEFAULT NULL,
  `Height` INTEGER DEFAULT NULL,
  `Category_id` INTEGER NOT NULL,
  PRIMARY KEY (`id`)
);

-- ---
-- Foreign Keys 
-- ---

ALTER TABLE `Picture` ADD FOREIGN KEY (Category_id) REFERENCES `Category` (`id`);

-- ---
-- Table Properties
-- ---

ALTER TABLE `Category` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `Picture` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Notare che le tabelle devono essere di tipo InnoDB per poter supportare l'integrità referenziale e le transazioni.

Inserimento di dati nelle tabelle

L'inserimento si ottiene con una query di tipo Insert:

<?php
$dbinfo=parse_ini_file('db.ini');
$conn = new mysqli($dbinfo['SERVER'], $dbinfo['USER'], $dbinfo['PASSWORD'], $dbinfo['DB']);

if ($mysqli_connect_error){
    die("Connessione al DB fallita: " . mysqli_connect_error());
    }

$query="INSERT INTO Category (Description, Rank) VALUES ('Paesaggi', 1)";

if ($conn->query($query))
{
    echo "Inserimento riuscito\n";
}
else
{
    echo "Inserimento fallito\n";
}

$conn->close();

Estrazione di dati

Quando si esegue una query di selezione si può decidere di ottenere i risultati in forma di array con indici numerici (fetch_row), di array associativo (fetch_assoc) o di oggetto (fetch_object):

<?php

$dbinfo=parse_ini_file('db.ini');

$conn = new mysqli($dbinfo['SERVER'], $dbinfo['USER'], $dbinfo['PASSWORD'], $dbinfo['DB']);

if ($mysqli_connect_error
{
    die("Connessione al DB fallita: " . mysqli_connect_error());
}

echo "Connesso al DB " . $conn->host_info . "\n";

$result = $conn->query("SELECT * FROM Category;");

while ($row = $result->fetch_row())
{
    print_r($row);
}

$result->free();
$conn->close();

Risultato:

Array
(
    [0] => 2
    [1] => Paesaggi
    [2] => 1
)
Array
(
    [0] => 3
    [1] => Persone
    [2] => 2
)

Usando $row = $result->fetch_assoc() otteniamo array associativi:

Array
(
    [id] => 2
    [Description] => Paesaggi
    [Rank] => 1
)
Array
(
    [id] => 3
    [Description] => Persone
    [Rank] => 2
)


Usando invece $row = $result->fetch_object() otteniamo:

stdClass Object
(
    [id] => 2
    [Description] => Paesaggi
    [Rank] => 1
)
stdClass Object
(
    [id] => 3
    [Description] => Persone
    [Rank] => 2
)

È naturalmente possibile specificare quale classe usare per istanziare l'oggetto ottenuto.

Query multiple

È possibile comporre una query multipla ed eseguirla per ottimare le prestazioni. Ad esempio:

$query="
SELECT Description FROM Category;
SELECT Rank FROM Category;
";

if ($conn->multi_query($query))
{
  do
  {
    if ($result=$conn->store_result())
    {
      while ($row = $result->fetch_assoc())
      {   
        print_r($row);
      }
      $result->close();
    }
  } while ($conn->next_result());
  $conn->close();
}
Risultato:

Array
(
    [Description] => Paesaggi
)
Array
(
    [Description] => Persone
)
Array
(
    [Rank] => 1
)
Array
(
    [Rank] => 2
)

Prepared statements

È possibile effettuare query di inserimento e di selezione usando delle istruzioni a cui vengono agganciate automaticamente alcune variabili (binding). Sebbene sia possibile farlo direttamente con MySQL, affronteremo l'argomento nella lezione in cui parleremo di PDO.

Implementazione tramite classe specifica

Una soluzione per gestire i dati di una tabella potrebbe essere di definire una classe specifica. Si veda questo esempio:

<?php

$dbinfo=parse_ini_file('db.ini');

class InvalidDBConnectionException extends Exception{};

class Category {
    private
    $conn,
    $id,
    $Description,
    $Rank;

    function __toString()
    {
        return sprintf('[%d] %s (rank: %d)', $this->id, $this->Description, $this->Rank);
    }

    function __construct(Mysqli $conn)
    {
        $this->conn=$conn;
    }

    function setData($info)
    {
        /* use an array as input */
       $this->id = $info['id'];
       $this->Description = $info['Description'];
       $this->Rank = $info['Rank'];
    }

    function retrieveData($id)
    {
        $query="SELECT * FROM Category WHERE id=$id";
        $result=$this->conn->query($query);
        if ($result->num_rows==1)
        {
           $row=$result->fetch_assoc();
           $this->setData($row);
           return true;
        }
        else
        {
           return false;
        }
        $result->free_result();
    }
}

$db = new mysqli($dbinfo['SERVER'], $dbinfo['USER'], $dbinfo['PASSWORD'], $dbinfo['DB']);

$testvalues=array(1, 2, 3, 4, 5);

foreach($testvalues as $id)
{
  $category=new Category($db);
  if ($category->retrieveData($id))
  {
    echo $category . "\n";
  }
  else
  {
    echo "--> La categoria con id $id non è presente nel DB\n";
  }
}
$db->close();

Risultato:

--> La categoria con id 1 non è presente nel DB
[2] Paesaggi (rank: 1)
[3] Persone (rank: 2)
--> La categoria con id 4 non è presente nel DB
--> La categoria con id 5 non è presente nel DB