Mysql

Créer une base avec MYSQL

Connectez vous à la console phpMyAdmin

phpmyadmin

Cliquez sur l'onglet Base de données

creer base donnee mysql

Donnez un nom à votre base de données : membres

phpmyadmin

Votre base de données apparaît dans la liste, cliquez dessus pour créer une table

creer base donnee mysql

Donnez un nom pour la table, et indiquez le nombre de colonnes qu'elle contiendra

creer table mysql

Vous allez définir pour chaque champ le type de donnée associé à la valeur qui sera enregistré

definir champ mysql

Le champ ID va se générer automatique grâce à l'option et est déclaré comme étant un clé primaire (il ne peut pas y avoir deux valeurs identiques)

cle primaire mysql

Après validation votre table s'affiche en dessous de la base sélectionnée membres, cliquez sur structure pour afficher le détail de la table

structure table mysql

Retrouvez les informations des champs de votre table

structure table mysql détail

Vous allez apprendre maintenant à créer une connexion avec votre base de données et à alimenter le contenu de la table membres avec PHP

Les types de champs

Les principaux types sont INT (pour les entiers), VARACHAR (pour les petites chaines de caractères), TEXT (pour les champs de grande taille) et DATE (pour les dates)

Entiers

  • TINYINT : codé sur 1 octet
  • INT : codé sur 4 octets
  • BIGINT : codé sur 8 octets

Remarque : Par défaut les entiers sont signés (ils peuvent être positifs ou négatifs). Si vous voulez que les nombres positifs, il faut rajouté le paramètre unsigned à votre entier (de 0 à 255 au lieu de -128 à 127)

unsigned INT

Nombres décimaux

  • REAL ou DOUBLE : nombre à virgule 8 octets
  • FLOAT : nombre à virgule 4 octet
  • DECIMAL ou NUMERIC : nombre stocké comme une chaine

Dates et heures

  • DATE : YYYY-MM-DD
  • YEAR : YYYY
  • TIME : HH:MM:SS
  • TIMESTAMP : YYYYMMDDHHMMSS
  • DATETIME : YYYY-MM-DD-HH-MM-SS

Remarque : La valeur par défaut CURRENT_TIME (sur un champ de type 'timestamp' ou 'datetime' seulement) va créer un champ qui se remplira automatiquement avec INSERT, UPDATE. On y accède donc qu'en lecture.

datetime sql

Chaines de caractères

  • CHAR : longueur de taille définie
  • VARCHAR : longeur de taille maximale

Remarque : Un CHAR(16) occupera toujours 16 octets, même si on entre 1 caractère. VARCHAR est utilisé dans ce cas pour des tailles dynamiques, VARCHAR(16) signifie 16 octets maximum

Champs de grande taille

  • TINYBLOB ou TINYTEXT : 255 caractères
  • BLOB ou TEXT : 65535 caractères
  • MEDIUMBLOB ou MEDIUMTEXT : 16777215 caractères
  • LONGBLOB ou LONGTEXT : 4294967295 caractères (environ 4Go)

Remarque : Les types BLOB sont faits pour stocker des données binaires, les types TEXT sont faits pour gérer des textes lisibles par un humain

Champ auto-incrémenté

Il s'agit d'un entier qui est géré en interne par le système de gestion de la base de données. A chaque insertion, Mysql calcule un nouveau numéro unique qu'il affecte à la ligne insérée. Il suffira de rien spécifier pour ce champ car Mysql renseignera lui-même sa valeur.

Gestion de la clé primaire

Une clé primaire désigne de manière unique un enregistrement de la table. On choisit souvent d'ajouter comme clé primaire un index numérique incrémenté automatiquement. Il en découle que deux enregistrements n'auront jamais la même clé primaire

Connexion avec PDO

PDO est le nouveau système de requêtes sur les bases de données avec php5, PDO est orienté objet et est compatible avec plusieurs types de bases de données (ODBC, Oracle...).

$PARAM_hote='localhost'; // le chemin vers le serveur
$PARAM_nom_bd='membres'; // le nom de votre base de données
$PARAM_utilisateur='root'; // nom d'utilisateur pour se connecter
$PARAM_mot_passe=''; // mot de passe de l'utilisateur pour se connecter
try{
	$connexion = new PDO("mysql:host=$PARAM_hote;dbname=$PARAM_nom_bd", $PARAM_utilisateur, $PARAM_mot_passe);
	$connexion->exec('SET NAMES utf8'); // pour éditer en utf-8
}
catch(Exception $e){
    echo 'Erreur : '.$e->getMessage().'
'; echo 'N° : '.$e->getCode(); }

Astuce : Il est conseillé de créer un fichier de configuration qui contient les paramètres de connexion et l'instanciation de la class PDO (voir ci-dessus) et de l'inclure au début de chaque script utilisant Mysql

include_once('connect.inc.php');
//...traitement mysql....

Les requêtes simples

Il existe deux types de requêtes : exec() et query(). Pour réaliser une requête qui récupére des données de la BDD (SELECT) il faut utiliser "query" et pour les trois autres requêtes comme l'insertion (INSERT) la modification (UPDATE) et la suppression (DELETE) de données, il faut utiliser "exec"

INSERT

$resultat = $connexion->exec("INSERT INTO inscription (nom, prenom, age ) VALUES ('tt', 'isa', 25)");

SELECT

$resultat = $connexion->query("SELECT * FROM inscription ORDER BY id ASC"); // recherche par ordre croissant

DELETE

$resultat = $connexion->exec("DELETE FROM inscription WHERE prenom='isa' ");

UPDATE

$connexion->exec("UPDATE inscription SET nom='tine' WHERE id=1");

Attention : Mysql, comme pour tous les langages, a besoin de protéger certains caractères (l'apostrophe peut faire dérailler la base de données qui l'interpretera comme une fin de chaîne et non pas un apostrophe dans la chaîne de caractères). PDO propose la méthode quote() de l'objet de connexion. Une autre façon de se protèger est l'utilisation des requêtes préparées

$nom = "Jean d'Ailleurs";
$nom = $connexion->quote($nom); // échappement de l'apostrophe 
$connexion->exec("UPDATE inscription SET nom=$nom WHERE id=1");

La méthode 'quote' peut accepter un deuxième paramètre (optionnel), qui indique le type de valeur à protéger

  • * PDO::PARAM_STR : type string
  • * PDO::PARAM_INT : type integer
  • * PDO::PARAM_NULL : type NULL
  • * PDO::PARAM_BOOL : type booléen
  • * PDO::PARAM_LOB : type 'objet large'
$nom = "Jean d'Ailleurs";
$nom = $connexion->quote($nom,PDO::PARAM_STR); // échappement sur le type string 
$connexion->exec("UPDATE inscription SET nom=$nom WHERE id=1");

Note : n'entourez pas, avec les apstrophes, dans votre requête SQL, les variables qui sont protégées par la méthode 'quote()'. PHP se chargera de le faire pour vous

$pseudo = "dupond";
$nom = "Jean d'Ailleurs";
$nom = $connexion->quote($nom); // échappement de l'apostrophe 
$connexion->exec("INSERT INTO inscription (pseudo, nom) VALUES ('$pseudo', $nom)");

Dans cet exemple, la variable protégée $nom n'est pas entre les apostrophes

Récupérer la derniere insertion

Il est parfois utile de connaitre l'identifiant de la dernière ligne insérée. Pour cela on peut utiliser la méthode lastInsertId()

$resultat = $connexion->exec("INSERT INTO inscription (nom, prenom, age ) VALUES ('tt', 'isa', 25)");
echo $connexion->lastInsertId();

Les requêtes préparées

Les requêtes préparées protègent mieux les requêtes. Elle se découpe en deux phases :stockage de la requête compilée en mémoire avec la méthode prepare() puis exécution avec la méthode exec(). Les avantages :

  • - limiter la bande passante utilisée entre le client et le serveur : dû au fait que l'échange d'informations est limité au strict minimum.
  • - éviter les injections Sql : cela concerne la sécurité et évite que les informations rentrées par un client (à travers un formulaire par exemple) soient interprétées.

INSERT

$statement = $connexion->prepare("INSERT INTO inscription (nom, prenom, age ) VALUES (:nom, :prenom, :age)");
$statement->execute(array("nom" => "tt", "prenom" => "isa", "age" => 25 ));

Récupération des données

PDO propose deux façons de récuperer les données après une requête de séléction :

  • - la méthode fetchAll() retourne l'ensemble des données sous forme de tableau et libère la base de données mais cela occasionne une charge importante au niveau du serveur car la totalité des données sont localisées en mémoire
    $resultats=$connexion->prepare("SELECT * FROM inscription ORDER BY id ASC"); 
    $resultats->execute();
    $nbligne = $resultats->fetchAll(PDO::FETCH_OBJ);
    foreach ($nbligne as $row){
    	echo 'Membre : '.$row->prenom.'
    '; // on affiche les inscription }
  • - la méthode fetch() permet une lecture séquentielle du résultat. Cette méthode est très utile pour le traitement de gros résultats. Cependant, vous devrez attendre la fin du traitement avant de faire une autre requête et il n'est pas possible de connaitre le nombre de lignes résultat avant d'avoir parcouru l'intégralité dudit résultat
    $resultats=$connexion->prepare("SELECT * FROM inscription ORDER BY id ASC"); 
    $resultats->execute();
    while( $nbligne = $resultats->fetch(PDO::FETCH_OBJ) ){   // on récupère la liste des inscription 
    	echo 'Membre : '.$nbligne->prenom.'
    '; // on affiche les membres }

Le format des résultats

Le paramètre fetch_style détermine la façon de retourner les résultats. Les trois différents types sont :

  • - PDO::FETCH_ASSOC qui retourne les données sous forme de tableau associatif (par le nom de la colonne)
    $resultats=$connexion->prepare("SELECT * FROM inscription ORDER BY id ASC"); 
    $resultats->execute();
    $nbligne = $resultats->fetchAll(FETCH_ASSOC);
    echo $nbligne[1]['prenom'];
    
  • - PDO::FETCH_BOTH qui retourne les données sous forme de tableau associatif et indexé(par le nom de la colonne et par le numéro de l'index)
    $resultats=$connexion->prepare("SELECT * FROM inscription ORDER BY id ASC"); 
    $resultats->execute();
    $nbligne = $resultats->fetchAll(FETCH_BOTH);
    echo $nbligne[1][0];
    
  • - PDO::FETCH_OBJ qui retourne les données sous forme d'objet (accès par une propriété)
    $resultats=$connexion->prepare("SELECT * FROM inscription ORDER BY id ASC"); 
    $resultats->execute();
    $nbligne = $resultats->fetchAll(PDO::FETCH_OBJ);
    echo $nbligne[1]->prenom;
    

Astuce : le type peut être appelé par la méthode de la class PDOStatement setFetchMode() plutôt qu'en paramètre de la méthode fetch()

$resultats=$connexion->prepare("SELECT * FROM inscription ORDER BY id ASC"); 
$resultats->execute();
$resultats->setFetchMode(PDO::FETCH_OBJ); // on dit ICI qu'on veut que le résultat soit récupérable sous forme d'objet
while( $nbligne = $resultats->fetch() ){   // on récupère la liste des membres 
	echo 'Membre : '.$nbligne->prenom.'
'; // on affiche les membres }

Connaitre le nombre de ligne

Pour connaitre le nombre d'enregistrements, il existe deux techniques qui dépendent de la méthode utilisée pour récupérer les données

* Si vous utilisez la méthode fetchAll(), le principe consiste à compter le nombre d'enregistrements retournés avec la fonction PHP count()

$resultats=$connexion->prepare("SELECT * FROM inscription ORDER BY id ASC"); 
$resultats->execute();
$nbligne = $resultats->fetchAll(PDO::FETCH_OBJ);
echo count($nbligne);

Cette technique peut occasionner une charge importante au niveau du serveur car la totalité des données sont en mémoire

* Une autre technique consiste à utiliser la fonction MYSQL count() qui compte directement le nombre d'énregistrements correspondant à une requête. On lit ensuite le nombre retourné avec la méthode fetchColumn() qui retourne une colonne depuis la ligne suivante d'un jeu de résultats.

$resultats=$connexion->prepare("SELECT count(*) FROM inscription"); 
$resultats->execute();													
$num_rows=$resultats->fetchColumn(); // retourne une colonne depuis la ligne suivante d'un jeu de résultats
echo 'nombre: ' . $num_rows;

Dans ce cas, la requête renvoie le nombre trouvé dans un tableau contenant une ligne et une colonne. La méthode fetchColumn() (voir sur 'php.net') va retourner par défaut la valeur de la première colonne si elle ne contient pas en argument le numéro de la colonne

Filtrer avec WHERE

Le mot clé WHERE permet d'indiquer une ou plusieurs conditions à la requête SELECT. Vous pouvez utiliser un certain nombre d'opérateur

égalité

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE age=30 "); 

différent de

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE prenom != 'daniel' "); 

plus grand que

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE age > 30 "); 

supérieur ou égal à

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE age >= 18 "); 

plus petit que

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE age < 45 "); 

inférieur ou égal à

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE age <= '35' "); 

opérateur ET

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE age <= '35' AND prenom= 'daniel' "); 

opérateur OU

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE prenom= 'robert' OR prenom= 'daniel' "); 

énumération IN

permet de regrouper plusieurs comparaisons OR

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE prenom IN('robert','daniel') "); 

énumération NOT IN

permet de définir une liste de valeurs à exclure

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE prenom NOT IN('jean','david' "); 

opérateur BETWEEN

permet de rechercher des valeurs comprises entre deux limites

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE age BETWEEN 25 AND 35 "); 

opérateur LIKE

permet de rechercher des ressemblances de chaines de caractères. Elle utilise le signe % comme caractère joker. L'exemple suivant permet d'extraire les membres dont les prénom commencent par d

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE prenom LIKE 'd%' "); 

Sélectionner les données

L'astérisque *

Pour sélectionner tous les champs d'une même table, utilisez *

$resultats=$connexion->prepare("SELECT * FROM inscription "); 

Nommer les champs (alias)

Il est possible d'associer un autre nom à une table ou une colonne existante grâce au mot clé AS. Cette méthode permet de raccourcir le nom des éléments pour une relecture et une utilisation plus facile de la requête.

$resultats=$connexion->prepare("SELECT vitesse AS vt FROM 'table'"); 

Un autre exemple avec le renommage d'un table :

$resultats=$connexion->prepare("SELECT vitesse FROM 'tableVoiture' AS 'voiture' "); 

La méthode permet aussi de renommer la colonne sur laquelle il y a une fonction SQL, comme dans l'exemple ci-dessous :

$resultats=$connexion->prepare("SELECT count(*) AS nb_ligne FROM inscription"); 

Trier les éléments

Pour classer les résultats d'une recherche, vous pouvez utiliser la classe ORDER BY

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE age > 30 ORDER BY ASC "); 

Limiter le nombre de résultat

Pour limiter le nombre de résultats, vous pouvez utiliser la clause LIMIT

$resultats=$connexion->prepare("SELECT * FROM inscription WHERE age >= 18 LIMIT 10 "); 

vous pouvez indiquez un nombre de résultat à partir d'une position de départ (à partir de zéro). L'exemple suivant retourne les enregistrements 5 à 19

$resultats=$connexion->prepare("SELECT * FROM inscription LIMIT 4,15 "); 

Gérer les doublons

Il est parfois nécéssaire d'utiliser le mot-clé DISTINCT pour éviter les doublons

$resultats=$connexion->prepare("SELECT DISTINCT  prenom FROM inscription WHERE age>30 "); 

Regrouper les données

Souvent utilisée avec les fonctions d'agrégation (COUNT, SUM), l'instruction GROUP BY permet de regrouper les données. L'exemple ci-dessous retourne la somme totale pour chaque produit.

 
$resultats=$connexion->prepare("SELECT produit, SUM(prix) AS prix_total FROM facture GROUP BY produit "); 

Gérer les jointures

Lorsque les informations se trouvent dans plusieurs tables, il est possible de croiser les données lors de la selection. L'exemple suivant recupère tous les messages (de la table message) précedés du nom de l'auteur (de la table inscription) selon deux méthodes différentes :

* La première méthode consiste à utiliser la clause WHERE et les alias (non obligatoire) pour les noms des tables

$res=$connect->prepare("SELECT mb.nom, mg.texte FROM auteur mb, message mg WHERE mb.id= mg.id_auteur"); 

* La deuxième méthode consiste à utiliser la clause INNER JOIN et les alias (non obligatoire) pour les noms des tables . Cette méthode permet de mieux distinguer les conditions de jointure des conditions de sélection.

$res=$connect->prepare("SELECT mb.nom, mg.texte FROM auteur mb INNER JOIN message mg ON mb.id= mg.id_auteur"); 

Contrairement à la clause WHERE, le ON est obligatoire sinon la requête ne sera pas exécutée

Les functions utiles

Récupérer une date

Pour récupérer une date insérée automatiquement au bon format, employez DATE_FORMAT() qui prend en paramètre le nom de la colonne et le format

$resultats=$connexion->prepare("SELECT titre, DATE_FORMAT(date_creation, '%d/%m/%Y à %H/%i/%s') 
AS date_creation_fr FROM billets ORDER BY date_creation"); 

Les formats utilisés sont les suivants:

  • %d : jour du mois entre 0-31
  • %m : mois au format numérique entre 0-12
  • %Y : année au format numérique sur 4 chiffres (1990)
  • %H : heure entre 00-23
  • %i : minute entre 00-59
  • %s : seconde entre 00-59

Calculer une moyenne

la fonction AVG() calcule la moyenne des valeurs de type numérique et non nul d'une colonne. L'exemple ci-dessous retourne la moyenne d'age des filles et des garçons.

$resultats=$connexion->prepare("SELECT genre, AVG(age) FROM membre GROUP BY genre"); 

Calculer une moyenne

la fonction SUM() calcule la somme des valeurs de type numérique d'une colonne. L'exemple ci-dessous retourne la somme totale pour chaque produit.

 
$resultats=$connexion->prepare("SELECT produit, SUM(prix) AS prix_total FROM facture GROUP BY produit "); 
© Untitled. Design by HTML5UP.