MySQL, présentation succincte (pour mémoire)

MySQL est peut-être le gestionnaire de base de données le plus populaire, et il est utilisable avec PHP. Cette page donne une initiation pratique mais pas suffisante: la sécurité dépasse le cadre de cette présentation. Il en est un peu question sur la page consacrée au PHP.

Attention! Le contenu de cette page est dépassé, PHP5 ne recevant plus de correction de sécurité, et PHP7 ayant définitivement remplacé les fonctions mysql_*() par MySQLi.

Il n'est pas possible de traiter ici des conditions d'utilisation de mySQL chez votre serveur, qui changent d'un fournisseur à l'autre. Vous trouverez des informations plus pointues sur dev.mysql.com. On considère donc que vous avez un accès aux services PHP et mySQL (c'est-à-dire une adresse de connexion, un identifiant et un mot de passe).

Si vous avez installé phpmyadmin sur votre système, vous y accédez par l'adresse http://localhost/phpmyadmin/index.php, voir LAMP.

1. Se connecter au serveur

2. Base de donnée et tables

3. Exploitation de données

3.1 Création/effacement d'enregistrements
3.2 Modifier des enregistrements
3.3 Condition avec WHERE

4. Lectures d'enregistrements

4.1 Compter des enregistrements
4.2 Lecture
4.3 Ordonner une sélection
4.4 Compter et sommer
4.5 Jointure de fichiers

5. Identification par cookie

1. Connexion

Pour utiliser PHP et mySQL, il faut avoir une connexion Internet vers un serveur ou installer un serveur sur son poste (LAMP: Linux-Apache-MySQL-PHP ou WAMP avec Windows). Nous considérons ici une connexion à un serveur extérieur. Vous disposez bien évidemment d'une adresse, d'un identifiant et d'un mot de passe de connexion. 

<?php

$connexion=mysql_connect($adresse_connexion,$votre_identifiant,$votre_mot_de_passe);
mysql_select_db($nom_DB,$connexion);

…vos requêtes et autre code PHP

mysql_close($connexion);
?>

Pour l'utilisation de votre poste comme serveur html et mysql, l'adresse de connexion est localhost, l'identifiant et mot de passe ont choisis lors de l'installation de phpmyadmin, et le nom de la base de données lors de votre premier passage sur phpmyadmin ou au point 2.1.

mysql_connect ne permet pas une connection permanente, c'est-à-dire qu'elle se terminera avec le script. Il existe bien la connexion permanente avec mysql_pconnect mais il n'est pas sûr qu'elle fonctionne dans toutes les configurations.

2. Base de données et tables

2.1 2.1 Base de données

Les serveurs utilisant PHP et mySQL ont généralement le service PHPmyAdmin, qui possède une interface graphique permettant la création et la gestion d'une base de donnée. Mais il est possible de tout faire en PHP. La première chose à préciser est la différence entre base de donnée et table. Une base de données est un ensemble de tables, ces dernières contenant les données, sous forme de tableaux à deux dimensions.

Pour tout connexion à une base de données (mysql_connect() ayant renvoyé $connexion):

$reponse=mysql_list_dbs($connexion); permet de visualiser les bases de données connectables
mysql_create_db('nom',$connexion); crée la base de donnée (vide) 'nom_bd'
mysql_drop_db('nom',$connexion); détruit la base de donnée 'nom_bd'

3.2 Tables

On visualiser généralement une table comme étant composée de colonnes de données de même type (les champs: codes_postaux, ages, taille, prix ne peuvent comprendre que lettres, chiffre et tiret_bas, un tiret simple est considéré comme le signe "moins"…) et des rangées de données appartenant à un même enregistrement (une personne particulière, un "individu" statistique). 

+----+------------+---------+--------+----------+------+
| id | date       | couleur | forme  | grandeur | etc. |
+----+------------+---------+--------+----------+-----
|  0 | 1253467287 | rouge   | ovoïde | petit    | …
|  1 | 1156726761 | vert    | carré  | moyen    |
|  2 | 1232627637 | vert    | rond   | petit
|  3 | 1023101231 | etc.    | …    | …
|  4 | …
| …
|

Étant connecté à une base de données, on crée une table en indiquant le nom de chaque champ, suivi de la nature de la variable (entier, chaine, et éventuellement le type d'organisation interne de la table (MyISAM est économe en place utilisée pour les chaînes): 

mysql("CREATE TABLE nom (nb INT, nom VARCHAR(20)) ENGINE=MYISAM");

$resultat=mysql_query("SELECT COUNT(*) table"); retourne le nombre d'enregistrements d'une table

mysql_query("ALTER TABLE table ORDER BY champ1, champ2 DESC"); ordonne une table, avec enregistrement physique de la modification

3. Exploitation et modification des donnéesAvril 2020

On suppose que la table est créée, et qu'on est connecté. Ce qui est écrit en majuscule est une instruction mySQL. S'il y a plusieurs champs, il faut les séparer par une virgule.

Le résultat de $res=mysql_query("Votre requête MySQL") est un booléen pour les requêtes qui ne renvoient aucune donnée, l'identifiant d'un tableau de réponses sinon.

mysql_query("Votre requête MySQL") or die(mysql_error()) arrêtera un script si MySQL détecte une erreur (die()) et en donne la raison (mysql_error()). C'est intéressant pour une mise au point, mais doit ensuite être être enlevé pour plus de confidentialité: personne ne doit pouvoir vusaliser vos messages d'erreur, qui peuvent renseigner les voleurs de données.

Attention: cela dépend des versions, mais les plus récentes exigent que les valeurs soient écrites entre guillemets: 

UPDATE table SET champ1='$val1', champ2='$val2' WHERE nr="35"

PHPmyAdmin écrit même les noms des champs entre ``. Ce sera peut-être obligatoire pour une prochaine version 

UPDATE table SET `champ1`='$val1', `champ2`='$val2' WHERE `nr`="35"

3.1 Création/effacement d'enregistrements

Pour ajouter un nouvel enregistrement (une rangée, un individu): 

$sql=mysql_query("INSERT INTO table (champ1,champ2,champ3) VALUES ('$val1','$val2','$val3')");

Pour supprimer tous les enregistrements d'une table (vider sans effacer la table elle-même): 

$sql=mysql_query("DELETE FROM table");

Pour supprimer une sélection d'enregistrements, il faut utiliser un filtre (§ suivant): 

$sql=mysql_query("DELETE FROM table WHERE condition");

3.2 Modifier des enregistrements

Pour modifier des champs de tous les enregistrements d'une table: 

$sql=mysql_query("UPDATE table SET champ1='$val1', champ2='$val2'");

Pour modifier une sélection d'enregistrements, il faut utiliser un filtre (§ suivant): 

$sql=mysql_query("UPDATE table SET champ1='$val1', champ2='$val2' WHERE condition");

3.3 Condition avec WHERE

Les commandes DELETE, UPDATE (supra) et SELECT (infra) traitent par défaut tous les enregistrements d'une table. Le mot WHERE permet de filtrer les enregistrements selon des critères qu'il est possible de définir. Par exemple, pour ne détruire que le(s) enregistrement(s) ayant le nombre ou la chaîne '43' dans le champ nbr: 

mysql_query("DELETE FROM table WHERE nbr='43'");

Attention: la relation d'égalité est représenté par un simple = .

Pour nombres et chaînes,

WHERE age IN (40, 50, 60) si age doit avoir une de ces trois valeurs
WHERE sigle NOT IN ('TPI', 'ONU') si sigle ne doit pas avoir une de ces deux valeurs

Pour les nombres,

WHERE age='50', WHERE age<107, WHERE age<=107, WHERE age>'18', WHERE age>=18, WHERE age<>43
WHERE age BETWEEN '45' AND '55' (45 et 55 sont inclus)

Pour les chaînes,

WHERE champ='cabot' définit l'entièreté du champ
WHERE champ LIKE 'cab%' définit un début de champ
WHERE champ LIKE '%ot' définit une fin de champ
WHERE champ LIKE '%b%' définit un milieu de champ
WHERE champ LIKE 'ca__t' définit un champ, avec une indétermination sur les 3e et 4e caractères

Note:

4 Lecture enregistrements

4.1 Comptage d'enregistrements

Pour compter le nombre d'enregistrements: 

$resultats=mysql_query("SELECT champ1 FROM table");
$nombre=mysql_num_rows($resultats);

Pour compter le nombre d'enregistrements répondant à un critère: 

$resultats=mysql_query("SELECT champ1 FROM table WHERE champ>'50'");
$nombre=mysql_num_rows($resultats);

Rappel: $resultat=mysql_query("SELECT COUNT(*) matable") retourne le nombre d'enregistrements d'une table.

4.2 Lecture

Pour la lecture de certains champs de tous les enregistrements d'une table: 

$resultats=mysql_query("SELECT champ1,champ2,champ3 FROM table");
while($rangee=mysql_fetch_array($resultats))
  {
  $var1=$rangee[champ1];
  $var2=$rangee[champ2];
  $var3=$rangee[champ3];
  echo "$var1 - $var2 - $var3<br>";
  }

La clause WHERE permet de sélectionner certains enregistrements. Dans le cas d'une recherche d'articles dont le prix doit être inférieur à 10, achetables par paquets d'au plus 5 articles, la requête sera: 

$resultats=mysql_query(SELECT article, prix, nbrmin FROM catalogue WHERE prix<'10' AND nbrmin<'6');
while($rangee=mysql_fetch_array($resultats))
   {
   // voir l'exemple précédent
   }

4.3 Ordonner une sélection

Pour sélectionner des articles, classés par prix décroissant, sous-classement par nom d'article: 

$resultat=mysql_query("SELECT article, prix, nombre FROM catalogue ORDER BY prix DESC, article");

ORDER BY prix DESC donnerait les enregistrements du plus grand au plus petit prix.

Vaut-il mieux ordonner la table ou la sélection des enregistrements? Plus la table aura d'enregistrements et plus le temps de tri, et donc la charge du serveur, croîtra. Si la sélection comporte peu d'enregistrements, nul doute qu'il vaut mieux trier le résultat de la requête plutôt que la table elle-même. Mais si une table reste désordonnée, le tri sur requêtes réitère un même résultat qui sera à chaque fois perdu. Si une table n'est que rarement modifiée, il est peut-être bon de l'ordonner après chaque modification, rendant les tris sur requêtes inutiles.

4.4 Compter et sommer

Pour compter le nombre de valeurs non NULL, on utilise COUNT(variable). Dans le cas où l'on veut ne compter qu'une fois chaque valeur pour un champ, on utilise COUNT(DISTINCT variable). GROUP BY précise le champ des valeurs qui décident du regroupement. Pour faire une sommation des valeurs d'un champ numérique, on utilise la fonction SQL SUM(variable).

Par exemple, dans le cadre d'une table 'factures_payees' comprenant les champs 'montant', 'prestataire' et 'categorie', pour connaître selon chaque catégorie la sommation des factures ainsi que le nombre de prestataires concernés: 

$resultat=mysql_query("SELECT categorie, SUM(montant), COUNT(DISTINCT prestataire)
FROM factures_payees GROUP BY categorie");

La sortie des résultats se fait de cette manière (ce sont les paramètres 'SUM(montant)' et 'COUNT(DISTINCT prestataire)' qui servent d'indices au tableau $rangee) : 

while($rangee=mysql_fetch_array($resultat))
 {
 $sommation=$rangee['SUM(montant)'];
 $nbr_prest=$rangee['COUNT(DISTINCT prestataire)'];
 $categorie=$rangee['categorie'];
 echo "Cat. $categorie: $sommation pour $nbr_prest<br>";
 }

4.5 Jointure de tables

Pour une base de données comportant un grand nombre d'ouvrages écrits par un nombre (plus) restreint d'auteurs, il est rationnel de ne pas réencoder les prénoms, nom, biographie… de chaque auteur dans la table des ouvrages. On utilise alors deux tables, la première pour les ouvrages, comprenant les champs id, titre… et un champ 'auteur', ou est inscrit un nombre correspondant à l'identifiant de l'auteur décrit dans une seconde table: id, nom…

La lecture de deux tables amène à un traitement plus lourd des lectures pour un affichage des ouvrages écrits par chaque auteur: le premier réflexe est de lire la table des auteurs, et à partir de l'id, refaire une requête sur les ouvrages écrits par chaque auteur: 

$resultat=mysql_query("SELECT id, nom FROM auteurs");
while($data=mysql_fetch_array($resultats))
  {
  $nom=$data['nom'];
  id=$data['id'];
  echo "<p><b>$auteur</b></p>";
  $resultat1=mysql_query("SELECT titre FROM ouvrages WHERE aut='$id'");
  while($data1=mysql_fetch_array($resultats1))
    {
    $titre=$data1['titre'];
    echo "$titre<br>";
    }
  }

Au total, cela nous fait une requête sur la table des auteurs, plus autant de requêtes dans la table des ouvrages qu'il y a d'auteurs. Il est possible de simplifier le travail par une jointure des deux tables: 

$res=mysql_query("SELECT auteurs.nom, ouvrages.titre FROM auteurs, ouvrages WHERE auteurs.id=livres.aut");
$nom0="";
while($data=mysql_fetch_array($res))
  {
  $nom=$data['auteurs.nom'];
  $titre=$data['titre'];
  if($nom!=$nom0) { echo "<p><b>$auteur</b></p>"; $nom0=$nom; }
  echo "$titre<br>";
  }

La sélection portent sur deux table: auteurs et ouvrages; les champs ont été préfixés du nom de la table dont ils font partie. La jointure se fait dans la clause WHERE, ou le numéro unique pour chaque auteur doit correspondre au champ auteur de la table des ouvrages.

5. Identification par cookie

Si vous devez octroyer une connexion sql sous condition d'identification, il n'est pas question de la recommencer à chaque page. Il est possible de s'en sortir par les cookies. Trois fichiers. 

<html><body>
<form method='post' action='log.php'>
<table><tr><td align='right'>
Identifiant <input name='id' size=10 value='' />
</td></tr><tr><td align='right'>
Mot de passe <input name='mp' size=10 value='' />
</td></tr><tr><td align='right' valign='top'>
<input type=submit value='Envoyer' />
</td></tr></table>
</form>
</body></html>

Le fichier log.php se charge de vérifier le mot de passe et de délivrer le cookie: 

<?php

$id=$_POST[id];
$mp=$_POST[mp];

$connexion=mysql_connect($adresse_connexion,$votre_identifiant,$votre_mot_de_passe);
mysql_select_db($nom_DB,$connexion);

$resultats=mysql_query("SELECT id,mp FROM mdp WHERE id='$id'");

if ($mp=$rangee['mp'])
 {
  set_cookie('nom_de_cookie','chaine');
 }

header('Location: page.php');
?>

Pour des raisons de protocole, il est nécessaire que set_cookie() soit écrit avant tout echo "<html><head>…";

Tous les fichiers devant se connecter à la base de donnée devront contenir cette séquence: 

<?php

if ($HTTP_COOKIE_VARS['nom_de_cookie']=='chaine')
 {
  $connexion=mysql_connect($adresse,$identifiant,$motdepasse);
  mysql_select_db($nom_DB,$connexion);
 }

…votre code…

mysql_close($link);
?>

Notons qu'il ne s'agit pas de la méthode la plus sécurisée, les cookies pouvant être récupérés ou interceptés.