Puissant pour les pros
Simple pour les utilisateurs

CMS Made Simple, un CMS open source, permet la gestion rapide et facile des contenus d'un site web. Ce CMS est adapté aussi bien pour les petites sociétés que pour les grandes entreprises.

archives

Requêter facilement la bdd de cmsmadesimple

11 October 2010
Catégorie : Astuces et Conseils
Posté par : admin
Petit exercice de style réalisé il y a peu de temps sur le forum : comment faire pour utiliser les données de la bdd afin d'afficher des éléments personnalisées sur Cms Made Simple. La procédure n'est pas compliquée en soit. Ce qui est un peu plus interessant est "comment bien procéder" pour éviter un maximum les erreurs, le temps de traitement à rallonge et les corrections à n'en plus finir lors de l'utilisation du code sur d'autres installations.

Présentation du problème

Le soucis se présentait sous la forme suivante : J'ai le module news installé et plein de billets renseignés par différents auteurs dans différentes catégories ayant toutes la même catégorie mère. 2 champs complémentaires viennent s'ajouter bien que pas toujours renseignés.

Je veux sortir une édition à l'écran sous la forme d'un tableau à X colonnes comportant :

  • nom de la catégorie
  • nom de l'auteur
  • date de la news
  • champs complémentaire 1 (ou vide)
  • champs complémentaire 2 (ou vide)

En regardant la BDD on trouve facilement nos tables et on parvient au constat qu'il sera facile de lier nos données avec toutes les clés étrangères.

les tables : module_news_categories, module_news, module_news_fieldvals et user pour respectivement les catégories, les articles, les champs additionnels et les utilisateurs.

La première réaction pour afficher notre tableau est de passer par du php. Pour afficher du php, le plus simple est de monter une balise utilisateur (un UDT) nommée "toto" , d'y coller notre code, d'enregistrer et dans la page souhaitée d'insérer la balise smarty : {toto} .Bref rien d'extra-ordinaire de ce coté

Le code qui a été réalisé en premier notre codeur a été celui ci (sans aucune modification de ma part)

echo '<table id="planning" align="center" cellspacing="2">';
echo '<thead><tr class="toprow">';
echo '<table id="planning" align="center" cellspacing="2">';
echo '<thead><tr class="toprow">';
echo '<th>Tâche</th>';
echo "<th>Description</th>";
echo "<th>Postée le</th>";
echo "<th>Postée par</th>";
echo "<th>Action</th>";
echo "<th>Prévue le</th>";
echo "</tr></thead>";
global $gCms; //pour pouvoir travailler avec les objets, classes et méthodes du cms
$db = &$gCms->GetDb(); //connection à la bd cms, recherche de la catégorie Travail et de sa filiation
$query = "SELECT news_category_id, news_category_name FROM xxx_module_news_categories WHERE parent_id = 7 ORDER BY hierarchy ";
$dbretour = &$db->Execute($query);//exécution de la requête
while ($dbretour && !$dbretour->EOF)
{
$CodeCategory = $dbretour->fields['news_category_id'];
$resultChamps1 = $dbretour->fields['news_category_name'];//recherche des news de la category
$query = "SELECT news_id, news_title, news_date, author_id FROM xxx_module_news WHERE news_category_id = '$CodeCategory' ORDER BY news_date ";
$dbresult = &$db->Execute($query);//exécution de la requête
while ($dbresult && !$dbresult->EOF)
{
$resultChamps2 = $dbresult->fields['news_title'];
$resultChamps3 = $dbresult->fields['news_date'];
$CodeAuteur = $dbresult->fields['author_id'];
$CodeNews = $dbresult->fields['news_id'];// recherche du nom de l'auteur de la news
$query = "SELECT username FROM xxx_users WHERE user_id = '$CodeAuteur' ";
$dbtoto = &$db->Execute($query);//exécution de la requête
$resultChamps4 = $dbtoto->fields['username']; // recherche des 2 autres champs
$query = "SELECT fielddef_id, value FROM xxx_module_news_fieldvals WHERE
news_id = '$CodeNews' SORTED BY fielddef_id ";
$dbresultat = &$db->Execute($query); //exécution de la requête
$codechamp = $dbresultat->fields['fielddef_id'];
while ($dbresultat && !$dbresultat->EOF)
{
$resultChamps5;
$resultChamps6;
if ($dbresultat->fields['fielddef_id'] == 1)
{
$resultChamps5 = $dbresultat->fields['value'];
}
elseif ($dbresultat->fields['fielddef_id'] == 3)
{
$resultChamps6 = $dbresultat->fields['value'];
}
$dbresultat->MoveNext();// enregistrement suivant
}// tous les champs sont remplis on affiche la ligne
echo <<<REQUETE
<tr>
<td class="leftcol">{$resultChamps1}</td>
<td class="datadesc">{$resultChamps2}</td>
<td class="data">{$resultChamps3}</td>
<td class="data">{$resultChamps4}</td>
<td class="data">{$resultChamps5}</td>
<td class="data">{$resultChamps6}</td>
</tr>
REQUETE;
$dbresult->MoveNext();//enregistrement suivant
}//Toutes les news de la categorie ont été lues
$dbretour->MoveNext();// catégorie suivante
}
if ($dbretour) $dbretour->Close();//Toutes les news de toutes les catégories sont lues, on ferme la connection
echo "</table>";

Sans rentrer dans les détails du php voilà ce que nous avons en résumé :

  • déclaration de l'entête du tableau en html
  • récupération de la liste des catégories
  • pour chaque catégorie je récupère la liste des news associées
  • pour chaque news je récupère le nom de l'auteur et les 2 champs complémentaires
  • je mixe le tout
  • dans une syntaxe heredoc je produit en sortie la liste des ligne de mon tableau html
  • je clôture les connexions à la bdd au fur et à mesure.

Le principe est bon. Il devrait marcher. Seulement voilà il y a un sacré paquet d'amélioration à faire ici.

Optimiser les requêtes.

Dans le cas ou j'ai 50 news dans 4 catégories différentes, ayant 6 auteurs différents et ayant tous +/- tes 2 champs complémentaires renseignés mon algorithme produira pas moins de 106 requêtes SQL pour arriver à ses fins..

Nous allons utiliser (avec modération) une solution SQL appelée la jointure afin de résoudre ce premier point :

SELECT c.news_category_id, c.news_category_name, n.news_id, n.news_title, n.news_date, u.username, f1.value as champs5, f2.value as champs6
FROM
xxx_module_news n
LEFT JOIN xxx_module_news_fieldvals f1 ON f1.news_id = n.news_id
LEFT JOIN xxx_module_news_fieldvals f2 ON f2.news_id = n.news_id
LEFT JOIN xxx_module_news_categories c ON c.news_category_id = n.news_category_id
LEFT JOIN xxx_users u ON u.user_id = n.author_id
WHERE
c.parent_id = 7 // l'ID de la catégorie mère
AND f1.fielddef_id = 1 // l'ID du premier champs spécial de la news
AND f2.fielddef_id = 3 // l'ID du second champs spécial de la news
ORDER BY
c.hierarchy ASC, n.news_date ASC

ici on récupère directement toutes les infos en une seule requête et directement combinées entre elles. Notez l'utilisation des alias de tables afin de rendre plus clair le code.

A noter que l'utilisation abusive des jointures sur des tables complexes ou chargées devient rapidement un mal plus qu'un bien dans notre recherche d'optimisation !

Ensuite se pose un soucis car telle quelle, ma requête va faire des jointure uniquement sur les news ayant les deux champs complémentaires renseignés. Pour corriger ce point il suffit de rajouter une condition dans les clauses WHERE :

WHERE
c.parent_id = 7
AND (f1.fielddef_id = 1 OR f1.fielddef_id is null)
AND (f2.fielddef_id = 3 OR f2.fielddef_id is null)

Et le tour est joué. Vous obtiendrez dans le résultat soit NULL soit la valeur du champs complémentaire.

Améliorer le traitement des résultats.

Dans l'exemple nous avions :

$dbresult = &$db->Execute($query);
while ($dbresult && !$dbresult->EOF)
{
//TRAITEMENT
$dbretour->MoveNext();// on déplace le curseur d'un cran
}
if ($dbretour) $dbretour->Close();//On a finit de parcourir les résultats, on ferme la connexion.

Il y a clairement plus simple et donc moins sujet aux erreurs de codes !

Dans le cas classique ou l'on remonte une liste d'information :

$query = 'SELECT valeur1, valeur2, valeur3 FROM ma_table';
$resultat = $db->execute($query);
if($resultat === false) {/* faire code de gestion de l'erreur */}
while ($row = $result->FetchRow()))
{
echo "valeur1 =". $row['valeur1'];
echo "valeur2 =". $row['valeur2'];
echo "valeur3 =". $row['valeur3'];
}

et dans le cas ou l'on ne remonte qu'une seule valeur dans une seule colonne comme un compteur par exemple

$query = 'SELECT count(*) FROM ma_table';
$compteur= $db->getOne($query);
if($compteur=== false) {/* faire code de gestion de l'erreur */}
echo "il y a ".$compteur." résultats en base";

Plus léger, plus simple, moins de chance de laisser passer une erreur :)

Gestion des codes erreurs SQL

De mon avis personnel, je préconise toujours de ne pas afficher les erreurs dans l'interface aux yeux des utilisateurs, c'est le meilleur moyen de les renseigner sur la structure de votre base de donnée et ainsi laisser la possibilité aux personnes mal intentionnée de tenter d'exploiter des failles de sécurité.

donc plutôt que faire un echo brutal du résultat en cas d'erreur, je préconise simplement dans mes exemple précédent :

if($dbretour === false) { echo "Erreur lors de la remontée des données";exit();}
if($compteur=== false) { echo "Erreur lors du comptage des lignes";exit();}

Affichage des données

Alors que certains utiliseraient à tout va les syntaxes echo, pensez herodoc ! démonstration :

echo <<<REQUETE
<tr>
<td class="leftcol">{$row['news_category_name']}</td>
<td class="datadesc">{$row['news_title']}</td>
<td class="data">{$row['news_date']}</td>
<td class="data">{$row['username']}</td>
<td class="data">{$row['Champs5']}</td>
<td class="data">{$row['Champs6']}</td>
</tr>
REQUETE;

pas de gestion des apostrophes, des guillemets et autre subtilités, vous insérez directement dans le code HTML pré-maché vos variables sous la forme $maVariable avec des accolades autour. D'autre part, PHP à tendance à gagner du temps lorsque vous utilisez l'herodoc plutôt qu'une série de 20 appels à echo. Bref totalement gagnant

Petite pause

A ce stade du tutorial nous avons déjà pas mal chamboulé le chantier... faisons un point sur le nouveau code

echo "<table>";
query = "SELECT c.news_category_id, c.news_category_name, n.news_id, n.news_title, n.news_date, u.username, f1.value as champs5, f2.value as champs6
FROM
xxx_module_news n
LEFT JOIN xxx_module_news_fieldvals f1 ON f1.news_id = n.news_id
LEFT JOIN xxx_module_news_fieldvals f2 ON f2.news_id = n.news_id
LEFT JOIN xxx_module_news_categories c ON c.news_category_id = n.news_category_id
LEFT JOIN xxx_users u ON u.user_id = n.author_id
WHERE
c.parent_id = 7
AND (f1.fielddef_id = 1 OR f1.fielddef_id is null)
AND (f2.fielddef_id = 3 OR f2.fielddef_id is null)
ORDER BY
c.hierarchy ASC, n.news_date ASC";

$dbretour = &$db->Execute($query);//exécution de la requête
if($dbretour === false) { echo "Erreur lors de la remontée des données";exit();}

while ($row = $dbretour->FetchRow())
{
echo <<<REQUETE
<tr>
<td class="leftcol">{$row['news_category_name']}</td>
<td class="datadesc">{$row['news_title']}</td>
<td class="data">{$row['news_date']}</td>
<td class="data">{$row['username']}</td>
<td class="data">{$row['Champs5']}</td>
<td class="data">{$row['Champs6']}</td>
</tr>
REQUETE;
}
echo "</table>";

Plus clair, une seule requête, séparation entre le code de requetage et celui de présentation, on est presque bon !

Portabilité du code

Votre code fonctionne chez vous? c'est super... mais fonctionnera t il sur l'installation du voisin ? les préfixes de tables : xxx_ sont paramétrables selon chaque installations, et il se trouve que bien souvent, les installations ont leur propre suffixes imposés par les hébergeurs.

Du coup une astuce va vous apporter une portabilité totale du code : c'est la fonction cms_db_prefix() de cmsms. Cette fonction va retourner justement "xxx_" en fonction de l'installation. Ainsi mon code finalisé ressemblera à ceci :

global $gCms;
$db = &$gCms->GetDb();
echo "<table>";
query = "SELECT c.news_category_id, c.news_category_name, n.news_id, n.news_title, n.news_date, u.username, f1.value as champs5, f2.value as champs6
FROM
".cms_db_prefix()."module_news n
LEFT JOIN ".cms_db_prefix()."module_news_fieldvals f1 ON f1.news_id = n.news_id
LEFT JOIN ".cms_db_prefix()."module_news_fieldvals f2 ON f2.news_id = n.news_id
LEFT JOIN ".cms_db_prefix()."module_news_categories c ON c.news_category_id = n.news_category_id
LEFT JOIN ".cms_db_prefix()."users u ON u.user_id = n.author_id
WHERE
c.parent_id = 7
AND (f1.fielddef_id = 1 OR f1.fielddef_id is null)
AND (f2.fielddef_id = 3 OR f2.fielddef_id is null)
ORDER BY
c.hierarchy ASC, n.news_date ASC";

$dbretour = &$db->Execute($query);//exécution de la requête
if($dbretour === false) { echo "Erreur lors de la remontée des données";exit();}

while ($row = $dbretour->FetchRow())
{
echo <<<REQUETE
<tr>
<td class="leftcol">{$row['news_category_name']}</td>
<td class="datadesc">{$row['news_title']}</td>
<td class="data">{$row['news_date']}</td>
<td class="data">{$row['username']}</td>
<td class="data">{$row['Champs5']}</td>
<td class="data">{$row['Champs6']}</td>
</tr>
REQUETE;
}
echo "</table>";

Ce code est virtuellement capable d'être installé sur n'importe quelle installation de CMS Made Simple dans une Balise utilisateur et s'excecuter avec joie dans votre coeur :)

Récupérer une date de la bdd

La question s'est posée : comment transformer une date issue de la bdd en date php afin d'être traitée comme tel.

Ma solution actuelle est cette fonction :

oubliez ce code et utilisez plutôt ceci

$dbdate = $db->UnixTimeStamp($row['news_date']);
echo date("d m Y",$dbdate);

pour rappel, l'ancien code que j'avais proposé

/**
* Transforme la date issue de la base en une veritable date php
**/
function _dbToDate($stringDate)
{
return mktime(substr($stringDate, 11,2),
substr($stringDate, 14,2),
substr($stringDate, 17,2),
substr($stringDate, 5,2),
substr($stringDate, 8,2),
substr($stringDate, 0,4));
}

et un exemple de son appel dans votre balise utilisateur

$dbdate = _dbToDate($row['news_date']);
echo date("d m Y",$dbdate);

affichera la date sous la forme "01 01 2001" selon le paramétrage php spécifié.

A noter que cette fonction n'est pas la plus parfaite qu'il soit, je recherche toujours un moyen plus léger pour transformer cette date issue de la bdd en date php et serait ravis d'avoir votre retour sur le sujet done ! voir un peu au dessus

Bilan

Fin du tuto du jour. J'espère qu'il sera utile pour tous ceux qui sont amenés à travailler directement sur la bdd. Merci de m'avoir suivit et bonne fin de semaine :)

Et pour tous ceux qui ne le savent pas encore ? CMS Made Simple c'est également Facebook, Twitter et même un flux RSS pour rester constamment au courant des nouveautés, des annonces et des préviews de la communauté ! Alors aucune excuse pour dire que vous n'étiez pas au courant :)

  Flux Rss Twitter Facebook