CM5 PHP BD/ PDO - Serveur pédagogique UFR Sciences et

PHP - BD
PostgreSQL
S. Lanquetin
LE2I
Aile sciences de l'ingénieur
Bureau G206
[email protected]
Présentation
Connexion (I)
PostgreSQL s'appuie sur le modèle
relationnel mais apporte les extensions
suivantes :
l  pg_connect(…) : renvoie true si la connexion est établie
$id_connexion = pg_connect("dbname=nom_base host=nom_serveur
port=num_port user=nom_utilisateur password=mot_passe");
l  pg_pconnect (…) : connexion persistante (ne se referme pas
automatiquement en fin de script).
l  pg_dbname(…) : retourne le nom de la base de données
–  les classes,
–  l'héritage,
–  les types de données utilisateurs (tableaux,
structures, listes..),
–  les fonctions,
$nom_base = pg_dbname ($id_connexion);
l 
pg_close($id_connexion);
l  On peut intégrer ce code dans un fichier que l’on pourra joindre
par include . (Sécuriser le mot de passe de connexion).
Site http://postgresql.org/
3
S. Lanquetin - L3 : Introduction Technologie pour le Web
4
S. Lanquetin - L3 : Introduction Technologie pour le Web
Connexion (II)
Requête
l Exemple 1 :
l pg_query(..) pour envoyer une requête à une base de données
retourne un identifiant de résultat ou FALSE si échec.
<?php
if( $id =pg_connect("dbname=nom_base host=nom_serveur
port=num_port user=nom_utilisateur password=mot_passe")) {
if(pg_dbname($id ) == "nom_base" ) {
echo "Succès de connexion.";
/* code du script … */
} else {
exit("Echec de connexion à la base.");
}
pg_close($id);
} else {
exit("Echec de connexion au serveur de base de données.");
}
?>
5
pg_close(…) : permet de fermer la connexion
S. Lanquetin - L3 : Introduction Technologie pour le Web
$id_resultat = pg_query($id_connexion, $requete);
Les requêtes les plus couramment utilisées :
– CREATE (création d’une table),
– SELECT (sélection),
– INSERT (insertion),
– UPDATE (mise à jour des données),
– DELETE (suppression),
– ALTER (modification d’une table), etc.
$result= pg_query ($id_connexion, "SELECT adresse FROM users
WHERE name = pg_escape_string($name) ");
6
S. Lanquetin - L3 : Introduction Technologie pour le Web
1
Extraction des données (I)
Extraction des données (II)
l  Affichage des résultats ligne par ligne.
pg_fetch_row($result) : retourne une ligne de résultat sous la
forme d’un tableau. Les éléments du tableau étant les valeurs des
attributs de la ligne. Retourne FALSE s’il n’y a plus aucune ligne.
Exemple :
SQL > SELECT * FROM etudiants;
ID
NOM
OPTION
---------------------------1 arthur
image
2 clement
multimedia
3 vincent
image pour multimedia
$requet = "SELECT * FROM etudiants";
if($result = pg_query($requet)) {
while($ligne = pg_fetch_row($result)) {
$id = $ligne[0];
$nom = $ligne[1];
$option = $ligne[2];
echo "$id - $nom, $option <br />";
}
} else {
echo "Erreur de requête de base de données.";
}
1ère ligne
2ème ligne
3è ligne
l plusieurs valeurs=>tableau, tableau associatif, ou objet.
7
S. Lanquetin - L3 : Introduction Technologie pour le Web
8
Extraction des données (III)
pg_fetch_array($result) : retourne tableau et tableau associatif.
pg_fetch_assoc($result) : retourne un tableau associatif. Les clés
étant les noms des attributs et leurs valeurs associées leurs valeurs
respectives. Retourne FALSE s’il n’y a plus aucune ligne.
Exemple :
$requet = "SELECT * FROM etudiants";
if($result = pg_query($requet)) {
while($ligne = pg_fetch_array($result)) {
$id = $ligne['id'];//$id = $ligne[0];
$nom = $ligne['nom'];
$option = $ligne['option'];
echo "$id - $nom, $option <br />";
}
} else {
echo "Erreur de requête de base de données.";
}
Ici, on accède aux valeurs de la ligne par l’attribut dans le tableau
associatif.
S. Lanquetin - L3 : Introduction Technologie pour le Web
$requet = "SELECT * FROM etudiants";
if($result = pg_query($requet)) {
while($ligne = pg_fetch_assoc($result)) {
$id = $ligne['id'];
$nom = $ligne['nom'];
$option = $ligne['option'];
echo "$id - $nom, $option <br />";
}
} else {
echo "Erreur de requête de base de données.";
}
10
Ici, on accède aux valeurs de la ligne par l’attribut dans le tableau
associatif.
S. Lanquetin - L3 : Introduction Technologie pour le Web
Extraction des données (IV)
Fonctions additionnelles
pg_fetch_object($result) : retourne un objet. Les attributs de
l’objet correspondent à ceux de la ligne de résultat. Et les valeurs
des attributs de l’objet correspondent à ceux de la ligne de
résultat. Retourne FALSE s’il n’y a plus aucune ligne.
Exemple 3 :
Quelques fonctions supplémentaires très utiles :
l pg_free_result($result) : efface de la mémoire du serveur les
lignes de résultat de la requête identifiées par $ result.
l pg_num_fields($result) : retourne le nombre d’attributs
dans un résultat.
$requet = "SELECT * FROM etudiants";
if($result = pg_query($requet)) {
while($ligne = pg_fetch_object($result)) {
$id = $ligne->id;
$nom = $ligne->nom;
$option = $ligne->option;
echo "$id - $name, $address <br />";
}
} else {
echo "Erreur de requête de base de données.";
}
11
S. Lanquetin - L3 : Introduction Technologie pour le Web
Extraction des données (III)
Exemple :
9
Ici, on accède aux valeurs de la ligne par leur indice dans le tableau.
Ici, on accède aux valeurs parS.leur
attribut
dans l’objet.
Lanquetin
- L3 : Introduction
Technologie pour le Web
l pg_num_rows($result) : retourne le nombre
d'enregistrement dans un résultat.
l Penser à bien tester la valeur de retour des fonctions
(pg_query et les autres) afin de détecter toute erreur
12
S. Lanquetin - L3 : Introduction Technologie pour le Web
2
Fonctions additionnelles
MySQL
l  $nom_hote = pg_host($id_connexion) : retourne le
nom de l'hôte pour la connexion spécifiée
l  $option_connexion = pg_options($id_connexion) :
retourne les options d'une connexion SQL
l  $num_port = pg_port($id_connexion) : retourne le
numéro de port pour la connexion indiquée
l  pg_set_client_encoding($id_connexion, $encodage) :
détermine l'encodage du client (SQL_ASCII, UNICODE,…)
l  $encodage = pg_client_encoding($id_connexion) :
retourne l'encodage du client;
13
S. Lanquetin - L3 : Introduction Technologie pour le Web
Présentation
Connexion (I)
l MySQL : base de données implémentant le langage
de requête SQL.
l  mysql_connect(…) : connection au serveur $server
retourne l’identifiant de connexion si succès, FALSE sinon
l phpMyAdmin : outil pour administrer aisément les
bases de données MySQL avec php.
http://www.phpmyadmin.net.
$id_connexion = mysql_connect($server,$user,$password)
l  mysql_pconnect (…) : connexion persistante (ne se referme
pas automatiquement en fin de script).
if(mysql_select_db($base[,$idconnexion]) )
l Avec MySQL vous pouvez créer plusieurs bases de
données sur un serveur. Une base est composée de
tables contenant des enregistrements.
http://www.mysql.com/.
15
l Documentation MySQL :
– http://www.mysql.com/documentation/
– En français chez nexen : http://dev.nexen.net/
docs/mysql/.
S. Lanquetin - L3 : Introduction Technologie pour le Web
l mysql_select_db(…) : choix de la base $base,
retourne TRUE en cas de succès, sinon FALSE
l mysql_close (…) : permet de fermer la connexion
mysql_close($id_connexion);
l intégration de ce code avec include. (Sécuriser le mot de passe de
connexion).
16
Connexion (II)
Requête
l Exemple 1 :
l mysql_query(..) pour envoyer une requête à une base de données
retourne un identifiant de résultat ou FALSE si échec.
<?php
if( $id =mysql_connect("nom_serveur","nom_utilisateur","mot_passe")) {
if($id_db=mysql_select_db($base,$id) ) {
echo "Succès de connexion.";
/* code du script … */
} else {
exit("Echec de connexion à la base.");
}
mysql_close($id);
} else {
exit("Echec de connexion au serveur de base de données.");
}
?>
17
S. Lanquetin - L3 : Introduction Technologie pour le Web
S. Lanquetin - L3 : Introduction Technologie pour le Web
$id_resultat = mysql_query($requete);
Les requêtes les plus couramment utilisées :
– CREATE (création d’une table),
– SELECT (sélection),
– INSERT (insertion),
– UPDATE (mise à jour des données),
– DELETE (suppression),
– ALTER (modification d’une table), etc.
$result = mysql_query(‘SELECT adresse FROM users WHERE name =\’ ’.
mysql_real_escape_string($name) .’\’ ’);
l Attention, contrairement à Oracle SQL, les requêtes MySQL ne se terminent
pas par un point virgule ‘;’
18
S. Lanquetin - L3 : Introduction Technologie pour le Web
3
Extraction des données (I)
Extraction des données (II)
l  Affichage des résultats ligne par ligne.
mysql_fetch_row($result) : retourne une ligne de résultat sous la
forme d’un tableau. Les éléments du tableau étant les valeurs des
attributs de la ligne. Retourne FALSE s’il n’y a plus aucune ligne.
Exemple :
SQL > SELECT * FROM etudiants;
ID
NOM
OPTION
---------------------------1 arthur
image
2 clement
multimedia
3 vincent
image pour multimedia
$requet = "SELECT * FROM etudiants";
if($result = mysql_query($requet)) {
while($ligne = mysql_fetch_row($result)) {
$id = $ligne[0];
$nom = $ligne[1];
$option = $ligne[2];
echo "$id - $nom, $option <br />";
}
} else {
echo "Erreur de requête de base de données.";
}
1ère ligne
2ème ligne
3ème ligne
l plusieurs valeurs=>tableau, tableau associatif, ou objet.
19
S. Lanquetin - L3 : Introduction Technologie pour le Web
20
Extraction des données (III)
mysql_fetch_array($result) : retourne tableau et tableau associatif.
mysql_fetch_assoc($result) : retourne un tableau associatif. Les
clés étant les noms des attributs et leurs valeurs associées leurs valeurs
respectives. Retourne FALSE s’il n’y a plus aucune ligne.
Exemple :
$requet = "SELECT * FROM etudiants";
if($result = mysql_query($requet)) {
while($ligne = mysql_fetch_array($result)) {
$id = $ligne["id"];
$nom = $ligne["nom"];
$option = $ligne["option"];
echo "$id - $nom, $option <br />";
}
} else {
echo "Erreur de requête de base de données.";
}
Ici, on accède aux valeurs de la ligne par l’attribut dans le tableau
associatif.
S. Lanquetin - L3 : Introduction Technologie pour le Web
$requet = "SELECT * FROM etudiants";
if($result = mysql_query($requet)) {
while($ligne = mysql_fetch_assoc($result)) {
$id = $ligne["id"];
$nom = $ligne["nom"];
$option = $ligne["option"];
echo "$id - $nom, $option <br />";
}
} else {
echo "Erreur de requête de base de données.";
}
22
Ici, on accède aux valeurs de la ligne par l’attribut dans le tableau
associatif.
S. Lanquetin - L3 : Introduction Technologie pour le Web
Extraction des données (IV)
Fonctions additionnelles
mysql_fetch_object($result) : retourne un objet. Les attributs
de l’objet correspondent à ceux de la ligne de résultat. Et les
valeurs des attributs de l’objet correspondent à ceux de la ligne
de résultat. Retourne FALSE s’il n’y a plus aucune ligne.
Exemple 3 :
Quelques fonctions supplémentaires très utiles :
l mysql_free_result($result) : efface de la mémoire du
serveur les lignes de résultat de la requête identifiées par
$requet. Très utile pour améliorer les performances du serveur.
l mysql_insert_id([$id]) : retourne l’identifiant d’un attribut
clé primaire AUTO_INCREMENT de la dernière insertion.
$requet = "SELECT * FROM etudiants";
if($result = mysql_query($requet)) {
while($ligne = mysql_fetch_object($result)) {
$id = $ligne->id;
$nom = $ligne->nom;
$option = $ligne->option;
echo "$id - $name, $address <br />";
}
} else {
echo "Erreur de requête de base de données.";
}
23
S. Lanquetin - L3 : Introduction Technologie pour le Web
Extraction des données (III)
Exemple :
21
Ici, on accède aux valeurs de la ligne par leur indice dans le tableau.
Ici, on accède aux valeurs parS.leur
attribut
dans l’objet.
Lanquetin
- L3 : Introduction
Technologie pour le Web
l mysql_num_fields($result) : retourne le nombre d’attributs
du résultats.
l mysql_num_rows($result) : retourne le nombre de lignes du
résultats. Et ainsi permet de remplacer le while par un for.
24
Penser à bien tester la valeur de retour des fonctions
(mysql_query et les autres) afin de détecter toute erreur et
d’éviter les Warnings.
S. Lanquetin - L3 : Introduction Technologie pour le Web
4
Qu'est ce que PDO
PDO
25
27
l  PHP Data Objects
l  Interface d'abstraction à l'accès de données
l  Écrit en C
l  Fournit avec php 5.1 extension pour php 5.0
l  Ne fonctionne pas avec les versions antérieures!
26
S. Lanquetin - L3 : Introduction Technologie pour le Web
Les BD supportées
Installation
l  MySQL 3,4,5
l  PostgreSQL
l  SQLite 2 & 3
l  ODBC
l  DB2
l  Oracle
l  Firebird
l  FreeTDS/Sybase/MSSQL
l  Activer PDO
S. Lanquetin - L3 : Introduction Technologie pour le Web
–  php.ini : extension=php_pdo.dll
l  Activer le driver PDO de la base de données
choisie
–  extension=php_pdo_mysql.dll
–  extension=php_pdo_pgsql.dll
28
S. Lanquetin - L3 : Introduction Technologie pour le Web
Afficher drivers PDO disponibles
Connexion
l  Lancer ce script pour connaître les drivers
disponibles
l  Création d'une instance de la classe de base de
PDO
–  Connexion MySQL
<?php
foreach( get_loaded_extensions () as $extension) {
if(strpos(strtolower($extension), 'pdo') !==
FALSE) {
echo $extension.'<br/>';
}
}
?>
29
S. Lanquetin - L3 : Introduction Technologie pour le Web
<?php
// connexion => création d’une instance
$bd =
new PDO('mysql:host=localhost;dbname=test', $user, $pass);
…
// déconnexion
$bd = null;
?>
30
S. Lanquetin - L3 : Introduction Technologie pour le Web
5
Connexion
Erreurs de connexion
l  Création d'une instance de la classe de base de
PDO
l  Gestion des erreurs de connexion
–  Connexion PostgreSQL
<?php
try {
<?php
// connexion => création d’une instance
$bd = new PDO('pgsql:host=localhost; port=5432;
dbname=testbd', 'toto', 'pass');
…
// déconnexion
$bd = null;
$bd = new PDO(…);
} catch (PDOException $e) {
echo $e->getMessage();
}
?>
?>
31
S. Lanquetin - L3 : Introduction Technologie pour le Web
32
S. Lanquetin - L3 : Introduction Technologie pour le Web
Connexion persistante
Requêtes
l  Tableau des options du driver passé au
constructeur PDO
l  query() pour récupérer une information
–  SELECT
// récupération de tous les éléments de la base table dans l'ordre croissant
$bd ->query("SELECT item FROM table ORDER BY item ASC");
<?php
// connexion persistante
$bd = new PDO('mysql:host=localhost;dbname=test',
$user, $pass, array(PDO::ATTR_PERSISTENT => true));
?>
l  exec() pour modifier la BD
–  INSERT
–  UPDATE
–  DELETE
// ajout d'un élément à la BD
$bd ->exec("INSERT INTO table VALUES(' ','login', 'pass') ");
33
S. Lanquetin - L3 : Introduction Technologie pour le Web
34
S. Lanquetin - L3 : Introduction Technologie pour le Web
Requêtes
Requêtes
l  exec() retourne le nombre de lignes affectées par
l'opération ou false en cas d'erreur
l  query() retourne un objet PDOStatement ou false
en cas d'erreur
Rque : Aucune ligne ne peut être modifiée : 0 ≠ false
// ajout d'un élément à la BD
$ nb_chgmt = $bd ->exec("INSERT INTO table VALUES(' ','login', 'pass') ");
if (!$nb_chgmt) // attention pas correct
// ajout d'un élément à la BD
$ nb_chgmt = $bd ->query("SELECT item FROM table ORDER BY item ASC");
if (!$nb_chgmt) // attention pas correct
if ($ nb_chgmt !== false) // Correct
if ($ nb_chgmt !== false) // Correct
35
S. Lanquetin - L3 : Introduction Technologie pour le Web
36
S. Lanquetin - L3 : Introduction Technologie pour le Web
6
Requêtes
Requête préparée
l  query() retourne un objet PDOStatement ou false
en cas d'erreur
l  Préparation de l'objet PDOStatement avec
l  Compilé une fois, exécuté n fois
–  PDO::prepare()
–  Appel avec PDOStatement::execute()
l  Séparation claire entre structure et données,
prévention des injections SQL
l  Souvent plus rapide que query() et exec()
37
S. Lanquetin - L3 : Introduction Technologie pour le Web
Requête préparée
Requête préparée
l  Sans marqueur
l  Avec marqueur nominatif
// préparation de la requête
$requete_prepare=$bd->prepare("SELECT identifiant FROM
membres");
// exécution de la requête
$requete_prepare->execute();
// affichage du résultat de la requête
while($lignes=$requete_prepare->fetch(PDO::FETCH_OBJ))
{ echo $lignes->identifiant.'<br />'; }
Requête préparée
// préparation de la requête
$requete_prepare=$bd->prepare("SELECT identifiant FROM
membres WHERE ID_membre = :id");
// exécution de la requête
$requete_prepare->execute(array( ':id' => 1 ));
// affichage du résultat de la requête
$lignes=$requete_prepare->fetch(PDO::FETCH_OBJ);
echo $lignes->identifiant.'<br />';
Requête préparée
l  Avec marqueur ?
// préparation de la requête
$requete_prepare=$bd>prepare("SELECT identifiant FROM
membres WHERE ID_membre = ? ");
// exécution de la requête
$requete_prepare->execute(array(1 ));
// affichage du résultat de la requête
$lignes=$requete_prepare->fetch(PDO::FETCH_OBJ);
echo $lignes->identifiant.'<br />';
// préparation de la requête
$stmt = $bd->prepare(
“SELECT * FROM users WHERE id=?”
);
// exécution de la requête
$stmt->execute(array($_GET[‘id’]));
// affichage du résultat de la requête
$stmt->fetch(PDO::FETCH_ASSOC);
7
Résultats de la requête
l Flexibilité de PDO pour la récupération des
données :
–  Array
–  Strings
–  Objets
–  Fonction de rappel
–  Chargement tardif
–  Itérateurs
Résultats de la requête : tableaux
$res = $db->query("SELECT * FROM table");
while ($row = $res->fetch(PDO::FETCH_NUM)){
// $row == tableau avec indices des colonnes
}
$res = $db->query("SELECT * FROM table");
while ($row = $res->fetch(PDO::FETCH_ASSOC)){
// $row == tableau avec noms des colonnes
}
$res = $db->query("SELECT * FROM table");
while ($row = $res->fetch(PDO::FETCH_BOTH)){
// $row == tableau avec indices et noms des colonnes
}
Résultats de la requête : String
Résultats de la requête : Objet
l  Résultats en une seule colonne
l  Récupération d'une ligne comme une instance
de stdClass où nom_colonne= nom_propriété.
$res = $db->query("SELECT users WHERE login='login' AND
password='password' ");
// fetch(PDO::FETCH_COLUMN)
if ($res->fetchColumn()) { // retourne un string
// login OK
} else { /* échec de l'authentication */ }
$res = $db->query("SELECT * FROM table");
while ($obj = $res->fetch(PDO::FETCH_OBJ)) {
// $obj == instance de stdClass
}
Résultats de la requête : Classe
Résultats de la requête : Classe
l  Nouvelle instance d'une classe de votre choix
avec colonne = membre de la classe
l  Détermine le nom de la classe à partir de la
valeur de la première colonne
$res = $db->query("SELECT * FROM table");
$res->setFetchMode(
PDO::FETCH_CLASS,
"ma_classe",
array('optional'='Constructor Params')
);
while ($obj = $res->fetch()) {
// $obj == instance de ma_classe
}
$res = $db->query(“SELECT * FROM foo”);
$res->setFetchMode(
PDO::FETCH_CLASS |
PDO::FETCH_CLASSTYPE
);
while ($obj = $res->fetch()) {
// $obj == instance de classe avec comme
//nom la valeur de la 1ere colonne
}
8
Résultats de la requête : Objet défini
Résultats de la requête : Itérateur
l  Récupération des données dans un objet
existant
$u = new userObject;
$res = $bd->query(“SELECT * FROM users”);
$res->setFetchMode(PDO::FETCH_INTO, $u);
while ($res->fetch()) {
// classe existante avec colonne
correspondant aux propriétés
}
//
$res = $bd>query(
“SELECT * FROM users”,
PDO::FETCH_ASSOC
);
foreach ($res as $row) {
// $row = tableau représentant les valeurs
//des lignes
}
Lazy Fetching
Callback Function
l  Ligne -> objet avec nom variable = noms
colonnes
l  Mode de récupération où le résultat est traité
par une fonction
$res = $bd>query(
“SELECT * FROM users”,
PDO::FETCH_LAZY
);
foreach ($res as $row) {
echo $row[‘name’];
// récupération des noms de colonnes uniquement
}
function draw_message($subject,$email) { … }
$res = $bd>query("SELECT * FROM msg");
$res->fetchAll(
PDO::FETCH_FUNC,
"draw_message"
);
Problème des requêtes directes
Caractères spéciaux
l  Requête interprétée à chaque exécution
=>gâchis en cas de requête répétée
l  quote() Protège une chaîne pour l'utiliser dans
une requête SQL PDO (guillemets autour de la
chaine protection des caractères spéciaux
l  Sécurité : les données de l'utilisateur peuvent
contenir des éléments menant à une injection
SQL
$qry = "SELECT * FROM users WHERE
login=".$db->quote($_POST['login'])."
AND
passwd=".$db->quote($_POST['pass']);
9
Récupération partielle de données
Informations de connexion
l  closeCursor() : Ferme le curseur, permettant à
la requête d'être de nouveau exécutée
l  getAttribute() pour obtenir des informations de
connexion
$res = $db->query("SELECT * FROM users");
foreach ($res as $v) {
if ($res['name'] == 'end') {
$res->closeCursor();
break;
}
}
// version de la base de données
$db->getAttribute(PDO::ATTR_SERVER_VERSION);
// version de la bibliothèque cliente
$db->getAttribute(PDO::ATTR_CLIENT_VERSION);
// metainformations sur le serveur de base de données
$db->getAttribute(PDO::ATTR_SERVER_INFO);
// statut de connexion
$db->getAttribute(PDO::ATTR_CONNECTION_STATUS);
http://www.php.net/manual/fr/book.pdo.php
http://www.siteduzero.com/tutoriel-3-34790-pdointerface-d-acces-aux-bdd.html
57
S. Lanquetin - L3 : Introduction Technologie pour le Web
10