Problème avec requête SQL multiple (3 tables) et comptage

[yazerty]

Bonjour,

Je suis actuellement en train d'écrire un petit code afin d'afficher des rêves sur mon site qui leur est dédié : -http://www.boiteareves.com (je remercie d'ailleurs une nouvelle fois Pipologue pour le site ).

Les premiers mots des rêves sont d'abord affichés les uns en dessous des autres, des liens menant ensuite sur les pages d'affichage individuel des rêves.

Je bloque actuelllement sur l'affichage du nombres de réponses (appelées pour le moment "interprétations") à chaque rêve sur ces pages de navigation affichant plusieurs rêves.

Données :
La chose est sur un 60gp d'ovh.
Table "reves_reves" : les textes des rêves y sont stockés avec leur n° d'id.
Table "reves_reveurs" : les membres du site y sont enregistrés avec leurs informations (notamment leur pseudo). Chaque rêveur a un id.
Table "reves_interpretations" : les "réponses" aux rêves. Chaque réponse a son propre id et un champ "id_reve" indiquant le n° d'id du rêve correspondant.

Je travaille actuellement sur le code suivant :

Code: Tout sélectionner

// Sélection des rêves online
$sql = "SELECT
        RR.id, RR.pseudo_id, RR.pseudo,
        RR.email, RR.email_publication, RR.titre,
        RR.reve, RR.date,
        RRVR.pseudo as pseudo_inscrit
FROM
        reves_reves RR, reves_interpretations RI

INNER JOIN reves_reveurs RRVR
ON RRVR.id = RR.pseudo_id

WHERE
        RR.activity='on'
GROUP BY
         RR.id
ORDER BY
         RR.id DESC
LIMIT
         $reves_bckid,$nb_reves_aff";

$req = mysql_query($sql) or die(mysql_error());

// Affichage des rêves
while($db_data_reves = mysql_fetch_assoc($req)) {
trululuuu...
}

Si le rêveur est membre alors quand il envoit son rêve, son pseudo n'est pas stocké dans la table reves_reves.
Si le rêveur est membre, alors il a un id supérieur à 0, et le code affichera son "pseudo_inscrit" (code du while{}), sinon il affichera son pseudo donné lors de l'envoi du rêve (pris dans le champ pseudo de la table reves_reves).

Partant de là, je ne vois pas du tout comment obtenir facilement et efficacement le nombre d'interprétations (cad le nombre d'enregistrements de la table reves_interpretations dont le champ id_reve est égal à l'id du rêve traité) pour ensuite l'afficher.
Il ne me semble évidemment pas très recommandé de faire une requête SQL à chaque tour de boucle .
J'ai un peu tout essayé mais sans succès aucun (à noter que s'il y a plus efficace pour le reste aussi, je prends également bien sûr ). J'y suis depuis un bon moment et je ne trouve rien de probant .

Comment faire svp ?
Je ne sais pas ce que je donnerais pour avoir la solution miracle là !

[kasar]

Bonjour;
Si la variable retournée est un associatif, tu dois pouvoir tester par une variable tampon la valeur de l'id du rêve, présente sur chaque ligne et tester alors la diff entre l'id du rêve courant et celle de la variable tampon à réaffecter à chaque tour dans le while et du coup commande rl'affichage ou pas.
En gros, quand l'id du rêve change, tu l'affiches ($idreve!=$reve_tampon) et pour le reste tu affiches les interprétations correspondantes.

[yazerty]

Bonjour;
Si la variable retournée est un associatif, tu dois pouvoir tester par une variable tampon la valeur de l'id du rêve, présente sur chaque ligne et tester alors la diff entre l'id du rêve courant et celle de la variable tampon à réaffecter à chaque tour dans le while et du coup commande rl'affichage ou pas.
En gros, quand l'id du rêve change, tu l'affiches ($idreve!=$reve_tampon) et pour le reste tu affiches les interprétations correspondantes.

Je ne comprends pas du tout ce que tu dis :-/.

Mais je ne veux pas afficher les interprétations correspondantes mais simplement les compter et afficher leur nombre.

[thierry8]

Je veux bien essayer de t'aider mais à vrai dire je n'y comprends pas grand chose. Les informations sont éparpillées...

Peut tu faire un mini shéma ou être plus explicite stp.

[yazerty]

Merci de ta réponse thierry8 . Je vais essayer de reprendre tout ça plus clairement alors . C'est parti ^^ :

I°) Contexte :
La Boîte à rêves est un site sur lequel les visiteurs peuvent déposer et lire leurs rêves. Ils peuvent également proposer des interprétations possibles des rêves.

A°) Rêves :
Les rêves sont stockés dans une table "reves_reves". Voici sa structure :

Code: Tout sélectionner

reves_reves` (
  `id` int(10) unsigned NOT NULL auto_increment, --> id du rêve.
  `pseudo_id` int(10) NOT NULL default '0', --> n° de pseudo du rêveur si et seulement si ce rêveur est un membre (une zone membres allant ouvrir ultérieurement).
  `pseudo` varchar(50) NOT NULL default '', --> pseudo du rêveur si et seulement si ce rêveur n'est pas un membre (s'il est membre ce champ reste vide).
  `email` varchar(50) NOT NULL default '', --> email du rêveur si et seulement si ce rêveur n'est pas un membre (s'il est membre ce champ reste vide).
  `email_publication` varchar(50) NOT NULL default '', --> état de publication publique de l'email du rêveur si et seulement si ce rêveur n'est pas un membre (s'il est membre ce champ reste vide).
  `titre` varchar(100) NOT NULL default '', --> titre du rêve.
  `reve` text NOT NULL, --> texte du rêve.
  `date` datetime NOT NULL default '0000-00-00 00:00:00', --> date du rêve.
  `ip` varchar(15) NOT NULL default '', --> ip du rêveur.
  `activity` char(3) NOT NULL default '', --> activité du rêve. le rêve peut être soit "on"(line) soit "off"(line). Par défaut les nouveux rêves enregistrés sont offline. Je les valide ensuite après vérification et les passe en "on"(line).
  PRIMARY KEY  (`id`) --> ...
)

B°) Rêveurs :
Les rêveurs auront bientôt la possibilité de s'inscrire en tant que membres. Cela leur permettra entre autres de ne pas avoir à retaper leurs infos à chaque nouveau rêve envoyé. De plus si le rêveur enregistré change certaines de ses données affichées sur les pages d'affichage de ses rêves, celles-ci prennent en compte ces modifications. C'est la raison pour laquelle dans la table "reves_reves" certains champs (comme le pseudo par exemple) restent vides : les infos sont en effet prises directement dans la table reves_reveurs.
Voici la structure de la table "reves_reveurs" reprenant les principales données de celle-ci :

Code: Tout sélectionner

`reves_reveurs` (
  `id` int(10) NOT NULL auto_increment, --> id du rêveur (le champ id_pseudo de la table "reves_reveurs" reprend cet id, qui permet d'identifier ainsi l'auteur d'un rêve d'un membre).
  `pseudo` varchar(30) NOT NULL default '', --> pseudo du rêveur.
  `email` varchar(50) NOT NULL default '',
  `email_publication` char(3) NOT NULL default '',
  `nb_reves_aff` int(2) NOT NULL default '0',, --> nombre de rêves à afficher par page (option non déterminante dans mon problème).
  PRIMARY KEY  (`id`)
)

C°) Interprétations :
La table "reves_interpretations" contient les interprétations proposées par les visiteurs et rêveurs enregistrés. En voici la structure :

Code: Tout sélectionner

`reves_interpretations` (
  `id` int(5) unsigned NOT NULL auto_increment, --> id de l'interprétation.
  `id_reve` int(5) unsigned NOT NULL default '0', --> id du rêve interprété (correpond donc au champ "id" de la table "reves_reves".
  `pseudo_id` int(10) NOT NULL default '0', --> id du pseudo si le rêveur est membre.
  `pseudo` varchar(50) NOT NULL default '', --> pseudo du rêveur s'il n'est pas membre.
  `email` varchar(50) NOT NULL default '', --> email du rêveur s'il n'est pas membre.
  `email_publication` varchar(50) NOT NULL default '', --> état de publication de l'email du rêveur s'il n'est pas membre.
  `interpretation` text NOT NULL, --> texte de l'interprétation proposée.
  `date` datetime NOT NULL default '0000-00-00 00:00:00', --> date de l'interprétation.
  `ip` varchar(15) NOT NULL default '', --> id du posteur de l'interprétation.
  `activity` varchar(5) NOT NULL default '', --> même système que pour les rêves, sauf que les interprétations sont publiées directement par défaut (le champ a donc la valeur "on" par défaut").
  PRIMARY KEY  (`id`)
)

II°) Objectif

A°) Définition de l'objectif
Le but de la maneuvre est d'afficher les 10 premiers rêves. Avec :
- titre/date/texte du rêve (pas trop dur ça, ça va ).
- le nombre d'interprétations proposées pour le rêve.
- le pseudo du rêveur.

B°) Cas du pseudo du rêveur :

1°) Rêveur non-membre :
Si le rêveur n'est pas membre, alors le champ "id_pseudo" de la table "reves_reves" correspondant au rêve est égal à "0". On affichera alors les informations alors renseignées dans les champs "pseudo", "email",... (qui sont vides si le rêveur est membre).

2°) Rêveur membre :
Si le rêveur est membre alors le champ "id_pseudo" de la table "reves_reves" correspondant au rêve est égal à son id (supérieur à 0). On affichera alors les informations alors renseignées dans les champs de la table "reves_reveurs", là où le champ "id" (de la table "reves_reveurs") est égal à la valeur du champ "id_pseudo" de la table "reves_reves"...


Voilà donc le code que j'ai bidouillé :

Code: Tout sélectionner

// Sélection des rêves online
$sql = "SELECT
        RR.id, RR.pseudo_id, RR.pseudo,
        RR.email, RR.email_publication, RR.titre,
        RR.reve, RR.date,
        RRVR.pseudo as pseudo_inscrit
FROM
        reves_reves RR

INNER JOIN reves_reveurs RRVR
ON RRVR.id = RR.pseudo_id

WHERE
        RR.activity='on'
GROUP BY
         RR.id
ORDER BY
         RR.id DESC
LIMIT
         0,10";

$req = mysql_query($sql) or die(mysql_error());

// Affichage des rêves
while($db_data_reves = mysql_fetch_assoc($req)) {

   $id_reve = $db_data_reves['id'];
   
   // Affichage du rêve
   echo "<h2 id=\"r".$id_reve."\"><a href=\"reve.php?id=".$id_reve."&amp;titre=";
   titre_url($db_data_reves['titre']);
   echo "\">".$db_data_reves['titre']."</a></h2>
   <p class=\"reve_infos\">Le ";
   datetime_fr($db_data_reves['date']);
   
   // Si le rêveur est inscrit
   if ($db_data_reves['pseudo_id'] >= 1) {
      echo ", <a href=\"reveur-infos.php?pseudo_id=".$db_data_reves['pseudo_id']."\">".$db_data_reves['pseudo_inscrit']."</a>";
   }
   
   // Si le rêveur n'est pas inscrit
   else {
      if ($db_data_reves['email_publication'] == 'yes') {
         echo ", ".$db_data_reves['pseudo']." (<a href=\"mailto:".$db_data_reves['email']."\">@</a>)";
      }
      else {
         echo ", ".$db_data_reves['pseudo'];
      }
   }
   $reve = $db_data_reves['reve'];
   $max_caracteres = 350;
   if (strlen($reve)>$max_caracteres) {
      $max_caracteres=strrpos(substr($reve, 0, $max_caracteres), " ");
      $reve = substr($reve, 0, $max_caracteres);
      $reve .= " (...)";
   }
   
   echo " a confié le rêve suivant à la boîte à rêves :</p>
   <p class=\"reve\">$reve</p>";

   echo "<p class=\"right\">";
   if (strlen($reve)>$max_caracteres) {
         echo "<a href=\"reve.php?id=".$id_reve."&amp;titre=";
      titre_url($db_data_reves['titre']);
      echo "\">Lire la suite du rêve...</a> | ";
   }
   
   echo "<a href=\"reve.php?id=".$id_reve."&amp;titre=";
   titre_url($db_data_reves['titre']);
   echo "#add-interpretation\">Proposer une interprétation</a>
   | <a href=\"reve.php?id=".$id_reve."&amp;titre=";
   titre_url($db_data_reves['titre']);
   echo "#interpretations\">Lire les interpretations proposées</a>
   </p>
   
   <p>&nbsp;</p>";
   
}

Mais ce code n'affiche pas le nombre d'interprétations proposées pour chacun des rêves affichés.


Vouala. J'espère avoir été plus précis et concis par rapport à ma situation. En cas de doute, n'hésitez surtout pas à me posez des questions ou faire des suggestions sur la structuration des choses (si vous pensez à un système plus simple mais tout aussi efficace par exemple).

[thierry8]

J'ai fait un petit tour vite fait, mais vu l'heure je prendrai un petit temps pour me pencher sur le problème plutôt demain.

Mais au niveau des tables déjà, ce n'est pas ça..

Sinon, lorsque tu parle d'interprétation, c'est un genre de réponse(s) au(x) rêve(s) ?

[thierry8]

Code: Tout sélectionner

$sql = "
   SELECT COUNT(*) AS nb_reve_interpret
   FROM reves_interpretations r_i
   INNER JOIN reves_reves r_r
   ON r_i.id_reve = r_r.pseudo_id
   WHERE r_r.activity='on'
   GROUP BY r_r.id
   ORDER BY r_r.id DESC
   LIMIT 0,10;
   ";

Essaye voir cette requête.
Si ça ne fonctionne pas, dis moi ce qui cloche !
(désolé, mais le pb c'est que je ne peux tester du fait
que je n'ai pas les tables, donc il va falloir jongler)

Normalement tu devrais obtenir le nombre d'interprétation pour tes 10 premiers rêves. En espérant avoir bien compris.

Tiens moi au courant de la chose.

PS: Je ne pense pas que le GROUP BY soit nécessaire.

[thierry8]

Tiens moi au courant !

[yazerty]

Uiui, les interprétations sont des sortes de réponses aux rêves, c'est ça .
Je veux afficher le nombre d'interprétations postées pour chaque rêve, à la manière de ce qu'on fait sur les pages de navigation des blogs (début du billet est écrit, avec le pseudo, l'heure et le nombre de commentaires postés / ici ce ne sont pas des commentaires mais des "interprétations").

Si je fais ça :

Code: Tout sélectionner

// Sélection des rêves online
$sql = "SELECT COUNT(*) AS nb_reve_interpret
   FROM reves_interpretations r_i
   INNER JOIN reves_reves r_r
   ON r_i.id_reve = r_r.pseudo_id
   WHERE r_r.activity='on'
   GROUP BY r_r.id
   ORDER BY r_r.id DESC
   LIMIT 0,10";

$req = mysql_query($sql) or die(mysql_error());

// Affichage des rêves
while($db_data_reves = mysql_fetch_assoc($req)) {
echo $db_data_reves['nb_reve_interpret'];
}

Cela m'affiche seulement "1". Et je ne sélectionne pas non plus le texte du rêve et le pseudo du rêveur :-/.

[thierry8]

Je pense que dans tous les cas il te faudra 2 requêtes. (pseudo+texte & nb interprétations)

Est-il possible d'avoir en fichier sql (pour mysql) ces trois tables avec des données ?

Cela me permettrais de tester, et de voir le résultat...
parce que là j'avoue que c'est difficile de me faire une idée sur les résultats.

J'aimerais faire deux trois test, je ne pense pas que ce soit bien difficile.
Ca sera plus pratique.

Si tu est d'accord je t'envois mon e-mail par mp.

[thierry8]

Arg ! Ma requête était juste je me suis trompé à un petit endroit !
( évident d'ailleurs ! tu aurais du le remarquer ! )
ON r_i.id_reve = r_r.pseudo_id au lieu de ON r_i.id_reve = r_r.id

Code: Tout sélectionner

   SELECT COUNT(*) AS nb_reve_interpret, r_r.id
   FROM reves_interpretations r_i
   INNER JOIN reves_reves r_r
   ON r_i.id_reve = r_r.id
   WHERE r_r.activity='on'
   GROUP BY r_r.id
   ORDER BY r_r.id DESC
   LIMIT 0,10;


OU

Code: Tout sélectionner

   SELECT COUNT( * ) AS nb_reve_interpret, r_i.id
   FROM reves_interpretations r_i
   INNER JOIN reves_reves r_r
   ON r_i.id_reve = r_r.id
   WHERE r_r.activity = 'on'
   GROUP BY r_i.id_reve
   ORDER BY r_i.id_reve DESC
   LIMIT 0 , 10;


Tu as le choix !
La première travail plus que sur la table reves_reves et la seconde principalement sur la table reves_interpretations

Le résultat sera identique.
En terme de performance/optimisation il faut choisir...

- la solution 1 si tu pense avoir plus de rêves que d'interprétations

- la solution 2 si tu pense avoir plus d'interprétations que de rêves

Pour chacune des deux requêtes j'ai laissé r_r.id après le SELECT COUNT( * ) AS nb_reve_interpret.
Cela te permettra de tester sous phpmyadmin et de voir le résultat trier par ordre croissant sur l'id
(donc sur les rêves) et à coté le nombre d'interprétation(s).

Voila ! Maintenant y a plus cas....

[thierry8]

Qu'est ce que ça donne ?

[thierry8]

up. Pas de nouvelle, bonne nouvelle ?

[thierry8]

... bon ben quelques nouvelles font toujours plaisir ...
(surtout si ça marche!)

[yazerty]

J'étais dans mon lit avec une gasto, donc pas encore eu le temps de tester, je suis vraiment désolé . Je commence à me remettre sur pieds et viens de tester ton code : ça marche presque parfaitement bien. En fait quand il n'y a aucune interprétation, le script saute ce rêve sans interprétation. Par exemple pour mes 10 derniers rêves :

Code: Tout sélectionner

// Sélection des rêves online
$sql = "SELECT COUNT(*) AS nb_reve_interpret, r_r.id
   FROM reves_interpretations r_i
   INNER JOIN reves_reves r_r
   ON r_i.id_reve = r_r.id
   WHERE r_r.activity='on'
   GROUP BY r_r.id
   ORDER BY r_r.id DESC
   LIMIT 0,10";

$req = mysql_query($sql) or die(mysql_error());

// Affichage des rêves
while($db_data_reves = mysql_fetch_assoc($req)) {
echo $db_data_reves['id']." : ".$db_data_reves['nb_reve_interpret']."<br />";
}

Cela m'affiche :

671 : 1
670 : 1
669 : 1
668 : 2
667 : 2
666 : 1
664 : 3
663 : 3
662 : 2
661 : 1

Alors qu'il existe un rêve 665, qui n'a aucune interprétation postée par contre.
Le but serait d'avoir la valeur "0" si il n'y a pas d'interprétation, afin de faire plus facilement ma boucle affichant les rêves + le nombre d'interprétations.
Mais je ne comprends pas bien pourquoi ça n'affiche pas au moins $db_data_reves['id'] (ici 665) :- ?