Apprendre PHP & MySQL : Base de Données MySQL

Qu’est-ce qu’une base de données ?

Définition

Une base de données permet de stocker et d’accéder à des données très structurées (bases de données relationnelles) ou moins structurées (NoSQL - orienté Document) et en rapport avec un thème ou une activité. Ces données peuvent être de natures différentes (nombre, chaînes de caractères…). Grossièrement, c’est une sorte de grand tableur Excel organisé.

Une base de données peut être localisée sur une unique machine dans un lieu ou répartie sur plusieurs machines à plusieurs endroits.

Pour fonctionner, une base de données repose sur ce que l’on appelle un Système de Gestion de Base de Données (SGBD). Il s’agit d’un logiciel système se chargeant de stocker, manipuler, gérer et partager des données dans une base de données. Un SGBD est gage de qualité, pérennité et confidentialité des informations stockées, tout en en cachant la complexité des opérations.

Le SGBD joue le rôle d’intermédiaire entre le serveur (où il y a PHP) et la base de données.

MySQL et les SGBD

MySQL est un des plus célèbres Système de Gestion de Base de Données utilisé avec PHP. Ce dernier est dit “relationnel” (SGBDR), cela signifie que les données stockées peuvent être reliées entre elles.

Le SGBD MariaDB est aujourd’hui bien souvent utilisé à la place de MySQL. MariaDB n’est ni plus ni moins qu’un fork communautaire de MySQL (une version plus évoluée). À notre niveau d’utilisation, utiliser l’un ou l’autre n’a aucune importance.

On pourrait citer d’autres SGBD connus comme :

• PostgreSQL (relationnel et objet)
• Oracle Database (relationnel)
• Microsoft SQL Server (relationnel)
• MongoDB (document)
• CouchDB (document)
• Etc…

Anatomie

La classification des données repose sur un modèle de tables et d’attributs.

Une table est un ensemble de données organisées sous forme d’un tableau où :

• Les colonnes correspondent à des entêtes / des caractéristiques, appelées attributs.
• Les lignes correspondent à des enregistrements, appelés entrées (= les données).

Exemple : la table users contiendra des enregistrements d’utilisateurs dont on stockera les attributs nom, prenom, date_naissance, genre.

Créer une base de données avec PHPMyAdmin

Connexion au serveur de base de données

Nous allons dès maintenant pouvoir tirer profit de phpMyAdmin en créant notre base de données via une interface graphique. Pour créer une base de données, rendez-vous à l’URL :

• localhost/phpmyadmin (Windows)
• localhost:8888/phpmyadmin (MacOS)

Une fois arrivé sur l’interface de connexion, les identifiants par défaut sont :

• login : root / mot de passe : ne rien saisir (Windows)
• login : root / mot de passe : root (MacOS)

Création de la base

Cliquer sur nouvelle base de données en haut à gauche de l’interface puis :

• Renseignez le nom de votre base de données. Il correspond généralement au nom de votre site web en snake_case.
• Précisez également l’interclassement : utf8_general_ci ou utf8mb4_general_ci.

Création des tables + attributs

Pour créer une table, se placer dans le menu déroulant de la sidebar dédiée à la base de données fraîchement créée et cliquer sur nouvelle table. Il vous sera alors demandé de saisir :

• Un nom pour votre table. Ce nom correspond généralement à l’entité (l’objet) à laquelle la table est associée dans le code / à la donnée qu’elle stockera, écrit en snake_case et au pluriel. Par exemple : users, posts, products…
• Des attributs et leurs caractéristiques : nom, type, taille, valeur par défaut… etc. Par exemple : firstname, varchar, 255
• Si votre base de données est vouée à contenir des relations entre tables, changer le moteur de stockage par défaut défini à MyISAM vers InnoDB (qui est un moteur de stockage relationnel).

Interagir avec une base de données en PHP

Connexion à la BDD

L’objet PDO

Nous avons fraîchement parlé d’objets, et bien, c’est via un objet PDO dédié que PHP va pouvoir communiquer avec les bases de données MySQL. PDO, signifiant PHP Data Object est en fait une couche d’abstraction qui joue le rôle d’interface entre votre application et la base de données.

PDO est un module, cela signifie que son code source n’est pas intégré au langage PHP lui-même, mais bien dans une bibliothèque de code annexe. Bonne nouvelle, PDO est installé par défaut dans les environnements WAMP et MAMP.

Et comme tout objet, nous allons devoir l’instancier afin de l’utiliser :

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');

On transmet ici 3 arguments au constructeur de la classe PDO :

1. La Data Source Name (DSN) - où est située la BDD. Il est constitué des éléments suivants :

• Le préfixe DSN : mysql:
• L’hôte sur lequel est hebergée la base de données : host=localhost
• Le nom de la BDD : dbname=nom_bdd
• Le jeu de caractère : charset=utf8

1. Le nom d’utilisateur pour accéder au serveur de base de données.
2. Le mot de passe pour accéder au serveur de base de données.

Par convention, on appelle cet objet initialisant la connexion $db, pour « database ». On factorise systématiquement cette ligne de code dans un fichier à part qui sera inclus avec require_once() dans les fichiers interagissant avec la base de données.

Gestion des erreurs

Erreurs SQL via PDO

Avant PHP 8, PDO n’affichait pas les potentielles erreurs de requêtes SQL. Aucun message d’erreur ne s’affichait et le script PHP continuait à s’exécuter.

Afin de pallier cela, nous pouvons faire appel aux exceptions. Le mode « exception » est très utile, car il permet de structurer notre gestionnaire d’erreur plus clairement en analysant nos requêtes SQL. En suivant notre configuration, elles pourraient apparaître via les alertes PHP traditionnelles (les fameuses alertes orange…).

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Exceptions avec les blocs try & catch

Les exceptions sont un système de gestion d’erreurs dont le principe est très simple :

1. On entoure le code qui peut potentiellement provoquer une erreur par un bloc try
2. On ajoute un bloc alternatif catch à la suite (à la manière d’un else), qui sera exécuté si une erreur se produit lors de l’exécution du code contenu dans le bloc try. Le bloc catch permet également de gérer et traiter l’erreur.

L’objet PDException sera idéal pour surveiller de potentielles erreurs lors de la connexion à la base de données.

try {
	$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
} catch (PDOException $e) {
	die('Erreur : ' . $e->getMessage());
	// Nous pourrions par exemple envoyer un email au webmaster et logger l'erreur en base de données
}

getMessage() n’est bien entendu pas la seule méthode contenue dans la classe PDOException. Vous trouverez de plus amples informations sur la documentation officielle de cette classe.

Encodage

Si les informations récupérées ne sont pas encodées en UTF-8, il est possible de préciser cela juste après la connexion à la BDD :

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
$db->exec('SET NAMES utf8');

Requêtes élémentaires

Interagir avec une base de données implique bien entendu d’avoir des bases en langage SQL. Le SQL permet d’écrire des requêtes, qui sont des directives pour les SGBDR.

On distingue 2 grandes familles et 4 grands types de requêtes :

1. Les requêtes de lecture qui vont récupérer des données en base.
2. Les requêtes d’écriture qui vont écrire, modifier ou effacer des données en base.

Nous étudierons ici les requêtes SQL basiques permettant de réaliser un système de CRUD, à savoir : Create, Read, Update et Delete.

Quelles que soient ces requêtes elles peuvent être “simples” (qui ne transmettent pas de paramètres) ou “préparées” (qui transmettent des paramètres).

C’est donc via PDO que nous écrirons ces requêtes SQL dans notre code PHP.

SELECT

Le mot-clé SELECT fait référence à la récupération de données en base : c’est le read.

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
$sql = "SELECT * FROM articles";
$query = $db->query($sql);
$articles = $query->fetchAll();

Pour exécuter une requête SELECT, on procède en 4 temps :

Si vous faites un var_dump($articles), vous remarquerez que les données (sous forme de string) sont retournées en doublon dans un tableau associatif dont les clés sont :

• Des indices numériques (0,1,2… à la manière d’un tableau classique).
• Les noms des colonnes (id, title… à la manière d’un tableau associatif).

array (size=9)
	0 =>
		array (size=12)
			'id' => string '1' (length=1)
			0 => string '1' (length=1)
			'title' => string 'Apprendre le HTML' (length=17)
			1 => string 'Apprendre le HTML' (length=17)
			'category_id' => string '3' (length=1)
			2 => string '3' (length=1)
			'content' => string 'bla blabla blablabla...' (length=23)
			3 => string 'bla blabla blablabla...' (length=23)
			'created_at' => string '2021-09-02 23:25:12' (length=19)
			4 => string '2021-09-02 23:25:12' (length=19)
			'updated_at' => null
			5 => null
	1 =>
		array (size=12)
			'id' => string '2' (length=1)
			0 => string '2' (length=1)
			'title' => string 'Apprendre le CSS' (length=16)
			1 => string 'Apprendre le CSS' (length=16)
			'category_id' => string '3' (length=1)
			2 => string '3' (length=1)
			'content' => string 'bla blabla blablabla...' (length=23)
			3 => string 'bla blabla blablabla...' (length=23)
			'created_at' => string '2021-09-02 23:26:12' (length=19)
			4 => string '2021-09-02 23:26:12' (length=19)
			'updated_at' => null
			5 => null
...

Si vous ne voulez pas vous retrouver avec ce dédoublement d’informations, vous avez la possibilité de spécifier à PDO que vous ne souhaitez associer les données qu’à leur nom de colonne. Pour cela, on appelle la méthode setAttribute() sur notre objet $db avec les paramètres suivants :

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

Concernant l’affichage de ces données :

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
$sql = "SELECT * FROM articles WHERE id = 1";
$query = $db->query($sql);
$article = $query->fetch();

<h1><?= $article['title']; ?></h1>
<p><?= $article['content']; ?></p>

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
$sql = "SELECT * FROM articles";
$query = $db->query($sql);
$articles = $query->fetchAll();

<?php foreach ($articles as $article) { ?>
<h1><?= $article['title']; ?></h1>
<p><?= $article['content']; ?></p>
<?php } ?>

INSERT

Le mot-clé INSERT, fait référence à l’insertion de données en base : c’est le create.

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
$sql = "INSERT INTO articles(title, content) VALUES('Apprendre le HTML', 'Blablabla blabla')";
$query = $db->exec($sql);

Pour exécuter une requête INSERT, on procède en 3 temps :

UPDATE

Le mot-clé UPDATE, fait référence à la modification de données en base.

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
$sql = "UPDATE articles SET title = 'Apprendre le CSS', content = 'blablabla' WHERE id = 3";
$query = $db->exec($sql);

Pour exécuter une requête UPDATE, on procède en 3 temps :

DELETE

Le mot-clé DELETE fait référence à la suppression de données en base.

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
$sql = "DELETE FROM articles WHERE id = 3";
$query = $db->exec($sql);

Requêtes préparées

Si lors de l’exécution de requêtes statiques nous avons utilisé les méthodes query() en lecture et exec() en écriture, celles-ci ne nous conviendront plus lorsqu’il s’agira d’effectuer des requêtes plus complexes, manipulant des paramètres.

Considérons l’extrait de code suivant :

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
$sql = "SELECT * FROM users WHERE email = '" . $email . "' AND password = '" . $password . "'";
$query = $db->exec($sql);

À première vue, ce code ne pose aucun problème : on exécute la requête avec 2 paramètres $email et $password venant la dynamiser.

MAIS…

Il est formellement interdit de concaténer une entrée utilisateur dans une requête SQL, car les paramètres pourraient contenir du code SQL malveillant.

Cet extrait de code est alors vulnérable aux injections SQL injection SQL : une faille de sécurité très réputée permettant aux assaillants de faire ce qu’ils veulent de votre BDD… 😢

Pour prévenir cette faille, nous devrons écrire ce que l’on appelle une requête préparée. Pour ces requêtes préparées, on utilisera la combinaison de prepare() et execute().

L’extrait de code précédent se verra remplacé par :

$db = new PDO('mysql:host=localhost;dbname=nom_bdd;charset=utf8', 'root', '');
$sql = "SELECT * FROM users WHERE email = :email AND password = :password";
$query = $db->prepare($sql);
$query->execute([
	':email' => $email,
	':password' => $password
]);

1. Au sein de la requête SQL, on place des marqueurs nominatifs à la syntaxe :nom_marqueur
2. On prépare la requête avec la méthode prepare()
3. Via la méthode execute(), on spécifie pour chaque marqueur la donnée à associer au sein d’un tableau associatif

Je vous invite à consulter le chapitre dédié aux injections SQL en PHP.