Bonjour, je continue alors le développement de cette conception. Je vais utiliser un exemple simplifié pour illustrer toutes les étapes du développement.
En premier lieu voici le modèle relationnel sur lequel je vais me baser:
Comme on peut le lire sur le modèle, un membre achète des livres et un livre est acheté par plusieurs membres.
Dans la table "membre", un membre peut enregistrer plusieurs mots-clés de livres favoris correspondant soit à des titres, des auteurs, des catégories de livres, ... Ces mots-clés sont stockés dans le champ "liste_mots_clés_favoris" séparés par des points-virgules ou des barres verticales | ou par tout autre caractère de séparation qu'on choisira techniquement plu-tard.
Par exemple : "informatique;histoire;php;word;microsoft;linux;...." ou "la fontaine|bureautique|excel|réseau|poésie|..."
Quand un membre a des mots-clés enregistrés le système doit remplir automatiquement le champ "liste_livres_favoris" par les identifiants des livres qui correspondent aux mots-clés enregistrés. Pour cela, on va utiliser des triggers (déclencheurs) qui peuvent surveiller tout ajout de nouveaux livres ou de nouveau mots-clés de membres.
Comme le champ des mots-clés, le champ "liste_livres_favoris" contiendra donc une liste d'identifiants des livres favoris déterminés par nos triggers.
Par exemple: "1;4;10" ici il s'agit de 3 livres ayant les numéros 1, 4 et 10.
Le champ "alerte_par_email" est un champ logique (booléen) qui prend soit VRAI soit FAUX (1 ou 0). Ce champ servira quand il vaudra VRAI (1) à envoyer au membre concerné un mail contenant la liste des livres favoris conformément aux identifiants listés dans le champ "liste_livres_favoris".
Et voici le script SQL qui traduit notre base de données sous MySQL:
--
-- Structure de la table `livre`
--
-- Création: Ven 07 Janvier 2011 à 14:31
--
DROP TABLE IF EXISTS `livre`;
CREATE TABLE IF NOT EXISTS `livre` (
`idlivre` int(11) NOT NULL AUTO_INCREMENT,
`titre` varchar(100) DEFAULT NULL,
`auteurs` varchar(100) DEFAULT NULL,
`prix` float DEFAULT NULL,
`categories` varchar(45) DEFAULT NULL,
PRIMARY KEY (`idlivre`),
UNIQUE KEY `livre_unique` (`titre`,`auteurs`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
--
-- Structure de la table `membre`
--
-- Création: Ven 07 Janvier 2011 à 14:31
--
DROP TABLE IF EXISTS `membre`;
CREATE TABLE IF NOT EXISTS `membre` (
`idmembre` int(11) NOT NULL AUTO_INCREMENT,
`nom` varchar(45) DEFAULT NULL,
`prenom` varchar(45) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
`mot_de_passe` varchar(45) DEFAULT NULL,
`liste_mots_cles_favoris` text,
`liste_livres_favoris` text,
`alerte_par_email` tinyint(1) DEFAULT '0',
PRIMARY KEY (`idmembre`),
UNIQUE KEY `membre_unique` (`nom`,`prenom`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
--
-- Structure de la table `achats`
--
-- Création: Ven 07 Janvier 2011 à 14:31
--
DROP TABLE IF EXISTS `achats`;
CREATE TABLE IF NOT EXISTS `achats` (
`dateachat` date NOT NULL,
`membre_idmembre` int(11) NOT NULL,
`livre_idlivre` int(11) NOT NULL,
`quantite` int(11) DEFAULT '1',
`adresse_de_livraison` varchar(255) DEFAULT NULL,
UNIQUE KEY `achat_unique` (`dateachat`,`membre_idmembre`,`livre_idlivre`),
KEY `fk_achats_livre` (`livre_idlivre`),
KEY `fk_achats_membre` (`membre_idmembre`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Contraintes relationnelles pour la table `achats`
--
ALTER TABLE `achats`
ADD CONSTRAINT `fk_achats_livre` FOREIGN KEY (`livre_idlivre`) REFERENCES `livre` (`idlivre`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_achats_membre` FOREIGN KEY (`membre_idmembre`) REFERENCES `membre` (`idmembre`) ON DELETE NO ACTION ON UPDATE NO ACTION;
Voici, un échantillon de données d'exemple que j'ai injecté pour les tests:
-- --------------------------------------------------------
--
-- Contenu de la table `livre`
--
INSERT INTO `livre` (`idlivre`, `titre`, `auteurs`, `prix`, `categories`) VALUES
(1, 'PHP débutant', 'Sadeq;Yves', 50, 'Programmation'),
(2, 'Excel 2007 avancé', 'Sadeq;Yves', 20, 'Bureautique'),
(4, 'Réseaux TCP/IP', 'Yves', 50, 'Informatique'),
(8, 'PHP avancé', 'Sadeq', 30, 'Programmation'),
(9, 'Excel 2007 débutant', 'Sadeq', 20, 'Bureautique'),
(12, 'Word 2007 débutant', 'Sadeq', 20, 'Bureautique'),
(13, 'La France médiévale en photos', 'Sadeq', 15, 'Histoire'),
(14, 'Le corbeau et le renard', 'La Fontaine', 5, 'Roman;Poésie');
-- --------------------------------------------------------
--
-- Contenu de la table `membre`
--
INSERT INTO `membre` (`idmembre`, `nom`, `prenom`, `email`, `mot_de_passe`, `liste_mots_cles_favoris`, `liste_livres_favoris`, `alerte_par_email`) VALUES
(1, 'Elmir', 'Sadeq', '[email protected]', '20EAfcH0JSFQY', 'php|programmation', ';1;8', 1),
(2, 'Legrand', 'Aline', '[email protected]', '20EAfcH0JSFQY', 'bureautique|réseau|php', ';1;2;4;8;9;12', 0),
(3, 'Dupont', 'Jean', '[email protected]', '20EAfcH0JSFQY', 'sadeq|informatique', ';1;2;4;8;9;12;13', 1),
(21, 'Varnier', 'Paul', '[email protected]', '20EAfcH0JSFQY', 'romans|poésie|histoire|la fontaine|php', ';1;8;13;14', 1),
(23, 'Niho', 'Lee', '[email protected]', '20EAfcH0JSFQY', 'informatique', ';4', 0);
Comme on peut remarquer dans cet échantillon de données et notamment dans la table "membre" que les listes de mots-clés sont des mots séparés par le caractère | et que les identifiants de livres favoris sont séparés par des points-virgules. C'est juste un choix technique, celui de spécifier le caractère de séparation | ou ;
Le caractère | par exemple me permet dans le cas des mots-clés d'utiliser une expression régulière pour faciliter la recherche de ces mots dans la table "livre" par rapport au titre, aux auteurs et aux catégories. et le fait de lister les données a pour avantage d'éviter des tables externes supplémentaires et simplifie considérablement les traitements.
Car on peut à tout moment transformer ces listes de données (CSV) en tableaux de programmation ou en listes déroulantes...
On remarque aussi qu'un membre peut choisir ou non d'être averti par mail quand il a des mots-clés mais il faut noter qu'il faut désactiver automatiquement l'alerte par mail d'un membre quand il n'a pas de mots-clés enregistrés.
Toutes ces contraintes de validité des données peuvent être prises en compte dans les triggers (déclencheurs) qu'on va construire.
Les Triggers (déclencheurs) nécessaires sont situés au niveau des 2 tables "livre" et "membre":
Un déclencheur attaché à la table "livre": Nom:
livre_ajout:
Quand un nouveau livre est ajouté alors il faut ajouter son identifiant dans tous le champs "liste_livres_favoris" de chaque membre si le champ "liste_mots_cles_favoris" contient un mot-clé correspondant au titre, aux auteurs et catégories du nouveau livre en question.
Algorithme:
Nom de la procédure : `livre_ajout`
Contexte
Ce programme se déclenche après l'ajout d'un nouvel enregistrement dans la table `livre`
Les valeurs insérées dans la table livre seront préfixées par l'objet conteneur nommé "NEW",
en voici la liste de ceux qui nous intéressent: New.idlivre, New.titre, New.auteurs, NEW.categories
Code : Tout sélectionner
DEBUT
Pour chaque enregistrement de la table `membre`:
SI le champ `liste_mots_cles_favoris` n'est pas vide
ET SI l'un des mots contenus dans le champ `liste_mots_cles_favoris` se trouve dans les chaines : (NEW.titre +' ' +NEW.auteurs +' ' +NEW.categories)
ALORS
Ajouter l'identifiant du livre NEW.idlivre à la fin de la chaine du champ `liste_livres_favoris`:
membre.`liste_livres_favoris` = membre.`liste_livres_favoris` + ";" + NEW.idlivre
:Refaire la procédure pour le membre suivant
FIN.
Code SQL correspondant à l'algorithme:
--
-- Déclencheurs `livre`
--
DELIMITER //
CREATE TRIGGER `livre_ajout` AFTER INSERT ON `livre`
FOR EACH ROW BEGIN
UPDATE `membre` SET liste_livres_favoris = concat( liste_livres_favoris, ';', NEW.idlivre )
WHERE TRIM(liste_mots_cles_favoris) <> ''
AND CONCAT(NEW.titre,' ',NEW.auteurs,' ',NEW.categories) REGEXP liste_mots_cles_favoris;
END
//
DELIMITER ;
Deux déclencheurs attachés à la table "membre" feront la même chose mais l'un surveillera l'jout et l'autre la mise à jour
Quand on ajoute un nouveau membre avec des mots-clés ou bien on modifie la liste des mots-clés d'un membre existant, alors il faut chercher tous les livres correspondant aux nouveaux mots-clés et ajouter leurs identifiants dans le champ `liste_livres_favoris` du membre en question. Par contre, si le champ `liste_mots_cles_favoris` est vide, aucune recherche de livres n'est nécessaire et le champ `alerte_par_email` doit être forcement mis à zéro.
Ce traitement doit donc se déclencher à l'ajout et à la modification d'un membre.
Algorithme général pour les deux déclencheurs:
Nom des procédures : `membre_ajout` et `membre_maj`
Contexte
Ce programme se déclenche à l'ajout et la modification d'un enregistrement de la table `membre`
Les valeurs insérées dans la table 'membre` seront préfixées par l'objet conteneur nommé "NEW",
en voici la liste de ceux qui nous intéressent: NEW.liste_mots_cles_favoris, NEW.liste_livres_favoris, NEW.alerte_par_email
Code : Tout sélectionner
DEBUT
INITIALISER NEW.liste_livres_favoris = vide
SI NEW.liste_mots_cles_favoris n'est pas vide ALORS
Pour chaque enregistrement de la table `livre`:
SI l'un des mots contenus dans le champ NEW.liste_mots_cles_favoris se trouve dans les chaines : (livre.titre +' ' +livre.auteurs +' ' +livre.categories) ALORS
Ajouter l'identifiant du livre livre.idlivre à la fin de la chaine du champ NEW.liste_livres_favoris :
NEW.liste_livres_favoris = NEW.liste_livres_favoris + ';' + livre.idlivre;
:Refaire la procédure pour le membre suivant
SINON
INITIALISER NEW.alerte_par_email = 0;
FIN SI;
FIN.
Code SQL correspondant à l'algorithme:
--
-- Déclencheurs `membre`
--
DROP TRIGGER IF EXISTS `livres`.`membre_ajout`;
DELIMITER //
CREATE TRIGGER `livres`.`membre_ajout` BEFORE INSERT ON `livres`.`membre`
FOR EACH ROW BEGIN
DECLARE var_idlivre INT;
DECLARE var_nonTrouve BOOL DEFAULT FALSE;
DECLARE var_liste_livres CURSOR FOR SELECT idlivre FROM livre WHERE CONCAT(titre,' ',auteurs,' ',categories) REGEXP NEW.liste_mots_cles_favoris;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_nonTrouve := TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION CLOSE var_liste_livres;
SET NEW.liste_livres_favoris = '';
IF TRIM(NEW.liste_mots_cles_favoris) <> '' THEN
OPEN var_liste_livres;
FETCH var_liste_livres INTO var_idlivre;
WHILE var_nonTrouve = FALSE DO
SET NEW.liste_livres_favoris = CONCAT(NEW.liste_livres_favoris, ';', var_idlivre) ;
FETCH var_liste_livres INTO var_idlivre;
END WHILE;
CLOSE var_liste_livres;
ELSE
SET NEW.alerte_par_email=0;
END IF;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `livres`.`membre_maj`;
DELIMITER //
CREATE TRIGGER `livres`.`membre_maj` BEFORE UPDATE ON `livres`.`membre`
FOR EACH ROW BEGIN
DECLARE var_idlivre INT;
DECLARE var_nonTrouve BOOL DEFAULT FALSE;
DECLARE var_liste_livres CURSOR FOR SELECT idlivre FROM livre WHERE CONCAT(titre,' ',auteurs,' ',categories) REGEXP NEW.liste_mots_cles_favoris;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_nonTrouve := TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION CLOSE var_liste_livres;
SET NEW.liste_livres_favoris = '';
IF TRIM(NEW.liste_mots_cles_favoris) <> '' THEN
OPEN var_liste_livres;
FETCH var_liste_livres INTO var_idlivre;
WHILE var_nonTrouve = FALSE DO
SET NEW.liste_livres_favoris = CONCAT(NEW.liste_livres_favoris, ';', var_idlivre) ;
FETCH var_liste_livres INTO var_idlivre;
END WHILE;
CLOSE var_liste_livres;
ELSE
SET NEW.alerte_par_email=0;
END IF;
END
//
DELIMITER ;
Maintenant, ces déclencheurs vont surveiller les actions INSERT sur la table `livre`et INSERT et UPDATE sur la table `membre`. Le traitement des mots-clés est donc automatique.
On peut s'intéresser maintenant au traitement du e-mailing pour envoyer un publipostage basé sur un modèle de lettre présentant pour chaque membre sa liste des livres favoris.
Pour chaque enregistrement de la table `membre` dont le champ `alerte_par_email`= 1, le contenu de la lettre se base sur les identifiants contenus dans le champ `liste_livres_favoris`.
Voici donc l'algorithme :
Nom de la procédure : membre_mailing
Contexte
L'objectif est de construire et d'envoyer une lettre de livres favoris pour chaque membre souhaitant la recevoir par mail.
Cette tâche peut être déclenchée manuellement ou être planifiée automatiquement dans le temps par un planificateur de tâche comme CRON.
Elle utilise un modèle de lettre-type et comme source de données la table `membre` agrémentée par la table 'livre'
pour compléter les données sur les livres favoris
Les données nécessaires sont:
1. De la table `membre` : nom, prenom, email, alerte_par_email, liste_livres_favoris (contenant des idlivres séparés par un ;)
2. De la table `livre` : idlivre, titre, auteurs, prix, catégories, ...
3. Les relations entre la table `membre` et la table `livre` sont réalisées par les valeurs `idlivre` qui se
trouvent dans le champ `liste_livres_favoris`
Code : Tout sélectionner
DEBUT
Préparer l'entête de la lettre-type en tant que Mail;
Pour chaque enregistrement de la table `membre`:
SI membre.alerte_par_email = 1 et membre.liste_livres_favoris n'est pas vide ALORS
Insérer dans la lettre les données identifiant le membre en cours : nom, prenom, email
DECLARER VARIABLE tableau_livres = convertir en tableau la chaine membre.liste_livres_favoris en utilisant le séparateur point-virgule
Le tableau contient à ce niveau N cases contenant chacune un idlivre
On va le parcourir pour afficher les infos des livres dans la lettre-type:
Pour chaque élément du tableau tableau_livres :
DECLARER VARIABLE var_idlivre = l'élément en cours;
DECLARER VARIABLE var_livre = Lire un enregistrement de la table `livre` dont le champ livre.idlivre = var_idlivre
SI var_livre n'est pas nul ALORS
le livre est trouvé, insérer donc dans la lettre-type les données identifiant le livre en cours: idlivre, titre, auteurs, prix, catégories, ...
FIN SI;
:Refaire la procédure pour l'élément livre suivant
A ce niveau la lettre-type pour le membre actuel est construite, il faut l'envoyer par mail:
MAIL (DE='Nous', A=membre.email, OBJET='Votre nouvelle sélection de livres favoris' , MESSAGE = la lettre-type rédigée);
FIN SI;
:Refaire la procédure pour le membre suivant
FIN.
Voici le programme PHP équivalent :
membre_mailing.php
<?php
//Paramètres du serveur Mail
ini_set("SMTP", "localhost");
//Entête du mail au Format HTML
$boundary = "nextPart";
$headers = "MIME-Version: 1.0\r\n";
$headers .= "From: Site Livres <[email protected]>\r\n";
$headers .= "Content-Type: multipart/alternative; boundary = $boundary\r\n";
//html version
$headers .= "\n--$boundary\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
$headers .= "This is the <b>HTML</b> version";
//Traitement de la lettre-type
//Obtenir la liste des membres
mysql_connect('localhost', 'root');
mysql_select_db('livres');
//Les membres à sélectionner doivent avoir choisi l'alerte email et avoir des livres favoris
$sql = "SELECT * FROM membre WHERE alerte_par_email = 1 AND TRIM(liste_livres_favoris) <>'' ";
$result_membres = mysql_query($sql);
while ($result_membres && $membre = mysql_fetch_array($result_membres)){
//insérer les données du membre dans la lettre-type
$lettre_type = sprintf("<h1>Vos Livres favoris</h1><p>Le %s</p><p>Membre n°:%s Nom: %s Prénom: %s Email: %s</p>", date("d/m/Y"), $membre['idmembre'], $membre['nom'], $membre['prenom'], $membre['email']);
$lettre_type .= "<p>Voici votre sélection de livres correspondant à vos critères de recherche</p><ul>";
//Obtenir la liste des livres favoris du membre correspondant à liste_livres_favoris
$sql = "SELECT * FROM livre WHERE '" . $membre['liste_livres_favoris'] ."' REGEXP idlivre" ;
$result_livres = mysql_query($sql);
while ($result_livres && $livre = mysql_fetch_array($result_livres)){
$lettre_type .= sprintf("<li>Livre n°:%s Titre: %s Auteurs: %s Catégories: %s Prix: %s </li>", $livre['idlivre'], $livre['titre'], $livre['auteurs'], $livre['categories'], $livre['prix']);
}
$lettre_type .= "</ul>";
//Enfin, on est prêt à envoyer le mail (A, OBJET, MESSAGE, EN-TÊTES_MAIL)
mail($membre['email'], "Vos Livres favoris", $lettre_type, $headers);
//on peut aussi faire une sauvegarde du mail au format HTML dans un dossier nommé "mails/"
$nom_fichier = "mails/membre_mail_".date("d-m-Y")."_".$membre['idmembre'].".htm";
file_put_contents($nom_fichier, $lettre_type);
//Et aussi afficher des liens vers les fichiers entegistrés
echo sprintf("<p><a href=\"%s\" />%s</a></p>", $nom_fichier, $nom_fichier);
} //répéter cette procédure pour le membre suivant
mysql_close();
?>
Finalement vous pouvez télécharger
l'archive complète de ce projet de démonstration en espérant avoir clarifier le sujet.
Bonjour, je continue alors le développement de cette conception. Je vais utiliser un exemple simplifié pour illustrer toutes les étapes du développement.
En premier lieu voici le modèle relationnel sur lequel je vais me baser:
[img]http://dl.free.fr/rASetGoLC[/img]
Comme on peut le lire sur le modèle, un membre achète des livres et un livre est acheté par plusieurs membres.
Dans la table "membre", un membre peut enregistrer plusieurs mots-clés de livres favoris correspondant soit à des titres, des auteurs, des catégories de livres, ... Ces mots-clés sont stockés dans le champ "liste_mots_clés_favoris" séparés par des points-virgules ou des barres verticales | ou par tout autre caractère de séparation qu'on choisira techniquement plu-tard.
Par exemple : "informatique;histoire;php;word;microsoft;linux;...." ou "la fontaine|bureautique|excel|réseau|poésie|..."
Quand un membre a des mots-clés enregistrés le système doit remplir automatiquement le champ "liste_livres_favoris" par les identifiants des livres qui correspondent aux mots-clés enregistrés. Pour cela, on va utiliser des triggers (déclencheurs) qui peuvent surveiller tout ajout de nouveaux livres ou de nouveau mots-clés de membres.
Comme le champ des mots-clés, le champ "liste_livres_favoris" contiendra donc une liste d'identifiants des livres favoris déterminés par nos triggers.
Par exemple: "1;4;10" ici il s'agit de 3 livres ayant les numéros 1, 4 et 10.
Le champ "alerte_par_email" est un champ logique (booléen) qui prend soit VRAI soit FAUX (1 ou 0). Ce champ servira quand il vaudra VRAI (1) à envoyer au membre concerné un mail contenant la liste des livres favoris conformément aux identifiants listés dans le champ "liste_livres_favoris".
Et voici le script SQL qui traduit notre base de données sous MySQL:
[sql]
--
-- Structure de la table `livre`
--
-- Création: Ven 07 Janvier 2011 à 14:31
--
DROP TABLE IF EXISTS `livre`;
CREATE TABLE IF NOT EXISTS `livre` (
`idlivre` int(11) NOT NULL AUTO_INCREMENT,
`titre` varchar(100) DEFAULT NULL,
`auteurs` varchar(100) DEFAULT NULL,
`prix` float DEFAULT NULL,
`categories` varchar(45) DEFAULT NULL,
PRIMARY KEY (`idlivre`),
UNIQUE KEY `livre_unique` (`titre`,`auteurs`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
--
-- Structure de la table `membre`
--
-- Création: Ven 07 Janvier 2011 à 14:31
--
DROP TABLE IF EXISTS `membre`;
CREATE TABLE IF NOT EXISTS `membre` (
`idmembre` int(11) NOT NULL AUTO_INCREMENT,
`nom` varchar(45) DEFAULT NULL,
`prenom` varchar(45) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
`mot_de_passe` varchar(45) DEFAULT NULL,
`liste_mots_cles_favoris` text,
`liste_livres_favoris` text,
`alerte_par_email` tinyint(1) DEFAULT '0',
PRIMARY KEY (`idmembre`),
UNIQUE KEY `membre_unique` (`nom`,`prenom`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
--
-- Structure de la table `achats`
--
-- Création: Ven 07 Janvier 2011 à 14:31
--
DROP TABLE IF EXISTS `achats`;
CREATE TABLE IF NOT EXISTS `achats` (
`dateachat` date NOT NULL,
`membre_idmembre` int(11) NOT NULL,
`livre_idlivre` int(11) NOT NULL,
`quantite` int(11) DEFAULT '1',
`adresse_de_livraison` varchar(255) DEFAULT NULL,
UNIQUE KEY `achat_unique` (`dateachat`,`membre_idmembre`,`livre_idlivre`),
KEY `fk_achats_livre` (`livre_idlivre`),
KEY `fk_achats_membre` (`membre_idmembre`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Contraintes relationnelles pour la table `achats`
--
ALTER TABLE `achats`
ADD CONSTRAINT `fk_achats_livre` FOREIGN KEY (`livre_idlivre`) REFERENCES `livre` (`idlivre`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_achats_membre` FOREIGN KEY (`membre_idmembre`) REFERENCES `membre` (`idmembre`) ON DELETE NO ACTION ON UPDATE NO ACTION;
[/sql]
Voici, un échantillon de données d'exemple que j'ai injecté pour les tests:
[sql]
-- --------------------------------------------------------
--
-- Contenu de la table `livre`
--
INSERT INTO `livre` (`idlivre`, `titre`, `auteurs`, `prix`, `categories`) VALUES
(1, 'PHP débutant', 'Sadeq;Yves', 50, 'Programmation'),
(2, 'Excel 2007 avancé', 'Sadeq;Yves', 20, 'Bureautique'),
(4, 'Réseaux TCP/IP', 'Yves', 50, 'Informatique'),
(8, 'PHP avancé', 'Sadeq', 30, 'Programmation'),
(9, 'Excel 2007 débutant', 'Sadeq', 20, 'Bureautique'),
(12, 'Word 2007 débutant', 'Sadeq', 20, 'Bureautique'),
(13, 'La France médiévale en photos', 'Sadeq', 15, 'Histoire'),
(14, 'Le corbeau et le renard', 'La Fontaine', 5, 'Roman;Poésie');
-- --------------------------------------------------------
--
-- Contenu de la table `membre`
--
INSERT INTO `membre` (`idmembre`, `nom`, `prenom`, `email`, `mot_de_passe`, `liste_mots_cles_favoris`, `liste_livres_favoris`, `alerte_par_email`) VALUES
(1, 'Elmir', 'Sadeq', '
[email protected]', '20EAfcH0JSFQY', 'php|programmation', ';1;8', 1),
(2, 'Legrand', 'Aline', '
[email protected]', '20EAfcH0JSFQY', 'bureautique|réseau|php', ';1;2;4;8;9;12', 0),
(3, 'Dupont', 'Jean', '
[email protected]', '20EAfcH0JSFQY', 'sadeq|informatique', ';1;2;4;8;9;12;13', 1),
(21, 'Varnier', 'Paul', '
[email protected]', '20EAfcH0JSFQY', 'romans|poésie|histoire|la fontaine|php', ';1;8;13;14', 1),
(23, 'Niho', 'Lee', '
[email protected]', '20EAfcH0JSFQY', 'informatique', ';4', 0);
[/sql]
Comme on peut remarquer dans cet échantillon de données et notamment dans la table "membre" que les listes de mots-clés sont des mots séparés par le caractère | et que les identifiants de livres favoris sont séparés par des points-virgules. C'est juste un choix technique, celui de spécifier le caractère de séparation | ou ;
Le caractère | par exemple me permet dans le cas des mots-clés d'utiliser une expression régulière pour faciliter la recherche de ces mots dans la table "livre" par rapport au titre, aux auteurs et aux catégories. et le fait de lister les données a pour avantage d'éviter des tables externes supplémentaires et simplifie considérablement les traitements.
Car on peut à tout moment transformer ces listes de données (CSV) en tableaux de programmation ou en listes déroulantes...
On remarque aussi qu'un membre peut choisir ou non d'être averti par mail quand il a des mots-clés mais il faut noter qu'il faut désactiver automatiquement l'alerte par mail d'un membre quand il n'a pas de mots-clés enregistrés.
Toutes ces contraintes de validité des données peuvent être prises en compte dans les triggers (déclencheurs) qu'on va construire.
[b]Les Triggers (déclencheurs) nécessaires sont situés au niveau des 2 tables "livre" et "membre":[/b]
[b]Un déclencheur attaché à la table "livre":[/b] Nom: [b]livre_ajout:[/b]
Quand un nouveau livre est ajouté alors il faut ajouter son identifiant dans tous le champs "liste_livres_favoris" de chaque membre si le champ "liste_mots_cles_favoris" contient un mot-clé correspondant au titre, aux auteurs et catégories du nouveau livre en question.
[b]Algorithme:[/b]
[u]Nom de la procédure : `livre_ajout`[/u]
[u]Contexte[/u]
Ce programme se déclenche après l'ajout d'un nouvel enregistrement dans la table `livre`
Les valeurs insérées dans la table livre seront préfixées par l'objet conteneur nommé "NEW",
en voici la liste de ceux qui nous intéressent: New.idlivre, New.titre, New.auteurs, NEW.categories
[code]DEBUT
Pour chaque enregistrement de la table `membre`:
SI le champ `liste_mots_cles_favoris` n'est pas vide
ET SI l'un des mots contenus dans le champ `liste_mots_cles_favoris` se trouve dans les chaines : (NEW.titre +' ' +NEW.auteurs +' ' +NEW.categories)
ALORS
Ajouter l'identifiant du livre NEW.idlivre à la fin de la chaine du champ `liste_livres_favoris`:
membre.`liste_livres_favoris` = membre.`liste_livres_favoris` + ";" + NEW.idlivre
:Refaire la procédure pour le membre suivant
FIN.[/code]
Code SQL correspondant à l'algorithme:
[sql]--
-- Déclencheurs `livre`
--
DELIMITER //
CREATE TRIGGER `livre_ajout` AFTER INSERT ON `livre`
FOR EACH ROW BEGIN
UPDATE `membre` SET liste_livres_favoris = concat( liste_livres_favoris, ';', NEW.idlivre )
WHERE TRIM(liste_mots_cles_favoris) <> ''
AND CONCAT(NEW.titre,' ',NEW.auteurs,' ',NEW.categories) REGEXP liste_mots_cles_favoris;
END
//
DELIMITER ;[/sql]
[b]Deux déclencheurs attachés à la table "membre" feront la même chose mais l'un surveillera l'jout et l'autre la mise à jour[/b]
Quand on ajoute un nouveau membre avec des mots-clés ou bien on modifie la liste des mots-clés d'un membre existant, alors il faut chercher tous les livres correspondant aux nouveaux mots-clés et ajouter leurs identifiants dans le champ `liste_livres_favoris` du membre en question. Par contre, si le champ `liste_mots_cles_favoris` est vide, aucune recherche de livres n'est nécessaire et le champ `alerte_par_email` doit être forcement mis à zéro.
Ce traitement doit donc se déclencher à l'ajout et à la modification d'un membre.
[b]Algorithme général pour les deux déclencheurs:[/b]
[u]Nom des procédures : `membre_ajout` et `membre_maj`[/u]
[u]Contexte[/u]
Ce programme se déclenche à l'ajout et la modification d'un enregistrement de la table `membre`
Les valeurs insérées dans la table 'membre` seront préfixées par l'objet conteneur nommé "NEW",
en voici la liste de ceux qui nous intéressent: NEW.liste_mots_cles_favoris, NEW.liste_livres_favoris, NEW.alerte_par_email
[code]
DEBUT
INITIALISER NEW.liste_livres_favoris = vide
SI NEW.liste_mots_cles_favoris n'est pas vide ALORS
Pour chaque enregistrement de la table `livre`:
SI l'un des mots contenus dans le champ NEW.liste_mots_cles_favoris se trouve dans les chaines : (livre.titre +' ' +livre.auteurs +' ' +livre.categories) ALORS
Ajouter l'identifiant du livre livre.idlivre à la fin de la chaine du champ NEW.liste_livres_favoris :
NEW.liste_livres_favoris = NEW.liste_livres_favoris + ';' + livre.idlivre;
:Refaire la procédure pour le membre suivant
SINON
INITIALISER NEW.alerte_par_email = 0;
FIN SI;
FIN.
[/code]
Code SQL correspondant à l'algorithme:
[sql]
--
-- Déclencheurs `membre`
--
DROP TRIGGER IF EXISTS `livres`.`membre_ajout`;
DELIMITER //
CREATE TRIGGER `livres`.`membre_ajout` BEFORE INSERT ON `livres`.`membre`
FOR EACH ROW BEGIN
DECLARE var_idlivre INT;
DECLARE var_nonTrouve BOOL DEFAULT FALSE;
DECLARE var_liste_livres CURSOR FOR SELECT idlivre FROM livre WHERE CONCAT(titre,' ',auteurs,' ',categories) REGEXP NEW.liste_mots_cles_favoris;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_nonTrouve := TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION CLOSE var_liste_livres;
SET NEW.liste_livres_favoris = '';
IF TRIM(NEW.liste_mots_cles_favoris) <> '' THEN
OPEN var_liste_livres;
FETCH var_liste_livres INTO var_idlivre;
WHILE var_nonTrouve = FALSE DO
SET NEW.liste_livres_favoris = CONCAT(NEW.liste_livres_favoris, ';', var_idlivre) ;
FETCH var_liste_livres INTO var_idlivre;
END WHILE;
CLOSE var_liste_livres;
ELSE
SET NEW.alerte_par_email=0;
END IF;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `livres`.`membre_maj`;
DELIMITER //
CREATE TRIGGER `livres`.`membre_maj` BEFORE UPDATE ON `livres`.`membre`
FOR EACH ROW BEGIN
DECLARE var_idlivre INT;
DECLARE var_nonTrouve BOOL DEFAULT FALSE;
DECLARE var_liste_livres CURSOR FOR SELECT idlivre FROM livre WHERE CONCAT(titre,' ',auteurs,' ',categories) REGEXP NEW.liste_mots_cles_favoris;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_nonTrouve := TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION CLOSE var_liste_livres;
SET NEW.liste_livres_favoris = '';
IF TRIM(NEW.liste_mots_cles_favoris) <> '' THEN
OPEN var_liste_livres;
FETCH var_liste_livres INTO var_idlivre;
WHILE var_nonTrouve = FALSE DO
SET NEW.liste_livres_favoris = CONCAT(NEW.liste_livres_favoris, ';', var_idlivre) ;
FETCH var_liste_livres INTO var_idlivre;
END WHILE;
CLOSE var_liste_livres;
ELSE
SET NEW.alerte_par_email=0;
END IF;
END
//
DELIMITER ;
[/sql]
Maintenant, ces déclencheurs vont surveiller les actions INSERT sur la table `livre`et INSERT et UPDATE sur la table `membre`. Le traitement des mots-clés est donc automatique.
On peut s'intéresser maintenant au traitement du e-mailing pour envoyer un publipostage basé sur un modèle de lettre présentant pour chaque membre sa liste des livres favoris.
Pour chaque enregistrement de la table `membre` dont le champ `alerte_par_email`= 1, le contenu de la lettre se base sur les identifiants contenus dans le champ `liste_livres_favoris`.
[b]Voici donc l'algorithme :[/b]
[u]Nom de la procédure : membre_mailing[/u]
[u]Contexte [/u]
L'objectif est de construire et d'envoyer une lettre de livres favoris pour chaque membre souhaitant la recevoir par mail.
Cette tâche peut être déclenchée manuellement ou être planifiée automatiquement dans le temps par un planificateur de tâche comme CRON.
Elle utilise un modèle de lettre-type et comme source de données la table `membre` agrémentée par la table 'livre'
pour compléter les données sur les livres favoris
Les données nécessaires sont:
1. De la table `membre` : nom, prenom, email, alerte_par_email, liste_livres_favoris (contenant des idlivres séparés par un ;)
2. De la table `livre` : idlivre, titre, auteurs, prix, catégories, ...
3. Les relations entre la table `membre` et la table `livre` sont réalisées par les valeurs `idlivre` qui se
trouvent dans le champ `liste_livres_favoris`
[code]
DEBUT
Préparer l'entête de la lettre-type en tant que Mail;
Pour chaque enregistrement de la table `membre`:
SI membre.alerte_par_email = 1 et membre.liste_livres_favoris n'est pas vide ALORS
Insérer dans la lettre les données identifiant le membre en cours : nom, prenom, email
DECLARER VARIABLE tableau_livres = convertir en tableau la chaine membre.liste_livres_favoris en utilisant le séparateur point-virgule
Le tableau contient à ce niveau N cases contenant chacune un idlivre
On va le parcourir pour afficher les infos des livres dans la lettre-type:
Pour chaque élément du tableau tableau_livres :
DECLARER VARIABLE var_idlivre = l'élément en cours;
DECLARER VARIABLE var_livre = Lire un enregistrement de la table `livre` dont le champ livre.idlivre = var_idlivre
SI var_livre n'est pas nul ALORS
le livre est trouvé, insérer donc dans la lettre-type les données identifiant le livre en cours: idlivre, titre, auteurs, prix, catégories, ...
FIN SI;
:Refaire la procédure pour l'élément livre suivant
A ce niveau la lettre-type pour le membre actuel est construite, il faut l'envoyer par mail:
MAIL (DE='Nous', A=membre.email, OBJET='Votre nouvelle sélection de livres favoris' , MESSAGE = la lettre-type rédigée);
FIN SI;
:Refaire la procédure pour le membre suivant
FIN.
[/code]
Voici le programme PHP équivalent : [b]membre_mailing.php[/b]
[php]
<?php
//Paramètres du serveur Mail
ini_set("SMTP", "localhost");
//Entête du mail au Format HTML
$boundary = "nextPart";
$headers = "MIME-Version: 1.0\r\n";
$headers .= "From: Site Livres <
[email protected]>\r\n";
$headers .= "Content-Type: multipart/alternative; boundary = $boundary\r\n";
//html version
$headers .= "\n--$boundary\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
$headers .= "This is the <b>HTML</b> version";
//Traitement de la lettre-type
//Obtenir la liste des membres
mysql_connect('localhost', 'root');
mysql_select_db('livres');
//Les membres à sélectionner doivent avoir choisi l'alerte email et avoir des livres favoris
$sql = "SELECT * FROM membre WHERE alerte_par_email = 1 AND TRIM(liste_livres_favoris) <>'' ";
$result_membres = mysql_query($sql);
while ($result_membres && $membre = mysql_fetch_array($result_membres)){
//insérer les données du membre dans la lettre-type
$lettre_type = sprintf("<h1>Vos Livres favoris</h1><p>Le %s</p><p>Membre n°:%s Nom: %s Prénom: %s Email: %s</p>", date("d/m/Y"), $membre['idmembre'], $membre['nom'], $membre['prenom'], $membre['email']);
$lettre_type .= "<p>Voici votre sélection de livres correspondant à vos critères de recherche</p><ul>";
//Obtenir la liste des livres favoris du membre correspondant à liste_livres_favoris
$sql = "SELECT * FROM livre WHERE '" . $membre['liste_livres_favoris'] ."' REGEXP idlivre" ;
$result_livres = mysql_query($sql);
while ($result_livres && $livre = mysql_fetch_array($result_livres)){
$lettre_type .= sprintf("<li>Livre n°:%s Titre: %s Auteurs: %s Catégories: %s Prix: %s </li>", $livre['idlivre'], $livre['titre'], $livre['auteurs'], $livre['categories'], $livre['prix']);
}
$lettre_type .= "</ul>";
//Enfin, on est prêt à envoyer le mail (A, OBJET, MESSAGE, EN-TÊTES_MAIL)
mail($membre['email'], "Vos Livres favoris", $lettre_type, $headers);
//on peut aussi faire une sauvegarde du mail au format HTML dans un dossier nommé "mails/"
$nom_fichier = "mails/membre_mail_".date("d-m-Y")."_".$membre['idmembre'].".htm";
file_put_contents($nom_fichier, $lettre_type);
//Et aussi afficher des liens vers les fichiers entegistrés
echo sprintf("<p><a href=\"%s\" />%s</a></p>", $nom_fichier, $nom_fichier);
} //répéter cette procédure pour le membre suivant
mysql_close();
?>
[/php]
Finalement vous pouvez télécharger [url=http://dl.free.fr/fcsWBPFpy]l'archive complète de ce projet de démonstration[/url] en espérant avoir clarifier le sujet.
