H2 - P2019

Développement web

Cours 23 - 2016-02-08

PHP

Base de données

Qu'est-ce que MySQL ?

  • Système de Gestion de Bases de Données Relationnelles (SGBDR)
  • Installé sur le serveur
  • Utilisable en PHP
  • Très répandu
  • Inventé par Michael Widenius
    (SQL = Structured Query Language / My = ?)
  • Une base de données contient des tables
  • Ces tables contiennent des lignes
  • Ces lignes contiennes des cellules / colonnes
  • Ces colonnes contiennes des données

L'ID est presque toujours présent dans chaque table

C'est un nombre qui commence à 1 et qui s'incrémente à chaque nouvelle ligne

Il permet d'identifier de façon unique chaque ligne

Chaque colonne doit être d'un type particulier

Il en existe plusieurs

  • INT : Un entier
  • TINYINT : Un petit entier
  • FLOAT : Un nombre à virgule
  • DATE : Une date au format YYYY-MM-DD
  • DATETIME : Une date au format YYYY-MM-DD HH:MM:SS
  • VARCHAR : Un texte court
  • TEXT : Un texte long
  • BOOLEAN : Un booléen (équivalent au TINYINT)
  • ENUM : Un choix parmi une liste

Et encore plein d'autres

Pour les colonnes de type text ou varchar, il faut spécifier l'encodage

L'encodage est important à la fois pour sauvegarder certains caractères, mais aussi pour faire des recherches sur la base de donnée

Les plus utilisés sont :

  • utf8_unicode_ci : Permet de sauvegarder la majorité des caractères
  • utf8_general_ci : Permet de sauvegarder la majorité des caractères et ne fait pas la différence entre certains caractères durant une recherche (é = è = ê = e par exemple)

PhpMyAdmin

  • Interface permettant de manipuler les données
  • Pas besoin de maîtriser SQL
  • Installé avec WAMP et MAMP
  • Existe en plusieurs versions
  • Utile pour la maintenance (créer/modifier des bases de données, vérifier/modifier des données, etc.)

Mac :

Windows :

  • Pour accéder à une base de données, il faut s'y connecter
  • MySQL fonctionne avec des comptes
  • Chaque compte possède des droits spécifiques (Ex : créer des tables, créer des colonnes, supprimer des lignes, ...)
  • Le compte par défaut, appelé root, possède tous les droits

Comme nous sommes en local, nous allons utiliser le compte root

Il est préférable, une fois en ligne, d'utiliser un compte avec moins de droits

Pour rajouter un compte, dans PhpMyAdmin, allez dans l'onglet Utilisateurs puis cliquez sur Ajouter un utilisateur

Créez votre première base de données dans l'onglet Bases de données

Appellez-la comme vous le voulez et choissez utf8_general_ci (lettres et underscores uniquement)

Rentrez dans la base de données (menu de gauche)

Créez une nouvelle table nommée users avec 5 colonnes

Cliquez sur Exécuter

Dans le formulaire qui apparait, chacune des lignes correspond aux colonnes de la base de données

Remplissez-les comme cela :

Essayez :

  • Ajouter des lignes
  • Les modifier
  • Les supprimer
  • Rentrer de mauvaises valeurs
    (trop de caractères, pas le bon type, etc.)
  • Explorer les différents onglets

Lorsque l'on utilise PhpMyAdmin, celui-ci effectue des requêtes SQL

Ce sont des requêtes indiquant à la base de donnée quoi faire (ajoute une donnée, supprime cette ligne, créé une colonne, ...)

Nous allons apprendre à effectuer ces requêtes SQL nous-même

Cliquez sur le petit icône SQL dans le menu de gauche pour ouvrir une popup

Essayez les requêtes SQL suivantes


INSERT INTO users (name, age, gender) 
VALUES ('bruno', 26, 'male')
                    

UPDATE users SET name = 'bueno' WHERE id = 1
                    

SELECT * FROM users
                    

SELECT id, age FROM users WHERE name = 'bueno'
                    

DELETE FROM users WHERE id = 1
  • Les principales requêtes SQL sont INSERT INTO, UPDATE, DELETE, SELECT
  • Les retours à la ligne et les espaces n'ont aucune importance
  • Les chaînes de caractères doivent être entre guillemets (simples ou doubles)
  • Les commandes (SELECT, WHERE, FROM, ...) n'ont pas besoin d'être en majuscules
  • Les noms des bases de données, tables et colonnes doivent être composés de lettres et underscores uniquement

En PHP

Il faut se connecter à la base de données

L'ancienne méthode :


$db = mysql_connect('localhost','user','password');
mysql_select_db('database');

Nouvelle méthode via PDO


try
{
    $pdo = new PDO('mysql:dbname=hetic-p2019-first;host=localhost','root','root');
}
catch (PDOException $e)
{
    die('error');
}

Le Try ... Catch permet de gérer les erreurs

Si PDO n'arrive pas à se connecter, la fonction catch sera éxecuté

Avantages de PDO

  • Plus sécurisé
  • Plus logique
  • Plus facile (presque)

Version plus complète

// Connexion variables
define('DB_HOST','localhost');
define('DB_NAME','hetic-p2019-first');
define('DB_USER','root');
define('DB_PASS','root'); // '' par défaut sur windows

try
{
    // Try to connect to database
    $pdo = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PASS);

    // Set fetch mode to object
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_OBJ);
}
catch (Exception $e)
{
    // Failed to connect
    die('Cound not connect');
}

// Delete next line if working
die('All good ;)');

Nous avons donc maintenant une variable $pdo qui gère la connexion à la base de données

C'est un objet qui possède plusieurs méthodes permettant d'intéragir avec la base de données

Cette variable aurait pu s'appeler autrement
($db, $connect, ...)

Récupérer des données avec query()

La méthode query() renvoie un objet de type PDOStatement.
C'est la préparation de la requête, mais sans l'exécuter

Cet objet possède des méthodes :

  • fetchAll() : renvoie un tableau de toutes les lignes
  • fetch() : renvoie chaque ligne à chaque appel

Avec fetchAll()


// Préparation de la requête
$query = $pdo->query('SELECT * FROM users');

// Éxécution de la requête et récupération des données
$users = $query->fetchAll();

// Affichage des données
echo '<pre>';
print_r($users);
echo '</pre>';

Avec fetch() dans un while


// Préparation de la requête
$query = $pdo->query('SELECT * FROM users');

// Boucle exécuté tant que le fetch renvoie quelque chose
while($user = $query->fetch())
{
    // Affichage de la donnée
    echo '<pre>';
    print_r($user);
    echo '</pre>';
}

$user correspond à chaque ligne

Arrivée à la fin, fetch() va renvoyer false et le while va donc s'arrêter

Par défaut, PDO renvoie un tableau dans un format étrange

Dans notre exemple, la ligne de code juste après la création de PDO nous permet de le forcer à renvoyer des objets


$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_OBJ);

Modifier, supprimer et ajouter des données avec exec()

La méthode exec() exécute une requête SQL et renvoie le nombre de lignes affectées

Avant d'écrire une requête SQL dans exec() il est conseillé de la tester directement dans PhpMyAdmin

En PHP, essayez les requêtes suivantes


// Ajoute une ligne dans la table users
$exec = $pdo->exec('INSERT INTO users (name,age,gender) VALUES (\'bruno\',26,\'male\')');

echo '<pre>';
var_dump($exec);
echo '</pre>';
                    

// Met à jour une ligne dans la table users
$exec = $pdo->exec('UPDATE users SET name = \'toto\' WHERE id = 2');

echo '<pre>';
var_dump($exec);
echo '</pre>';

Si la requête s'est bien exécutée, elle renvoie le nombre de ligne affectées

Si la requête a échouée, elle renvoie false

Les requêtes SQL deviennent vite trop compliquées comme ci-dessous


$exec = $pdo->exec('INSERT INTO users (name,age,gender) VALUES (\''.$data['name'].'\','.$data['age'].',\''.$data['gender'].'\')');

Le mélange PHP/SQL rend trop difficile la distinction des différents éléments dans la requête SQL

La solution : PDO Prepare


// Prépare la requête
$prepare = $pdo->prepare('INSERT INTO users (name,age,gender) VALUES (:name,:age,:gender)');

// Bind les valeur
$prepare->bindValue(':name',$data['name']);
$prepare->bindValue(':age',$data['age']);
$prepare->bindValue(':gender',$data['gender']);

// Execute la requête
$exec = $prepare->execute();

Ce n'est pas plus rapide à écrire, mais c'est beaucoup plus clair


$prepare = $pdo->prepare('INSERT INTO users (name,age,gender) VALUES (:name,:age,:gender)');

/* ... */

La méthode prepare() prépare la requête SQL

Les différentes valeurs sont remplacées par des identifiants avec le signe : devant


/* ... */

$prepare->bindValue(':name',$data['name']);
$prepare->bindValue(':age',$data['age']);
$prepare->bindValue(':gender',$data['gender']);

/* ... */

La méthode bindValue() sur l'objet renvoyé par prepare() permet d'assigner les valeurs à chaque identifiant


/* ... */

$exec = $prepare->execute();

Il ne reste plus qu'à exécuter la requête avec la méthode execute() sur l'objet renvoyé par prepare()

L'object renvoyé par execute() fonctionne comme l'objet renvoyé par exec() ou par query()

Il est possible de binder les valeurs directement dans le execute() en lui passant un tableau associatif

  • Les clés du tableau doivent correspondre aux identifiants dans la requête
  • Il ne doit pas y avoir d'autres clés

// Valeurs
$data = array('name'=>'Tommy','age'=>30,'gender'=>'male');

// Prépare la requête
$prepare = $pdo->prepare('INSERT INTO users (name,age,gender) VALUES (:name,:age,:gender)');

// Execute la requête
$exec = $prepare->execute($data);