Questo
post fa parte di una serie preparata qualche anno fa per
delle lezioni su PHP.
Creazione di un utente e di un database
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
Nessun commento:
Posta un commento