Site Web avec PHP et MySQL
Base de donn?es
Cr?ation d'une table avec phpMyAdmin
Les diff?rentes colonnes de la table sont appel?es "champ".
Les param?tres des champs sont :
Champ : permet de d?finir le nom du champ;
Type : le type de donn?es que va stocker le champ (nombre entier, texte, date? ) ;
Taille/Valeurs : permet d'indiquer la taille maximale du champ, utile pour le type VARCHAR notamment, afin de limiter le nombre de caract?res autoris?s ;
Index : active l'indexation du champ. Ceci signifie que votre champ sera adapt? aux recherches. Le plus souvent, on utilise l'index PRIMARY sur les champs de type id ;
AUTO_INCREMENT : permet au champ de s'incr?menter tout seul ? chaque nouvelle entr?e. On l'utilise fr?quemment sur les champs de type id.
Plusieurs types de champs MySQL existent :
INT : nombre entier ;
VARCHAR : texte court (entre 1 et 255 caract?res) ;
TEXT : long texte (on peut y stocker un roman sans probl?me) ;
DATE : date (jour, mois, ann?e).
Toute table doit poss?der un champ qui joue le r?le de cl? primaire. La cl? primaire permet d'identifier de mani?re unique une entr?e dans la table. En g?n?ral c'est le champ "id".
Connexion ? la base de donn?es
Pour cela on va utiliser MySQL. Les fonctions de l'extension mysql_ sont obsol?tes. Il faudrait donc utiliser l'extension mysqli_ ou PDO permettant de se connecter aussi bien ? MySQL que PostgreSQL ou Oracle. PDO est activ? par d?faut.
4 renseignement sont n?c?ssaires :
le nom de l'h?te : c'est l'adresse de l'ordinateur o? MySQL est install? (comme une adresse IP) : localhost.
la base : c'est le nom de la base de donn?es ? laquelle vous voulez vous connecter.
le login : il permet de vous identifier.
le mot de passe : il y a des chances pour que le mot de passe soit le m?me que celui que vous utilisez pour acc?der au FTP. Renseignez-vous aupr?s de votre h?bergeur.
Code PHP :
<?php //connexion et test de la pr?sence d'erreur try{ $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', ''); } catch (Exception $e){ die('Erreur : ' . $e->getMessage()); } ?>
En cas d'erreur, PDO renvoie ce qu'on appelle une exception qui permet de ? capturer ? l'erreur.
R?cup?rer les donn?es
Code PHP :
<?php try{ // On se connecte ? MySQL $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', ''); } catch(Exception $e){ // En cas d'erreur, on affiche un message et on arr?te tout die('Erreur : '.$e->getMessage()); } // Si tout va bien, on peut continuer // On r?cup?re tout le contenu de la table jeux_video $reponse = $bdd->query('SELECT * FROM jeux_video'); // On affiche chaque entr?e une ? une while ($donnees = $reponse->fetch()) { ?> <p> <strong>Jeu</strong> : <?php echo $donnees['nom']; ?><br /> Le possesseur de ce jeu est : <?php echo $donnees['possesseur']; ?>, et il le vend ? <?php echo $donnees['prix']; ?> euros !<br /> Ce jeu fonctionne sur <?php echo $donnees['console']; ?> et on peut y jouer ? <?php echo $donnees['nbre_joueurs_max']; ?> au maximum<br /> <?php echo $donnees['possesseur']; ?> a laiss? ces commentaires sur <?php echo $donnees['nom']; ?> : <em><?php echo $donnees['commentaires']; ?></em> </p> <?php } $reponse->closeCursor(); // Termine le traitement de la requ?te ?>
Vous devez effectuer cet appel ? closeCursor() chaque fois que vous avez fini de traiter le retour d'une requ?te, afin d'?viter d'avoir des probl?mes ? la requ?te suivante.
Crit?res de s?lection : WHERE, ORDER BY, LIMIT
WHERE : permet de trier les donn?es;
ORDER BY : permet d'ordonn?es les donn?es;
LIMIT : s?lection d'une partie des r?sultats (par exemple les 20 premiers).
Code PHP :
/************************************************************************ WHERE : Trier ************************************************************************/ //S?lectionner tous les champs de la table jeux_video lorsque le champ possesseur est ?gal ? Patrick $reponse = $bdd->query('SELECT * FROM jeux_video WHERE possesseur=\'Patrick\''); $reponse = $bdd->query('SELECT nom, possesseur FROM jeux_video WHERE possesseur=\'Patrick\''); //combinaison de plusieurs conditions: AND et OR $reponse = $bdd->query("SELECT * FROM jeux_video WHERE possesseur='Patrick' AND prix < 20"); /************************************************************************ ORDER BY : Ordonner ************************************************************************/ //Les r?sultats sont ordonn?s par prix croissants $reponse = $bdd->query('SELECT nom, prix FROM jeux_video ORDER BY prix'); //Les r?sultats sont ordonn?s par prix d?croissants $reponse = $bdd->query('SELECT nom, prix FROM jeux_video ORDER BY prix DESC'); /************************************************************************ LIMIT : s?lection d'une partie des r?sultats ************************************************************************/ //S?lectionne les dix premi?res entr?es ; $reponse = $bdd->query('SELECT nom FROM jeux_video LIMIT 0, 10'); //LIMIT 5, 10 : affiche de la sixi?me ? la quinzi?me entr?e ; $reponse = $bdd->query('SELECT nom FROM jeux_video LIMIT 5, 10'); /************************************************************************ Utiliser tous les crit?res en m?me temps. ATTENTION : Il faut respecter l'ordre : WHERE, ORDER BY, LIMIT ************************************************************************/ $reponse = $bdd->query("SELECT nom, possesseur, console, prix FROM jeux_video WHERE console='Xbox' OR console='PS2' ORDER BY prix DESC LIMIT 0,10");
Il existe aussi le mot-cl? OR qui signifie ? ou ?.
Si on avait utilis? ORDER BY sur un champ contenant du texte, le classement aurait ?t? fait par ordre alphab?tique.
Les requ?ttes pr?par?es
Eviter de concatener des variables dans une requ?tte.
La mauvaise id?e : concat?nation
Code PHP :
<?php $reponse = $bdd->query('SELECT nom FROM jeux_video WHERE possesseur=\'' . $_GET['possesseur'] . '\''); ?>
Le syst?me de requ?tes pr?par?es est plus s?r mais aussi plus rapide pour la base de donn?es si la requ?te est ex?cut?e plusieurs fois.
Dans un premier temps, la requ?te est pr?par?e sans sa partie variable, que l'on repr?sentera par un point d'interrogation. La requ?te est ensuite ex?cut?e ? l'aide des param?tres que l'on a indiqu?s sous forme d'array:
Code PHP :
<?php try{ $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', ''); } catch(Exception $e){ die('Erreur : '.$e->getMessage()); } /************************************************************************ //Exemple 1 : une seule variable ************************************************************************/ $req = $bdd->prepare('SELECT nom FROM jeux_video WHERE possesseur = ?'); $req->execute(array($_GET['possesseur'])); /************************************************************************ //Exemple 2 : Plusieurs variables (les param?tre doivent ?tre indiqu?es dans le bon ordre) //lister les jeux appartenant ? une personne et dont le prix ne d?passe pas une certaine somme ************************************************************************/ $req = $bdd->prepare('SELECT nom FROM jeux_video WHERE possesseur = ? AND prix <= ?'); $req->execute(array($_GET['possesseur'], $_GET['prix_max'])); echo '<ul>'; while ($donnees = $req->fetch()){ echo '<li>' . $donnees['nom'] . ' (' . $donnees['prix'] . ' EUR)</li>'; } echo '</ul>'; $req->closeCursor(); /************************************************************************ //Exemple 3 : Avec des marqueurs nominatifs (dans le cas o? la requette est trop longue) ************************************************************************/ $req = $bdd->prepare('SELECT nom, prix FROM jeux_video WHERE possesseur = :possesseur AND prix <= :prixmax'); $req->execute(array('possesseur' => $_GET['possesseur'], 'prixmax' => $_GET['prix_max'])); ?>
Traquer les erreurs
Prenez l'habitude de rajouter le code or die(print_r($bdd->errorInfo())) sur la m?me ligne que votre requ?te pour afficher des d?tails sur l'erreur.
Code PHP :
<?php //Requ?tte simple $reponse = $bdd->query('SELECT nom FROM jeux_video') or die(print_r($bdd->errorInfo())); //Requ?tte pr?par?e $req = $bdd->prepare('SELECT nom, prix FROM jeux_video WHERE possesseur = :possesseur'); $req->execute(array('possesseur' => 'Patrick', 'prixmax' => 20)) or die(print_r($req->errorInfo())); ?>
Supposons que j'ai mal ?cris le nom du champ. L'erreur affich?e sera :
Code :
Unknown column 'champinconnu' in 'field list'
?crire des donn?es
INSERT : Ajouter des donn?es
Code PHP :
<?php try{ $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', ''); } catch(Exception $e){ die('Erreur : '.$e->getMessage()); } // On ajoute une entr?e dans la table jeux_video $bdd->exec('INSERT INTO jeux_video(ID, nom, possesseur, console, prix, nbre_joueurs_max, commentaires) VALUES('', \'Battlefield 1942\', \'Patrick\', \'PC\', 45, 50, \'2nde guerre mondiale\')'); echo 'Le jeu a bien ?t? ajout? !'; ?>
On commence par les mots-cl?s INSERT INTO, suivis ensuite par le nom de la table (ici jeux_video), puis les noms des champs dans lesquels vous souhaitez placer des informations. On inscrit VALUES suivi des valeurs ? ins?rer dans le m?me ordre que les champs que l'on a indiqu?s.
Pour le premier champ (ID), les es apostrophes sont vides. C'est voulu : le champ a la propri?t? auto_increment, MySQL mettra donc le num?ro d'ID lui-m?me
Insertion de donn?es variables gr?ce ? une requ?te pr?par?e
Le principe est le m?me que dans le chapitre pr?c?dent
Code PHP :
<?php $req = $bdd->prepare('INSERT INTO jeux_video(nom, possesseur, console, prix, nbre_joueurs_max, commentaires) VALUES(:nom, :possesseur, :console, :prix, :nbre_joueurs_max, :commentaires)'); $req->execute(array( 'nom' => $nom, 'possesseur' => $possesseur, 'console' => $console, 'prix' => $prix, 'nbre_joueurs_max' => $nbre_joueurs_max, 'commentaires' => $commentaires )); echo 'Le jeu a bien ?t? ajout? !'; ?>
UPDATE : Modifier des donn?es
Code PHP :
<?php //Le nombre de lignes modifi? est renvoy? $nb_modifs = $bdd->exec('UPDATE jeux_video SET possesseur = \'Florent\' WHERE possesseur = \'Michel\''); echo $nb_modifs . ' entr?es ont ?t? modifi?es !'; //===Requ?te pr?par?e== $req = $bdd->prepare('UPDATE jeux_video SET prix = :nvprix, nbre_joueurs_max = :nv_nb_joueurs WHERE nom = :nom_jeu'); $req->execute(array( 'nvprix' => $nvprix, 'nv_nb_joueurs' => $nv_nb_joueurs, 'nom_jeu' => $nom_jeu )); ?>
DELETE : Supprimer des donn?es
Code SQL :
DELETE FROM jeux_video WHERE nom='Battlefield 1942'
Les fonctions SQL
Les fonctions scalaires : UPPER, LOWER, LENGTH, ROUND
Les fonctions scalaires modifient les valeurs.
Exemple : Conversion d'un champ en majuscule
Code PHP :
<?php $reponse = $bdd->query('SELECT UPPER(nom) AS nom_maj FROM jeux_video'); while ($donnees = $reponse->fetch()){ echo $donnees['nom_maj'] . '<br />'; } $reponse->closeCursor(); ?>
Les autres fonctions s'utilisent de la m?me mani?re:
Code SQL :
-- Conversion du champ en majuscule SELECT UPPER(nom) AS nom_maj FROM jeux_video -- Conversion du champ en minuscule SELECT LOWER(nom) AS nom_min FROM jeux_video -- Compte le nombre de caract?res SELECT LENGTH(nom) AS longueur_nom FROM jeux_video -- Arrondi un nombre d?cimal SELECT ROUND(prix, 2) AS prix_arrondi FROM jeux_video
Vous pouvez aussi r?cup?rer le contenu des autres champs comme avant sans forc?ment leur appliquer une fonction :
Code SQL :
SELECT UPPER(nom) AS nom_maj, possesseur, console, prix FROM jeux_video
Une liste d?taill?e des fonctions est disponible ici
Les fonctions d?agr?gats : AVG, SUM, MAX, MIN, COUNT
Les fonctions d'agr?gats font des op?rations.
Exemple : calcul de la moyenne
Code PHP :
<?php $reponse = $bdd->query('SELECT AVG(prix) AS prix_moyen FROM jeux_video'); $donnees = $reponse->fetch(); echo $donnees['prix_moyen']; $reponse->closeCursor(); ?>
Vous ne devez pas r?cup?rer d'autres champs de la table quand vous utilisez une fonction d'agr?gat, contrairement ? tout ? l'heure avec les fonctions scalaires.
Les autres fonctions s'utilisent de la m?me mani?re:
Code SQL :
-- AVG : Calcul de la moyenne SELECT AVG(prix) AS prix_moyen FROM jeux_video -- SUM : Additionner les valeurs SELECT SUM(prix) AS prix_total FROM jeux_video WHERE possesseur='Patrick' -- MAX : retourner la valeur maximale SELECT MAX(prix) AS prix_max FROM jeux_video -- MIN : retourner la valeur minimale SELECT MIN(prix) AS prix_min FROM jeux_video -- COUNT : compter le nombre d'entr?es SELECT COUNT(*) AS nbjeux FROM jeux_video SELECT COUNT(*) AS nbjeux FROM jeux_video WHERE possesseur='Florent'
Il est possible de compter le nombre de valeurs distinctes sur un champ pr?cis.
Code SQL :
SELECT COUNT(DISTINCT possesseur) AS nbpossesseurs FROM jeux_video
GROUP BY et HAVING : groupement de donn?es
GROUP BY est utilis? pour obtenir des informations int?ressantes sur des groupes de donn?es.
Il faut utiliser GROUP BY en m?me temps qu'une fonction d'agr?gat, sinon il ne sert ? rien.
Code SQL :
-- Ici, on r?cup?re le prix moyen et la console, et on choisit de grouper par console --On a la liste des consoles et le prix moyen des jeux associ?s. SELECT AVG(prix) AS prix_moyen, console FROM jeux_video GROUP BY console
HAVING est utilis? pour filtrer les donn?es ? la fin des op?rations.
Code SQL :
--on r?cup?re uniquement la liste des consoles et leur prix moyen si ce prix moyen ne d?passe pas 10 euros. SELECT AVG(prix) AS prix_moyen, console FROM jeux_video GROUP BY console HAVING prix_moyen <= 10 --exemple 2 : combiner GROUP BY et HAVING SELECT AVG(prix) AS prix_moyen, console FROM jeux_video WHERE possesseur='Patrick' GROUP BY console HAVING prix_moyen <= 10
HAVING ne doit s'utiliser que sur le r?sultat d'une fonction d'agr?gat. Voil? pourquoi on l'utilise ici sur prix_moyen et non sur console.
Les dates en SQL
Les champs de type date
Plusieurs types de champs existent. Les plus utilis?s sont :
DATE : stocke une date au format AAAA-MM-JJ (Ann?e-Mois-Jour) ;
DATETIME : stocke la combinaison d'une date et d'un moment de la journ?e au format AAAA-MM-JJ HH:MM:SS.
Lorsque vous cr?ez votre table MySQL, nommer le champ comme ceci : date_creation, ou encore date_modification. Ne jamais utiliser "date" tout court. Cela peut g?n?rer des erreur car date est une fonction MySQL.
Utilisation des dates
Code SQL :
-- Affiche la liste des messages post?s le 02/04/2010 SELECT pseudo, message, DATE FROM minichat WHERE DATE = '2010-04-02' -- Affiche la liste des messages post?s le 02/04/2010 ? 15h28min22s SELECT pseudo, message, DATE FROM minichat WHERE DATE = '2010-04-02 15:28:22' -- Affiche la liste des messages post?s apr?s le 02/04/2010 ? 15h28min22s SELECT pseudo, message, DATE FROM minichat WHERE DATE >= '2010-04-02 15:28:22' -- Affiche la liste des messages post?s entre deux dates: SELECT pseudo, message, DATE FROM minichat WHERE DATE >= '2010-04-02 00:00:00' AND DATE <= '2010-04-18 00:00:00' SELECT pseudo, message, DATE FROM minichat WHERE DATE BETWEEN '2010-04-02 00:00:00' AND '2010-04-18 00:00:00' -- Ins?rer un message avec date INSERT INTO minichat(pseudo, message, DATE) VALUES('Mateo', 'Message !', '2010-04-02 16:32:22')
Les fonctions de gestions des dates
NOW() : obtenir la date et l'heure actuelles
Code SQL :
-- La date sera alors remplac?e par la date et l'heure actuelles au format AAAA-MM-JJ HH:MM:SS INSERT INTO minichat(pseudo, message, DATE) VALUES('Mateo', 'Message !', NOW())
Il existe aussi les fonctions CURDATE() et CURTIME() qui retournent respectivement uniquement la date (AAAA-MM-JJ) et l'heure (HH:MM:SS).
DAY(), MONTH(), YEAR() : extraire le jour, le mois ou l'ann?e
Code SQL :
SELECT pseudo, message, DAY(DATE) AS jour FROM minichat
HOUR(), MINUTE(), SECOND() : extraire les heures, minutes, secondes
Code SQL :
SELECT pseudo, message, HOUR(DATE) AS heure FROM minichat
DATE_FORMAT : formater une date
DATE_FORMAT vous permet d'adapter directement la date au format que vous pr?f?rez.
Code SQL :
-- R?cup?ration des dates avec un champ nomm? date sous la forme 11/03/2010 15h47min49s. SELECT pseudo, message, DATE_FORMAT(DATE, '%d/%m/%Y %Hh%imin%ss') AS DATE FROM minichat
DATE_ADD et DATE_SUB : ajouter ou soustraire des dates
Code SQL :
-- Afficher une date d'expiration du message : la date o? a ?t? post? le message + 15 jours SELECT pseudo, message, DATE_ADD(DATE, INTERVAL 15 DAY) AS date_expiration FROM minichat -- Indiquer que les messages expirent dans deux mois SELECT pseudo, message, DATE_ADD(DATE, INTERVAL 2 MONTH) AS date_expiration FROM minichat
Le mot-cl? INTERVAL ne doit pas ?tre chang? ; en revanche, vous pouvez remplacer DAY par MONTH, YEAR, HOUR, MINUTE, SECOND, etc.
Les jointures entre table
Soit deux tables : jeux_videos(contenant description et prix des jeux) et proprietaires (contenant le nom et adresse des propri?taires de jeux).
Les jeux et leurs propri?taires ont une correspondance via le champ ID_proprietaire (de la table jeux_video) et le champ ID (de la table proprietaires)
Les jointures internes : elles ne s?lectionnent que les donn?es qui ont une correspondance entre les deux tables ;
Les jointures externes : elles s?lectionnent toutes les donn?es, m?me si certaines n'ont pas de correspondance dans l'autre table.
Les jointures internes
Code SQL :
--=================================== -- Jointure interne avec WHERE --=================================== --On demande clairement de r?cup?rer le nom du jeu et le pr?nom du propri?taire avec cette requ?te. SELECT jeux_video.nom, proprietaires.prenom FROM proprietaires, jeux_video WHERE jeux_video.ID_proprietaire = proprietaires.ID --=================================== -- Utilisation des alias --=================================== -- il est possible de donner des alias aux noms de champs et noms de tables SELECT j.nom AS nom_jeu, p.prenom AS prenom_proprietaire FROM proprietaires AS p, jeux_video AS j WHERE j.ID_proprietaire = p.ID --Le mot cl? AS est facultatif SELECT j.nom nom_jeu, p.prenom prenom_proprietaire FROM proprietaires p, jeux_video j WHERE j.ID_proprietaire = p.ID --=================================== -- Jointure interne avec JOIN --=================================== --On r?cup?re les donn?es depuis une table principale (proprietaires) et on fait une jointure interne avec une autre table (jeux_video) SELECT j.nom nom_jeu, p.prenom prenom_proprietaire FROM proprietaires p INNER JOIN jeux_video j ON j.ID_proprietaire = p.ID --=================================== -- Introduire des filtres avec WHERE, ORDER BY, LIMIT --=================================== -- R?cup?re le nom du jeu et le pr?nom du propri?taire dans les tables proprietaires et jeux_video, -- la liaison entre les tables se fait entre les champs ID_proprietaire et ID, -- prends uniquement les jeux qui tournent sur PC, --trie-les par prix d?croissants et ne prends que les 10 premiers. SELECT j.nom nom_jeu, p.prenom prenom_proprietaire FROM proprietaires p INNER JOIN jeux_video j ON j.ID_proprietaire = p.ID WHERE j.console = 'PC' ORDER BY prix DESC LIMIT 0, 10
WHERE et JOIN permettent d'effectuer des jointures internes. Il est conseill? d'utiliser JOIN qui est beaucoup plus efficace.
Les jointures externes
Les jointures externes permettent de r?cup?rer toutes les donn?es, m?me celles qui n'ont pas de correspondance.
LEFT JOIN : r?cup?rer toute la table de gauche
Code SQL :
SELECT j.nom nom_jeu, p.prenom prenom_proprietaire FROM proprietaires p LEFT JOIN jeux_video j ON j.ID_proprietaire = p.ID
proprietaires est appel?e la ? table de gauche ? et jeux_video la ? table de droite ?. Le LEFT JOIN demande ? r?cup?rer tout le contenu de la table de gauche, donc tous les propri?taires, m?me si ces derniers n'ont pas d'?quivalence dans la table jeux_video.
RIGHT JOIN : r?cup?rer toute la table de droite
Code SQL :
SELECT j.nom nom_jeu, p.prenom prenom_proprietaire FROM proprietaires p RIGHT JOIN jeux_video j ON j.ID_proprietaire = p.ID
