Site avec une base de données en MYSQL

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

Les variables sont des données éphémères ; les variables simples durent le temps d'affichage d'une page ; les variables de session durent le temps d'une visite d'un site ; les cookies peuvent durer plus longtemps sauf si l'internaute les supprime.

Une base de données est un ensembles structuré de données permanentes.

SGBD

MySQL
PostgresSQL
Oracle
MS SQL Server
SQLite (très utilisée conjointement avec Python)

MySQL est un SGBD fort utilisé ; il est libre et gratuit et surtout il est inclus dans le pack WAMP !
De plus il est proposé avec un outil d'administration : PHPMyAdmin.

Grâce à cet outil il n'est pas nécessaire d'être un champion de SQL pour administrer une base de données ; il suffit d'utiliser des interfaces (qui génèrent du SQL).
En effet tous les SGBD sont administrés grâce à des requêtes basées sur le langage SQL (Structured Query Language).

Structure d'une base de données

Une base de données a un nom et comprend une ou plusieurs tables.
Un table c'est comme un tableau à double entrées : des lignes et des colonnes.

Imaginons que vous êtes chargé de créer le site pour une association. On vous précise que ce site doit contenir une page "infos" (news).
On vous précise qu'il peut y avoir plusieurs "news" chaque semaine !
Si vous ne voulez pas recourir à une base de données, vous devrez changer plusieurs fois par semaine le code HTML de la page "infos". Ce sera très très fastidieux et de plus la maintenance devra être faite par une personne compétente en HTML& CSS.
Par contre si vous créez un site avec base de données et en particulier une table "news" il suffira d'ajouter une ligne dans cette table et forcément la page "infos" affichera cette nouvelle ligne. Cet ajout pourra être effectué par un administrateur de site même ignorant dans les standards du web (HTML & CSS). Il lui suffira de remplir un formulaire.
Grâce à la base de données votre site est devenu dynamique.

Présentation de la table "news"

Donc la table "infos" du site affichera la table "news" de la base de données.

Mais comment va se présenter cette table ?

Cette table sera un tableau avec 4 colonnes et autant de lignes (ou enregistrements) que d'infos.

ID	date_new	titre	message
1	2017-01-20	convocation AG	Blabla ....
2	2017-02-10	convocation bureau	Blabla ....
3	2017-03-15	sortie membres	Blabla ....

Ici la table ne comprend que trois lignes (ou 3 enregistrements) car il n'y a pour l'instant que trois "news".

un numéro unique ou Identifiant (ID)
la date de création de l'info
titre de l'info
le message

Utilisation de PHPMyAdmin

Nous allons utiliser PHPMyAdmin pour créer une base de données et une table au sein de cette base.

PHPMyAdmin est une application écrite en PHP qui permet d'administrer une base de données.
Cette application fait partie du pack Wamp et elle aussi proposée par les hébergeurs.
PHPMyAdmin propose des interfaces permettant de générer des requêtes SQL. Il évite donc d'utiliser la console SQL.

Démarrez Wamp puis sélectionnez "PHPMyAdmin"

Premier écran

C'est la page d'accueil de l'application PHPMyAdmin
Pour le champ "utilisateur" il faut saisir "root" et par défaut il n'y a pas de mot de passe.

Deuxième écran

Dans ce deuxième écran vous pouvez attribuer un mot de passe pour se connecter à PHPMyAdmin.
Dans cet écran vous pouvez créer une nouvelle base de données. il suffit de cliquer sur le lien "Nouvelle base de donnée".

Des bases existent déjà dont "MySQL" et "Information_schema". N'y touchez surtout pas car ces bases sont indispensables au bon fonctionnement de l'application PHPMyAdmin.

Créez une nouvelle base et nommez cette dernière test.

Vous voyez que l'on est dans la nouvelle base (test) et cet écran indique que pour le moment cette base est vide de toute table.
L'interface vous propose de créer une table. Nommez cette table "infos" et précisez qu'elle aura 4 colonnes.

Quatrième écran

un nom
un type
une taille éventuellement
d'autres attributs

La première colonne se nomme ID (comme identifiant). La colonne est de type INT (integer) et j'ai coché A-I (Auto-Increment). Ce qui veut dire qu'à chaque fois qu'une ligne sera ajoutée dans la table son champ ID sera rempli automatiquement.

La deuxième colonne se nomme "date_new" et est de type date. Elle permet de stocker une date sous la forme AAA-MM-JJ.

Le troisième champ se nomme "titre" et de type varchar. Pour un champ de type varchar il faut préciser la taille. Ici c'est 30. Donc on peut stocker un chaîne ayant 30 caractères maxi. Attention la taille d'un champ de type varchar ne peut dépasser 255.

Donc pour pouvoir stocker un texte long pouvant dépasser 255 caractères il faut il utiliser le type text. C'est ce que je fais pour le quatrième champ de la table qui se nomme message.

La colonne ID est non seulement auto-increment mais aussi clé primaire de la table. Donc il ne peut y avoir deux champs ID ayant la même valeur.
Il n'est pas obligatoire de définir une clé primaire dans une table mais c'est fortement conseillé. Comme le champ ID est clé primaire il ne peut y avoir de doublons sur cette colonne ; c'est bien ce qu'on demande à un identifiant ... qu'il soit unique !

Les différents types de champs MySQL

Nous avons vu les types INT(pour stocker un entier), DATE, VARCHAR et TEXT.

TINYINT pour stocker un petit entier
BIGINT pour stocker un gros entier
DECIMAL
TIME pour stocker une heure
DATETIME pour stocker une date et une heure
etc.

Pages d'administration de la base de données

On peut ajouter des lignes (enregistrements) à une table via PHPMyAdmin mais ce n'est pas à la porté de l'internaute lambda et de plus ce serait extrèment dangereux en matière de sécurité des données. PHPMyAdmin ne doit être utilisé que par le webmaster et pour des tâches rares.

Par exemple dans le cadre d'une inscription à un site il suffit de proposer à l'internaute une page contenant un formulaire. Dès que le formulaire est renseigné un script PHP appelle une requête SQL qui ajouter un enregistrement à la table des inscrits.

Maintenant reprenons le thème gestion des news (ou des infos) pour le site de l'association.
On peut donc imaginer une page (page publique) pour afficher les infos les plus récentes et une autre page (page réservée) pour saisir de nouvelles infos. Dans les deux pages il y aura du code PHP appelant des requêtes SQL.

Connexion à la base de données

Pour écrire ou lire une table via une page PHP il faut d'abord se connecter à la base de données contenant cette table.

l'extension mysql_ qui ne doivent plus être utilsées ; les fonctions sont obsolètes.
L'extension PDO (PHP Data Objects) qui est une extension orientée objet et qui permet de se connecter à n'importe quel type de base de données (MYSQL,Oracle, PostGresSQL, etc.)

Nous allons utiliser l'extension PDO !
Pour se connecter à la base de données MYSQL test l'instruction est alors :
$base = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');

Cette instruction peut vous paraître étrange si vous n'ètes pas habitué à la POO (Programmation Orientée Objet).
Sachez simplement que PHP est un langage qui autorise la programmation procédurale mais aussi depuis la version 5 la programmation orienté objet.

à une base MYSQL locale (mysql:host)
nommée test
encodage en utf8
login : root
mot de passe : aucun

La page pour saisir de nouvelles infos

Elle doit bien sûr contenir l'instruction de connexion et en plus le code qui suit.

Le code PHP

...
<?php
if(isset($_POST['titre']))
{	// requête qui ajoute un enregistrement dans la table infos
	$titre = $_POST['titre'];
	$message = $_POST['message']; 
	$base->exec("INSERT INTO news(date_new,titre,message) VALUES (CURDATE(),'$titre','$message')") ;
	echo "<p class ='remarque' >info enregistrée !</p>"; 
	}
?>

La variable $base représente la base connectée.
$base->exec : méthode de l'objet qui permet d'exécuter une requête action (requête qui commence par INSERT ou UPDATE ou DELETE c'est à dire une requête qui modifie les données de la base).

Observez bien la requête d'insertion passée en paramètre de la méthode.

Le champ date_new du nouvel enregistrement est rempli via la fonction SQL curdate() ; fonction qui permet de récupérer la date courante.
Il existe aussi la fonction now() qui retourne l'instant présent (date et heure courante). C'est cette fonction SQL qu'il aurait fallu utiliser si le champ date_new avait été de type datetime (mais il est de type date).
Il existe aussi la fonction SQL curtime(). qui retourne l'heure courante : très pratique pour remplir un champ de type time.

Le code du formulaire

<h1>Ajouter une info du club </h1>
<form action='#' method='post'>
<p class ="remarque">* : saisie obligatoire ! </p> 
<label>Titre de la new  *</label>	
	<input type='text' name='titre' required />
<label>Entrer le message de la new  *</label>	
	<textarea name='message' required> </textarea>
<label></label>
<button type='submit' name='submit'>Validez</button>
</form>
...

La page pour afficher les dernières infos

Elle doit bien sûr contenir aussi l'instruction de connexion et en plus le code indiqué ci-dessous : ... <?php $selection = $base->query("SELECT * FROM news ORDER BY id DESC limit 5") ; while($record = $selection->fetch()) { echo "<div>"; $date_new = $record['date_new']; $titre = $record['titre']; $message = $record['message']; echo "<h2>Titre : $titre</h2>"; echo "<p> info du : $date_new</p>"; echo "<p>Contenu : $message</p>" ; echo "</div>"; } // fin tant que ?> ...

Dans le script il y a une requête SQL SELECT (ou requête sélection). La syntaxe des requêtes sélection sera expliquée dans le chapitre suivant ("SQL : les requêtes sélection").

$selection récupère 5 enregistrements (voire moins) dans la table news.

La méthode query associée à l'objet $base (variable qui, je le rappelle, référence la base de donnée connectée) permet d'appeler une requête sélection (requête qui commence par SELECT c'est à dire qui affiche des données de la base).
Select * from news : sélection de toutes les colonnes de la table "news"
order by ID desc limit 5 : sélection de 5 lignes - les cinq dernières saisies - celles ayant les ID les plus élevés

while($record = $selection->fetch()) : boucle permet de traiter à chaque passage un enregistrement de la sélection.
Les données correspondant à l'enregistrement courant sont stockées dans le tableau associatif $record; la date de l'info est donc dans $record['date_new'], le titre de l'info dans $record['titre'], etc.

Création de la base chez l'hébergeur

Il faut maintenant reproduire la base de données sur le serveur de l'hébergeur.
Rassurez vous il n'est pas nécessaire de recommencer la même procédure qu'en local.

Première étape : produire en local un script SQL

Il suffit de produire à partir de PHPMyAdmin local le script SQL de la base de données et ensuite d'exécuter les différentes requêtes au niveau de PHPMyAdmin de l'hébergeur.

Dans PHPMyAdmin local sélectionnez la base puis cliquez sur "exporter".
L'application produit une fichier d'extension .sql que l'on retrouve dans le dossier "téléchargements" de votre ordinateur.

Le script SQL obtenu

une requête pour créer la table "news"
une requête pour créer un index sur cette table
une requête pour ajouter N lignes à la table

Je ne donne pour l'instant très peu d'explications sur la syntaxe SQL car le langage SQL sera évoqué dans des chapitres ultérieurs.

La requête de création de la table "news"

CREATE TABLE `news` 
(
  `id` int(11)AUTO_INCREMENT NOT NULL,
  `date_new` date NOT NULL,
  `titre` varchar(30)NOT NULL,
  `message` text NOT NULL
);

La requête pour créer un index sur la table "news'

Dans le cadre de la requête de création de table j'ai oublié de définir une clé primaire. Mais il n'est pas trop tard ; je peux exécuter une requête de type ALTER TABLE.

ALTER TABLE `news` ADD PRIMARY KEY (`id`);
La colonne ID devient clé primaire de la table "news" ; il ne peut y avoir de doublons sur cette colonne.

Requête pour insérer des lignes dans la table

Dans la version locale de la base j'avais rempli la table "news" avec quatre lignes via l'écran "insertion". Donc le script généré par la commande "export" contient une requête ajout. Code SQL ci-dessous : INSERT INTO `news` (`id`, `date_new`, `titre`, `message`) VALUES (9, '2017-02-10', 'Convocation AG', 'Convocation AG.\r\nBlabla ...\r\nBlabla ...'), (10, '2017-02-20', 'Convocation bureau', 'Convocation bureau.\r\nBlabla ...\r\nBlabla ...'), (11, '2017-03-20', 'Compte rendu AG', 'Compte rendu AG.\r\nBlabla ...\r\nBlabla ...'), (12, '2017-03-21', 'Compte rendu réunion bureau', 'Compte rendu réunion bureau.\r\nBlabla ...\r\nBlabla ....');

Deuxième étape : exécuter le script chez l'hébergeur

Souvent l'hébergeur à déjà créé pour votre site une base vide.
Il suffit donc d'ouvrir PHPMyAdmin à partir du panneau de configuration de l'hébergeur puis de sélectionner la base existante.
Ensuite il suffit de coller le code de chaque requête dans l'onglet "SQL" de PHPMyAdmin.

Ci-dessous capture d'écran de PHPMyAdmin chez mon hébergeur (One.com).
Notez que la base de données se nomme ici "darchevillepatr" .

Etape 3 : adapter le code des pages PHP

Dans les pages "ecrire_news.php" et "lire_news.php" nous avons vu qu'il avait un instruction PHP pour se connecter à la base locale. Instruction de connexion que je rappelle ci-dessous :
$base = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');

Dans la version en ligne de ces pages l'instruction de connexion va devenir :
$base = new PDO('mysql:host=...;dbname=...;charset=utf8', '...', '...'); // connexion chez one.com

hôte
nom de la base
utilisateur
mot de passe

Le reste du code est sans changement par rapport à la version locale.

Tests

J'ai installé chez mon hébergeur une base avec deux tables : "news" et "membres".
Je mets à votre disposition deux pages d'administration : une pour lire le contenu de la page "news" et une autre pour lire le contenu de la page "membres".

Il n'y a donc pas de pages pour écrire dans ces tables car je suis du genre prudent ...
Dès que vous autorisez les internautes à écrire dans des tables de votre base de données vous devez redoubler de vigilance. Il faut empêcher que l'internaute mal intentionné ne puisse injecter du code HTML (lien vers un site de charme, par exemple) ou du code Javascript dans la base.

Le rendu :

Afficher la date dans un format plus lisible

En testant la page "lire_news.php" vous pouvez constatez que les dates des infos sont affichées au format
AAAA MM JJ. C'est assez déroutant pour un francophone !

Il faudrait que les dates soient affichées sous la forme JJ MM AAAA. Il y a deux solutions qui sont du ressort de SQL !

Première solution

La première solution consiste à utiliser les fonctions SQL day(), month(), year().

Le code de la requête SQL devient alors : $selection = $bdd->query("SELECT DAY(date_new)AS jour, MONTH(date_new)AS mois, YEAR(date_new) AS annee, titre, message FROM news ORDER BY id DESC LIMIT 5") ;

La requête sélection est un peu plus compliquée. Il faut créer trois champs calculés : jour, mois, annee.
DAY(date_new) AS jour : extraction du jour dans le champ date_new pour l'affecter au champ calculé jour.

Notez que dans la requête les mots réservés (select, as, from, order by, ...) sont en écrits en majuscules.
SQL est un langage insensible à la casse. On peut écrire les mots réservés en minuscules. Mais l'usage veut que les mots clés soient écris en majuscules pour rendre la requête plus lisible.

Inconvénient de la première solution

Donc la date de l'info est "éclatée" dans trois champs.
Le script d'affichage de l'info doit être modifié en conséquence ! ... $jour = $record['jour']; $mois = $record['mois']; $annee = $record['annee']; ... echo "<p> info du : $jour-$mois-$annee</p>"; ...

Deuxième solution

Il y a plus simple ! Il suffit d'utiliser la fonction date_format.
La requête SQL devient : $selection = $bdd->query("select date_format(date_new,'%d-%m-%Y')as date_info, titre, message from news order by id desc limit 5") ;

Ainsi le contenu du champ calculé date_info sera affiché sous la forme JJ-MM-AAAA car le format d'affichage est %d-%m-%Y
%Y veut dire affichage de la date sur 4 chiffres (%y : affichage sur deux chiffres).
Les trois composantes de la date seront séparées par un trait d'union mais sont dans un champ unique : date_info.
On peut ensuite affecter le contenu de ce champ à une variable : $date_new = $record['date_info'];