MySQLi, présentation succincte
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 issue d'un début de systématisation de notes personnelles rassemblées selon mes besoins. 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!
- PHP 7.0 a définitivement remplacé les fonctions mysql_*() par mysqli_*(), mais les nouvelles fonctions ne correspondent pas toujours aux anciennes. Les corrections pour cette page n'ont peut-être pas toutes été testées.
- Le gestionnaire MariaDB, remplacement libre de MySQL, reste pleinement compatible avec les fonctions incluses dans mysqli_query().
Il n'est pas possible de traiter ici des conditions d'utilisation de votre serveur SQL, 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 très communs 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 devriez y accéder par l'adresse http://localhost/phpmyadmin ou http://localhost:3306
Un document officiel et très lisible se trouve à cette adresse.
1. Base de données
1.1 Connexion au serveur
1.2 Création d'une base de données
1.3 Connexion à la base de données
2. Tables de données
2.1 Création / effacement de tables
2.2 Types de champ
2.3 Modification de la structure
3. Exploitation des données
3.1 Création d'un enregistrement
3.2 Lecture d'enregistrement(s)
3.3 Modification d'enregistrement(s)
3.4 Effacement d'enregistrement(s)
4. Conditions
5. Autres
5.1 Comptage d'enregistrements
5.2 Tri de table ou de sélection
5.3 Compter et sommer
6. Identification par cookie
7. Sécurité
0. Syntaxe Nv 20.04
Les mots réservés de MySQL sont insensibles à la casse: DROP DATABASE ;, drop database ; et dRoP dAtAbAsE ; sont équivalents. Pour mieux les répérer dans les exemple sur cette page, ils sont systématiquement écrits en capitale.
Le nom des bases, tables ou des champs sont sensibles à la casse: AA, Aa, aA et aa sont considérés comme différents. Ces noms peuvent contenir des caractères accentués et peuvent être entourés de guillemets inverses ``, par exemple pour les différencier des mots réservés. DROP TABLE `DROP` ; est donc possible.
En mode interactif mySQL, une ligne de commande doit se terminer par un point-virgule. Ce n'est pas le cas si la chaîne de commande est passée par un mysqli_query() d'un script PHP.
Il est possible d'inclure un commentaire dans un script de commandes (concerne moins l'utilisation de mySQL dans un script de PHP):
# met en commentaire tout ce qui suit jusqu'à la fin de la ligne
-- idem, mais l'espace est obligatoire juste après les deux traits d'union
De /* jusqu'à */ (éventuellement sur plusieurs lignes) comme dans la syntaxe du C et consorts.
1. Base de données
La location d'un espace WEB chez un serveur implique souvent le service MySQL, à activer ou non. En général, le nom de la base de données (DB pour DataBase) est décidé pour vous, et parfois même le mot de passe. Cela est à vérifier sur votre compte chez le serveur.
Les serveurs utilisant PHP et mySQL offrent généralement le service PHPmyAdmin, qui possède une interface graphique permettant la gestion de la base de données. Mais il est possible de tout faire à partir de scripts.
Une base de données contient au moins une table, le plus souvent un ensemble de tables, ces dernières contenant les données, sous forme de tableaux à deux dimensions, voir 2.
Il est possible d'installer un serveur sur son système (LAMP: Linux-Apache-MySQL-PHP) ou WAMP avec Windows. Dans ce cas, l'adresse de connexion (sur UNIX) est http://localhost, l'identifiant et le mot de passe sont choisis lors de l'installation, et le nom de la base de données lors de votre premier passage sur PHPmyAdmin, ou en mode interactif, voir 1.2.
Nous considérons ici une connexion à un serveur extérieur.
1.1 Connexion au serveur
Vous disposez bien évidemment d'une adresse, d'un identifiant et d'un mot de passe de connexion.
Toute page PHP utilisant une base de donnée doit comprendre (ou charger un fichier contenant) ceci:
$connexion=mysqli_connect("sql.monserveur.org", "utilisateur", "motdepasse") ;
La fin du fichier devrait également comporter:
mysqli_close($connexion);
1.2 Création de base de données
Dans le cas où la création même de la base de donnée est à faire (serveur WEB sans PHPmyAdmin, ou chez vous), il faut créer (un seule fois!) la DB. Le fichier minimal sera:
<?php
$connexion=mysqli_connect("sql.monserveur.org", "utilisateur", "motdepasse");
mysqli_query($connexion, "CREATE DATABASE 'MaDB'");
mysqli_close($connexion);
?>
À ce moment, la base de données est créée mais ne contient rien, même pas une table vide.
mysqli_query($connexion, "DROP DATABASE 'MaDB'"); détruit irrémédiablement la base de données MaDB, ses tables et leurs enregistrements.
1.3 Connexion à la base de données
Pour les prochaines étapes, il faudra toujours que la connexion ait été faite et une base de donnée sélectionnée (mais ce ne sera plus jamais précisé):
<?php
$connexion=mysqli_connect("sql.monserveur.org", "utilisateur", "motdepasse", "basededonnee");
… votre script PHP contenant les commandes mysqli_xxx();
mysqli_close($connexion);
?>
mysqli_connect permet une connection qui s'arrête normalement avec le script. Il existe également la connexion permanente avec p: en préfixe du nom de serveur:
$connexion=mysqli_connect("p:sql.monserveur.org", "utilisateur", "motdepasse", "base de données);
…mais elle est déconseillée (il vaut mieux fermer les portes) et elle dépend de la configuration du serveur.
2. Tables de données Rév. 20.04
On visualise généralement une table comme étant composée de colonnes de données de même type (les champs: taille, age, tva, code_postal… ne peuvent comprendre que des lettres (même accentuée mais je le déconseille), tiret_bas et chiffres (pas en première place?) et des lignes de données appartenant à un même enregistrement (un événément, une personne particulière… un «individu» statistique).
+----+------------+---------+--------+----------+---+---- | id | date | couleur | forme | grandeur | … | +----+------------+---------+--------+----------+------ | 0 | 2020-04-19 | rouge | ovoïde | petit | … | 1 | 2020-04-19 | vert | carré | moyen | | 2 | 2020-04-21 | vert | rond | petit | 3 | 2020-04-22 | … | … | … | 4 | … | … | |
Attention: il est préférable que les noms de tables et de variables ne soient pas prédictibles (kwR9_z plutôt que name), parce que certaines techniques de crackage se basent sur les noms les plus courants.
Une base de données relationnelles n'a rien de particulier: il s'agit simplement d'une technique permettant de ne pas répéter certaines données en ne les plaçant qu'une fois dans une autre table. Au lieu d'une seul base, on en fait deux sans redondance, en liant les données par exemple par un numéro d'ordre (id doit être unique):
Table unique avec redondances Deux tables liées par un nombre, sans redondance +----------------+-----------+ +----+-----------+ +--------------------+ | nom | telephone | | qi | telephone | | id | nom | +----------------+-----------+ +----+-----------+ +----+---------------+ | Durant, Jean | 111111111 | | 1 | 111111111 | | 1 | Durant, Jean | | Durant, Jean | 222222222 | | 1 | 222222222 | | 2 | Dubois, Paul | | Durant, Jean | 333333333 | | 1 | 333333333 | | … | | Dubois, Paul | 444444444 | | 2 | 444444444 | | Dubois, Paul | 555555555 | | 2 | 555555555 | | … | | … | |
Cela ne sert pas à grand chose s'il n'existe que peu de redondances (bien qu'un numéro d'identification unique soit conseillé sur toutes les tables, ce que l'on ne peut pas associer à un coût en terme de place mais à une nécessité). Cela permettra de gagner énormément de place en cas de quelques centaines de maisons d'édition (avec numéro de téléphone, site web, adresse, responsable des ventes…) pour quelques millions d'ouvrages.
Il existe des techniques et commandes pour joindre des tables reliées par un champ «commun». Celui-ci ne ne doit pas nécessairement être numérique, mais comme ils peuvent être auto-incrémentés, la gestion d'un numéro unique est assez simple.
2.1 Création d'une table
Étant connecté à une base de données (voir section précédente), on crée une table en indiquant le nom de chaque champ, suivi de la nature de la variable (entier, chaîne, et éventuellement le type d'organisation interne de la table (MyISAM est économe en place utilisée pour les textes et les blobs).
mysqli_query("CREATE TABLE Gens (nb INT AUTO_INCREMENT, nom VARCHAR(25) NOT NULL) ENGINE=MYISAM";
Attention: il semble impossible de forcer a posteriori l'auto-incrémentation sur une si variable contient un 0. De toute façon, un 0 comme identificateur n'est jamais une bonne idée car 0 est souvent le résultat d'une erreur.
mysqli_query($connexion, "DROP TABLE 'Gens'"); efface irrémédiablement une table, sa structure et ses enregistrements.
2.2 Types de champs
Cette partie est moins intuitive qu'il n'y paraît, les possibilités de requêtes variant selon le type des champs auxquels elles s'adressent.
Champs numériques
Les entiers, leurs capacités et la longueur requise pour l'affichage:
TINYINT (1 octet en mémoire) : de -128 à 127 (4 octet de sortie). UNSIGNED : de 0 à 255 (3)
SMALLINT (2 octets) : de -32768 à 32767 (6). UNSIGNED : de 0 à 65535 (5)
MEDIUMINT (3 octets) : de -8388608 à 8388607 (8). UNSIGNED : de 0 à 16777215 (8)
INT (4 octets) : de -2147483648 à 2147483647 (11). UNSIGNED : de 0 à 4294967295 (10)
BIGINT (8 octets) : de -9223372036854775808 à 9223372036854775807 (20). UNSIGNED : de 0 à 18446744073709551615 (20)
- ZEROFILL peut être ajouté pour l'affichage de 0 à la place d'espaces lorsque la totalité du champ n'est pas utilisée
- ? En cas de dépassement de capacité, pas d'erreur retournée, mais la valeur la plus proche est écrite.
Les non entiers
DECIMAL (ou NUMERIC), qualifié par un nombre de chiffres et un nombre de chiffres après la virgule. DECIMAL (5,2) permet de -999,99 à 9999,99, l'absence de signe négatif pouvant être utilisé pour un chiffre supplémentaire.
FLOAT (4 octets) est un nombre à virgule flottante (possibilité de paramétrage comme pour DECIMAL, possibilité de passage à 8 octets)
? REAL et DOUBLE (8 octets), précision non paramétrable
Champs «chaînes»
Mode «caractères»
CHAR (n) permet un stockage de chaîne jusqu'à n caractères (<256), taille fixe en mémoire
VARCHAR (n) permet le stockage de chaîne jusqu'à n (<256) caractères + 1 octets en mémoire, en ne stockant en mémoire que le nombre réel de caractères + 1 octet
TINYTEXT semble un synonyme pour VARCHAR (255) ; TEXT, MEDIUMTEXT et LONGTEXT (sans spécification de longueur) permettent des textes allant respectivement jusqu'à 65535, 16777215 et 4294967295 caractères. Comme VARCHAR, il ne prennent en mémoire que leur taille réelle, + 2, 3 ou 4 octets codant leur longueur.
Un caractère se codant parfois sur plus d'un octet (UTF), le stockage peut être plus grand que 255 pour CHAR et 256 pour VARCHAR.
- ? La façon de coder les caractères (cp1252, UTF8, UCS…) dépend du paramétrage de la base de données
- La COLLATION (interclassement) permet un mode de classement alphabétique particulier (en français, 'œ' entre 'od' et 'of')
Mode «octet»
BINARY (n) permet un stockage de chaîne jusqu'à n octets (<256), taille fixe de n octets en mémoire
VARBINARY (n) permet le stockage de chaînes jusqu'à n (<256) octets octet en mémoire, en ne stockant en mémoire que le nombre réel d'octets + 1
TINYBLOB semble un synonyme pour VARBINARY (255) ; BLOB, MEDIUMBLOB et LONGBLOB (sans spécification de longueur) permettent des textes respectivement de 65535, 16777215 et 4294967295 octets. Comme VARBINARY, il ne prennent en mémoire que leur taille réelle.
- Le tri se fait selon la valeur des octets, aucun tri particulier COLLATION n'est possible.
Date
DATE pour une date au format AAAA-MM-JJ, intervalle garanti de "1000-01-01" à "9999-12-31", année sur quatre chiffres ; mois et jour sur deux. "830714" devrait être interprété "1983-04-14"
TIME pour une date au format HH-MM-SS ou HHH-MM-SS, intervalle garanti de "-838:59:59" à "838:59:59", qui permet de compter des différences d'heures entre deux dates.
DATETIME pour une date-heure au format AAAA-MM-JJ HH:MM:SS, mois, jour, heure, minute et seconde toujours sur deux chiffres, date garantie de "1000-01-01 00:00:00" à "9999-12-31 23:59:59"
TIMESTAMP est limité aux dates entre 1970 et 2037 (limites d'un entier signé sur quatre octets à partir de 0 pour le 1er janvier 1970 à 00:00:00)
- d'autres délimiteurs que '-' et ':' sont possibles, et même une absence de tout délimiteur: 20090214003130 sera interprété "2009-02-14 00:31:30"
- on peut nourrir de tels champs avec NOW() ou CURRENT_DATE dans une requête
- un nombre issu de timestamp() de PHP dans un champ INT est parfois plus pratique
Autres
Il existe encore les champs de type YEAR (entre 1901 et 2155), SET et ENUM.
2.3 Modification d'une structure de table ?
Pour ajouter un champ:
mysqli_query($connexion, "ALTER TABLE 'Gens' ADD 'nom' VARCHAR(25) AFTER 'id'");
L'ajout du champ se fait en fin de liste, sauf si l'endroit est précisé par FIRST (en première place) ou AFTER 'nom' (après le champ nom):
Pour retirer un champ d'une table (MySQLi):
mysqli_query($connexion, "ALTER TABLE 'Gens' DROP 'nom'");
3. 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=mysqli_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.
mysqli_query("requête MySQL") or die(mysqli_error()); arrête un script si MySQL détecte une erreur (die()) et en donne la raison). Ce n'est plus vrai ou il faut installer une librairie complémentaire. Il est toujours possible de prévoir une chaîne permettant de pister la requête défaillante.
C'est intéressant pour une mise au point, mais devrait être ensuite être enlevé pour plus de confidentialité: personne ne doit connaître votre cuisine interne.
mysqli_connect_errno(); renvoie le numéro (non nul) de l'erreur.
3.1 Création d'enregistrements 2020.03
Pour ajouter un nouvel enregistrement (une rangée, un individu):
mysqli_query($connexion, "INSERT INTO Gens (id, nom) VALUES ('123', 'Robert')");
Autant de couple champ/valeur que l'on veut ; les valeurs doivent être entourées de guillemets. À condition de respecter l'ordre et l'exhaustivité des valeurs, il est possible de ne pas citer les champs:
mysqli_query($connexion, "INSERT INTO Gens VALUES ('123', '$nom', '$prenom', '$naissance', 'mort', 'bio')") or die();
Attention: un guillemet simple n'est pas le bienvenu car il permettrait d'insérer du code dans la requête (attaque par injection). La configuration du serveur MySQL/MariaDB devrait normalement empêcher l'enregistrement. Il est donc nécessaire de remplacer les guillemets simples par les guillemets typographiques:
$var=str_replace("'", "’", $var);
Quelques pistes en cas d'erreur :
- un champ à valeurs uniques ne peut recevoir une valeur qu'il contient déjà dans un de ses enregistrements ;
- la requête doit prévoir une valeur pour les champs non NULL (ils le sont par défaut) sauf s'il a été prévu dans la table une valeur par défaut, une empreinte temporelle ou une auto-incrémentation.
3.2 Lecture
Pour la lecture de certains champs de tous les enregistrements d'une table:
$resultats= mysqli_query($connexion, "SELECT nom, prenom FROM Gens");
while($rangee=mysqli_fetch_array($resultats))
{
$var1=$rangee['champ1'];
$var2=$rangee['champ2'];
$var3=$rangee['champ3'];
echo "$var1 - $var2 - $var3<br>";
}
Il est possible de sélectionner des entregistrements avec un filtre (voir Conditions):
$resultats=mysqli_query($connexion, "SELECT nom, prenom, age FROM gens WHERE condition");
while($rangee=mysqli_fetch_array($resultats))
{
…
}
3.3 Modifier des enregistrements
Pour modifier des champs de tous les enregistrements d'une table:
mysqli_query($connexion, "UPDATE Gens SET champ1='$val1', champ2='$val2');
Pour modifier une sélection d'enregistrements, il faut utiliser un filtre (voir Conditions):
mysqli_query($connexion, "UPDATE Gens SET champ1='$val1', champ2='$val2' WHERE condition");
Attention: un guillemet simple n'est pas le bienvenu car il perturbe la requête (et permettrait de la modifier, on a appelé cette technique «attaque par injection de code»). La configuration du serveur MySQL/MariaDB devrait normalement empêcher l'enregistrement. Il est donc nécessaire de remplacer les guillemets simples par les guillemets typographiques avant tout enregistrement:
$var=str_replace("'", "’", $var);
Il est possible d'ajouter d'incrémenter un nombre et allonger une chaîne d'un champ avec la requête suivante:
UPDATE maTable SET ajout=ajout+10, texte = CONCAT(texte, '$msg') WHERE nr = '$n'
Pour permettre un départ avec un champ NULL, il faut spécifier:
UPDATE maTable SET texte = CONCAT(IFNULL(texte,''), '$msg') WHERE nr='$n'
3.4 Effacement d'enregistrements
Pour supprimer tous les enregistrements d'une table (vider sans effacer la table elle-même):
mysqli_query($connexion, "DELETE FROM 'Gens'");
Pour supprimer une sélection d'enregistrements, il faut utiliser un filtre (voir Conditions):
mysqli_query($connexion, "DELETE FROM 'Gens' WHERE condition");
4. Condition(s) avec WHERE
Les commandes SELECT, UPDATE et DELETE traitent par défaut tous les enregistrements d'une table. Le mot WHERE permet de filtrer les enregistrements selon des critères définis. Par exemple, pour ne détruire que le(s) enregistrement(s) ayant le nombre ou la chaîne 43 dans le champ nbr:
mysqli_query("DELETE FROM table WHERE nbr='43'");
Attention: contrairement à de nombreux langages, le test d'égalité est représenté par un simple =
4.1 WHERE pour les nombres
… WHERE nbr=50 lorsque le champ nbr doit être exactement égal à 50
… WHERE nbr<>43 lorsque le champ nbr doit être différent de 43
… WHERE nbr<107 lorsque le champ nbr doit être strictement inférieur à 107
… WHERE nbr<=107 lorsque le champ nbr doit être inférieur ou égal à 107
… WHERE nbr>20 lorsque le champ nbr doit être strictement supérieur à 20
… WHERE nbr>=20 lorsque le champ nbr doit être supérieur ou égal à 20
… WHERE nbr BETWEEN 45 AND 55 lorsque le champ nbr doit être compris entre 45 et 55 (45 et 55 inclus)
… WHERE nbr IN (40, 50, 60) si nbr doit être égal à une de ces trois valeurs
… WHERE nbr NOT IN (40, 50, 60) si nbr ne peut être égal à une de ces trois valeurs
4.2 WHERE pour les chaînes
… WHERE texte='cabot' si l'entièreté du champ texte doit être cabot
… WHERE texte LIKE 'cab%' si le champ texte doit commencer par cab
… WHERE texte LIKE '%bot' si le champ texte doit finir avec bot
… WHERE texte LIKE '%abo%' si le champ texte doit contenir abo
… WHERE texte LIKE 'ca__t' si le champ texte doit commencer avec ca, termine avec t et de caractères indéterminés sur les 3e et 4e positions
… WHERE texte IN ('TPI', 'ONU', 'UNESCO') si texte figure parmi les valeurs proposées
… WHERE texte NOT IN ('TPI', 'ONU', 'UNESCO') si texte ne figure pas parmi les valeurs proposées
Notes:
- le % représente n'importe quel nombre de caractères, même une chaîne vide
- pour rendre les comparaisons = et LIKE sensibles à la casse: … WHERE champ=BINARY 'Cabot'
4.3 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:
- ouvrages contenant les champs titre et aut, reprenant le numéro de l'auteur de l'ouvrage
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 auteurs, et à partir de l'id, faire une requête sur les ouvrages écrits par chaque auteur:
$resultat=mysql("SELECT id, nom FROM auteurs WHERE id LIKE 'a%'");
while($data=mysqli_fetch_array($resultats))
{
$nom=$data['nom'];
$id=$data['id'];
echo "<p><b>$auteur</b></p>";
$resultat1=mysqli_query("SELECT titre, aut FROM ouvrages WHERE aut='$id'");
while($data1=mysqli_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, auteurs.id, ouvrages.titre, ouvrages.aut FROM auteurs, ouvrages WHERE auteurs.id=ouvrages.aut ORDER BY auteurs.nom");
$nom0="";
while($data=mysqli_fetch_array($res))
{
$nom=$data['auteurs.nom'];
$titre=$data['titre'];
if($nom!=$nom0) { echo "<p><b>$auteur</b></p>"; $nom0=$nom; }
echo "- $titre<br>";
}
Comme la sélection portent sur deux tables, auteurs et ouvrages, il faut préfixer les champs du nom de la table dont ils font partie. La jointure se fait dans la clause WHERE, où le numéro unique du champ id de la table auteurs doit correspondre au champ aut de la table des ouvrages.
Attention à l'ordre dans lequel les enregistrements sont lus, il y a parfois des surprises.
5. Autres
5.1 Comptage d'enregistrements
Pour compter tous les enregistrements d'une table:
$resultat=mysqli_query("SELECT COUNT(*) gens")
Pour compter le nombre d'enregistrements répondant à un critère:
$resultats=mysqli_query("SELECT champ1 FROM table WHERE champ1>'50'");
$nombre=mysqli_num_rows($resultats);
5.2 Tri de table ou de sélection
Pour sélectionner des articles, classés par prix décroissant, sous-classement par nom d'article:
$resultat=mysqli_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.
Pour ordonner une table, avec enregistrement physique de la modification dans la table (non testé):
mysqli_query("ALTER TABLE gens ORDER BY nom, prenom DESC");
- DESC permet d'inverser l'ordre du champ qu'il suit
- le classement des chaînes se fera selon la méthode choisie à la création de la table: pour un tri ne prenant pas en compte les majuscules ni les accents, utiliser l'interclassement utf8_unicode_ci. utf8_bin commence avec les majuscules, et termine avec les lettres accentuées.
- ne pas abuser: un travail de tri croît généralement au carré du nombre d'éléments traités
5.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=mysqli_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=mysqli_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. 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=mysqli_connect("sql.monserveur.org", "utilisateur", "motdepasse", "basededonnee");
$resultats=mysqli_query("SELECT id,mp FROM mdp WHERE id='$id'");
if ($mp=$rangee['mp'])
{
$now=time(); $tps=lcg_value(); $cookie=;
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=mysqli_connect("sql.monserveur.org", "utilisateur", "motdepasse", "basededonnee");
}
else
{
…procédure de connexion
}
…votre code…
mysqli_close($link);
?>
Les cookies pouvant être interceptés (connexions non sécurisées) ou récupérés (cas d'ordinateurs publics), il faut prévoir de les effacer, de les modifier à chaque nouvelle page, voire de limiter la durée de validité du cookie… Le cookie de session respecteux de la vie privée devrait être limité à un nombre aléatoire, l'heure-limite étant à conserver du côté serveur avec la valeur du cookie.
7. Sécurité
Protéger la base de donnée est du ressort du langage du script utilisé pour l'exploiter. Pour PHP, voir cette page.
Utiliser le protocole https
Les données envoyées par un internaute au serveur de votre site peuvent faire le tour de la terre. Il faut donc qu'elles soient transmises par liaison sécurisée, ce que permet le protocole https (s pour «secure»).
Utiliser des noms improbables
Certaines techniques s'attaquant à des pages PHP/MySQL naïves permettent de lire des enregistrements, voire les effacer, ainsi que des tables. C'est surtout vrai quand on peut facilement deviner leur nom. Utilisez donc toujours des noms de tables et de champs qui «ne tombent pas sous le sens», donc pas de main, personnes, users, articles… pour les tables, ni de id, name, user, age, password… pour les champs. C'est d'autant plus vrai pour l'installation de scripts récupérés sur Internet qui n'ont de secret pour personne.
Méfiez-vous du 0 ou d'une chaîne vide
Un code erroné peut générer des variable égales à zéro cotenant une chaîne vide. Dans le cas d'une condition, tous les champs vides ou égaux à zéro permettent la lecture, la modification ou la destruction des enregistrement d'une table. L'enregistrement 0 d'une table est de la même façon particulièrement sensible.
Vérifiez les valeurs saisies par les internautes
Une technique souvent utilisée est l'«injection de code» par introduction de chaînes spéciales dans un formulaire (POST) ou d'une adresse URL (GET), qui parvient à modifier la requête en permettant la lecture de données, la destruction de champs ou de table, ou la connexion en tant qu'administrateur.
Il faut donc toujours vérifier les données renvoyées par formulaires et URL avant de les confier à SQL.
- vérifier qu'il s'agit d'un nombre si la donnée doit l'être
- traquer les guillemets dans les chaînes qui ne doivent pas en contenir (attention: ajouter des antislashes \ ne suffit pas).
- interdire certains caractères dans des mots de passe, comme = ; # % --
Ne jamais stocker les mots de passe en clair
La sécurité n'est jamais sûre à 100%, il est donc possible que la table contenant les identifiants et mots de passe ait été lue. Tout n'est pas perdu si les mots de passe n'ont pas été codé en clair, mais transformés en bouillie de chiffres et de lettres avant stockage par une fonction de hashage.