PHP et Bases de Données PHP et Bases de Données TABLE DES MATIERES Chapitre 1 ACCES BD .............................................................................................................................4 1.1 Rappel sur les BDR ........................................................................................................................4 1.1.1 Un SGBDR .............................................................................................................................4 1.1.2 Principaux objets et principales actions d'une base de données ..................................................6 1.1.3 PHP et les bases de données ...................................................................................................7 1.1.4 La BD utilisée : son schéma ....................................................................................................8 1.1.5 Script de création des tables ...................................................................................................8 1.1.6 Scripts d'insertion de données .................................................................................................8 1.2 LECTURE D'UNE TABLE MySQL .......................................................................................................9 1.3 Le même avec affichage dynamique des colonnes .......................................................................... 11 1.4 Jointures .................................................................................................................................... 13 1.4.1 Equi-jointure ........................................................................................................................ 13 1.4.2 Jointure externe à gauche ..................................................................................................... 14 1.4.3 Jointure externe à droite ....................................................................................................... 14 1.4.4 Jointure multi-bases ............................................................................................................. 14 1.4.5 Affichage d'une jointure dans une table HTML ........................................................................ 15 Chapitre 2 GESTION DES ERREURS MYSQL ............................................................................................ 19 2.1 Prémisses ................................................................................................................................... 20 2.2 Codage....................................................................................................................................... 20 2.3 Gestion personnaliséé des erreurs ................................................................................................. 22 Chapitre 3 MISES A JOUR DE LA BD ...................................................................................................... 24 3.1 Généralités sur les mises à jour .................................................................................................... 24 3.2 Insertion d'une nouvelle ville (Dynamique et Self) .......................................................................... 25 3.3 Inclusion des fonctions de connexion ............................................................................................ 28 3.4 Suppression d'une ville (Dynamique et Self) ................................................................................... 30 3.5 Gestion des transactions .............................................................................................................. 31 Chapitre 4 LES TABLES ET LES LISTES .................................................................................................. 33 4.1 Afficher une table dans une liste ................................................................................................... 33 4.2 Affichage générique d'une table dans une liste ............................................................................... 34 4.3 Saisie d'un client avec une liste déroulante pour les cp (Version 1) .................................................. 37 4.4 Saisie d'un client avec une liste déroulante pour les cp (Version 2) .................................................. 39 Chapitre 5 AUTHENTIFICATION ............................................................................................................ 43 5.1 L'inscription ................................................................................................................................ 44 5.2 Boîte de connexion ...................................................................................................................... 45 5.3 Déconnexion ............................................................................................................................... 46 5.4 Restriction de page ...................................................................................................................... 47 Chapitre 6 GESTION DES DATES ........................................................................................................... 48 6.1 Fonctions MySQL ......................................................................................................................... 48 6.2 Fonctions PHP ............................................................................................................................. 48 6.3 Scripts d'insertion ........................................................................................................................ 49 6.3.1 Script d'insertion de dates avec des fonctions MySQL .............................................................. 49 6.3.2 Script d'insertion de dates avec des fonctions PHP .................................................................. 50 6.4 Scripts de visualisation avec des fonctions date de MySQL .............................................................. 51 Chapitre 7 TRAITEMENTS GENERIQUES ................................................................................................. 52 7.1 Affichage d'une table sur plusieurs pages ...................................................................................... 52 7.1.1 Création de la table .............................................................................................................. 52 7.1.2 Visualisation de la table sur plusieurs pages ........................................................................... 53 7.2 Visualisation générique ................................................................................................................ 55 7.3 Insertion générique (La metabase) ............................................................................................... 58 Chapitre 8 MULTICRITERES .................................................................................................................. 64 8.1 Objectif : faire des recherches multi-critères .................................................................................. 64 8.1.1 Ecrans................................................................................................................................. 65 8.1.2 Script .................................................................................................................................. 66 Chapitre 9 PROCEDURES ET FONCTIONS STOCKEES MySQL ................................................................... 68 9.1 Introduction ................................................................................................................................ 68 © Pascal Buguet Imprimé le 15 juin 2017 Page 2 PHP et Bases de Données 9.1.1 Principes ............................................................................................................................. 68 9.1.2 Syntaxe de base .................................................................................................................. 68 9.1.3 La gestion des jeux de caractères .......................................................................................... 69 9.2 Exemples de procédures stockées MYSQL ...................................................................................... 71 9.2.1 Insertion d'une ville .............................................................................................................. 71 9.2.2 Suppression d'une ville ......................................................................................................... 73 9.3 Procédure stockée qui renvoie un SELECT ..................................................................................... 74 9.4 Procédures stockées avec un argument OUT .................................................................................. 75 9.5 Fonctions stockées ...................................................................................................................... 76 9.5.1 Fonction stockée technique : une addition de 2 nombres ......................................................... 77 9.5.2 Fonction stockée en relation avec une table ............................................................................ 79 9.6 Une procédure qui gère un curseur ............................................................................................... 80 Chapitre 10 ANNEXES ............................................................................................................................ 81 10.1 BD et CharSet ............................................................................................................................. 81 10.1.1 Les tables ............................................................................................................................ 82 10.1.2 Le SELECT ........................................................................................................................... 83 10.1.3 Le DELETE........................................................................................................................... 84 10.1.4 Les INSERTS ....................................................................................................................... 85 10.1.5 Insertion à partir d'un fichier ................................................................................................. 87 10.2 Tableau des fonctions mysql_ ....................................................................................................... 88 10.3 Jointures multi-bases ................................................................................................................... 89 10.4 mysql_insert_id et transactions ..................................................................................................... 90 10.5 Un petit moteur de recheche ........................................................................................................ 93 10.6 Un forum .................................................................................................................................... 95 10.7 BD 2 CSV .................................................................................................................................... 96 10.8 BD – Création des tables .............................................................................................................. 97 10.9 BD – Ajout des enregistrements .................................................................................................. 100 © Pascal Buguet Imprimé le 15 juin 2017 Page 3 PHP et Bases de Données CHAPITRE 1 ACCES BD CF AUSSI LE SUPPORT php_bd_supplements.doc 1.1 1.1.1 RAPPEL SUR LES BDR Un SGBDR Un SGBDR est un Système de Gestion de Bases de Données Relationnelles. Un système de Gestion est un ensemble de machines et de logiciels de gestion d'objets (Textes, feuilles de calcul, Images, Tables,…) Une base de données est un ensemble de tables reliées entre elles pour représenter le système d'informations d'une organisation. Une table est un rectangle composé de colonnes et de lignes permettant de stocker des données structurées homogènes représentant un "objet" du monde réel. Les colonnes sont les champs. Les lignes des enregistrements. Les enregistrements doivent être uniques. L'unicité est garantie par la clé primaire de la table. Une clé primaire est composée d'un champ ou de plusieurs. Une clé étrangère est un champ d'une table qui correspondant à une clé primaire dans une autre table. Elle permet de relier les tables entre elles et de garantir l'intégrité référentielle. Avec MySQL le moteur de tables MyISAM ne permet pas de créer des clés étrangères. Le moteur de tables InnoDB permet de créer des clés étrangères. De plus le moteur InnoDB gère les transactions. © Pascal Buguet Imprimé le 15 juin 2017 Page 4 PHP et Bases de Données SQL SQL (Structured Query Language) est un langage prédicatif qui permet de définir des objets, de manipuler les données d'une base de données. C'est l'implémentation de l'algèbre relationnelle. Quatre actions fondamentales (CRUD : Create, Read, Update, Delete) sont exercées sur les données : L'insertion, L'extraction, La modification, La suppression. Quatre instructions du LMD (Langage de Manipulation de Données) correspondent à ces actions : INSERT, SELECT, UPDATE, DELETE. Syntaxes de base de ces instructions. (Se référer au cours SQL). SELECT col1, col2,… FROM nom_de_table [ WHERE col1 = v1 …] INSERT INTO nom_de_table(col1, col2,…) VALUES (v1, v2, …) DELETE FROM nom_de_table [WHERE col1=v1 …] UPDATE nom_de_table SET col2=v2 [, col3=v3] [WHERE col1=v1] A côté du LMD il existe le LDD (Langage de Définition de Données) qui permet de gérer des objets de données. Ce langage permet de créer des objets (Table, Index, User, …). Il possède trois instructions : CREATE (pour créer) , ALTER (pour modifier) et DROP (pour supprimer). Syntaxes de base : CREATE TABLE villes(cp char(5), nom_ville varchar(50)); ALTER TABLE villes ADD PRIMARY KEY ('cp'); DROP TABLE villes; Le LCD (Langage de Contrôle de Données) permet de contrôler des accès aux objets. Il comprend au moins deux instructions pour donner des droits (GRANT) et pour les retirer (REVOKE). Syntaxes de base : GRANT SELECT ON villes TO invite; REVOKE SELECT ON villes FROM invite; © Pascal Buguet Imprimé le 15 juin 2017 Page 5 PHP et Bases de Données 1.1.2 Principaux objets et principales actions d'une base de données Les principaux objets sont les suivants : Serveur, Base de Données, Tables, Requêtes, Curseur, Champs, Procédures stockées et fonctions stockées, Triggers. Les principales actions sont des suivantes : Connexion au serveur, Déconnexion du serveur, Sélection de la Base de Données, Ouverture d'un curseur, Parcours d'un curseur, Fermeture d'un curseur, Exécution d'une requête de mise à jour, Exécution d'une procédure stockée. NB : il est possible aussi via PHP de réaliser des actions de DDL (Data Definition Language) telles que la création et/ou la suppression de tables, d'index, de users, … mais l'objet de ce support est principalement de faire du LMD (Data Manipulation Language). © Pascal Buguet Imprimé le 15 juin 2017 Page 6 PHP et Bases de Données 1.1.3 PHP et les bases de données A la différence de nombreux autres produits servant de frontal à une base de données les rédacteurs de PHP ont choisi d'avoir une syntaxe (et donc une bibliothèque) propre à chaque fournisseur. Ce choix optimise l'accès mais pénalise la maintenance (en cas de changement de cible). Il existe un accès standard avec ODBC ainsi qu'un accès via les couches d'abstraction des bibliothèques PEAR ou PDO (cf cours perfectionnement). PHP possède des bibliothèques pour MYSQL, ODBC, Oracle, DB2, Sybase, POSTGRES, SQL-SERVER,…. Dans ce support nous utiliserons la bibliothèque MYSQL. © Pascal Buguet Imprimé le 15 juin 2017 Page 7 PHP et Bases de Données 1.1.4 La BD utilisée : son schéma 1.1.5 Script de création des tables Cf annexes 1.1.6 Scripts d'insertion de données Cf annexes © Pascal Buguet Imprimé le 15 juin 2017 Page 8 PHP et Bases de Données 1.2 LECTURE D'UNE TABLE MYSQL Objectif Lire et afficher quelques champs de la table Villes. Ecran Instructions utilisées Syntaxe Lien = mysql_connect(hote , ut , mot de passe) Fonctionnalité Se connecter à un serveur de bases de données. Renvoie l'identifiant de connexion ou False en cas d'erreur. booléen = mysql_select_db(nom de la BD[ , lien]) Sélectionner une base de données. Renvoie False en cas d'erreur. Curseur = mysql_query(requete[ , lien]) Exécuter une requête qui renvoie un curseur ou False en cas d'erreur. Enr = mysql_fetch_row(curseur) Récupérer une ligne, un enregistrement sous forme de tableau ordinal. Renvoie False s'il n'y a plus de lignes. Booléen = mysql_free_result(curseur) Fermer un curseur Booléen = mysql_close([lien]) Fermer la connexion Int = mysql_num_rows([lien]) Array = mysql_fetch_array ( curseur [,type de résultat] ) Array = mysql_fetch_assoc (curseur) © Pascal Buguet Renvoie le nombre d'enregistrements du curseur. Récupérer une ligne, un enregistrement sous forme de tableau ordinal ou de tableau associatif. Renvoie False s'il n'y a plus de lignes. Les constantes sont MYSQL_ASSOC, MYSQL_NUM, et la valeur par défaut est MYSQL_BOTH. Récupérer une ligne, un enregistrement sous forme de tableau associatif. Renvoie False s'il n'y a plus de lignes. Imprimé le 15 juin 2017 Page 9 PHP et Bases de Données Script <?php header("Content-Type: text/html; charset=UTF-8"); // --- Connexion au serveur MySQL $lien = mysql_connect("localhost","root","") ; // --- Sélection de la base mysql mysql_select_db("cours", $lien) ; // --- Exécuter une requête $lsSql = "SELECT * FROM villes" ; $curseur = mysql_query($lsSql, $lien) ; // --- Boucle de balayage while ($enr = mysql_fetch_row($curseur)) { print("$enr[0] – $enr[1] <br />") ; } // --- Fermeture du curseur mysql_free_result($curseur) ; // --- La déconnexion mysql_close($lien) ; ?> Commentaires Les principales étapes du script Connexion au serveur (1), Connexion à la base de données (2), Préparation et exécution de la requête (3), Boucle sur les enregistrements du curseur et extraction des enregistrements (4), Affichage des valeurs des champs (5), Fermeture du curseur (6), Déconnexion de la base (7). 75011-Paris 11$enr 75012-Paris 12 5 $enr 4 $curseur 3 MySQL Localhost Cinémas 2 1 $lien PHP © Pascal Buguet Imprimé le 15 juin 2017 Page 10 PHP et Bases de Données 1.3 LE MEME AVEC AFFICHAGE DYNAMIQUE DES COLONNES Objectif Démarche Le script est quasiment le même. On boucle sur les colonnes grâce à une boucle ForEach sur l'enregistrement à l'intérieur de la boucle sur le curseur. Script <?php header("Content-Type: text/html; charset=UTF-8"); mysql_connect("localhost","root",""); mysql_select_db("cours"); $curseur = mysql_query("SELECT * FROM villes"); while ($enr = mysql_fetch_row($curseur)) { foreach($enr as $champ) echo $champ, " "; echo "<br />"; } ?> Exercice Affichez les données de la table Villes avec un lien vers le site de chaque ville et la photo de la ville. © Pascal Buguet Imprimé le 15 juin 2017 Page 11 PHP et Bases de Données Corrigé <?php $lien = mysql_connect("localhost", "root", ""); mysql_select_db("cours", $lien); $lsSql = "SELECT * FROM villes"; $curseur = mysql_query($lsSql, $lien); $lsTableHTML = "<table border='1'>"; while($enr = mysql_fetch_row($curseur)) { $lsTableHTML .= "<tr>"; $lsTableHTML .= "<td>$enr[0]</td>"; $lsTableHTML .= "<td>$enr[1]</td>"; $lsTableHTML .= "<td><a href='http://$enr[2]'>$enr[2]</a></td>"; $lsTableHTML .= "<td><img src='../../images/divers/$enr[3]' alt='Photo manquante' width='100' /></td>"; $lsTableHTML .= "</tr>"; } $lsTableHTML .= "</table>"; echo $lsTableHTML; ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 12 PHP et Bases de Données 1.4 JOINTURES 1.4.1 Equi-jointure Une jointure est la concaténation des champs d'une table avec ceux d'une autre table et la concaténation de chaque enregistrement de la table 1 avec tous ceux de la table 2 quand la condition de jointure est remplie. Cette définition s'étend à n tables. Exemple Nous voulons afficher les noms des clients et le nom de la ville où ils habitent. Les champs se trouvent dans 2 tables : la table Clients et la table Villes. Le résultat 1-Buguet-Paris 11 2-Serra-Paris 19 3-Chaubet-Paris 12 L'ordre Select $lsSql = "SELECT c.id_client, c.nom, v.nom_ville FROM clients c, villes v WHERE c.cp = v.cp" ; ou bien selon la syntaxe ANSI $lsSql = "SELECT c.id_client, c.nom, v.nom_ville FROM clients c INNER JOIN villes v ON c.cp = v.cp" ; Buveurs d’évian : "SELECT clients.nom, produits.designation FROM ((clients INNER JOIN cdes ON clients.id_client = cdes.id_client) INNER JOIN ligcdes ON cdes.id_cde = ligcdes.id_cde) INNER JOIN produits ON ligcdes.id_produit = produits.id_produit WHERE (((produits.designation)='evian'))" © Pascal Buguet Imprimé le 15 juin 2017 Page 13 PHP et Bases de Données 1.4.2 Jointure externe à gauche Une jointure externe est une jointure qui permet d'obtenir tous les enregistrements d'une table joints à ceux d'une autre table même s'ils n'ont pas de correspondants dans l'autre table. L'objectif est d'obtenir tous les enregistrements de la table Villes et les correspondants dans la table Clients. Et aussi ceux qui n'ont pas de correspondants. Le résultat Paris Paris Paris Paris 11-75011-Buguet-75011 12-75012-Chaubet-75012 13-75013-19-75019-Serra-75019 L'ordre Select $lsSql = "SELECT v.nom_ville, v.cp, c.nom, c.cp FROM villes v LEFT JOIN clients c ON c.cp = v.cp" ; 1.4.3 Jointure externe à droite Même résultat que l'équi-jointure. La jointure à droite dans ce contexte n'a pas de sens puisque le CP est obligatoire dans la table Clients comme il se doit étant donnée la contrainte d'intégrité référentielle. L'ordre Select $lsSql = "SELECT v.nom_ville, v.cp, c.nom, c.cp FROM villes v RIGHT JOIN clients c ON c.cp = v.cp" ; 1.4.4 Jointure multi-bases Cf annexes © Pascal Buguet Imprimé le 15 juin 2017 Page 14 PHP et Bases de Données 1.4.5 Affichage d'une jointure dans une table HTML Objectif Afficher le résultat d'une jointure dans une table HTML avec les en-têtes de colonne. Les balises <table>, <caption>, <thead>, <th>, <tbody>, <tr> et <td> sont utilisées. Les fonctions suivantes sont utilisées. Fonction $curseur = mysql_query($requete, [ $lien]) $nb = mysql_num_fields($curseur) $nom_champ = mysql_field_name($curseur, $i) © Pascal Buguet Description Ouvre un curseur Récupère le nombre de champs d'un curseur Récupère le nom du champ courant d'un curseur Imprimé le 15 juin 2017 Page 15 PHP et Bases de Données Script <?php $lien = mysql_connect("localhost","root","") ; mysql_select_db("cours",$lien) ; $lsSql = "SELECT c.nom, c.prenom, c.cp, v.nom_ville FROM clients c, villes v WHERE c.cp = v.cp" ; $curseur = mysql_query($lsSql, $lien) ; $lsTableHTML = "<table border='1'>"; $lsTableHTML .= "<caption>Jointure Clients x Villes</caption>"; $lsTableHTML .= "<thead>"; $lsTableHTML .= "<tr>"; for($i=0; $i < mysql_num_fields($curseur); $i++) $lsTableHTML .= "<th>" . mysql_field_name($curseur, $i) . "</th>"; $lsTableHTML .= "</tr>"; $lsTableHTML .= "</thead>"; $lsTableHTML .= "<tbody>"; while($enr = mysql_fetch_row($curseur)) { $lsTableHTML .= "<tr>"; foreach($enr as $champ) { $lsTableHTML .= "<td>$champ</td>"; } $lsTableHTML .= "</tr>"; } $lsTableHTML .= "</tbody>"; $lsTableHTML .= "</table>"; echo $lsTableHTML; ?> Exercice Utilisez d’autres balises HTML … au choix ! © Pascal Buguet Imprimé le 15 juin 2017 Page 16 PHP et Bases de Données Corrigé ul/li <style type="text/css"> *{margin:0; padding:0px;} body{margin-left:10px;} ul{list-style:none; } li{display:block; width:200px; border:1px solid black; float:left; padding:2px;} p{clear:both;} </style> <?php $lien = mysql_connect("localhost","root","") ; mysql_select_db("cours",$lien) ; $lsSql = "SELECT c.nom, c.prenom, c.cp, v.nom_ville FROM clients c, villes v WHERE c.cp = v.cp" ; $curseur = mysql_query($lsSql, $lien) ; $lsTable = "<div>"; $lsTable .= "<h3>Jointure Clients x Villes</h3>"; $lsTable .= "<p>"; $lsTable .= "<ul>"; for($i=0; $i<mysql_num_fields($curseur); $i++) { $lsTable .= "<li>" . mysql_field_name($curseur, $i) . "</li>"; } $lsTable .= "<ul>"; $lsTable .= "</p>"; while($enr = mysql_fetch_row($curseur)) { $lsTable .= "<p>"; $lsTable .= "<ul>"; foreach($enr as $champ) { $lsTable .= "<li>$champ</li>"; } $lsTable .= "</ul>"; $lsTable .= "</p>"; } $lsTable .= "</div>"; echo $lsTable; ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 17 PHP et Bases de Données Corrigé div/span <style type="text/css"> *{margin:0; padding:0px;} body{margin-left:10px;} span{display:block; width:200px; border:1px solid black; float:left; padding:2px;} p{clear:both;} </style> <?php $lien = mysql_connect("localhost","root","") ; mysql_select_db("cours",$lien) ; $lsSql = "SELECT c.nom, c.prenom, c.cp, v.nom_ville FROM clients c, villes v WHERE c.cp = v.cp" ; $curseur = mysql_query($lsSql, $lien) ; $lsTable = "<div>"; $lsTable .= "<h3>Jointure Clients x Villes</h3>"; $lsTable .= "<p>"; for($i=0; $i<mysql_num_fields($curseur); $i++) { $lsTable .= "<span>" . mysql_field_name($curseur, $i) . "</span>"; } $lsTable .= "</p>"; while($enr = mysql_fetch_row($curseur)) { $lsTable .= "<p>"; foreach($enr as $champ) { $lsTable .= "<span>$champ</span>"; } $lsTable .= "</p>"; } $lsTable .= "</div>"; echo $lsTable; ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 18 PHP et Bases de Données CHAPITRE 2 GESTION DES ERREURS MYSQL © Pascal Buguet Imprimé le 15 juin 2017 Page 19 PHP et Bases de Données 2.1 PREMISSES Les erreurs de script s'affichent par défaut à l'exécution (Cela peut être modifié via une directive du php.ini). Mais les erreurs SQL ne s'affichent pas. Il faut donc les traiter de façon particulière. 2.2 CODAGE Objectif Gérer les erreurs SQL : soit une erreur de syntaxe (Nom de table erroné, nom de colonne erroné). Ecrans Instructions utilisées Syntaxe Var = mysql_errno( [lien] ) Var = mysql_error( [lien] ) Fonctionnalité Récupère le numéro d'erreur Récupère le message d'erreur Note : pour afficher les messages en français il faut modifier my.ini et ajouter dans la section [mysqld] la directive suivante language=french ou démarrer le serveur avec l'option – language=french. [mysqld] port=3306 language=french © Pascal Buguet Imprimé le 15 juin 2017 Page 20 PHP et Bases de Données Script <?php $lien = mysql_connect("localhost","root","") ; mysql_select_db("cours",$lien) ; $lsSql $curseur = "SELECT * FROM ville" ; = mysql_query($lsSql, $lien) ; if(!$curseur) print("Erreur " . mysql_errno($lien) . " : " . mysql_error($lien)); mysql_close($lien) ; ?> Commentaires L'ordre SQL comporte une erreur sur le nom de la table. L'instruction mysql_errno renvoie 0 s'il n'y a pas d'erreur et un code d’erreur si une erreur survient. Un branchement est effectué en cas d'erreur d’exécution de la fonction mysql_query() qui renvoie false si elle échoue. © Pascal Buguet Imprimé le 15 juin 2017 Page 21 PHP et Bases de Données 2.3 GESTION PERSONNALISEE DES ERREURS Principes et démarche La personnalisation tient à un fichier personnalisé des messages d'erreur. Il a cette structure-ci : codeErreur, Message. Lorsque qu'une erreur surviendra on ira lire le fichier d'erreurs pour extraire le massage adéquat. erreurs_sql.txt 1062,L'enregistrement existe déjà 1146, La table n'existe pas 1054, La colonne n'existe pas Interfaces possibles : © Pascal Buguet Imprimé le 15 juin 2017 Page 22 PHP et Bases de Données Script <?php // --- Ajout de la ville dans la table Villes if(isset($_GET["cb_valider_ville"])) { $cp = $_GET["cp"] ; $nom_ville = $_GET["nom_ville"] ; $photo = $_GET["photo"] ; $site = $_GET["site"] ; $resultat = mysql_query("INSERT INTO villes(cp, nom_ville, photo, site) VALUES('$cp','$nom_ville','$photo','$site')"); if(!$resultat) { $tF = file("erreurs_sql.txt"); for($i=0; $i<count($tF); $i++) { $tEnr = explode(",", $tF[$i]); if(mysql_errno() == $tEnr[0]) echo $tEnr[1]; } } } ?> <form action="" method="get"> <label>CP </label><input name="cp" type="text" /><br /> <label>Nom ville </label><input name="nom_ville" type="text" /><br /> <label>Photo </label><input name="photo" type="text" /><br /> <label>Site </label><input name="site" type="text" /><br /> <input type="submit" value="Valider ville" name="cb_valider_ville" /> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 23 PHP et Bases de Données CHAPITRE 3 MISES A JOUR DE LA BD 3.1 GENERALITES SUR LES MISES A JOUR Comme précédemment la fonction mysql_query() sera utilisée pour effectuer une mise à jour. Alors que la fonction mysql_query() renvoie un curseur si un ordre SELECT est passé en argument, elle renvoie True ou False dans le cas où l'on passe un ordre SQL de mise à jour. mysql_affected_rows() permet de connaître le nombre d'enregistrements mis à jour (de 0 à n). Syntaxe booléen = mysql_query(requete [, lien] ) int = mysql_affected_rows( [lien] ) Fonctionnalité Exécuter une requête qui renvoie un True en cas de succès ou False en cas d'échec. Récupérer le nombre d'enregistrements mis à jour. Si votre navigateur envoie des données UTF-8 utilisez mysql_query("SET NAMES UTF8"); pour être sûr que les données dans la table soient avec les caractères accentués. © Pascal Buguet Imprimé le 15 juin 2017 Page 24 PHP et Bases de Données 3.2 INSERTION D'UNE NOUVELLE VILLE (DYNAMIQUE ET SELF) Principe Une page PHP pour saisir les valeurs dans des champs de formulaire et pour insérer les saisies dans la BD. Ecrans En cas de doublon … © Pascal Buguet Imprimé le 15 juin 2017 Page 25 PHP et Bases de Données Scripts villes_insert.php <?php if(isSet($_REQUEST["tb_cp"])) { $cp = $_REQUEST["tb_cp"] ; $ville = addslashes($_REQUEST["tb_nom_ville"]) ; mysql_connect("localhost","root","") or die ("Connexion impossible<br />") ; mysql_select_db("cours") or die("S&eacute;lection de base en &eacute;chec<br />") ; // --- Construction de l'ordre SQL $lsInsert = "INSERT INTO villes(cp, nom_ville) VALUES('$cp', '$ville')" ; // --- Insertion mysql_query("SET NAMES UTF8"); $resultat = mysql_query($lsInsert) ; if($resultat) print ("<strong>Nouvel enregistrement ins&eacute;r&eacute;</strong><br />") ; else print ("<br /><strong>Erreur</strong> " . mysql_errno() . " : " . mysql_error() . "<br />") ; mysql_close() ; } ?> <!-- Ce devrait être du POST --> <form action="" method="get"> <label>Cp : </label><input type="text" name="tb_cp" value="75021" /> <label>Ville : </label><input type="text" name="tb_nom_ville" value="Paris 21" /> <input type="submit" /> </form> Si l'ajout portait sur une table avec un id en auto_increment il serait possible de récupérer la dernière valeur créée grâce à l'ordre Insert avec cette fonction : $li_id_commande = mysql_insert_id([$lien]) ; Cf exemple en Annexes. Exercice : Insertion dans la table Clients et récupérez le nouvel ID. © Pascal Buguet Imprimé le 15 juin 2017 Page 26 PHP et Bases de Données Corrigé <?php if(isSet($_POST["tb_cp"])) { $cp = $_POST["tb_cp"] ; $nom = addslashes($_POST["tb_nom"]) ; $prenom = addslashes($_POST["tb_prenom"]) ; mysql_connect("localhost","root","") or die ("Connexion impossible<br />") ; mysql_select_db("cours") or die("S&eacute;lection de base en &eacute;chec<br />") ; // --- Construction de l'ordre SQL $lsInsert = "INSERT INTO clients(nom, prenom, cp) VALUES('$nom','$prenom','$cp')" ; // --- Insertion mysql_query("SET NAMES UTF8"); $resultat = mysql_query($lsInsert) ; if($resultat) { print ("<strong>Nouvel enregistrement ins&eacute;r&eacute;</strong><br />") ; print ("Le nouveau code client est : " . mysql_insert_id() . "<br />") ; } else print ("<br /><strong>Erreur</strong> " . mysql_errno() . " : " . mysql_error() . "<br />") ; mysql_close() ; } ?> <form action="" method="post"> <label>Pr&eacute;nom : </label><input type="text" name="tb_prenom" value="Julia" /> <label>Nom : </label><input type="text" name="tb_nom" value="Roberts" /> <label>Cp : </label><input type="text" name="tb_cp" value="75011" /> <input type="submit" /> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 27 PHP et Bases de Données 3.3 INCLUSION DES FONCTIONS DE CONNEXION Scripts Première version coursBdConnexion.inc.php <?php // ---------------------------------------function seConnecter() // ---------------------------------------{ // --- Ici les paramètres pour la connexion $serveur = "localhost"; $utilisateur = "root"; $passe = ""; $base = "cours"; // --- Connexion au serveur $lien = mysql_connect($serveur , $utilisateur , $passe); if(!$lien) die("<br />Connexion impossible"); // --- Sélection de la base mysql $selectBase = mysql_select_db($base, $lien); if(!$selectBase) die("<br />Sélection de base en échec"); return $lien; } // ---------------------------------------function seDeconnecter($lien) // ---------------------------------------{ mysql_close($lien); } ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 28 PHP et Bases de Données Deuxième version Les paramètres sont dans un autre fichier à inclure. Ainsi la mise à jour est plus souple. Le fichier est nommé : connexionParametres.inc.php <?php $serveur = "127.0.0.1"; $utilisateur = "root"; $passe = ""; $base = "cours"; ?> Le script de la fonction de connexion du fichier coursBdConnexion.inc.php devient : // ---------------------------------------function seConnecter() // ---------------------------------------{ require_once("connexionParametres.inc.php"); // --- Connexion au serveur $lien = mysql_connect($serveur, $utilisateur, $passe); if (!$lien) die ("<br />Connexion impossible"); $selectBase = mysql_select_db($base,$lien); if (!$selectBase) die ("<br />Sélection de base en échec"); return $lien; } Troisième version avec saisie des paramètres dans un formulaire. // ---------------------------------------function seConnecterParam($serveur, $base, $utilisateur, $passe) // ---------------------------------------{ // --- Connexion au serveur $lien = mysql_connect($serveur, $utilisateur, $passe); if (!$lien) die ("<br />Connexion impossible"; // --- Sélection de la base $selectBase = mysql_select_db($base, $lien); if(!$selectBase) die ("<br />Sélection de base en échec"); return $lien; } © Pascal Buguet Imprimé le 15 juin 2017 Page 29 PHP et Bases de Données 3.4 SUPPRESSION D'UNE VILLE (DYNAMIQUE ET SELF) Objectif et principe. Les mêmes que précédemment. Ecrans Script <?php function suppressionVille($asCp) { require_once("coursBdConnexion.inc.php"); $lien = seConnecter(); // --- Construction de l'ordre SQL et suppression $lsSql = "DELETE FROM villes WHERE cp = '$asCp'"; $resultat = mysql_query($lsSql, $lien); if ($resultat) print("<strong>Enregistrement $asCp supprim&eacute;</strong><br />"); else print("Erreur : " . mysql_error($lien) . "<br />"); seDeconnecter($lien); } if(isSet($_GET["tb_cp"])) suppressionVille($_GET["tb_cp"]); ?> <form action="<?php print($_SERVER["PHP_SELF"]); ?>" method="get"> <label>CP ? </label><input type="text" name="tb_cp" size="5" value="75021" /> <input type="submit" /> </form> Exercice : Affinez le message … © Pascal Buguet Imprimé le 15 juin 2017 Page 30 PHP et Bases de Données 3.5 GESTION DES TRANSACTIONS La gestion des transactions permet de valider ou d'invalider une ou plusieurs de mises à jour. Seules les tables InnoDB ou BDB gère les transactions. Par défaut le Commitment est à True ie chaque instruction de mise à jour est validée définitivement dans la base. Pour gérer les transactions il faut exécuter la commande BEGIN. Pour valider une transaction il faut exécuter la commande COMMIT. Pour invalider une transaction il faut exécuter la commande ROLLBACK. Exemple : <?php // --- La table doit être InnoDB ou BDB. $lien = mysql_pconnect("localhost","root",""); mysql_select_db("cours", $lien); $r = mysql_query("BEGIN", $lien); if($r) echo "<br />BEGIN OK"; else echo "<br />BEGIN KO"; $sql = "INSERT INTO villes(cp, nom_ville) VALUES('44000','Nantes')"; $r = mysql_query($sql, $lien); if($r) echo "<br />INSERT OK"; else echo "<br />INSERT KO"; $r = mysql_query("ROLLBACK", $lien); if($r) echo "<br />ROLLBACK OK"; else echo "<br />ROLLBACK KO"; // $r = mysql_query("COMMIT", $lien); // if($r) echo "<br />COMMIT OK"; else echo "<br />COMMIT KO"; ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 31 PHP et Bases de Données Script comparatif MyISAM et InnoDB + Commit. <?php // --- insert_1000.php require_once("coursBdConnexion.inc.php"); $lien = seConnecter(); $table = "table_isam"; $table = "table_innodb"; // --- Suppression de la table $sql = "DROP TABLE IF EXISTS $table"; $r = mysql_query($sql); // --- Création de la table if($table == "table_isam") { $sql = "CREATE TABLE $table( id int(11) default NULL auto_increment, nom char(50) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM"; $r = mysql_query($sql); } if($table == "table_innodb") { $sql = "CREATE TABLE $table( id int(11) default NULL auto_increment, nom char(50) default NULL, PRIMARY KEY (id) ) TYPE=innoDB"; $r = mysql_query($sql); } // // // // // $r ----------= INITIALISE UNE TRANSACTION ... L'AUTOCOMITMENT EST DESACTIVE RESULTATS MyISAM : 0.31 secondes innoDB, transaction désactivéee : 0.31 secondes innoDB, transaction activée : 29.07 secondes mysql_query("BEGIN"); $debut = microtime(true); for($i=1; $i<=1000; $i++) { $sql = "INSERT INTO $table(nom) VALUES('Tintin$i')"; $r = mysql_query($sql); } $r = mysql_query("COMMIT"); $fin = microtime(true); $duree = ($fin - $debut); printf("%s %03.2f %s","Insertions en ", $duree, " secondes"); ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 32 PHP et Bases de Données CHAPITRE 4 LES TABLES ET LES LISTES 4.1 AFFICHER UNE TABLE DANS UNE LISTE Objectif : Afficher le contenu d’une table BD dans une liste Après s'être connecté à la BD, un curseur est ouvert et le contenu de ce dernier est "inséré" dans une liste déroulante. La propriété size de la balise select détermine la hauteur et le type de la liste. Size='1' produit une liste déroulante et size='3' une liste avec 3 lignes. La récupération de la valeur se fait via le nom (Name) de la liste. Ecran Script <form action="" method="get"> <select name="lb_villes" size="3"> <?php require_once("coursBdConnexion.inc.php"); $lien = seConnecter(); $lsSelect = "SELECT cp, nom_ville FROM villes" ; $curseur = mysql_query($lsSelect, $lien) ; while($enr = mysql_fetch_row($curseur)) { print("\t<option value='$enr[0]'>$enr[1]</option>") ; } ?> </select> <br /><input type="submit" /> </form> Exercice : la liste des clients (Nom et id_client). © Pascal Buguet Imprimé le 15 juin 2017 Page 33 PHP et Bases de Données 4.2 AFFICHAGE GENERIQUE D'UNE TABLE DANS UNE LISTE Objectif : Afficher le contenu d’une table dans une liste Après s'être connecté à la BD, un curseur est ouvert et via une fonction le contenu de ce dernier est "inséré" dans une liste. La fonction possède cinq arguments : le nom de la liste, le nom de la table, la colonne à récupérer, la colonne à afficher, le lien vers la BD. Script <?php function creerListe($asNomListe, $asTable, $asColValue, $asColAffiche, $lien) { // --- Construction de l'ordre SQL $lsSelect = "SELECT $asColValue, $asColAffiche FROM $asTable ORDER BY $asColAffiche"; $curseur = mysql_query($lsSelect, $lien); $lsSelectHTML = "<select name='$asNomListe' size='3' >\n"; $lsSelectHTML .= "\t<option value=''>&lt;Choisissez&gt;</option>\n"; while ($enr = mysql_fetch_row($curseur)) { $lsSelectHTML .= "\t<option value='$enr[0]'>$enr[1]</option>\n"; } $lsSelectHTML .= "</select>\n"; return $lsSelectHTML; } ?> <?php $lien = mysql_connect("localhost","root",""); mysql_select_db("cours",$lien); // --- Appel de la fonction de création de liste // --- creerListe(nomDeLaListe, nomDeLaTable, colonneValue, colonneAffichage, connexion) echo creerListe("lb_villes", "villes", "cp", "nom_ville", $lien) ; echo creerListe("lb_produits", "produits", "id_produit", "designation", $lien); ?> Créez une bibliothèque nommée outils.inc.php et ajoutez-y cette fonction. Utilisez ensuite cette bibliothèque. Profitez-en pour variabiliser un peu plus… © Pascal Buguet Imprimé le 15 juin 2017 Page 34 PHP et Bases de Données Corrigé <form action="" method="get"> <?php require_once("coursBdConnexion.inc.php"); require_once("outils.inc.php"); $lien = seConnecter(); // function creerListe($asNomListe, $aiTaille, $multiple, $asTable, $asColValue, $asColAffiche, $lien) $ancienneValeur, echo creerListe("lb_villes", 1, 0, "", "villes", "cp", "nom_ville", $lien) ; echo creerListe("lb_produits", 3, 0, "Evian", "produits", "id_produit", "designation", $lien); echo creerListe("lb_clients", 3, 1, "Buguet", "clients", "id_client", "nom", $lien); ?> <input type="submit" /> </form> <?php function creerListe($asNomListe, $aiTaille, $abMultiple, $asAncienneValeur, $asTable, $asColValue, $asColAffiche, $lien) { // --- Construction de l'ordre SQL $lsSelect = "SELECT $asColValue, $asColAffiche FROM $asTable ORDER BY $asColAffiche"; $curseur = mysql_query($lsSelect, $lien); if($abMultiple) $lsSelectHTML = "<select name='$asNomListe' id='$asNomListe' size='$aiTaille' multiple='multiple' >\n"; if(!$abMultiple) $lsSelectHTML = "<select name='$asNomListe' id='$asNomListe' size='$aiTaille' >\n"; $lsSelectHTML .= "\t<option value=''>&lt;Choisissez&gt;</option>\n"; while ($enr = mysql_fetch_row($curseur)) { if($enr[1] == $asAncienneValeur) © Pascal Buguet Imprimé le 15 juin 2017 Page 35 PHP et Bases de Données $lsSelectHTML .= "\t<option value='$enr[0]' selected='selected'>$enr[1]</option>\n"; else $lsSelectHTML .= "\t<option value='$enr[0]'>$enr[1]</option>\n"; } $lsSelectHTML .= "</select>\n"; return $lsSelectHTML; } ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 36 PHP et Bases de Données 4.3 SAISIE D'UN CLIENT AVEC UNE LISTE DEROULANTE POUR LES CP (VERSION 1) Objectif Insérer un enregistrement dans la table Clients. La clé étrangère est présentée dans une liste. Ecrans Démarche Un seul script PHP est créé. Le formulaire permet de saisir le nom du client et son CP. L'id_client est généré automatiquement. Le CP est saisi via une liste. La liste affiche les noms des villes et permet de récupérer le CP correspondant. La liste est construite via une fonction : creerListe($asNomListe, $asTable, $asColValue, $asColAffiche, $aiTaille, $abMultiple, $lien). On passe comme arguments le nom de la liste, le nom de la colonne de la table à récupérer, le nom de la colonne à afficher, le nom de la table, la taille de la liste et le lien vers la BD. Lorsque l'utilisateur clique sur le bouton de soumission le script PHP est exécuté à nouveau. Un test est effectué sur l'existence de l'élément tb_nom. Le cas échéant l'ordre SQL Insert est composé et une tentative d'insertion est réalisée. Les erreurs SQL sont gérées. © Pascal Buguet Imprimé le 15 juin 2017 Page 37 PHP et Bases de Données Script clientsAjoutV1.php <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>ClientsAjoutV1.php</title> </head> <body> <?php require_once("outils.inc.php"); require_once("coursBdConnexion.inc.php"); $lien = seConnecter(); if(isSet($_REQUEST["tb_nom"])) { $nom = addslashes($_REQUEST["tb_nom"]); $cp = $_REQUEST["lb_villes"]; mysql_query("SET NAMES UTF8"); $sql = "INSERT INTO clients(nom, cp) VALUES('$nom', '$cp')"; $r = mysql_query($sql, $lien); if($r) echo "<br />Insertion réussie<br />"; else echo "<br />Erreur : " . mysql_error($lien); } ?> <form action="" method="get"> <label>Nom : </label> <input type="text" name="tb_nom" value="Castafiore" /> <label>Ville : </label> <?php echo creerListe("lb_villes","villes","cp","nom_ville", 1, false, $lien) ; ?> <input type="submit" /> </form> </body> © Pascal Buguet Imprimé le 15 juin 2017 Page 38 PHP et Bases de Données 4.4 SAISIE D'UN CLIENT AVEC UNE LISTE DEROULANTE POUR LES CP (VERSION 2) Objectif Saisir un nouveau client et éventuellement une nouvelle ville. Si la ville du client est dans la liste elle est sélectionnée puis on valide. Autrement on clique sur un autre bouton submit qui affiche une nouvelle page de saisie. Au moment de la validation de la nouvelle ville une redirection est effectuée sur l’écran précédent (Saisie client). Comme dans les exemples précédents en cliquant sur un nom de ville dans la liste des villes c’est le CP correspondant qui est récupéré. Cinématique des écrans © Pascal Buguet Imprimé le 15 juin 2017 Page 39 PHP et Bases de Données Démarche Cette procédure comporte 2 scripts PHP : Saisie du client et insertion dans la table Clients (ClientsAjout.php) Saisie de la nouvelle ville et insertion dans la table Villes (VillesAjout.php). Les problèmes à résoudre sont les suivants : Si le nom du client a été saisi avant de cliquer sur le bouton Nouvelle Ville, il faut pouvoir le récupérer au retour. La ville nouvelle saisie doit être automatiquement sélectionnée dans la liste au retour dans le document de saisie du client. Nous allons donc envoyer le nom du client au script nouvelle ville. header("Location: villesAjout.php?gsNomClient=$gsNomClient"); Nous le récupérons dans nouvelle ville dans un input text caché. Puis valider ville et le récupérer dans nouveau client. header("location: clientsAjout.php?gsNomClient=$nomClient&gsNomVille=$nom_ville"); Pour que la nouvelle ville soit automatiquement sélectionnée dans la liste nous ajoutons un nouvel argument pour créer la liste, la valeur à sélectionner. Le script de création est modifié. On teste la valeur de l'argument avec la valeur du champ de l'enregistrement lu dans la boucle. © Pascal Buguet Imprimé le 15 juin 2017 Page 40 PHP et Bases de Données Scripts clientsAjout.php <?php ob_start(); require_once("coursBdConnexion.inc.php"); require_once('outils.inc.php'); $lien = seConnecter(); // -- Quand on a cliqué sur Ajouter ville if(isSet($_REQUEST["cb_ajouter_ville"])) { $gsNomClient = $_GET["tb_nom"]; header("Location: villesAjout.php?gsNomClient=$gsNomClient"); } // --- Quand on revient de villesAjout.php if(isSet($_REQUEST["gsNomClient"])) { $nom = addslashes($_REQUEST["gsNomClient"]); } // --- Quand on a cliqué sur Valider Client if(isSet($_REQUEST["cb_valider_client"])) { $nom = addslashes($_REQUEST["tb_nom"]); $cp = $_REQUEST["listeVilles"]; $sql = "INSERT INTO clients(nom, cp) VALUES('$nom', '$cp')"; $r = mysql_query($sql, $lien); if($r) echo "<br />Insertion r&eacute;ussie<br />"; else echo "<br />Erreur : " . mysql_error($lien); } ?> <form action="" method="get"> <label>Nom client : </label> <input type="text" name="tb_nom" value="<?php echo $nom ; ?>" /> <label>Ville : </label> <?php echo creerListe("listeVilles", "villes", "cp", "nom_ville", 1, false, $lien) ; ?> <input type="submit" value="Nouvelle ville" name="cb_ajouter_ville" /> <input type="submit" value="Valider client" name="cb_valider_client" /> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 41 PHP et Bases de Données villesAjout.php <?php ob_start(); require_once("coursBdConnexion.inc.php"); $lien = seConnecter(); $nomClient = ""; // --- Ajout de la ville dans la table Villes if(isSet($_REQUEST["cb_valider_ville"])) { $cp = $_REQUEST["cp"] ; $nom_ville = $_REQUEST["nom_ville"] ; $photo = $_REQUEST["photo"] ; $site = $_REQUEST["site"] ; $resultat = mysql_query("INSERT INTO villes(cp, nom_ville, photo, site) VALUES('$cp','$nom_ville','$photo','$site')"); if(!$resultat) print(mysql_error()); else { // --- Si on est venu au préalable de clientsAjout.php if($_REQUEST["cacheNom"] != "") { $nomClient = $_REQUEST["cacheNom"]; // --- On "renvoie" le nom du client et le nom de la ville header("location: clientsAjout.php?gsNomClient=$nomClient&gsNomVille=$nom_ville"); } } } // --- Si l'on vient de clientsAjout.php if(isSet($_REQUEST["gsNomClient"])) $nomClient = $_REQUEST["gsNomClient"]; ?> <form action="" method="get"> <label>CP </label><input name="cp" type="text" /><br /> <label>Nom ville </label><input name="nom_ville" type="text" /><br /> <label>Photo </label><input name="photo" type="text" /><br /> <label>Site </label><input name="site" type="text" /><br /> <input name="cacheNom" type="hidden" value="<?php echo $nomClient ; ?>" /> <input type="submit" value="Valider ville" name="cb_valider_ville" /> </form> Exercice accompagné Modifiez pour gérer le prénom et le retour avec la nouvelle ville sélectionnée dans la liste. © Pascal Buguet Imprimé le 15 juin 2017 Page 42 PHP et Bases de Données CHAPITRE 5 AUTHENTIFICATION © Pascal Buguet Imprimé le 15 juin 2017 Page 43 PHP et Bases de Données 5.1 L'INSCRIPTION Le visiteur peut éventuellement s'abonner. C'est une insertion dans la table utilisateurs. Et on crée le cookie "qualite" avec la valeur FO. <?php ob_start(); $lien = mysql_connect("localhost","root",""); mysql_select_db("cours", $lien); ?> <h4>Inscription</h4> <?php if(isSet($_REQUEST["cb_valider"])) { $ut = $_REQUEST["ut"]; $mdp = $_REQUEST["mdp"]; $sql = "INSERT INTO utilisateurs(ut,mdp,qualite) VALUES('$ut','$mdp','FO')"; $ok = mysql_query($sql); if(!$ok) echo mysql_errno(), " --> ", mysql_error(); else setcookie("qualite", "FO"); } ?> <form action="" method="get"> <label>Nom </label><input name="ut" type="text" value="p" /> <label>Mot de passe </label><input name="mdp" type="password" value="b" /> <input type="submit" value="Valider" name="cb_valider" /> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 44 PHP et Bases de Données 5.2 BOITE DE CONNEXION Objectif Proposer une "boîte de dialogue" pour se connecter en tant qu'abonné ou BackOffice. Créer le cookie "qualite". Script <?php ob_start(); require_once("coursBdConnexion.inc.php"); $lien = seConnecter(); ?> <h4>Authentification</h4> <?php if(isSet($_GET["cb_valider"])) { $ut = $_GET["ut"]; $mdp = $_GET["mdp"]; $sql = "SELECT qualite FROM utilisateurs WHERE ut='$ut' AND mdp='$mdp'"; $curseur = mysql_query($sql); if(mysql_num_rows($curseur) == 1) { $enr = mysql_fetch_row($curseur); setCookie("qualite", $enr[0]); echo "Vous &ecirc;tes connect&eacute; avec vos pouvoirs"; } else echo "Ou vous n'&ecirc;tes pas inscrit ou vos saisies sont erron&eacute;es"; } ?> <form action="" method="get"> <label>Nom d'utilisateur </label><input type="text" name="ut" value="p" /> <label>Mot de passe </label><input type="password" name="mdp" value="b" /> <input type="submit" name="cb_valider" /> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 45 PHP et Bases de Données 5.3 DECONNEXION On détruit le cookie et on redirige vers Accueil.php. <?php setcookie("qualite", "", time()) ; header("location: accueil.php") ; ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 46 PHP et Bases de Données 5.4 RESTRICTION DE PAGE Objectif Restreindre l'accès à une page en fonction de la qualité de l'utilisateur connecté. Un BO accède à toutes les pages. Un FO accède à certaines pages. Un visiteur-invité accède à moins de pages encore. La gestion peut être faite page par page ou sur l'affichage du sommaire. Script (Exemple sur une page) En tout début de page Pour les pages BO. <?php if(!isSet($_COOKIE["qualite"]) || $_COOKIE["qualite"] == "FO") header("location: accueil.php"); ?> Pour les pages BO et FO. <?php if(!isSet($_COOKIE["qualite"])) header("location: accueil.php"); ?> Pour les pages accessibles à tous vous ne scriptez rien. © Pascal Buguet Imprimé le 15 juin 2017 Page 47 PHP et Bases de Données CHAPITRE 6 GESTION DES DATES 6.1 FONCTIONS MYSQL Curdate() Now() Fonctions Description Date du serveur Date et heure du serveur Year(date) Month(date) Day(date) Date_format(date,format) SysDate() Renvoie l'année d'une date Renvoie le mois d'une date Renvoie le jour d'une date Renvoie une date formatée (%Y,%m,%d,…) Date du serveur NB : pour plus de détails sur les fonctions MySQL cf le support MySQL ou la documentation. 6.2 FONCTIONS PHP Fonctions Date('Y-m-d') Date('Y-m-d H:i:s') Mktime(h,m,s,M,d,y) StrToTime(chaine) GetDate() Description Renvoie la date du jour selon un format Idem Renvoie une date sous forme de TimeStamp (nombre de secondes depuis 1970) Renvoie une date sous forme de TimeStamp (nombre de secondes depuis 1970) Renvoie un tableau associatif correspondant à la date du jour ou une date NB : pour plus de détails sur les fonctions PHP cf le support PHP initiation ou la documentation. © Pascal Buguet Imprimé le 15 juin 2017 Page 48 PHP et Bases de Données 6.3 SCRIPTS D'INSERTION Les dates seront insérées selon trois méthodes : Utilisation de fonctions MYSQL. Utilisation de chaînes de caractères. Utilisation de fonctions PHP. 6.3.1 Script d'insertion de dates avec des fonctions MySQL La table de test news : CREATE TABLE IF NOT EXISTS news ( id_news int(11) NOT NULL auto_increment, titre varchar(50) collate latin1_general_ci NOT NULL, auteur varchar(50) collate latin1_general_ci NOT NULL, date_news datetime NOT NULL, PRIMARY KEY (id_news) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ; Note : Il existe au moins trois types MySQL pour le temps : DATE, DATETIME et TIME. <?php header("Content-Type: text/html; charset=UTF-8"); $lien = mysql_connect("localhost", "root", "") or die("Connexion impossible au serveur"); mysql_select_db("cours", $lien) or die(mysql_error($lien)); mysql_query("SET NAMES UTF8"); // --- curdate() renvoie yyyy-mm-dd $lsInsert = "INSERT INTO news(titre, auteur, date_news) VALUES('MYSQLcurdate()', 'pb', curdate())"; mysql_query($lsInsert, $lien) or die(mysql_error($lien)); // --- now() renvoie yyyy-mm-dd hh:ii:ss $lsInsert = "INSERT INTO news(titre, auteur, date_news) VALUES('MYSQL-now()', 'pb', now())"; mysql_query($lsInsert, $lien) or die(mysql_error($lien)); // --- Chaîne $lsChaine = "Chaîne"; $lsInsert = "INSERT INTO news(titre, auteur, date_news) VALUES('$lsChaine', 'pb', '2008-11-28')"; mysql_query($lsInsert, $lien) or die(mysql_error($lien)); echo "Terminé"; ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 49 PHP et Bases de Données 6.3.2 Script d'insertion de dates avec des fonctions PHP La date est générée dans une chaîne de caractères soit avec la fonction Date() soit via un TimeStamp. <?php header("Content-Type: text/html; charset=UTF-8"); $lien = mysql_connect("localhost", "root", ""); mysql_select_db("cours",$lien); mysql_query("SET NAMES UTF8"); $ld_date = date('Y-m-d'); $lsInsert = "INSERT INTO news(titre, auteur, date_news) VALUES('PHP-Date(Y-m-d)', 'pb', '$ld_date')"; mysql_query($lsInsert, $lien) or die(mysql_error($lien)); $ld_date_heure = date('Y-m-d H:i:s'); $lsInsert = "INSERT INTO news(titre, auteur, date_news) VALUES('PHP-Date(Y-m-d H:i:s)', 'pb', '$ld_date_heure')"; mysql_query($lsInsert, $lien) or die(mysql_error($lien)); $ts = mktime(10,10,0,01,30,2008); // --- OK $ts = strtotime("30 october 2008 10:10:00"); // --- OK $ts = strtotime("2008/10/03 10:10:00"); // --- OK $ld_date_heure = date('Y-m-d H:i:s', $ts); $lsInsert = "INSERT INTO news(titre, auteur, date_news) VALUES('PHP-mktime', 'pb', '$ld_date_heure')"; mysql_query($lsInsert, $lien) or die(mysql_error($lien)); echo "Terminé"; ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 50 PHP et Bases de Données 6.4 SCRIPTS DE VISUALISATION AVEC DES FONCTIONS DATE DE MYSQL Utilisation des fonctions présentées dans le tableau précédent. <?php header("Content-Type: text/html; charset=UTF-8"); $lien = mysql_connect("localhost", "root", "") or die("Connexion impossible au serveur"); mysql_select_db("cours", $lien) or die(mysql_error($lien)); // -------------------------------function afficherCurseur($as_select) // -------------------------------{ $curseur = mysql_query($as_select); echo "<table border='1'>"; while($enr = mysql_fetch_assoc($curseur)) { print("<tr><td>" . $enr['titre'] . "</td><td>" . $enr['date_news'] . "</td></tr>"); } echo "</table>"; } // --- La table $lsSelect = "SELECT * FROM news"; // --- SELECT avec un format $lsSelect = "SELECT titre, DATE_FORMAT(date_news,'%d-%m-%Y') as 'date_news' FROM news"; // --- Avec un WHERE dont la date est celle du jour (Attention ne renvoie que si la date est exacte pas les secondes donc celles dont l'heure est égal à 00:00:00) $lsSelect = "SELECT * FROM news WHERE date_news = CURDATE()"; // --- Pour avoir toutes celles du jour la fonction date(une date) renvoie la partie date et supprime la partie heure $lsSelect = "SELECT * FROM news WHERE DATE(date_news) = DATE(CURDATE())"; // --- Celles inférieures à la date du jour $lsSelect = "SELECT * FROM news WHERE date_news < CURDATE()"; // --- Celles inférieures 31 décembre 2008 $lsSelect = "SELECT * FROM news WHERE date_news < '2008-12-31'"; // --- Celles de 2008 avec YEAR() $lsSelect = "SELECT * FROM news WHERE YEAR(date_news) = '2008'"; // --- Celles d'octobre 2008 avec YEAR() et MONTH() $lsSelect = "SELECT * FROM news WHERE YEAR(date_news) = '2008' AND MONTH(date_news) = '10'"; // --- Celles d'octobre 2008 avec >= et <= $lsSelect = "SELECT * FROM news WHERE date_news >= '2008-10-01' AND date_news <= '2008-10-30'"; // --- Celles du jour ... via la fonction PHP Date() $ld_date = date('Y-m-d'); $lsSelect = "SELECT * FROM news WHERE date_news = '$ld_date'"; afficherCurseur($lsSelect); ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 51 PHP et Bases de Données CHAPITRE 7 TRAITEMENTS GENERIQUES 7.1 AFFICHAGE D'UNE TABLE SUR PLUSIEURS PAGES 7.1.1 Création de la table Principe Supprimer la table si elle existe (DROP TABLE IF EXISTS grosse_table). Créer la table de structure suivante CREATE grosse_table(id_gt INT(5), nom VARCHAR(50)). Ajouter des enregistrements avec d'une boucle d'insertion (INSERT INTO …). Script <?php mysql_connect("localhost", "root", "") or die("Connexion impossible au serveur"); mysql_select_db("cours") or die(mysql_error()); $lsDrop = "DROP TABLE IF EXISTS grosse_table"; $resultat = mysql_query($lsDrop) or die(mysql_error()); $lsCreate = "CREATE TABLE IF NOT EXISTS grosse_table(id_gt int(5) default NULL, nom varchar(50) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;"; $resultat = mysql_query($lsCreate) or die(mysql_error()); for($i=1; $i<=101; $i++) { $lsNom = "Tintin" . (string)$i ; $lsInsert = "INSERT INTO grosse_table(id_gt , nom) VALUES($i,'$lsNom')"; $resultat = mysql_query($lsInsert); } print("C'est fini"); ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 52 PHP et Bases de Données 7.1.2 Visualisation de la table sur plusieurs pages Principe Lorsque qu'une table contient de nombreux enregistrements on va afficher sur plusieurs pages en numérotant les pages. On utilise la syntaxe SQL suivante : SELECT col1, col2, … FROM nom_de_table LIMIT début, nombre_d_enregistrements Note : le premier enregistrement est 0. D'où pour la première page SELECT * FROM grosse_table LIMIT 0, 10 Ecrans © Pascal Buguet Imprimé le 15 juin 2017 Page 53 PHP et Bases de Données Script <?php // --- gtPaginee.php header("Content-Type: text/html; charset=UTF-8"); mysql_connect("localhost","root",""); mysql_select_db("cours"); if(!isset($_GET['li_debut'])) $liDebut=0; else $liDebut=$_GET["li_debut"]; $liEnrParPage=10; // --- Calcul $lsSqlCount = $curseur = $enr = $liCount = du nombre d'enregistrements de la table "SELECT COUNT(*) FROM grosse_table"; mysql_query($lsSqlCount); mysql_fetch_row($curseur); $enr[0]; // --- Calcul du nombre d'ancres à mettre en bas de page $liAncres = ceil($liCount / $liEnrParPage); // --- Calcul à l'arrondi supérieur du nombre de pages nécessaires // --- Construction de l'ordre SQL $lsSql = "SELECT * FROM grosse_table LIMIT $liDebut, $liEnrParPage" ; $curseur = mysql_query($lsSql); if(!$curseur) die("<br />Erreur<br />"); // --- Table et titre et en-têtes print("<table cellspacing='1' border='1'>"); print("<caption>Grosse table</caption>\n"); print("<thead><tr><th>id_gt</th><th>Nom</th></tr></thead>\n"); // --- Boucle de balayage while($enr = mysql_fetch_row($curseur)) { print("<tr><td align='center'>$enr[0]</td><td>$enr[1]</td></tr>\n"); } // --- Composition d'une chaîne de caractères comprenant les ancres. $lsAncresPages = "Pages "; $liDebut = 0; for($i=1; $i<=$liAncres; $i++) { $liDebut = (($i-1) * $liEnrParPage); $lsAncresPages .= "<a href='" . $_SERVER['PHP_SELF'] . "?li_debut=$liDebut'>$i</a>\n"; } print("<tr><td colspan='2' align='center'>$lsAncresPages</td></tr>\n"); print("</table>\n"); ?> Commentaires Déterminer le point de départ (0 si c'est pour la première fois ou la valeur correspondant au début en fonction de l'ancre cliquée). Calculer le nombre d'enregistrements de la table ou du SELECT. Calculer le nombre d'ancres (le nombre de pages) en fonction du nombre d'enregistrements de la table et du nombre d'enregistrements à afficher par page (10 dans ce script). Afficher les enregistrements correspondant à l'ancre cliquée. Afficher les ancres. Exercice : l’utilisateur paramètre le nombre d’enregistrements par page. © Pascal Buguet Imprimé le 15 juin 2017 Page 54 PHP et Bases de Données 7.2 VISUALISATION GENERIQUE Principe L'utilisateur saisit le nom de la table dans un input text, la table est affichée dans un tableau html. Fonctions utilisées Fonction Description $curseur = mysql_query($requete, $lien) Ouvre un curseur $nb = mysql_num_fields($curseur) Récupère le nombre de champs d'un curseur $nom_champ = mysql_field_name($curseur , Récupère le nom du champ courant d'un curseur $i) $enregistrement = Récupère un enregistrement dans un tableau mysql_fetch_array($curseur) Ecrans Produits © Pascal Buguet Villes Imprimé le 15 juin 2017 Page 55 PHP et Bases de Données Script <?php // -------------------------------function visuTable($asTable) // -------------------------------{ mysql_connect("localhost" , "root" , "") ; mysql_select_db("cours") ; $lsTableau = ""; $lsSql = "SELECT * FROM $asTable" ; // --- Construction de l'ordre SQL $curseur = mysql_query($lsSql) ; // --- Exécuter la requête if(!$curseur) die("Erreur de lecture<br />") ; // --- Boucle de balayage $liCount = mysql_num_fields($curseur) ; // --- comptage du nombre de champs $lsTableau .= "<table border='1'>" ; // --- Le nom de la table comme titre $lsTableau .= "<tr><td colspan='$liCount' align='center'>$asTable</td></tr>"; $lsTableau .= "<tr>" ; for($i=0; $i<$liCount; $i++) // --- Affichage des en-têtes (Noms des champs) $lsTableau .= "<td>" . mysql_field_name($curseur , $i) . "</td>" ; $lsTableau .= "</tr>" ; while (($enr = mysql_fetch_array($curseur))) // --- Fetch array : récupération de l'enregistrement { $lsTableau .= "<tr>" ; for($i=0; $i<$liCount; $i++) // --- Affichage des champs if ($enr[$i] != "") $lsTableau .= "<td>" . $enr[$i] . "</td>" ; else $lsTableau .= "<td>&nbsp;</td>" ; $lsTableau .= "</tr>" ; } $lsTableau .= "</table>" ; return $lsTableau; } // -------------------------------if(isSet($_GET["tb_table"])) echo visuTable($_GET["tb_table"]) ; ?> <form action="<?php print($_SERVER["PHP_SELF"]); ?>" method="get"> <label>Nom de la table </label><input type="text" name="tb_table" value="villes" /> <input type="submit" /> </form> Commentaires Composer un ordre SELECT en fonction du nom de la table saisi. Exécuter l'ordre SELECT (mysql_query). Récupérer le nombre de colonnes du SELECT (mysql_num_fields). Récupérer les noms des colonnes (mysql_field_name). Récupérer les enregistrements (mysql_fecth_array) un par un pour les afficher champ par champ dans une boucle FOR imbriquée dans une boucle WHILE. © Pascal Buguet Imprimé le 15 juin 2017 Page 56 PHP et Bases de Données Autre version avec une boucle FOREACH et sans les en-têtes de colonnes. <?php // -------------------------------function visuTable($asTable, $lien) // -------------------------------{ $lien = mysql_connect("localhost" , "root" , "") ; mysql_select_db("cours" , $lien) ; $lsTableau = ""; $lsSql = "SELECT * FROM $asTable" ; $curseur = mysql_query($lsSql , $lien) ; if (!$curseur) die("Erreur de lecture<br />") ; $lsTableau .= "<table border='1'>" ; $lsTableau .= "<tr>" ; while($enr = mysql_fetch_row($curseur)) { $lsTableau .= "<tr>" ; foreach($enr as $valeur) $lsTableau .= "<td>$valeur</td>" ; $lsTableau .= "</tr>" ; } $lsTableau .= "</table>" ; return $lsTableau; } // -------------------------------if(isSet($_GET["tb_table"])) echo visuTable($_GET["tb_table"] , $lien) ; ?> <form action="<?php print($_SERVER["PHP_SELF"]); ?>" method="get"> Nom de la table <input type="text" name="tb_table" value="villes" /> <input type="submit" /> </form> Exercice : l’envoi peut être un SELECT col1, col2, … ou le nom d’une table. © Pascal Buguet Imprimé le 15 juin 2017 Page 57 PHP et Bases de Données 7.3 INSERTION GENERIQUE (LA METABASE) Principe L'utilisateur sélectionne le nom d'une table dans une liste remplie avec SHOW TABLES. Un écran est affiché avec la liste des colonnes (récupérées avec SHOW COLUMNS FROM table) et autant d'input text. Il saisit et valide. L'ordre SQL INSERT est généré automatiquement. Problématiques : récupérer la liste des tables de la base, la liste et le type des champs de la table sélectionnée. Fonctions utilisées Fonction Curseur = mysql_list_tables("nom_de_la_base" , "serveur_de_base_de données") liste_champs = mysql_list_fields("base_de_données","nom_de_table") enregistrement = mysql_fetch_array(curseur) Curseur = mysql_query("SHOW TABLES", lien) Curseur = mysql_query("SHOW COLUMNS FROM table", lien) n = mysql_num_fields(curseur) Nom_champ = mysql_field_name(curseur, i) Type_champ = mysql_field_type(curseur, i) String = mysql_field_flags(curseur, i) Description Récupère dans un curseur la liste des tables de la BD passée en paramètre. Obsolète. Préférez la solution suivante. Récupère les noms des champs d'une table. Obsolète. Préférez la solution suivante. Récupère un enregistrement dans un tableau Récupère dans un curseur la liste des tables de la BD passée en paramètre. Récupère les noms des champs d'une table. Récupère le nombre de champs d'un curseur Récupère le nom du champ courant d'un curseur Récupère le type du champ courant d'un curseur Renvoie une chaîne de caractères contenant les caractéristiques d'une colonne (primary_key, not_null, auto_increment,…). Chaque valeur est séparée par un espace (à exploser). Pour ne pas avoir comme labels les noms des champs mais des libellés il faudrait ajouter dans MySQL des Comment sur chaque champ de table. Puis utiliser la requête suivante en adaptant au nom de la BD et de la table : SELECT column_name, column_comment, extra FROM information_schema.COLUMNS WHERE table_name = 'VILLES' AND table_schema = 'COURS'; La colonne extra contient des informations du type "auto_increment". © Pascal Buguet Imprimé le 15 juin 2017 Page 58 PHP et Bases de Données Ecrans Phase 1 : Affichage de la liste des tables Phase 2 : Création du formulaire Création du formulaire de saisie en fonction de la structure de la table dans la BD. Si le champ est un de type auto_increment il est en readonly. © Pascal Buguet Imprimé le 15 juin 2017 Page 59 PHP et Bases de Données Phase 3 : Saisie des valeurs Phase 4 : Insertion dans la table Insertion réussie INSERT INTO clients(nom,prenom,date_naissance,cp) VALUES('Roberts','Julia','1965-10-3','75011') © Pascal Buguet Imprimé le 15 juin 2017 Page 60 PHP et Bases de Données Script <?php header("Content-Type: text/html; charset=UTF-8"); ?> <!-- CSS --> <style> div{float:left; padding:10px;} </style> <!-- FONCTIONS PHP --> <?php $lien = mysql_connect("localhost","root",""); mysql_select_db("cours",$lien); // -------------------------------function genererFormulaireListeTables($lien) // -------------------------------{ $lsFormulaire = "<form action='' method='get'>"; // --- Liste des noms de tables $curseur = mysql_query("SHOW TABLES",$lien); $lsFormulaire .= "<select name='lb_tables' size='5'>" ; while($enr = mysql_fetch_row($curseur)) { $lsFormulaire .= "<option value='$enr[0]'>$enr[0]</option>" ; } $lsFormulaire .= "</select>" ; $lsFormulaire .= "<br /><input type='submit' value='Valider sélection' name='__cb_valider_selection' />"; $lsFormulaire .= "</form>"; return $lsFormulaire; } // -------------------------------function genererFormulaireSaisie($asTable, $lien) // -------------------------------{ // --- Création d'un curseur vide pour récupérer les types des champs $curseur = mysql_query("SELECT * FROM $asTable WHERE 1 = 0", $lien); // --- Récupère la liste des champs de la table $listeChamps = mysql_query("SHOW COLUMNS FROM $asTable", $lien); $lsFormulaire = "<form action='' method='get'>"; $lsFormulaire .= "<table>"; // --- Affichage des en-têtes (Noms des champs) et des IT $i = 0; while($enr = mysql_fetch_row($listeChamps)) { $lsCaracteristiquesChamps = mysql_field_flags($curseur, $i); if(!ereg("auto_increment",$lsCaracteristiquesChamps)) { $lsFormulaire .= "<tr><td>$enr[0]</td><td><input type='text' name='$enr[0]'></td></tr>"; } else { $lsFormulaire .= "<tr><td>$enr[0]</td><td><input type='text' name='$enr[0]' readonly='readonly'></td></tr>"; } $i++; } $lsFormulaire .= "<tr><td><input type='hidden' value='$asTable' name='__table' /></td>"; $lsFormulaire .= "<td><input type='submit' value='Valider Ajout' name='__cb_valider_ajout' /></td></tr>"; $lsFormulaire .= "</table>"; $lsFormulaire .= "</form>"; return $lsFormulaire; } © Pascal Buguet Imprimé le 15 juin 2017 Page 61 PHP et Bases de Données // -------------------------------function genererEtExecuterSQL($lien) // -------------------------------{ $lsColonnes = ""; $lsValeurs = ""; $lsTable = $_GET["__table"]; // --- Création d'un curseur pour récupérer les types des champs $curseur = mysql_query("SELECT * FROM $lsTable WHERE 1 = 0", $lien); $i = 0; foreach($_GET as $colonne => $valeur) { if(substr($colonne,0,2) != "__") { $lsCaracteristiquesChamps = mysql_field_flags($curseur, $i); if(!ereg("auto_increment",$lsCaracteristiquesChamps)) { // --- Récupère le nom de la colonne $lsColonnes .= $colonne . ","; // --- Récupère le type de la colonne $typeColonne = mysql_field_type($curseur , $i); // --- Récupère la valeur de la colonne // --- et en fonction du type ajoute ou non des ' if($typeColonne == "string" || $typeColonne == "date") { if($typeColonne == "date") { // --- Si date au format jj/mm/aaaa if(ereg("[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}", $valeur)) { // --- On passe au format yyyy-mm-dd $valeur = ereg_replace("(.*)/(.*)/(.*)", "\\3-\\2\\1", $valeur); } } $lsValeurs .= "'" . $valeur . "',"; } else $lsValeurs .= $valeur . ","; } $i++; } } $lsColonnes = substr($lsColonnes, 0, strlen($lsColonnes) - 1); $lsValeurs = substr($lsValeurs , 0, strlen($lsValeurs) - 1); $lsSQL = "INSERT INTO $lsTable($lsColonnes) VALUES($lsValeurs)"; $lbOk = mysql_query($lsSQL, $lien); if($lbOk) echo "<br />Insertion réussie<br />"; else echo "<br />", mysql_error($lien), "<br />"; return $lsSQL; } ?> <!-- HTML !!! --> <div id="liste"> <?php echo genererFormulaireListeTables($lien); ?> </div> <div id="saisies"> <?php // -------------------------------// --- Appel de la fonction d'affichage des champs de saisie if(isSet($_GET["__cb_valider_selection"]) && $_GET["lb_tables"] != "") { echo genererFormulaireSaisie($_GET["lb_tables"], $lien) ; © Pascal Buguet Imprimé le 15 juin 2017 Page 62 PHP et Bases de Données } ?> </div> <div id="insertion"> <?php if(isSet($_GET["__cb_valider_ajout"])) { echo genererEtExecuterSQL($lien); } ?> </div> © Pascal Buguet Imprimé le 15 juin 2017 Page 63 PHP et Bases de Données CHAPITRE 8 MULTICRITERES 8.1 OBJECTIF : FAIRE DES RECHERCHES MULTI-CRITERES Un écran permet de saisir plusieurs valeurs pour faire une recherche multi-critères. Le script appelé générera un ordre SQL Select en fonction des saisies faites. Par exemple : SELECT * FROM Clients SELECT * FROM Clients WHERE cp = '75012' SELECT * FROM Clients WHERE nom = 'Tintin' AND cp = '75012'. Les valeurs sont récupérées grâce à une boucle sur le tableau $_GET. Un test est fait pour savoir si la valeur est une chaîne vide ou non, et pour savoir si c’est la première valeur ou non, car dans ce cas ce sera le prédicat WHERE qui sera concaténé et dans l'autre cas ce sera l’opérateur logique AND. Le bouton a été nommé. Il faut donc repérer les Input Text concernés par le SELECT. © Pascal Buguet Imprimé le 15 juin 2017 Page 64 PHP et Bases de Données 8.1.1 Ecrans © Pascal Buguet Imprimé le 15 juin 2017 Page 65 PHP et Bases de Données 8.1.2 Script <?php header("Content-Type: text/html; charset=UTF-8"); ?> <form action="" method="get"> <table> <tr><td>Code client</td><td><input type="text" name="tb_id_client" value="" /></td></tr> <tr><td>Nom client</td><td><input type="text" name="tb_nom" value="Tintin" /></td></tr> <tr><td>Cp</td><td><input type="text" name="tb_cp" value="75011" /></td></tr> <tr><td></td><td><input type="submit" name="cb_valider" value="Rechercher" /></td></tr> </table> </form> <?php if(isSet($_GET["cb_valider"])) { $liCount = 0 ; $lsResultat = ""; $lien = mysql_connect("localhost","root","") ; mysql_select_db("cours",$lien) ; $lsSelect = "SELECT * FROM clients " ; $lbDebut = true ; foreach($_GET as $colonne => $valeur) { if (substr($colonne,0,3) == "tb_") { if((!$lbDebut) and ($valeur != "")) { $lsSelect .= " AND " ; $lsSelect .= substr($colonne,3) . "= '" . $valeur . "'" ; } if(($lbDebut) and ($valeur != "")) { $lsSelect .= " WHERE " ; $lsSelect .= substr($colonne,3) . "= '" . $valeur . "'" ; $lbDebut = false ; } } } mysql_query("SET NAMES UTF8"); $curseur = mysql_query($lsSelect, $lien) ; echo $lsSelect; $lsResultat .= "<table border='1'>" ; // --- Les entêtes $lsResultat .= "<thead><tr>"; for($i=0; $i < mysql_num_fields($curseur); $i++) { $lsColonne = mysql_field_name($curseur, $i); $lsResultat .= "<th>$lsColonne</th>"; } $lsResultat .= "</tr></thead>"; // --- Les données while($enr = mysql_fetch_row($curseur)) { $lsResultat .= "<tr>"; for($i=0; $i<count($enr); $i++) { if($enr[$i]!="") $lsResultat .= "<td>$enr[$i]</td>" ; else $lsResultat .= "<td>&nbsp;</td>" ; © Pascal Buguet Imprimé le 15 juin 2017 Page 66 PHP et Bases de Données } $lsResultat .= "</tr>"; $liCount++ ; } $liColonnes = mysql_num_fields($curseur); $lsResultat .= "<tr><td colspan='$liColonnes' align='center'>$liCount enregistrement(s)</td></tr>" ; $lsResultat .= "</table>" ; if($liCount > 0) echo "<br />$lsResultat"; else echo "<br />Aucun résultat"; } ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 67 PHP et Bases de Données CHAPITRE 9 PROCEDURES ET FONCTIONS STOCKEES MYSQL 9.1 INTRODUCTION 9.1.1 Principes Les procédures et fonctions stockées MYSQL servent à stocker sur le serveur de bases de données, dans la métabase exactement, des instructions SQL précompilées. L'avantage d'une telle implémentation permet de gagner en productivité réseau et serveur. Les ordres SQL sont ainsi disponibles pour n'importe quel programme client. Le programme client n'a plus qu'à faire un appel paramétré. 9.1.2 Syntaxe de base Création d'une procédure Pour stocker une procédure stockée MySQL 5 il faut un script qui modifie les délimiteurs d'instructions. Par défaut c'est le ;. Or les ordres SQL contenus dans les procédures stockées contiennent elles aussi des ;. D'où la présence en début et en fin de script de L'opérateur DELIMITER qui modifie le délimiteur d'exécution puis le rétablit. Utilisez MySQLQueryBrowser (File/New script tab) pour stocker procédures et fonctions ou l'outil mysql ou PHPMyADMIN. D'où la syntaxe CREATE PROCEDURE NomDeProcedure([DIRECTION argument_1 TYPE [, DIRECTION argument_2 TYPE ]]) BEGIN Instructions SQL ou autre ; Instructions SQL ou autre ; END Les arguments peuvent être de direction IN, OUT ou INOUT. Les types sont ceux de MYSQL. © Pascal Buguet Imprimé le 15 juin 2017 Page 68 PHP et Bases de Données 9.1.3 La gestion des jeux de caractères Les variables pour les charsets character_set_results character_set_client character_set_connection character_set_server Les variables pour les collations collation_connection collation_server Exemple de message d'erreur lors de l'exécution #1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' Instructions d'initialisation SET CHARACTER_SET_SERVER = 'latin1'; SET COLLATION_SERVER ='latin1_general_ci'; © Pascal Buguet Imprimé le 15 juin 2017 Page 69 PHP et Bases de Données Création d'une procédure d'insertion dans la table villes DELIMITER $$ CREATE PROCEDURE villesInsert (IN asCp VARCHAR(5), IN asVille VARCHAR(50)) BEGIN INSERT INTO villes(cp, nom_ville) VALUES(asCp, asVille) ; COMMIT ; END $$ DELIMITER ; Suppression d'une procédure stockée DROP PROCEDURE NomDeProcedure; Modification d'une procédure ALTER PROCEDURE … Visualisation du code d'une procédure stockée SHOW CREATE PROCEDURE NomDeProcedure; Exemple : SHOW CREATE PROCEDURE villesDelete; © Pascal Buguet Imprimé le 15 juin 2017 Page 70 PHP et Bases de Données 9.2 EXEMPLES DE PROCEDURES STOCKEES MYSQL 9.2.1 Insertion d'une ville Code DELIMITER $$ DROP PROCEDURE IF EXISTS villesInsert $$ CREATE PROCEDURE villesInsert(IN asCp VARCHAR(5), IN asVille VARCHAR(50)) BEGIN INSERT INTO villes(cp, nom_ville) VALUES(asCp, asVille) ; END $$ DELIMITER ; Test sous MYSQL CALL villesInsert('75022', 'Paris 22') Utilisation avec PHP Syntaxe Avec la fonction mysql_query("CALL nom_de_procedure(arguments)" , $lien) ; Exemple statique $resultat = mysql_query("CALL villesInsert('75033','Paris 33')", $lien) ; © Pascal Buguet Imprimé le 15 juin 2017 Page 71 PHP et Bases de Données La même en dynamique procInsertVilleDyn.php <?php header("Content-Type: text/html; charset=UTF-8"); if(isSet($_GET["tb_cp"])) { $lien = mysql_connect("localhost", "root", ""); mysql_select_db("cours", $lien); $lsCp = $_GET["tb_cp"]; $lsVille = $_GET["tb_ville"]; $resultat = mysql_query("CALL villesInsert('$lsCp','$lsVille')", $lien); if($resultat) print("Nouvel enregistrement inséré"); else print(mysql_error($lien)); } ?> <form action="" method="get"> <input type="text" name="tb_cp" value="75021" /> <input type="text" name="tb_ville" value="Paris 21" /> <input type="submit" /> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 72 PHP et Bases de Données 9.2.2 Suppression d'une ville Script MYSQL DELIMITER $$ DROP PROCEDURE IF EXISTS villesDelete $$ CREATE PROCEDURE villesDelete(IN asCp CHAR(5)) BEGIN DELETE FROM villes WHERE cp = asCp ; END $$ DELIMITER ; Test sous MYSQL CALL villesDelete('75033') Avec PHP <?php header("Content-Type: text/html; charset=UTF-8"); if(isSet($_GET["tb_cp"])) { $cp = $_GET["tb_cp"] ; $lien = mysql_connect("localhost", "root", ""); mysql_select_db("cours", $lien); $lsSql = "set character_set_server=latin1"; $resultat = mysql_query($lsSql, $lien); $lsSql = "set collation_server=latin1_general_ci"; $resultat = mysql_query($lsSql, $lien); $resultat = mysql_query("CALL villesDelete('$cp')", $lien); if($resultat) print("Enregistrement supprimé"); else print(mysql_error($lien)) ; } ?> <form action="" method="get"> <label>Quel CP à supprimer ?</label> <input name="tb_cp" type="text" value="75021" /> <input type="submit" /> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 73 PHP et Bases de Données 9.3 PROCEDURE STOCKEE QUI RENVOIE UN SELECT Script MYSQL DELIMITER $$ DROP PROCEDURE IF EXISTS villesSelect $$ CREATE PROCEDURE villesSelect () BEGIN SELECT * FROM villes; END $$ DELIMITER ; Script PHP Ne fonctionne pas cf PDO ou mysqli … <?php header("Content-Type: text/html; charset=UTF-8"); mysql_connect("localhost", "root", ""); mysql_select_db("cours"); $curseur = mysql_query("CALL villesSelect()"); if($curseur) { print("Table Villes<br/>"); while ($enr = mysql_fetch_row($curseur)) { print("$enr[0] – $enr[1]<br />") ; } } else print(mysql_error()); ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 74 PHP et Bases de Données 9.4 PROCEDURES STOCKEES AVEC UN ARGUMENT OUT Syntaxe Les arguments sont de direction OUT ou INOUT. Un argument OUT est un argument en sortie. Un argument INOUT est un argument en entrée modifiable. Pour renvoyer une valeur il faut utiliser l'opérateur INTO dans l'ordre SELECT : … INTO argument …lorsque la valeur est renvoyée via un ordre SQL SELECT. Script de création d'une procédure SELECT … DELIMITER $$ DROP PROCEDURE IF EXISTS villesCount $$ CREATE PROCEDURE villesCount(OUT aiNbVilles INT) BEGIN SELECT COUNT(*) INTO aiNbVilles FROM villes; END $$ DELIMITER ; Test MySQL NB : ne fonctionne pas dans MySQLQueryBrowser !!! mais fonctionne en mode commande. C:\…\bin>mysql –h localhost –u root cours Ou C:\…\bin>mysql –u root Mysql>use cours Mysql>CALL villesCount(@r); Mysql>SELECT @r; Script PHP Cf le support PDO ou MYSQLI pour l'exécution de procédures stockées avec des paramètres OUT. © Pascal Buguet Imprimé le 15 juin 2017 Page 75 PHP et Bases de Données 9.5 FONCTIONS STOCKEES Les fonctions stockées MySQL sont assez limitées. Une fonction renvoie un résultat et ne peut posséder que des arguments IN. Donc il ne faut pas mentionner de direction pour les arguments. Syntaxe de création CREATE FUNCTION NomDeFonction([Argument_1 Type [, argument_2 type, …]]) RETURNS Type BEGIN Instructions ; RETURN expression ; END Syntaxe de suppression Pour supprimer une fonction stockée il faut utiliser la syntaxe suivante : DROP FUNCTION NomDeFonction; Visualisation du code d'une fonction stockée SHOW CREATE FUNCTION NomDeFonction ; Exemple : SHOW CREATE FUNCTION addition ; © Pascal Buguet Imprimé le 15 juin 2017 Page 76 PHP et Bases de Données 9.5.1 Fonction stockée technique : une addition de 2 nombres La fonction stockée #Fonction stockée DELIMITER $$ DROP FUNCTION IF EXISTS addition $$ CREATE FUNCTION addition(aiX INT, aiY INT) RETURNS INT BEGIN RETURN aiX + aiY; END $$ DELIMITER ; Test MySQL ou autre (MySQL Query Browser, phpMyAdmin, …) SELECT addition(3,4); Ou SELECT addition(3,4) AS "Addition"; Autre Exemple : Calculer le prix TTC La fonction DELIMITER $$ CREATE FUNCTION TTC(aiPrixHT DOUBLE) RETURNS DOUBLE BEGIN RETURN aiPrixHT * 1.195; END $$ DELIMITER ; Exécution SELECT prix "Prix HT", ttc(prix) "Prix TTC" FROM produits; © Pascal Buguet Imprimé le 15 juin 2017 Page 77 PHP et Bases de Données Test PHP <?php header("Content-Type: text/html; charset=UTF-8"); ?> <form action="" method="get"> <input name="tb_x" type="text" size="5" value="3" /> + <input name="tb_y" type="text" size="5" value="4" /> <input type="submit" value="=" /> </form> <?php // --- fctAddition.php if(isSet($_GET["tb_x"])) { $xx = $_GET["tb_x"]; $yy = $_GET["tb_y"]; $cn = mysql_connect("localhost", "root", "") ; $bd = mysql_select_db("cours", $cn) ; $lsSelect = "SELECT addition($xx, $yy)"; $curseur = mysql_query($lsSelect, $cn) ; $enr = mysql_fetch_row($curseur); print($enr[0]); } ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 78 PHP et Bases de Données 9.5.2 Fonction stockée en relation avec une table Objectif Créer une fonction stockée qui renvoie le résultat scalaire d'un SELECT. La fonction renvoie le nombre d'enregistrements de la table Villes. La fonction stockée DELIMITER $$ DROP FUNCTION IF EXISTS fctNombreDeVilles $$ CREATE FUNCTION fctNombreDeVilles() RETURNS int(11) BEGIN DECLARE nVilles INT DEFAULT 0; SELECT COUNT(*) INTO nVilles FROM villes; RETURN nVilles; END $$ DELIMITER ; Le script <?php // --- Connexion $lien = mysql_pconnect("localhost","root",""); mysql_select_db("cours", $lien); // --- Exécution $lsSelect = "SELECT fctNombreDeVilles()"; $curseur = mysql_query($lsSelect, $lien) ; $enr = mysql_fetch_row($curseur); echo "Nombre de villes de la table Villes : " . $enr[0]; ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 79 PHP et Bases de Données 9.6 UNE PROCEDURE QUI GERE UN CURSEUR Objectif Créer une procédure stockée qui gère un curseur. On veut transférer dans la table VILLES_94 les enregistrements de la table VILLES qui correspondent au critère suivant : le CP commence par 94. Scripts MySQL CREATE TABLE cours.villes_94 ( cp CHAR( 5 ) NOT NULL , nom_ville VARCHAR( 50 ) NOT NULL , PRIMARY KEY ( cp ) ) ENGINE = INNODB CHARACTER SET latin1 COLLATE latin1_general_ci DELIMITER $$ DROP PROCEDURE IF EXISTS ajout_94 $$ CREATE PROCEDURE ajout_94() BEGIN DECLARE done INT DEFAULT 0; DECLARE v_cp, v_ville VARCHAR(50); DECLARE villes_94 CURSOR FOR SELECT cp, nom_ville FROM villes WHERE cp LIKE '94%'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN villes_94; REPEAT FETCH villes_94 INTO v_cp, v_ville; IF NOT done THEN INSERT INTO villes_94(cp, nom_ville) VALUES (v_cp, v_ville); END IF; UNTIL done END REPEAT; CLOSE villes_94; END $$ DELIMITER ; Scripts PHP <?php // --- proc_curseur.php header("Content-Type: text/html; charset=UTF-8"); $cn = mysql_connect("localhost", "root", ""); $bd = mysql_select_db("cours", $cn); $ls_sql = "CALL ajout_94()"; $r = mysql_query($ls_sql, $cn); if($r) echo "Transfert réussi"; else echo mysql_error($cn); ?> CF AUSSI LE SUPPORT php_bd_supplements.doc © Pascal Buguet Imprimé le 15 juin 2017 Page 80 PHP et Bases de Données CHAPITRE 10 ANNEXES 10.1 BD ET CHARSET Le problème : les accents du français en fonction du CharSet de la BD. BD UTF8 ISO-8859-1 Select Select Script Header UTF-8 et htmlentities() Header UTF-8 et htmlentities() Header ISO-8859-1 et rien ou htmlentities() UTF8 ISO-8859-1 Insert à partir d'un formulaire Insert à partir d'un formulaire Header UTF-8 et mysql_query("SET NAMES UTF8"); Header ISO-8859-1 UTF8 ISO-8859-1 Insert à partir d'un fichier Insert à partir d'un fichier Header UTF-8 Header ISO-8859-1 © Pascal Buguet Action Imprimé le 15 juin 2017 Page 81 PHP et Bases de Données 10.1.1 Les tables -- Création de la BD CREATE DATABASE `bd_utf8` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE bd_utf8; --- Structure de la table `villes` de la BD bd_utf8 -DROP TABLE IF EXISTS `villes`; CREATE TABLE IF NOT EXISTS `villes` ( `cp` char(5) NOT NULL, `nom_ville` varchar(45) NOT NULL, PRIMARY KEY (`cp`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; --- Contenu de la table `villes` -INSERT INTO `villes` (`cp`, `nom_ville`) VALUES ('75011', 'Paris 11'), ('24200', 'Sarlat-La Canéda'); -- Création de la BD CREATE DATABASE `bd_iso8859_1` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; USE bd_iso8859_1; --- Structure de la table `villes` de la BD bd_iso8859_1 -DROP TABLE IF EXISTS `villes`; CREATE TABLE IF NOT EXISTS `villes` ( `cp` char(5) COLLATE latin1_general_ci NOT NULL, `nom_ville` varchar(45) COLLATE latin1_general_ci NOT NULL, PRIMARY KEY (`cp`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; --- Contenu de la table `villes` -INSERT INTO `villes` (`cp`, `nom_ville`) VALUES ('75011', 'Paris 11'), ('24200', 'Sarlat-La Canéda'); © Pascal Buguet Imprimé le 15 juin 2017 Page 82 PHP et Bases de Données 10.1.2 Le SELECT <?php //header("Content-Type: text/html; charset=UTF-8"); //header("Content-Type: text/html; charset=ISO-8859-1"); // // // // // ----------- La et Et Il ou BD est UTF8, la table aussi ... le projet aussi avec header UFT-8 ça casse !!! faut mettre htmlentities() !!! bien pas de header et pas de htmlentities !!! // // // // // // // // ----------------------------------- La BD est ISO 8859-1, la table aussi ... le projet en UTF8 --- et avec header UFT-8 --- Et ça casse !!! --- Il faut mettre htmlentities() !!! --- ou bien pas de header et pas de htmlentities !!! --- ou bien header ISO-8859-1 et pas de htmlentities() --- ou bien header ISO-8859-1 et htmlentities() $bd = "bd_utf8"; //$bd = "bd_iso8859_1"; echo "<label>Select $bd</label><br/><br/>"; mysql_connect("localhost","root","") ; mysql_select_db($bd) ; $lsSql = "SELECT * FROM villes" ; $curseur = mysql_query($lsSql) ; while ($enr = mysql_fetch_row($curseur)) { echo $enr[0], "-", htmlentities($enr[1]), "<br />" ; //echo $enr[0], "-", $enr[1], "<br />" ; } ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 83 PHP et Bases de Données 10.1.3 Le DELETE <?php if(isSet($_REQUEST["tb_cp"])) { $cp = $_REQUEST["tb_cp"] ; $bd = "bd_utf8"; //$bd = "bd_iso8859_1"; echo "<label>DELETE $bd</label><br/><br/>"; mysql_connect("localhost","root","") or die ("Connexion impossible<br />") ; mysql_select_db($bd) or die("S&eacute;lection de base en &eacute;chec<br />") ; $resultat = mysql_query("DELETE FROM villes WHERE CP ='$cp'") ; if($resultat) print (mysql_affected_rows() . " enregistrement supprim&eacute;<br />") ; else print ("<br /><strong>Erreur</strong> " . mysql_errno() . " : " . mysql_error() . "<br />") ; } ?> <form action="" method="get"> <label>Cp : </label><input type="text" name="tb_cp" value="94230" /> <input type="submit" /> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 84 PHP et Bases de Données 10.1.4 Les INSERTS Sur la base UTF-8. <?php header("Content-Type: text/html; charset=UTF-8"); if(isSet($_REQUEST["tb_cp"])) { $cp = $_REQUEST["tb_cp"] ; $ville = addslashes($_REQUEST["tb_nom_ville"]) ; mysql_connect("localhost","root","") or die ("Connexion impossible<br />") ; mysql_select_db("bd_utf8") or die("S&eacute;lection de base en &eacute;chec<br />") ; mysql_query("SET NAMES UTF8"); $lsInsert = "INSERT INTO villes(cp, nom_ville) VALUES('$cp', '$ville')" ; $resultat = mysql_query($lsInsert) ; if($resultat) print (mysql_affected_rows() . " enregistrement ins&eacute;r&eacute;<br />") ; else print ("<br /><strong>Erreur</strong> " . mysql_errno() . " : " . mysql_error() . "<br />") ; } ?> <label>Insert UTF-8</label><br/> <form action="" method="get"> <label>Cp : </label><input type="text" name="tb_cp" value="94230" /> <label>Ville : </label><input type="text" name="tb_nom_ville" value="StMand&eacute;" /> <input type="submit" /> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 85 PHP et Bases de Données Sur la base ISO-8859-1 <?php header("Content-Type: text/html; charset=ISO-8859-1"); if(isSet($_REQUEST["tb_cp"])) { $cp = $_REQUEST["tb_cp"] ; $ville = addslashes($_REQUEST["tb_nom_ville"]) ; mysql_connect("localhost","root","") or die ("Connexion impossible<br />") ; mysql_select_db("bd_iso8859_1") or die("S&eacute;lection de base en &eacute;chec<br />") ; $lsInsert = "INSERT INTO villes(cp, nom_ville) VALUES('$cp', '$ville')" ; $resultat = mysql_query($lsInsert) ; if($resultat) print (mysql_affected_rows() . " enregistrement ins&eacute;r&eacute;</strong><br />") ; else print ("<br /><strong>Erreur</strong> " . mysql_errno() . " : " . mysql_error() . "<br />") ; } ?> <label>Insert ISO-8859</label><br/> <form action="" method="get"> <label>Cp : </label><input type="text" name="tb_cp" value="94230" /> <label>Ville : </label><input type="text" name="tb_nom_ville" value="StMand&eacute;" /> <input type="submit" /> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 86 PHP et Bases de Données 10.1.5 Insertion à partir d'un fichier Le fichier : villes.txt 56160;Guémené Sur Scorff Le script <?php header("Content-Type: text/html; charset=UTF-8"); //header("Content-Type: text/html; charset=ISO-8859-1"); $tFichier = file("villes.txt"); $enr = $tFichier[0]; $champs = explode(";", $enr); $cp $ville = $champs[0] ; = addslashes($champs[1]) ; $bd = "bd_utf8"; //$bd = "bd_iso8859_1"; mysql_connect("localhost","root","") or die ("Connexion impossible<br />") ; mysql_select_db($bd) or die("S&eacute;lection de base en &eacute;chec<br />") ; $lsDelete = "DELETE FROM villes WHERE cp = '56160'"; $resultat = mysql_query($lsDelete) ; if($resultat) print (mysql_affected_rows() . " enregistrement supprim&eacute;<br />") ; else print ("<br /><strong>Erreur</strong> " . mysql_errno() . " : " . mysql_error() . "<br />") ; //mysql_query("SET NAMES UTF8"); // --- Surtout pas !!! $lsInsert = "INSERT INTO villes(cp, nom_ville) VALUES('$cp', '$ville')" ; $resultat = mysql_query($lsInsert) ; if($resultat) print (mysql_affected_rows() . " enregistrement ins&eacute;r&eacute;<br />") ; else print ("<br /><strong>Erreur</strong> " . mysql_errno() . " : " . mysql_error() . "<br />") ; ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 87 PHP et Bases de Données 10.2 TABLEAU DES FONCTIONS MYSQL_ Fonctions Lien = mysql_connect(hote , ut , mot de passe) Booléen = mysql_select_db(nom de la BD , lien) Curseur = mysql_query(requete , lien) Enr = mysql_fetch_row(curseur) Int = mysql_free_result(curseur) Int = mysql_close(lien) int = mysql_errno(lien) String = mysql_error(lien) Description Se connecter à un serveur de bases de données Sélectionner une base de données Exécuter une requête qui renvoie un curseur Récupérer un enregistrement dans un tableau ordinal. C'est-à-dire que l'on récupère les valeurs des champs via un index. Récupère un enregistrement dans un tableau associatif. C'est-à-dire que l'on récupère les valeurs des champs soit via le nom de champ. Récupère un enregistrement dans un tableau soit ordinal soit associatif. Le type peut être MYSQL_BOTH, MYSQL_ASSOC, MYSQL_NUM. Par défaut c'est MYSQL_BOTH c'est-à-dire que l'on peut récupérer les valeurs des champs soit via un index, soit via le nom du champ. Fermer un curseur Fermer la connexion Récupère le numéro d'erreur Récupère le message d'erreur int = mysql_num_fields(curseur) string = mysql_field_name(curseur, i) Récupère le nombre de champs d'un curseur Récupère le nom du champ courant d'un curseur Enr = mysql_fetch_assoc(curseur) Enr = mysql_fetch_array(curseur [, type de tableau]) Curseur = mysql_list_tables("BD", "Serveur") Curseur = mysql_query("SHOW TABLES", lien) Récupère dans un curseur la liste des tables de la BD passée en paramètre. Obsolète. Préférez la solution suivante. Récupère dans un curseur la liste des tables de la BD passée en paramètre. liste_champs = mysql_list_fields("BD","nom_de_table") Récupère les noms des champs d'une table. Obsolète. Préférez la solution suivante. Curseur = mysql_query("SHOW COLUMNS FROM table", lien) Récupère les noms des champs d'une table. String = mysql_field_type(curseur, i) Enr = mysql_fetch_array(curseur) String = mysql_field_flags(curseur, i) Récupère le type du champ courant Récupère un enregistrement dans un tableau Renvoie une chaîne de caractères contenant les caractéristiques d'une colonne (primary_key, not_null, auto_increment,…). Chaque valeur est séparée par un espace (à exploser). String = mysql_result(curseur, numéroLigne, champ) Int = mysql_num_rows(curseur) int = mysql_field_len(curseur, rang du champ) Renvoie la valeur d'un champ Renvoie le nombre d'enregistrements d'un curseur Renvoie la taille du champ © Pascal Buguet Imprimé le 15 juin 2017 Page 88 PHP et Bases de Données 10.3 JOINTURES MULTI-BASES Il est possible de faire simplement des jointures multi-bases avec PHP et MySQL. Admettons que l'on ait une base Cours qui possède la table Villes et la base Librairie qui contienne la table Auteurs (avec CP comme clé étrangère). Les deux tables ont en commun la colonne cp codée dans le même jeu de caractères. Il suffit de se connecter au serveur, de sélectionner une base et de requêter sur la base par défault pour une table et de préfixer l'autre nom de table par son nom de base pour que cela fonctionne ou même sans select_db mais en préfixant les deux tables. <?php header("Content-Type: text/html; charset=UTF-8"); $lien = mysql_connect("localhost","root",""); //mysql_select_db("librairie", $lien); $lsSql = "SELECT a.nom_auteur , v.nom_ville FROM librairie.auteurs a , cours.villes v WHERE a.cp = v.cp"; mysql_query("SET NAMES UTF8"); $curseur = mysql_query($lsSql, $lien); if(!$curseur) die(mysql_error($lien)); while($enr = mysql_fetch_row($curseur)) { echo $enr[0] . "-" . $enr[1] . "<br />"; } ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 89 PHP et Bases de Données 10.4 MYSQL_INSERT_ID ET TRANSACTIONS Objectif Utilisez la fonction mysql_insert_id pour récupérer la dernière valeur de l'auto_increment dans une transaction avec l'ajout de plusieurs enregistrements (cdes et ligcdes). © Pascal Buguet Imprimé le 15 juin 2017 Page 90 PHP et Bases de Données <?php // --- SET AUTOCOMMIT=0 dans my.ini header("Content-Type: text/html; charset=UTF-8"); // --- Connexion $lien = mysql_connect("localhost","root",""); mysql_select_db("cours",$lien); // --------------------------------function creerListe($asNomListe, $asTable, $asColValue, $asColAffiche, $asValueAncienne, $lien) // --------------------------------{ // --- Construction de l'ordre SQL $lsSelect = "SELECT $asColValue, $asColAffiche FROM $asTable ORDER BY $asColAffiche"; $curseur = mysql_query($lsSelect, $lien) ; $lsSelectHTML = "<select name='$asNomListe'>\n" ; $lsSelectHTML .= "<option value=''>-Choisissez-</option>\n"; while($enr = mysql_fetch_row($curseur)) { if($asValueAncienne==$enr[0]) $lsSelectHTML .= "<option selected='selected' value='$enr[0]'>$enr[1]</option>\n"; else $lsSelectHTML .= "<option value='$enr[0]'>$enr[1]</option>\n"; } $lsSelectHTML .= "</select>\n" ; return $lsSelectHTML; } ?> <?php if(isSet($_GET["lb_clients"])) { // --- DEBUT DE LA TRANSACTION mysql_query("BEGIN", $lien); if(mysql_errno($lien) != 0) die(mysql_error($lien)); // --- INSERTION DE LA COMMANDE $dateCde = date('Y-m-d'); $idClient = $_GET["lb_clients"]; $insertCdes = "INSERT INTO cdes(date_cde, id_client) VALUES('$dateCde', $idClient)"; mysql_query($insertCdes, $lien); if(mysql_errno($lien) != 0) die(mysql_error($lien)); // --- RECUPERATION DU CODE COMMANDE $id_cde = mysql_insert_id($lien); // --- INSERTION DES LIGNES DE COMMANDE $id_produit = $_GET["lb_produits_1"]; $qte = $_GET["qte_1"]; $insertLigcdes = "INSERT INTO ligcdes(id_cde, id_produit, qte) VALUES ($id_cde, $id_produit, $qte)"; // --- VALIDATION/INVALIDATION //mysql_query("ROLLBACK", $lien); mysql_query("COMMIT", $lien); if(mysql_errno($lien) != 0) die(mysql_error($lien)); mysql_query($insertLigcdes, $lien); $id_produit = $_GET["lb_produits_2"]; $qte = $_GET["qte_2"]; $insertLigcdes = "INSERT INTO ligcdes(id_cde, id_produit, qte) VALUES ($id_cde, $id_produit, $qte)"; mysql_query($insertLigcdes, $lien); } © Pascal Buguet Imprimé le 15 juin 2017 Page 91 PHP et Bases de Données ?> <form action="" method="get"> <label>Client </label> <?php if(isSet($_GET["lb_clients"])) $lb_clients = $_GET["lb_clients"]; echo creerListe("lb_clients", "clients", "id_client", "nom", $lb_clients, $lien); ?> <table border="1"> <tr><td>Designation</td><td>Quantité</td></tr> <tr> <td> <?php if(isSet($_GET["lb_produits_1"])) $lb_produits_1 = $_GET["lb_produits_1"]; echo creerListe("lb_produits_1", "produits", "id_produit", "designation", $lb_produits_1, $lien); ?> </td> <td><input name="qte_1" type="text" value="1"></td> </tr> <tr> <td> <?php if(isSet($_GET["lb_produits_2"])) $lb_produits_2 = $_GET["lb_produits_2"]; echo creerListe("lb_produits_2", "produits", "id_produit", "designation", $lb_produits_2, $lien); ?> </td> <td><input name="qte_2" type="text" value="1"></td> </tr> </table> <input type="submit" value="Valider"> </form> © Pascal Buguet Imprimé le 15 juin 2017 Page 92 PHP et Bases de Données 10.5 UN PETIT MOTEUR DE RECHECHE Objectif Rechercher un texte dans la BD (Dans tous les champs de toutes les tables). Il faut dynamiquement générer des SELECT du type : SELECT COUNT(*) FROM villes WHERE nom_ville LIKE = '%PARIS%'; SELECT COUNT(*) FROM clients WHERE nom LIKE = '%PARIS%'; On extrait la liste des tables de la BD (SHOW TABLES …). Pour chaque table on extrait la liste des champs (SHOW COLUMNS …). On compose dynamiquement l'ordre SELECT : $lsSql = "SELECT COUNT(*) FROM $table WHERE $champ LIKE UPPER('%$mot%')" ; © Pascal Buguet Imprimé le 15 juin 2017 Page 93 PHP et Bases de Données Script <form action="" method="get"> Mot <input type="text" name="mot" value="paris" /> <input type="submit" value="Valider" name="cb_valider" /> </form> <?php if(isSet($_GET["cb_valider"])) { $lien = mysql_connect("localhost","root",""); mysql_select_db("cours", $lien); $mot = strToUpper($_GET['mot']); // --- Liste de tables $curseurTables = mysql_query("SHOW TABLES FROM cours"); while($enrTables = mysql_fetch_row($curseurTables)) { $table = $enrTables[0]; // --- Liste des champs $curseurChamps = mysql_query("SHOW COLUMNS FROM $table"); while($enrChamps = mysql_fetch_row($curseurChamps)) { $champ = $enrChamps[0]; $lsSql = "SELECT COUNT(*) FROM $table WHERE UPPER($champ) LIKE '%$mot%'" ; $curseur = mysql_query($lsSql, $lien); $enr = mysql_fetch_row($curseur); if($enr[0] > 0) echo "Valeur <strong>$mot</b> ", $enr[0], "</strong> fois dans <strong>$champ</strong> de <strong>$table</strong><br />"; } } } ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 94 PHP et Bases de Données 10.6 UN FORUM © Pascal Buguet Imprimé le 15 juin 2017 Page 95 PHP et Bases de Données 10.7 BD 2 CSV Objectif : Une table vers un fichier CSV <?php header("Content-Type: text/html; charset=UTF-8"); // --- Connexion à la BD mysql_connect("localhost", "root", ""); mysql_select_db("cours"); $lsTable = "clients"; $fichier = "$lsTable.csv"; // --- Création du fichier $canal = fopen($fichier, "w") ; // --- Requête SQL $curseur = mysql_query("SELECT * FROM $lsTable"); // --- Boucle sur le curseur while ($enr = mysql_fetch_row($curseur)) { $lsLigne = ""; // --- Boucle sur les champs foreach($enr as $champ) { // --- Composition de la ligne $lsLigne .= "$champ;"; } // --- On enlève le dernier caractère $lsLigne = substr($lsLigne, 0, strlen($lsLigne) - 1); // --- Ajout d'un enregistrement avec RC en plus fputs($canal, $lsLigne . "\r\n") ; } // --- Fermeture du fichier fclose($canal) ; print("Fichier $fichier créé") ; ?> © Pascal Buguet Imprimé le 15 juin 2017 Page 96 PHP et Bases de Données 10.8 BD – CREATION DES TABLES --- Base de données: cours -CREATE DATABASE cours DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE cours; SET FOREIGN_KEY_CHECKS=0; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- Structure de la table 'ligcdes' DROP TABLE IF EXISTS ligcdes; CREATE TABLE IF NOT EXISTS ligcdes ( id_cde int(5) NOT NULL, id_produit int(5) NOT NULL, qte int(5) NOT NULL, PRIMARY KEY (id_cde,id_produit), KEY FK_ligcdes_id_produit (id_produit) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; DROP TABLE IF EXISTS cdes; CREATE TABLE IF NOT EXISTS cdes ( id_cde int(5) NOT NULL auto_increment, date_cde date NOT NULL, id_client int(5) NOT NULL, PRIMARY KEY (id_cde), KEY FK_cdes_client (id_client) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Structure de la table 'clients' DROP TABLE IF EXISTS clients; CREATE TABLE IF NOT EXISTS clients ( id_client int(5) NOT NULL auto_increment, nom varchar(50) NOT NULL, prenom varchar(50) NOT NULL, adresse varchar(100) default NULL, date_naissance date default NULL, cp char(5) NOT NULL, PRIMARY KEY (id_client), KEY Index_cp (cp) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; -- Structure de la table 'vendeurs_villes' DROP TABLE IF EXISTS vendeurs_villes; CREATE TABLE IF NOT EXISTS vendeurs_villes ( id_vendeur int(10) unsigned NOT NULL auto_increment, cp varchar(5) NOT NULL, Date_debut date NOT NULL default '0000-00-00', date_fin date NOT NULL default '0000-00-00', PRIMARY KEY (id_vendeur,cp,Date_debut), KEY cp (cp) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; -- Structure de la table 'ventes' DROP TABLE IF EXISTS ventes; CREATE TABLE IF NOT EXISTS ventes ( © Pascal Buguet Imprimé le 15 juin 2017 Page 97 PHP et Bases de Données id_vendeur int(10) unsigned NOT NULL default '0', id_produit int(10) unsigned NOT NULL default '0', vente int(10) unsigned NOT NULL default '0', date_vente date default NULL, PRIMARY KEY (id_vendeur,id_produit,vente) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; -- Structure de la table 'ventes_croisees' DROP TABLE IF EXISTS ventes_croisees; CREATE TABLE IF NOT EXISTS ventes_croisees ( nom_vendeur varchar(50) NOT NULL, designation varchar(50) NOT NULL, vente int(10) unsigned NOT NULL default '0', PRIMARY KEY (nom_vendeur,designation) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; -- Structure de la table 'vendeurs' DROP TABLE IF EXISTS vendeurs; CREATE TABLE IF NOT EXISTS vendeurs ( id_vendeur int(10) unsigned NOT NULL auto_increment, nom varchar(45) NOT NULL, chef int(10) unsigned NOT NULL default '0', cp char(5) NOT NULL, PRIMARY KEY (id_vendeur), KEY FK_vendeurs_cp (cp) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; -- Structure de la table 'villes' DROP TABLE IF EXISTS villes; CREATE TABLE IF NOT EXISTS villes ( cp varchar(5) NOT NULL, nom_ville varchar(50) NOT NULL, site varchar(50) default NULL, photo varchar(50) default NULL, id_pays char(3) NOT NULL, PRIMARY KEY (cp), KEY Index_id_pays (id_pays) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; -- Structure de la table 'pays' DROP TABLE IF EXISTS pays; CREATE TABLE IF NOT EXISTS pays ( id_pays char(3) NOT NULL, nom_pays varchar(50) NOT NULL, PRIMARY KEY (id_pays) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; -- Structure de la table 'produits' DROP TABLE IF EXISTS produits; CREATE TABLE IF NOT EXISTS produits ( id_produit int(5) NOT NULL auto_increment, designation varchar(50) NOT NULL, prix double(7,2) default NULL, qte_stockee int(5) default NULL, photo VARCHAR(50) default '', PRIMARY KEY (id_produit) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Structure de la table 'utilisateurs' DROP TABLE IF EXISTS utilisateurs; CREATE TABLE IF NOT EXISTS utilisateurs ( ut varchar(50) NOT NULL, © Pascal Buguet Imprimé le 15 juin 2017 Page 98 PHP et Bases de Données mdp varchar(50) NOT NULL, e_mail varchar(50) NOT NULL, qualite varchar(50) NOT NULL, PRIMARY KEY (ut), UNIQUE KEY e_mail (e_mail) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- Contraintes pour les tables exportes ---- Contraintes pour la table cdes -ALTER TABLE cdes ADD CONSTRAINT cdes_ibfk_1 FOREIGN KEY (id_client) REFERENCES clients (id_client) ON DELETE CASCADE ON UPDATE CASCADE; --- Contraintes pour la table clients -ALTER TABLE clients ADD CONSTRAINT clients_ibfk_1 FOREIGN KEY (cp) REFERENCES villes (cp) ON DELETE CASCADE ON UPDATE CASCADE; --- Contraintes pour la table ligcdes -ALTER TABLE ligcdes ADD CONSTRAINT ligcdes_ibfk_2 FOREIGN KEY (id_produit) REFERENCES produits (id_produit) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT ligcdes_ibfk_1 FOREIGN KEY (id_cde) REFERENCES cdes (id_cde) ON DELETE CASCADE ON UPDATE CASCADE; --- Contraintes pour la table vendeurs -ALTER TABLE vendeurs ADD CONSTRAINT vendeurs_ibfk_1 FOREIGN KEY (cp) REFERENCES villes (cp) ON DELETE CASCADE ON UPDATE CASCADE; --- Contraintes pour la table vendeurs_villes -ALTER TABLE vendeurs_villes ADD CONSTRAINT vendeurs_villes_ibfk_2 FOREIGN KEY (id_vendeur) REFERENCES vendeurs (id_vendeur) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT vendeurs_villes_ibfk_1 FOREIGN KEY (cp) REFERENCES villes (cp) ON DELETE CASCADE ON UPDATE CASCADE; --- Contraintes pour la table ventes -ALTER TABLE ventes ADD CONSTRAINT ventes_ibfk_1 FOREIGN KEY (id_vendeur) REFERENCES vendeurs (id_vendeur) ON DELETE CASCADE ON UPDATE CASCADE; --- Contraintes pour la table villes -ALTER TABLE villes ADD CONSTRAINT villes_ibfk_1 FOREIGN KEY (id_pays) REFERENCES pays (id_pays) ON DELETE CASCADE ON UPDATE CASCADE; © Pascal Buguet Imprimé le 15 juin 2017 Page 99 PHP et Bases de Données 10.9 BD – AJOUT DES ENREGISTREMENTS -- Contenu de la table pays INSERT INTO pays (id_pays, nom_pays) VALUES ('033', 'France'), ('035', 'Angleterre'), ('039', 'Italie'); -- Contenu de la table villes INSERT INTO villes (cp, nom_ville, site, photo, id_pays) VALUES ('13000', 'Marseille', 'www.ma.net', NULL, '033'), ('24200', 'Sarlat', NULL, NULL, '033'), ('24300', 'Carsac', NULL, NULL, '033'), ('24400', 'Aillac', NULL, NULL, '033'), ('59000', 'Lille', 'www.lille.fr', 'lille.jpg', '033'), ('69000', 'Lyon', 'www.lyon.fr', 'lyon.jpg', '033'), ('75011', 'Paris 11', 'www.paris.fr', 'paris.jpg', '033'), ('75012', 'Paris 12', 'www.paris.fr', 'paris.jpg', '033'), ('75019', 'Paris XIX', 'www.paris.fr', 'paris.jpg', '033'), ('78000', 'Versailles', NULL, NULL, '033'), ('94100', 'Vincennes', NULL, NULL, '033'), ('94200', 'St Mand', NULL, NULL, '033'), ('99391', 'ROME', NULL, NULL, '039'), ('99392', 'MILAN', NULL, NULL, '039'); -- Contenu de la table clients INSERT INTO clients (id_client, nom, prenom, adresse, date_naissance, cp) VALUES (1, 'Buguet', 'Pascal', NULL, '1955-10-03', '75011'), (2, 'Serra', 'MH', NULL, '1958-11-27', '75019'), (4, 'Buguet', 'MJ', NULL, '1948-08-22', '75011'), (5, 'Fassiola', 'Annabelle', NULL, '1985-05-10', '75011'), (6, 'Roux', 'Françoise', NULL, '1950-10-10', '59000'), (7, 'Tintin', 'Albert', NULL, NULL, '75011'), (8, 'Sordi', 'Alberto', NULL, NULL, '99391'), (9, 'Muti', 'Ornella', NULL, NULL, '99392'), (10, 'Milou', 'Le chien', NULL, NULL, '75019'), (11, 'Tournesol', 'Bruno', NULL, NULL, '75011'), (17, 'Roberts', 'Julia', NULL, '1965-10-03', '75011'); -- Contenu de la table cdes INSERT INTO cdes (id_cde, date_cde, id_client) VALUES (1, '2005-10-03', 1), (2, '2005-10-10', 2), (3, '2005-11-01', 1), (4, '2000-11-01', 1), (5, '2000-12-10', 2), (6, '2009-11-01', 1), (7, '2009-11-01', 1), (8, '2009-11-01', 1); -- Contenu de la table produits INSERT INTO produits (id_produit, designation, prix, qte_stockee, photo) VALUES (1, 'Evian', 1.81, 10, 'evian.jpg'), (2, 'Badoit', 1.93, 10, 'badoit.jpg'), (3, 'Graves', 13.20, 10, 'graves.jpg'), (4, 'Ruinard', 110.00, 10, 'ruinard.jpg'), (5, 'Dom Prignon', 165.00, 10, 'dom.jpg'), (7, 'Picpoul', 5.00, 500, NULL), (8, 'Picmal', 5.00, 10, NULL), © Pascal Buguet Imprimé le 15 juin 2017 Page 100 PHP et Bases de Données (9, 'Coca', 1.00, 10, 'coca.jpg'); -- Contenu de la table ligcdes INSERT (1, 1, (1, 2, (2, 1, (3, 1, (3, 2, (3, 3, (4, 1, (5, 4, (6, 1, (6, 2, (6, 3, (6, 4, (7, 4, (8, 1, INTO ligcdes (id_cde, id_produit, qte) VALUES 2), 3), 2), 6), 2), 1), 5), 10), 1), 1), 1), 1), 100), 10); -- Contenu de la table vendeurs INSERT INTO vendeurs (id_vendeur, nom, chef, cp) VALUES (1, 'Lucky', 0, '75011'), (2, 'Dalton', 1, '75012'), (3, 'Mickey', 1, '75012'), (4, 'Donald', 2, '75011'); -- Contenu de la table vendeurs_villes INSERT INTO vendeurs_villes (id_vendeur, cp, Date_debut, date_fin) VALUES (1, '75011', '2006-01-01', '2006-12-31'), (1, '75011', '2007-01-01', '2007-12-31'), (2, '75011', '2006-01-01', '2006-12-31'), (2, '75012', '2007-01-01', '2007-12-31'), (3, '75011', '2007-01-01', '2007-12-31'), (3, '75012', '2006-01-01', '2006-12-31'); -- Contenu de la table ventes INSERT (1, 1, (1, 2, (2, 1, (2, 2, (2, 3, INTO ventes (id_vendeur, id_produit, vente, date_vente) VALUES 20, '2007-04-16'), 100, '2007-04-16'), 1, '2007-04-16'), 10, '2008-04-16'), 5, '2008-04-16'); -- Contenu de la table ventes_croisees INSERT INTO ventes_croisees (nom_vendeur, designation, vente) VALUES ('Casta', 'Evian', 20), ('Casta', 'Graves', 5), ('Haddock', 'Badoit', 1), ('Haddock', 'Evian', 1), ('Haddock', 'Graves', 10), ('Tintin', 'Badoit', 5), ('Tintin', 'Evian', 10), ('Tintin', 'Graves', 10); -- Contenu de la table utilisateurs INSERT INTO utilisateurs (ut, mdp, e_mail, qualite) VALUES ('a', 'f', '[email protected]', 'FO'), ('p', 'b', '[email protected]', 'BO'); © Pascal Buguet Imprimé le 15 juin 2017 Page 101