Tutoriel SQL en une page: Gestion des utilisateurs

Nous verrons comment créer une table SQL pour stocker la liste des membres inscrits, et comment écrire toutes les requêtes nécessaires à la gestion d'un fichier d'utilisateurs. L'essentiel des commandes SQL sera ainsi mis en pratique.

Les requêtes à la base de donnée sont écrites en PHP. Toutes les versions de PHP et MySQL supportent ces commandes.

Sommaire
  1. Créer la base de données des utilisateurs.
  2. Table SQL des utilisateurs.
  3. Effacer la table.
  4. Ajouter une colonne.
  5. Supprimer une colonne.
  6. Ajouter un utilisateur.
  7. Modifier une information.
  8. Supprimer un utilisateur.
  9. Rechercher un utilisateur.
  10. Vérifier un login.
  11. Retourner le dernier enregistrement.
  12. Le formulaire d'enregistrement.

Créer la base de données

Dans le fichier de configuration, nommé par exemple config.php, on place les données d'accès à la base:

$HOST = "localhost";
$USER = "nomutilisateur";
$PASS = "motdepasse";
$NAME ="nombase";
$PREFIX = "MyApp_"; 

Si la base de donnée est créé automatiquement par le serveur, remplacer les valeurs par les valeurs fournies par le serveur. Sinon on choisit ses propres noms.
Le nom d'utilisateur est généralement le même que celui de la base de donnée. Il s'agit du logiciel qui se connecte à la base et non de l'internaute.

On ajoute dans config.php le nom des tables en l'occurence la table des utilisateurs:

$users = "nomtable"; 

Pour simplifier, on place en outre le code d'ouverture de connexion à la base dans le même fichier config.php:

$base = mysqli_connect($HOST, $USER, $PASS);
mysqli_select_db($base, $dbname);

Ainsi on inclut config dans chaque fichier faisant appel à la base afin d'y être automatiquement connecté.
La ressource $base sera utilisée par toutes les commandes SQL.

Si la base de donnée est créée par le serveur, passer à l'étape suivante, sinon créez la base, avec cette commande:

mysqli_query($base, "CREATE DATABASE $NAME"); 

Table SQL des utilisateurs

Dans notre exemple simplifier, on enregistre le login et le mot de passe de chaque utilisateur, et un numéro d'identification unique qui sert à relier la table des utilisateurs aux autres tables.

Voici la table, sur la première ligne le nom des colonnes et sur la seconde le type de données:

id login password
integer varchar varchar

La requête de création de la table sera alors:

$query="CREATE TABLE  $users(
           id integer PRIMARY KEY NOT NULL,  
           login varchar(12) UNIQUE NOT NULL,
           password varchar(12) NOT NULL
       )";

La commande PHP devient:

$handler = mysqli_query($base, $query)
    or die("Connexion impossible à $users"); 

Effacer la table

DROP TABLE $users 

Ajouter une colonne

Si l'on veut ajouter une information supplémentaire, par exemple enregistrer les emails, il est possible de modifier la table en ajoutant une colonne dont on précise le nom et le type de donnée:

ALTER TABLE $users ADD emails varchar(64) 

Supprimer une colonne

Pour effacer seulement un champ dans une table déjà créée et remplie:

ALTER TABLE $users DROP COLUMN emails  

La colonne "emails" est supprimée et les données dans la colonne le sont également.

Ajouter un utilisateur

Les données sont fournies par le formulaire dans des variables récupérées par un script PHP:

$id = "123";
$login = "moi";
$password = "xxx";

On passe la requête suivante:

INSERT INTO $users(id, login, password) VALUES('$id','$login','$password')  

Modifier une information

L'utilisateur veut changer de login, on peut modifier cette seule information dans la table:

UPDATE $users SET login='$login' WHERE id=$id

Pour modifier plusieurs données, on les sépare par une virgule:

UPDATE $users SET login='$login', password='$password' WHERE id=$id

Supprimer un utilisateur

Commandé par l'administrateur en se basant sur l'identificateur:

DELETE FROM $users WHERE id=$id 

On peut se baser sur le nom avec WHERE login=$login.

Rechercher un utilisateur

Connaissant le login d'un utilisateur, on veut accéder aux autres données.

SELECT * FROM $users WHERE login=$login

Les données sont récupérées par la commande PHP mysql_fetch_assoc.
Exemple:

$handler = mysqli_query($base, "SELECT * FROM $users WHERE login = '$login'");
$data = mysqli_fetch_assoc($handler);
$id = $data['id'];
$password = $data['password'];

Vérifier un login

Chaque utilisateur utilise un nom unique pour se loguer. Avant toute inscription, il convient de vérifier si le nom proposé n'est pas déjà utilisé, et donc n'existe pas déjà dans la base.

La fonction mysql_num_rows retourne le nombre de colonnes sélectionnées par une requête. Ce nombre vaut 0 quand on cherche une ligne et qu'elle n'existe pas.
D'où la fonction suivante:

function isLoginUsed($login)
{
   global $base;
   global $users;
   $handler = mysqli_query($base, "SELECT * FROM $users WHERE login = '$login'");
   if($handler == false) return 0;
   return(mysqli_num_rows($handler) == 0);
}

La fonction retourne true si le login existe et occupe une ligne dans la base.

Retourner le dernier enregistrement

L'identificateur de chaque utilisateur est un numéro qui s'incrémente automatiquement avec chaque nouvelle inscription.

Le numéro du dernier utilisateur inscrit n'est pas conservé en mémoire entre deux sessions, il faut le retrouver dans la base.

SELECT id FROM $users ORDER BY id DESC LIMIT 1 

- On ne sélectionne que l'id: SELECT id
- On classe par id et en ordre descendant: ORDER BY id DESC
- On ne conserve qu'un seul enregistrement: LIMIT 1

On reprend donc le premier enregistrement dans la liste des identificateurs classés en ordre descendant.
Il reste à incrémenter la valeur retrouvée pour créer un nouvel identificateur.

$handler = mysqli_query($base, "SELECT id FROM $users ORDER BY id DESC LIMIT 1");
$data = mysqli_fetch_assoc($handler);
$id = $data['id'];
$id = intval($id) + 1;

Le formulaire d'enregistrement

Il envoie les données à un script PHP qui ajoute un utilisateur.

<form method="post" action="register.php">
  <input type="text" name="login" value="" >
  <input type="password" name="pass" value="">
</form>

Le script register.php simplifié:

<?php
$login=$_POST['login'];
$password =$_POST['pass'];


// controle du login
isLoginUsed($login) die("Login déjà utilisé");


// création de l'id
$handler = mysqli_query($base, "SELECT id FROM $users ORDER BY id DESC LIMIT 1");
$data = mysqli_fetch_assoc($handler);
$id = $data['id'];
$id = intval($id) + 1;

// ajout de l'utilisateur
$query="INSERT INTO $users(id, login, password) VALUES('$id','$login','$password')";
mysqli_query($base, $query);
?>  

Ce script est purement didactique et sert comprendre la gestion d'un fichier d'utilisateurs. Vous pouvez vous éviter la peine de réaliser des scripts complets par l'emploi d'un framework PHP.