Utilisation : requête avec INSERT, UPDATE et DELETE sur MYSQL

Accès à mySQL via PHP avec PDO.

• PHP permet d’interfacer des pages avec de multiples serveurs de base de données, notamment mySQL.
• PHP permet de se connecter à un serveur MYSQL (avec un nom d’utilisateur et un mot de passe) sur une base de données. PHP permet d’exécuter des requêtes SQL sur cette connexion.
• Il existe 2 principaux API interfaces de programmation (abr. API pour Application Programming Interface) : MYSQLI et PDO (abr. PHP DATA OBJECT)
  
• On utilisera de préférence PDO car cette API est compatible avec MYSQL et d’autres SGBD (oracle postgreSQL ….) , cependant elle fonctionne uniquement en mode Objet.
  • http://php.net/manual/fr/mysqlinfo.api.choosing.php
  • http://fmaz.developpez.com/tutoriels/php/comprendre-pdo/

Connexion à un serveur de bases de données MYSQL

• Ajouter dans une page PHP ( page1_tp9.php ) le script ci dessous :
• Modifier les paramètres de connexion :
  • La constante hostname a pour valeur serveurmysql qui est le nom de la machine du serveur mysql que vous voulez utiliser ( localhost sur un ordinateur personnel car le serveur mysql est sur votre machine)
  • La constante username a pour valeur votre login (pour vous connecter à MYSQL)
  • La constante password a pour valeur votre Mot De Passe (jjmm)
  • La constante database a pour valeur le nom de votre base de données
• Remarque : signification DSN, voir documentation http://php.net/manual/fr/ref.pdo-mysql.connection.php

<?php
define("hostname","serveurmysql"); 
define("database","BDD_votreLogin");
define("username","votreLogin"); 
define("password","votreMotDePasse");

$dsn = 'mysql:dbname='.database.';host='.hostname.';charset=utf8';    

$ma_connexion_mysql = new PDO($dsn, username, password);
             // pour afficher les erreurs
$ma_connexion_mysql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
             // pour récupérer le résultat des requêtes SELECT sous forme de tableaux associatifs 
$ma_connexion_mysql->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); 

var_dump($ma_connexion_mysql); // ligne à supprimer
?>

• Pour exécuter le script ci dessus :
  • créer et enregistrer un fichier connexion.php sur votre dossier /nfs1/web/votreLogin/public_html/S1_bdd avec les droits de lecture pour le serveur web
  • chaque consultation du fichier php par un navigateur web retourne un contenu html. Le fichier php est interprété (exécuté) par le serveur web (apache) qui retourne de l’html.
• Résultat possible de ce script sur un navigateur web :

Connexion échouée : SQLSTATE[28000] [1045] Access denied for user 'votreMotLogin'@'localhost' (using password: YES) 

=> une/les constante(s) hostname, username password, database ne sont pas correctes

object(PDO)#1 (0) { } 

=> la connexion est correcte, $ma_connexion_mysql est un objet PDO sur lequel on peut utiliser des méthodes (fonctions associées à l’objet)   la fonction var_dump affiche les informations structurées d’une variable.

• Si la connexion est correcte au serveur mysql, la variable $ma_connexion_mysql est un objet PDO qui permet d’exécuter des requêtes SQL sur le serveur MYSQL

exécution d’une instruction SQL

• Dans la documentation, on peut lire ( réf : http://php.net/manual/fr/pdo.exec.php )
  • PDO::exec() exécute une requête SQL dans un appel d’une seule fonction, cette fonction retourne le nombre de lignes affectées par la requête.
  • PDO::exec() ne retourne pas de résultat pour une requête SELECT.
  • Pour une requête utilisant l’instruction SELECT(TP suivant), utiliser la fonction(méthode) PDO::query().
• Conclusion pour effectuer une requête SQL de type INSERT UPDATE DELETE, utiliser la méthode exec . Pour une requête de type SELECT, utiliser la méthode query

Application : Requête UPDATE, DELETE et INSERT

préparation

• Dans un terminal, connectez vous au serveur mysql .
• à partir des Tps précédents, utiliser le script SQL sur cours-info (à télécharger) ou réaliser un script SQL pour :
  • supprimer la table Etudiant
  • créer la table Etudiant
  • insérer tous les enregistrements de la table Etudiant . (Reprendre le fichier source de la base de données du TP6).
• Exécuter ce script avec la commande : source initEtudiant.sql (exemple)

exemple de script SQL à compléter avec le TP6 :

DROP TABLE IF EXISTS Etudiant;

CREATE TABLE Etudiant (
  idEtudiant INT AUTO_INCREMENT
  , nom VARCHAR(20)
  , adresse VARCHAR(40)
  , ville VARCHAR(20)
  , code_postal INT(5)  ZEROFILL
  , telephone NUMERIC(10) ZEROFILL
  , date_naissance DATE
  , sexe ENUM('F','H')
  , boursier ENUM('O','N')
  , groupe_id INT
  , semestre_id INT
  , note_ue1 NUMERIC(4,2)
  , note_ue2 DECIMAL(4,2)
  , PRIMARY KEY(idEtudiant)
);



INSERT INTO Etudiant (idEtudiant, nom, adresse, ville, code_postal, telephone, date_naissance, sexe, boursier, groupe_id, semestre_id, note_ue1, note_ue2) VALUES
(1, 'BERNARD', '1 rue sous bois', 'Belfort', 90000, 0384545401, '2000-01-01', 'F', 'N', 1, 1, 10.50, 9.50),
(19, 'CHAVEAUX', '5 rue du chasseur', 'Strasbourg', 68000, 0384545419, '2000-01-01', 'H', 'N', 2, 2, 10.50, 9.50),
(20, 'PRETTOT', '8 rue vilapogo', 'Belfort', 90000, 0384545420, '2000-07-12', 'H', 'O', 2, 2, 13.50, 8.50),

     ....

  // à compléter avec les TPs précédents (tp6)

SELECT idEtudiant, nom, adresse, ville, code_postal, boursier FROM Etudiant;

recommandation pour concaténer en PHP des variables et du texte :

recommandation
documentation

Exemple :

• Tester une requête pour modifier le champ adresse par “IUT de Belfort” pour les étudiants ayant un identifiant supérieur à une valeur. Ces données sont dans des variables : $adresse, $id_etudiant.

<?php 
$adresse="IUT de Belfort";
$id_etudiant=9; 

$ma_commande_SQL ="UPDATE Etudiant SET adresse ='${adresse}' WHERE idEtudiant >= ${id_etudiant};"; 

echo $ma_commande_SQL."<br>"; 

if($ma_connexion_mysql!= NULL){ 
    $nbr_lignes_affectees=$ma_connexion_mysql->exec($ma_commande_SQL); 
    echo "<br> nbre de lignes affectees : ".$nbr_lignes_affectees; 
} 

?>

autres solutions pour concaténer du texte et des variables en PHP :

$ma_commande_SQL ="UPDATE Etudiant SET adresse ='".$adresse."' WHERE idEtudiant >= ".$id_etudiant.";"; 

$ma_commande_SQL ="UPDATE Etudiant SET adresse ='$adresse' WHERE idEtudiant >= $id_etudiant;"; 

• étape 1 : Dans un terminal, recharger le script pour, supprimer, créer la table Etudiant puis insérer tous les enregistrements
• étape 2 : Dans un fichier PHP, recopier le en utilisant les variables ci dessus (le travail est déjà fait !)
• étape 3 : Dans un navigateur WEB, exécuter la requête SQL (chaîne de texte) à l’aide de l’objet PDO sur MYSQL. Il suffit de charger la page PHP qui se trouve sur un serveur Web (pour exécuter le script php).
• étape 4 : Dans un terminal, afficher tous les enregistrements de la table pour vérifier que le script php a bien fonctionné. (exemple : SELECT idEtudiant, nom, adresse, ville, code_postal, boursier FROM Etudiant;)

Exercice 1

• tester une requête pour modifier la ville (ville) par « valdoie», le code postal par « 90300 » et l’adresse pas « 7 rue du chateau » pour l’étudiant ayant l’identifiant 26. Ces données sont dans des variables : $rue, $ville, $code_postal et $id_etudiant

• Après avoir créer une connexion sur la base de données, tester le code ci dessous :

<?php 
$rue='7 rue du chateau'; // pour l'adresse
$ville='Valdoie'; 
$code_postal='90300'; 
$id_etudiant=26; 

$ma_commande_SQL = "à modifier";

echo $ma_commande_SQL."<br>"; 

if($ma_connexion_mysql!= NULL){ 
    $nbr_lignes_affectees=$ma_connexion_mysql->exec($ma_commande_SQL); 
    echo "<br> nbre de lignes affectees : ".$nbr_lignes_affectees; 
} 

?>

• étape 1 : Dans un terminal : Tester une requête SQL qui exécute la requête ci dessus. Puis Recharger le script pour supprimer, créer la table Etudiant puis insérer tous les enregistrements.
• étape 2 : Dans un fichier PHP, écrire en PHP une chaîne de texte qui corresponde à votre requête SQL en utilisant les variables ci dessus
• étape 3 : Dans un navigateur WEB, exécuter la requête SQL (chaîne de texte) à l’aide de l’objet PDO sur MYSQL. Il suffit de charger la page PHP qui se trouve sur un serveur Web (pour exécuter le script php).
• étape 4 : Dans un terminal, afficher tous les enregistrements de la table pour vérifier que le script php a bien fonctionné. (exemple : SELECT idEtudiant, nom, adresse, ville, code_postal, boursier FROM Etudiant;)

Exercice 2 :

sur le même principe

• Les étudiants “Delanoe”, “Rigoulot” respectent les critères pour être boursiers : modifier le critère de bourse de ces étudiants : (mettre le champ boursier à “O”). Utiliser leurs identifiants qui existent dans 3 variables, voici un exemple de début de script :

<?php 
$id_etu_11=15;
$id_etu_13=17;
$boursier='O';

$ma_commande_SQL ="";

• étape 1 : Dans un terminal, tester une requête SQL qui exécute la requête ci dessus, puis afficher tous les enregistrements. Remettre tous les enregistrements à leurs valeurs initiales.
• étape 2 : Dans un nouveau fichier PHP, écrire en PHP une chaîne de texte qui corresponde à votre requête SQL en utilisant les variables ci dessus
• étape 3 : Dans un navigateur WEB, exécuter la requête SQL (chaîne de texte) à l’aide de l’objet PDO sur MYSQL
• étape 4 : Dans un terminal, afficher tous les enregistrements de la table pour vérifier que le script php a bien fonctionné. (exemple : SELECT idEtudiant, nom, adresse, ville, code_postal, boursier FROM Etudiant;)

Exercice 3 :

sur le même principe

• Les étudiants “Zilliox” et “Monnier” se réorientent : supprimer les étudiants de la base de données. Utiliser leurs identifiants, ils sont définis dans 2 variables :

<?php 
$id_etu_3=3;
$id_etu_4=4;

$ma_commande_SQL ="";

• étape 1 : Dans un terminal, tester une requête SQL qui exécute la requête ci dessus, puis afficher tous les enregistrements.
• étape 2 : Dans un nouveau fichier PHP, écrire en PHP une chaîne de texte qui corresponde à votre requête SQL en utilisant les variables ci dessus
• étape 3 : Dans un navigateur WEB, exécuter la requête SQL (chaîne de texte) à l’aide de l’objet PDO sur MYSQL
• étape 4 : Dans un terminal, afficher tous les enregistrements de la table dans un terminal pour vérifier que le script php a bien fonctionné.

Exercice 4 :

• Insérer un étudiant un étudiant dans la base de données :

<?php 
$Nom='dubois';
$Adresse='7 rue du bois';
$Ville ='Belfort';
$Code_Postal ='90000';
$Telephone='0381940000';
$Date_Naissance ='1997-07-27';
$Sexe='H';
$Boursier='N';

$ma_commande_SQL ="";

• Pour les champs “groupe_id”, “semestre_id”, “note_ue1”,“note_ue2” mettre des valeurs “NULL”.

• étape 1 : Dans un terminal, tester une requête SQL qui exécute la requête ci dessus, puis afficher tous les enregistrements.
• étape 2 : Dans un nouveau fichier PHP, écrire en PHP une chaîne de texte qui corresponde à votre requête SQL en utilisant les variables ci dessus
• étape 3 : Dans un navigateur WEB, exécuter la requête SQL (chaîne de texte) à l’aide de l’objet PDO sur MYSQL
• étape 4 : Dans un terminal, afficher tous les enregistrements de la table dans un terminal pour vérifier que le script php a bien fonctionné.