Partie4 PHP && MySQL PHP & MYSQL Introduction MySQL dérive directement de SQL (Structured Query Language) qui est un langage de requête vers les bases de données exploitant le modèle relationnel. Le serveur de base de données MySQL est très souvent utilisé avec le langage de création de pages web dynamiques PHP. Base de Données MySQL Requêtes SQL Serveur Script PHP HTTP Page HTML Client Théorie de bases de données Une relation est une table comportant des colonnes (appelées aussi attributs) dont le nom et le type caractérisent le contenu qui sera inséré dans la table. Imaginons que l’on veuille stocker dans notre base de données notre carnet d’adresses. On va donc créer la relation Personne qui aura pour attributs : nom, prénom, adresse, téléphone. Autrement dit, c’est une table nommée Personne possédant les colonnes : nom, prénom, adresse, téléphone. Les lignes que contiendra cette table seront appelées enregistrements ou tuples. nom prénom adresse téléphone OUADFEL SALIMA CONSTANTINE 031343434 Algèbre relationnelle L’algèbre relationnelle regroupe toutes les opérations possibles sur les relations. Voici la liste des opérations possibles : Projection : on ne sélectionne qu’un ou plusieurs attributs d’une relation (on ignore les autres). Par exemple n’afficher que les colonnes nom et prénom de la table Personnes. Jointure : on fabrique une nouvelle relation à partir de 2 ou plusieurs autres en prenant comme pivot 1 ou plusieurs attributs. Par exemple, on concatène la table du carnet d’adresse et celle des inscrits à la bibliothèque en fonction du nom de famille. Sélection : on sélectionne tous les tuples ou bien seulement une partie en fonction de critères de sélection qui portent sur les valeurs des attributs. Par exemple n’afficher que les lignes de la table Personnes qui vérifient la condition suivante : le nom ne commence pas par la lettre ‘C’. Cette algèbre est facilement possible avec les commandes de MySQL (SELECT… FROM… WHERE…) Algèbre relationnelle Projection SELECT champ1,champ2,champ3 FROM table Jointure SELECT table1.champ_a, table2.champ_b FROM table1, table2 WHERE table1.champ_pivot = table2.champ_pivot Sélection SELECT * FROM table WHERE champ_a = ‘’valeur’’ Syntaxe MySQL Type des attributs Les propriétés des objets peuvent être de types différents : - Nombre entier signé ou non (quantité commandée, âge) - Nombre à virgule (prix, taille) - Chaîne de caractères (nom, adresse, article) - Date et heure (date de naissance, heure de parution) Il s’agit de choisir le plus adapté aux besoins. Ces types requièrent une plus ou moins grande quantité de données à stocker. Par exemple, ne pas choisir un LONGTEXT pour stocker un prénom mais plutôt un VACHAR(40) ! Type des attributs: entiers nom TINYINT TINYINT UNSIGNED SMALLINT SMALLINT UNSIGNED MEDIUMINT MEDIUMINT UNSIGNED INT INT UNSIGNED BIGINT BIGINT UNSIGNED borne inférieure borne supérieure -128 127 0 255 -32768 32767 0 65535 -8388608 8388607 0 16777215 -2147483648 2147483647 0 4294967295 -9223372036854775808 9223372036854775807 0 18446744073709551615 Type des attributs: flottants Les flottants – dits aussi nombres réels – sont des nombres à virgule. nom FLOAT DOUBLE domaine négatif : borne inférieure borne supérieure Domaine positif : borne inférieure borne supérieure -3.402823466 E+38 -1.175494351 E-38 1.175494351 E-38 3.402823466 E+38 -1.7976931348623157 E+308 -2.2250738585072014 E-308 2.2250738585072014 E-308 1.7976931348623157 E+308 Type des attributs: chaînes nom longueur CHAR(M) Chaîne de taille fixée à M, où 1<M<256, complétée avec des espaces si nécessaire. CHAR(M) BINARY Idem, mais insensible à la casse lors des tris et recherches. VARCHAR(M) Chaîne de taille variable, de taille maximum M, où 1<M<256, complété avec des espaces si nécessaire. VARCHAR(M) BINARY Idem, mais insensible à la casse lors des tris et recherches. TINYTEXT Longueur maximale de 255 caractères. TEXT Longueur maximale de 65535 caractères. MEDIUMTEXT Longueur maximale de 16777215 caractères. LONGTEXT Longueur maximale de 4294967295 caractères. DECIMAL(M,D) * Simule un nombre flottant de D chiffres après la virgule et de M chiffres au maximum. Chaque chiffre ainsi que la virgule et le signe moins (pas le plus) occupe un caractère. Type des attributs: dates et heures nom description DATE Date au format anglophone AAAA-MM-JJ. DATETIME Date et heure au format anglophone AAAA-MM-JJ HH:MM:SS. TIMESTAMP Affiche la date et l’heure sans séparateur : AAAAMMJJHHMMSS. TIMESTAMP(M) Idem mais M vaut un entier pair entre 2 et 14. Affiche les M premiers caractères de TIMESTAMP. TIME Heure au format HH:MM:SS. YEAR Année au format AAAA. Créer une base de données La création d’une base de donnée utilise la commande CREATE DATABASE selon la syntaxe suivante : CREATE DATABASE nom_base Exemple: CREATE DATABASE mybase Créer une relation La création d’une relation utilise la commande CREATE TABLE selon la syntaxe suivante : CREATE TABLE nom_relation ( nom_attribut TYPE_ATTRIBUT OPTIONS … ) Créer une relation Exemple du carnet d’adresse : CREATE TABLE Personne ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(40), ‘prénom’ VARCHAR(40), adresse TINYTEXT, ‘téléphone’ DECIMAL(10,0)) Notre carnet d’adresse est stocké dans un tableau (appelé Relation) de nom Personne qui comporte les colonnes (dites aussi attributs) suivantes : nom (chaîne de 40 caractères maximum), prénom (idem), adresse (texte de longueur variable mais inférieure à 255 caractères) et téléphone (chaîne de 10 caractères). Chacune des personnes à ajouter au carnet d’adresse occupera une ligne de cette table. Une ligne est dite enregistrement dans le jargon des bases de données. Clé primaire Pour des raisons pratiques, nous souhaitons pouvoir associer à chacun des enregistrements de la relation un identifiant numérique unique qui puise être passé en paramètre à nos scripts PHP. CREATE TABLE Personne ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(40), prénom’ VARCHAR(40), adresse TINYTEXT, ‘téléphone’ DECIMAL(10,0)) ) . Valeur par défaut Pour donner une valeur par défaut à un attribut, on utilise l’option DEFAULT. Lors de l’ajout d’un enregistrement cette valeur sera affectée à l’attribut si aucune valeur n’est donnée. Exemple ‘téléphone’ DECIMAL(10,0) DEFAULT ‘0123456789’ ne peuvent pas avoir de valeur par défaut. Attribut non nul Considérons que l’on souhaite que certains attributs aient obligatoirement une valeur. On utilisera l’option NOT NULL. Dans ce cas, si malgré tout, aucune valeur n’est fournie, la valeur par défaut – si elle est déclarée à la création de la relation – sera automatiquement affectée à cet attribut dans l’enregistrement. Si aucune valeur par défaut n’est déclarée : - la chaîne vide ‘’’’ sera affectée à l’attribut s’il est de type chaîne de caractères - la valeur zéro 0 s’il est de type nombre - la date nulle 0000-00-00 et/ou l’heure nulle 00:00:00 s’il est de type date, heure ou date et heure. Exemple adresse TINYTEXT NOT NULL Au contraire, on utilisera l’option NULL si on autorise l’absence de valeur. Supprimer une relation La commande DROP TABLE prend en paramètre le nom de la table à supprimer. Toutes les données qu’elle contient sont supprimées et sa définition aussi. Syntaxe DROP TABLE relation Exemple DROP TABLE Personnes Modifier une relation La création d’une relation par CREATE TABLE n’en rend pas définitives les spécifications. Il est possible d’en modifier la définition par la suite, à tout moment par la commande ALTER TABLE. Ajouter un attribut Syntaxe ALTER TABLE relation ADD définition [ FIRST | AFTER attribut] Ajoutons l’attribut fax qui est une chaîne représentant un nombre de 10 chiffres: ALTER TABLE Personnes ADD fax DECIMAL(10,0) Nous aurions pu forcer la place où doit apparaître cet attribut. Pour le mettre en tête de la liste des attributs de la relation, il faut ajouter l’option FIRST en fin de commande. Pour le mettre après l’attribut ‘téléphone’, il aurait fallu ajouter AFTER ‘téléphone’. Supprimer un attribut Supprimer un attribut implique la suppression des valeurs qui se trouvent dans la colonne qui correspond à cet attribut. Syntaxe ALTER TABLE relation DROP attribut Exemple ALTER TABLE Personnes DROP prénom Créer une clé primaire La création d’une clé primaire n’est possible qu’en l’absence de clé primaire dans la relation. Syntaxe ALTER TABLE relation ADD PRIMARY KEY (attribut) Exemple ALTER TABLE Personnes ADD PRIMARY KEY (nom) Supprimer une clé primaire Comme une clé primaire est unique, il n’y a aucune ambiguïté lors de la suppression. Syntaxe ALTER TABLE relation DROP PRIMARY KEY Exemple ALTER TABLE Personnes DROP PRIMARY KEY S’il n’y a aucune clé primaire lorsque cette commande est exécutée, aucun message d’erreur ne sera généré, la commande sera simplement ignorée. Ajouter un enregistrement INSERT INTO relation(liste des attributs) VALUES(liste des valeurs) Exemple INSERT INTO Personnes(nom,prénom) VALUES(‘M’,’J’) . Modifier un enregistrement Pour modifier un ou des enregistrement(s) d’une relation, il faut préciser un critère de sélection des enregistrement à modifier (clause WHERE), il faut aussi dire quels sont les attributs dont on va modifier la valeur et quelles sont ces nouvelles valeurs (clause SET). Syntaxe UPDATE relation SET attribut=valeur, … [ WHERE condition ] Exemple UPDATE agenda SET téléphone=’012345678’ WHERE nom=‘Boubakeur’ AND prénom = ‘Mohamed’ Supprimer un enregistrement Syntaxe DELETE FROM relation [ WHERE condition ] Exemple DELETE FROM Personnes WHERE nom=‘M’ AND prénom=‘Malik’ Pour vider une table de tous ces éléments, ne pas mettre de clause WHERE. Cela efface et recrée la table, au lieu de supprimer un à un chacun des tuples de la table (ce qui serait très long). Exemple DELETE FROM Personnes Sélectionner des enregistrements Pour extraire de la base de données des informations, comme la liste des personnes d’un carnet d’adresse qui vivent à Annaba. Syntaxe générale SELECT [ DISTINCT ] attributs FROM relation [ WHERE condition ] [ORDER BY attributs [ ASC | DESC ] ] [ LIMIT [a,] b ] Exemple SELECT nom,prénom FROM Personnes WHERE adresse LIKE ‘Annaba’ Interface de Mysql avec PHP Pour qu’un utilisateur puisse effectuer des requêtes sur une base de données il doit : 1. 2. 3. 4. Se connecter au serveur MySQL Choisir la base de données de travail Effectuer la requête Récupérer et parcourir le résultat de la requête FONCTIONS DE CONNEXION : permet de se connecter au serveur $server en tant qu’utilisateur $user avec le mot de passe $password, retourne l’identifiant de connexion si succès, FALSE sinon mysql_select_db($base[,$id]) : permet de choisir la base $base, retourne TRUE en cas de succès, sinon FALSE mysql_close([$id]) : permet de fermer la connexion $id=mysql_connect($server,$user,$password) Gestion des erreurs mysql_errno([$id]) : retourne le numéro d’erreur de la dernière opération MySQL effectuée sur la connexion courante ou celle d’identifiant $id. mysql_error([$id]) : retourne le message d’erreur de la dernière opération MySQL effectuée sur la connexion courante ou celle d’identifiant $id. Exemple : $conn = mysql_connect('localhost', 'root', ''); if ( mysql_errno($conn)!== 0) { echo "Erreur n°".mysql_errno().": ".mysql_error(); } else { echo "Connexion au serveur est &eacute;tablie !<br> "; EXEMPLE EXEMPLE Exemple 1 : if( $id = mysql_connect(‘’localhost’’,’’user’’,’’’’) ) { if( $id_db = mysql_select_db(‘’gigabase’’) ) { echo ‘’Succès de connexion.’’; /* code du script … */ } else { die(‘’Echec de connexion à la base.’’); } mysql_close($id); } else { die(‘’Echec de connexion au serveur de base de données.’’);} INTERROGATION Pour envoyer une requête à une base de donnée, il existe la fonction : mysql_query($str) Les requêtes les plus couramment utilisées sont : 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 address FROM users WHERE name=\’’$name\’’’’); EXEMPLE1 EXEMPLE2 EXEMPLE3 EXEMPLE4 EXTRACTION DES DONNÉES mysql_num_rows($result) : retourne le nombre de lignes du résultats. 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. $requet = ‘’SELECT * FROM users’’; if($result = mysql_query($requet)) { while($ligne = mysql_fetch_row($result)) { $id = $ligne[0]; $name = $ligne[1]; $address = $ligne[2]; EXEMPLE echo ‘’$id - $name, $address <br />’’; }} else { echo ‘’Erreur de requête de base de données.’’;} EXTRACTION DES DONNÉES mysql_fetch_array($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. $requet = ‘’SELECT * FROM users’’; if($result = mysql_query($requet)) { while($ligne = mysql_fetch_array($result)) { $id = $ligne[‘’id’’]; $name = $ligne[‘‘name’’]; $address = $ligne[‘’address’’]; echo ‘’$id - $name, $address <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. Exemple: gestion d’une librairie Soit la base librairie constituée de trois tables: table livres code_livre INT(10) NOT NULL AUTO_INCREMENT, titre VARCHAR(100) DEFAULT NULL, auteur VARCHAR(100) DEFAULT NULL, genre VARCHAR(10) DEFAULT NULL, date_publication YEAR DEFAULT NULL, prix DEC(10,2) DEFAULT NULL, PRIMARY KEY(code_livre)) Exemple: gestion d’une librairie Soit la base librairie constituée de trois tables: table CLIENTS code_client INT(10) NOT NULL AUTO_INCREMENT, nom VARCHAR(100) DEFAULT NULL, rue VARCHAR(100) DEFAULT NULL, ville VARCHAR(50) DEFAULT NULL, code_postal VARCHAR(5) DEFAULT NULL, telephone VARCHAR(10) DEFAULT NULL, adresse_mail VARCHAR(50) DEFAULT NULL, mot_de_passe VARCHAR(150) DEFAULT NULL, statut CHAR(1) DEFAULT NULL, PRIMARY KEY(code_client)) Exemple: gestion d’une librairie Soit la base librairie constituée de trois tables: table COMMANDE code_client INT(10) NOT NULL AUTO_INCREMENT, CODE_livre VARCHAR(100) DEFAULT NULL, date Date quantité int statut char(1) Exemple: gestion d’une librairie Étapes: Création de la librairie Création des tables livres, clients, commande Insertion de livres et de clients dans les tables livres et clients ce qu’on peut faire sur notre base: - la consultation de la table livre - Ajout de nouveaux livres (réservé seulement à l’administrateur - Commande de livres