site sans réclame PHPCont@ctS'abonner

MySQL, présentation succincte

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

Réorganisation des notes au 2017.11.05.

1. Connexion au serveur

2. Base de données

2.1 Création d'une base de données
2.2 Effacement d'une base de données
2.3 Listage des bases de données

3. Tables de données

3.1 Création d'une table
3.2 Effacement d'une table
3.3 Tri d'une table

4. Exploitation des données

4.1 Création d'un enregistrement
4.2 Effacement d'enregistrement(s)
4.3 Lecture d'enregistrement(s)
4.4 Modification d'enregistrement(s)

5. Conditions

5.1 Nombres
5.2 Chaînes

6. Autres

6.1 Comptage d'enregistrements
6.2 Ordonner une sélection
6.3 Compter et sommer
6.4 Jointure de fichiers

7. Identification par cookie

1. Connexion au serveur

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 elle est déconseillée (il vaut mieux fermer les portes) et il n'est pas sûr qu'elle fonctionne dans toutes les configurations.

2. 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. Rappelons que mysql_connect() a renvoyé la variable $connexion.

2.1 Création de base de données

mysql_create_db('mesbd',$connexion); crée une base de donnée (vide) mesbd. À ce stade, elle ne contient encore rien, même pas une structure de données.

2.2 Base de données

mysql_drop_db('mesbd',$connexion); détruit la base de donnée mesbd.

2.3 Lister les bases de données

Pour visualiser les bases de données connectables:

$reponses=mysql_list_dbs($connexion);

$reponses est une variable-tableau contenant les noms des bases de données.

3. Tables de données

On visualise généralement une table comme étant composée de colonnes de données de même type (les champs: taille, age, tva1, code_postal... ne peuvent comprendre que des lettres non accentuées, chiffres (pas en première place) et tiret_bas) 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 | ...
| ...
|

3.1 Création d'une table

Étant connecté à une base de données (voir paragraphes précédents), 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");

3.2 Effacement d'une table

Pour effacer une table et sa structure (et les enregistrements éveentuels):

mysql("DROP TABLE nom");

3.3 Tri d'une table

Pour ordonner une table, avec enregistrement physique de la modification dans la table:

mysql_query("ALTER TABLE table ORDER BY champ1, champ2 DESC");

4. Création, effacement et modification de données

On suppose que la table est créée et connectée. 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()). C'est intéressant pour une mise au point, mais devrait être ensuite être enlevé pour plus de confidentialité: personne ne doit savoir le nom des variables que vous utilisez.

4.1 Création 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')");

4.2 Effacement d'enregistrements

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 (voir Conditions):

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

4.3 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.4 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 (voir Conditions):

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

5. Condition(s) 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 à 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 =

5.1 WHERE pour les nombres

... WHERE age=50 lorsque le champs age est exactement égal à 50
... WHERE age<107 lorsque le champs age est strictement inférieur à 107
... WHERE age<=107 lorsque le champs age est inférieur ou égal à 107
... WHERE age>18 lorsque le champs age est strictement supérieur à 18
... WHERE age>=18 lorsque le champs age est supérieur ou égal à 18
... WHERE age<>43 lorsque le champs age est différent de 43
... WHERE age BETWEEN 45 AND 55 lorsque le champs age est compris entre 45 et 55 (45 et 55 inclus)

Comme pour les chaînes:

... WHERE age IN (40, 50, 60) si age doit être égal à une de ces trois valeurs
... WHERE age NOT IN (40, 50, 60) si age ne peut être égalà une de ces trois valeurs

5.1 WHERE 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

Notes:

Comme pour les nombres:

... WHERE sigle IN ('TPI', 'ONU') si sigle doit être une de ces valeurs
... WHERE sigle NOT IN ('TPI', 'ONU') si sigle ne peut pas avoir une de ces deux valeurs

6. Autres

6.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);

Pour compter tous les enregistrements d'une table:

$resultat=mysql("SELECT COUNT(*) matable")

6.2 Trier 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 et que le tri sur un seul champ est nécessaire, il est peut-être bon de l'ordonner après chaque modification, rendant les tris sur requêtes inutiles.

6.3 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>";
 }

6.4 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("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("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("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.

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.