Concevez votre site web avec PHP et MySQL

Allez plus loin avec les fonctions SQL

Découvrons une fonctionnalité bien utile du langage SQL : les fonctions.

Nous allons mettre en application deux cas d'utilisation qui complèteront votre projet :

1. Récupérer et afficher la moyenne des notes obtenues pour une recette.

2. Améliorer les commentaires en ajoutant une date de publication.

Utilisez une fonction scalaire SQL

Pour nos exemples, nous allons nous baser sur la table comments  que nous connaissons bien maintenant.

Pour rappel, voici à quoi elle pourrait ressembler, en considérant les commentaires sur la recette qui a pour identifiant unique la valeur 1 :

Pour vous montrer comment on utilise les fonctions scalaires SQL, je vais me baser sur la fonction DATE_FORMAT()  qui permet de convertir un timestamp en date.

La requête SQL suivante va convertir la date obtenue en quelque chose de lisible :

SELECT *, DATE_FORMAT(c.created_at, "%d/%m/%Y") FROM recipes r LEFT JOIN comments c on r.recipe_id = c.recipe_id WHERE r.recipe_id = 1

À l'aide de cette fonction, nous récupérons directement la date au format jour/mois/année plutôt que les heures/minutes/secondes qui ne nous intéressent pas.

Non ! La table reste la même.

La fonction DATE_FORMAT modifie seulement la valeur envoyée à PHP. On ne touche donc pas au contenu de la table.

Cela crée en fait un « champ virtuel » qui n'existe que le temps de la requête. Il est conseillé de donner un nom à ce champ virtuel qui représente les dates de commentaire.

Il faut utiliser pour cela le mot-clé AS :

SELECT DATE_FORMAT(c.created_at, "%d/%m/%Y") AS comment_date FROM recipes r LEFT JOIN comments c on r.recipe_id = c.recipe_id WHERE r.recipe_id = 1

On récupère les dates de commentaires via un champ virtuel appelé comment_date  .

Voici le tableau que retournera MySQL après la requête précédente :

PHP ne récupère que le champ nommé comment_date  (même s'il n'existe pas dans la table).

En affichant le contenu de ce champ, on ne récupère que les dates de publication des commentaires correctement formatées.

Bien entendu, vous pouvez aussi récupérer le contenu des autres champs comme avant, sans forcément leur appliquer une fonction :

SELECT *, DATE_FORMAT(c.created_at, "%d/%c/%Y") AS comment_date FROM recipes r LEFT JOIN comments c on r.recipe_id = c.recipe_id WHERE r.recipe_id = 1

Vous savez maintenant utiliser une fonction SQL scalaire !

Utilisez une fonction d'agrégat

Comme précédemment, nous allons d'abord voir comment on utilise une fonction d'agrégat dans une requête SQL. L'essentiel est de comprendre comment s'utilise ce type de fonctions : vous pourrez ensuite appliquer ce que vous connaissez à n'importe quelle autre fonction du même type.

Par exemple, ROUND()  est une fonction scalaire qui permet d'arrondir une valeur. On récupère donc autant d'entrées qu'il y en avait dans la table. En revanche, une fonction d'agrégat comme AVG() renvoie une seule entrée : la valeur moyenne de tous les lignes.

Regardons de près la fonction d'agrégat  AVG  .

Elle calcule la moyenne d'un champ contenant des nombres.

Utilisons-la sur le champ review qui – pour rappel – permet à l'utilisateur d'évaluer une recette avec l'identifiant unique  1  :

SELECT AVG(c.review) as rating FROM recipes r LEFT JOIN comments c on r.recipe_id = c.recipe_id WHERE r.recipe_id = 1

On donne là encore un alias au résultat donné par la fonction. La particularité, c'est que cette requête ne va retourner qu'une seule entrée, à savoir l'évaluation moyenne de la recette :

Si ! Tout comme en PHP on peut appeler une fonction dans une fonction, ici on va pouvoir appliquer la fonction ROUND() avec 1 décimale sur le résultat du calcul de la moyenne !

La requête devient donc la suivante :

SELECT ROUND(AVG(c.review),1) as rating FROM recipes r LEFT JOIN comments c on r.recipe_id = c.recipe_id WHERE r.recipe_id = 1

Pour afficher cette information en PHP, on pourrait faire comme on en a l'habitude (cela fonctionne). Mais pourquoi s'embêterait-on à faire une boucle, étant donné qu'on sait qu'on ne va récupérer qu'une seule entrée, puisqu'on utilise une fonction d'agrégat ?

<?php
$sqlQuery = 'SELECT ROUND(AVG(c.review),1) as rating FROM recipes r LEFT JOIN comments c on r.recipe_id = c.recipe_id WHERE r.recipe_id = 1';

// Préparation
$averageRatingStatment = $mysqlClient->prepare($sqlQuery);

// Exécution
$averageRatingStatment->execute();

/** La fonction fetch est plus performante que fetchAll()
 * quand nous sommes certain(e)s de ne récupérer qu'une ligne.
 * https://www.php.net/manual/fr/pdostatement.fetch.php
 */
$averageRating = $averageRatingStatment->fetch();

Ce code est plus simple et plus logique :

1. On récupère la première et seule entrée avec fetch()  .

2. Et on affiche ce qu'elle contient. Inutile de le faire dans une boucle, étant donné qu'il n'y a pas de seconde entrée.

Ne mélangez pas une fonction d'agrégat avec d'autres champs

Soyez attentif à ce point car il n'est pas forcément évident à comprendre :

En effet, quel sens cela aurait-il de faire :

# Requête qui provoque une erreur !
SELECT AVG(review) AS rating, user_id FROM comments

On récupérerait d'un côté la note moyenne de tous les commentaires et de l'autre la liste des auteurs de ces commentaires… Il est impossible de représenter ceci dans un seul et même tableau.

Comme vous le savez, SQL renvoie les informations sous la forme d'un tableau. Or, on ne peut pas représenter la moyenne des notes (qui tient en une seule entrée) en même temps que la liste des auteurs. Si on voulait obtenir ces deux informations, il faudrait faire deux requêtes.

C'est d'ailleurs ce qui est fait dans le projet fil rouge pour récupérer à la fois les commentaires et la note moyenne d'une recette !

Regroupez des données avec GROUP BY et HAVING

Je vous disais un peu plus tôt qu'on ne pouvait pas récupérer d'autres champs lorsqu'on utilisait une fonction d'agrégat.

Prenons par exemple la requête suivante :

SELECT AVG(review) AS rating, user_id FROM comments

Ça n'a pas de sens de récupérer la note moyenne de toutes les recettes et le  « User ID » à la fois. MySQL ne peut pas renvoyer un tableau correspondant à ces informations-là.

Groupez des données avec GROUP BY

En revanche, ce qui pourrait avoir du sens, ce serait de demander le note moyenne des commentaires pour chaque recette !

Voici un exemple d'utilisation de GROUP BY :

SELECT AVG(review) AS rating, recipe_id FROM comments GROUP BY recipe_id

Il faut utiliser GROUP BY  en même temps qu'une fonction d'agrégat, sinon il ne sert à rien.

Ici, on récupère la note moyenne des commentaires, que l'on regroupe ensuite par recette. Par conséquent, on obtiendra la liste des recettes de la table et la note moyenne des commentaires !

Cette fois, les valeurs sont cohérentes !

Filtrez les données regroupées avec HAVING

Voyez la requête suivante :

SELECT AVG(review) AS rating, recipe_id FROM comments GROUP BY recipe_id HAVING rating >= 3

Avec cette requête, on récupère seulement les recettes dont la note moyenne est supérieure ou égale à 3.

HAVING  ne doit s'utiliser que sur le résultat d'une fonction d'agrégat. Voilà pourquoi on l'utilise ici sur rating  et non sur recipe_id  .

Oui, mais pas au même moment :

• WHERE  agit en premier, avant le groupement des données ; 

• HAVING  agit en second, après le groupement des données.

On peut d'ailleurs très bien combiner les deux, regardez l'exemple suivant :

SELECT AVG(review) AS rating, recipe_id FROM comments WHERE user_id = 1 GROUP BY recipe_id HAVING rating >= 2

Ça commence à faire de la requête costaude. 😉

Ici, on demande à récupérer la note moyenne par recette de Mickaël ( WHERE  ), dont la valeur moyenne est supérieure ou égale à 2 ( HAVING  ).

Exercez-vous

Il est temps de retrouver pour la dernière fois notre fil rouge.

Ajoutons un système de notation et une date de création des commentaires :

Étape 1 : Mise en place de la date de création sur les commentaires

Utilisez le script SQL fourni (improve_comments.sql) pour ajouter un champ de date de création et champ pour la note à la table des commentaires.

Étape 2 : Affichage des commentaires de la date la plus récente à la plus ancienne

Modifiez le fichier recipes_read.php pour afficher les commentaires associés à une recette dans l’ordre antichronologique (du plus récent au plus ancien). Vous pouvez afficher la date de création.

Étape 3 : Saisie d'une note d'évaluation de 1 à 5 sur les recettes

Ajoutez un formulaire sur la page détail d'une recette permettant à l'utilisateur de saisir une note d'évaluation de 1 à 5. Validez et enregistrez cette note dans la base de données.

Étape 4 : Affichage de la moyenne des notes en haut de la page

Affichez en haut de la page détail de la recette la moyenne des notes des commentaires associés à cette recette.

En résumé

• MySQL permet d'exécuter certaines fonctions lui-même, sans avoir à passer par PHP. Ces fonctions modifient les données renvoyées.

• Il existe deux types de fonctions :

  1. Les fonctions scalaires : elles agissent sur chaque entrée récupérée. Elles permettent par exemple de convertir tout le contenu d'un champ en majuscules, ou d'arrondir chacune des valeurs.

  2. Les fonctions d'agrégat : elles effectuent des calculs sur plusieurs entrées pour retourner une et une seule valeur. Par exemple : calcul de la moyenne, somme des valeurs, comptage du nombre d'entrées…

• On peut donner un autre nom aux champs modifiés par les fonctions, en créant des alias à l'aide du mot-clé AS .

• Lorsqu'on utilise une fonction d'agrégat, il est possible de regrouper des données avec GROUP BY .

• Après un groupement de données, on peut filtrer le résultat avec HAVING . Il ne faut pas le confondre avec WHERE qui filtre avant le groupement des données.

C'est la fin de ce cours, merci de l'avoir suivi !

Vous avez maintenant les bases en PHP : bravo, c'est déjà un vrai morceau !

Prenez le temps de refaire les différentes étapes que l'on a vues au cours de notre projet fil rouge.

Eh bien vous êtes loin d'avoir fini : le monde de la programmation est riche, et vous pouvez aller bien plus loin.

Allez plus loin en PHP

Voici ce que je vous recommande de faire après avoir fini ce cours :

1. Découvrez comment mieux organiser votre code avec l'architecture MVC (Modèle Vue Contrôleur). Il s'agit d'une "façon de programmer" très connue qui vous permet de bien séparer chaque morceau de votre code. Si vous voulez travailler dans un milieu professionnel à plusieurs, cela vous sera absolument indispensable ! Ça tombe bien, j'ai écrit un cours là-dessus : Adoptez une architecture MVC en PHP ! (C'est en fait la suite de ce cours sur PHP.)

2. Découvrez comment faire de la programmation orientée objet (POO), une autre technique de programmation incontournable. Elle est complémentaire à MVC : Programmez en orienté objet en PHP.

3. Apprenez à utiliser un framework PHP comme Symfony. Il vous facilitera grandement le travail pour vos projets professionnels. Il se base sur la POO et MVC ; donc si vous avez suivi les deux cours précédents, vous devriez vous y retrouver bien plus facilement : Construisez un site web avec le framework Symfony 5.

Si vous faites tout ça, vous aurez fortement progressé, croyez-moi ! 🤓

N'oubliez pas de pratiquer régulièrement sur des projets pour vous entraîner !

C'est la fin de ce cours, merci de l'avoir suivi ! Et comme vous vous en doutez : vous devez passer un dernier quiz pour valider vos connaissances. Allez, on y va !