requête complexe SELECT et UPDATE 2 tables.

Version imprimable

Bonjour. Je dois construire une seule requête mysql de ce qui suit si possible:

Il faut que je détermine le nombre de commentaire de idimg situé dans
ori_comen et inscrire ce nombre dans la table ori_images à l'image à quoi il
appartient.

mon image 57 a 5 commentaires
mon image 58 a 10 commentaires

Ceci est ma tentative :

Code:

1
2
UPDATE `ori_images` SET `img_id`='' WHERE 
(SELECT COUNT(*) FROM `ori_comen` GROUP BY idimg)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE IF NOT EXISTS `ori_comen` (
  `id` int(4) NOT NULL auto_increment,
  `idimg` int(4) NOT NULL,
  `name` varchar(12) NOT NULL,
  `desc` text NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=134 ;
 
INSERT INTO `ori_comen` (`id`, `idimg`, `name`, `desc`, `date`) VALUES
(133, 57, 'lenom', 'soncommentaire', '2011-05-12'),
 
CREATE TABLE IF NOT EXISTS `ori_images` (
  `id` int(4) NOT NULL auto_increment,
  `img_id` int(4) NOT NULL,
  `titre` varchar(100) NOT NULL,
  `images` varchar(30) NOT NULL,
  `desc` varchar(500) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=64 ;
 
INSERT INTO `ori_images` (`id`, `img_id`, `titre`, `images`, `desc`) VALUES
(56, 0, 'titredelimage', 'aaa_0000.jpg', ' descriptiondelimage '),

14/05/2011, 10h40
Séb.

1. Il faudrait dans ta table images un champ contenant le nombre de commentaires
2. Normalement tu ne devrais pas faire cela puisque tu peux obtenir facilement cette valeur avec une jointure

Ex. :

images
------------
image_id
image_titre
image_commentaires # nbre de commentaires
...

commentaires
------------
commentaire_id
commentaire_message
commentaire_image # clef étrangère sur images.image_id
...

Avec le champ image_commentaires :

UPDATE images
SET image_commentaires = (
SELECT COUNT(*)
FROM commentaires
WHERE commentaire_image = image_id
)

Récupérer images et nbres de commentaires sans champ supplémentaire :

SELECT ALL image_id, image_titre, COUNT(commentaire_id) AS commentaires_nb
FROM images LEFT OUTER JOIN commentaires ON image_id = commentaire_image
GROUP BY image_id

Bonjour. Merci Séb pour l'aide. J'ai retenu la version jointure et je l'ai adapté à mon code. Le code fonctionne.

J'obtiens le résultat souhaité pour le nombre mais un problème se pose,
je n'arrive pas à intégrer ce nombre à chaque image incluant s'il n'y a pas
de résultat donc 0.

Ceci va afficher les images :
Code:

1 2 3 4 5 6 $resultat = "SELECT * FROM ori_images ORDER BY id ASC"; $result = mysqli_query($connexion,$resultat); while ($ligne = mysqli_fetch_assoc($result)){ echo $ligne['images'].'<br>'; }
Affichage des commentaires qui devrait être à coté des images correspondantes :
Code:

1 2 3 4 5 6 7 $res=$connexion->query("SELECT ALL *, COUNT(idimg) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY idimg ORDER BY i.id ASC"); while ($li = $res->fetch_object()){ $nb = $li->n; echo $nb; }
Je crois qu'il faut que je fasse une autre jointure pour que chaque image puisse avoir son nombre de commentaires.

J'ai mis du temps pour faire des scénarios de tests et je ne vois pas comment.

J'obtiens soit tous les nombres à toutes les images ou bien le dernier nombre à toutes les images. Un petit coup de main serait apprécié. :roll:

Citation:

Envoyé par Séb.

2. Normalement tu ne devrais pas faire cela puisque tu peux obtenir facilement cette valeur avec une jointure
Récupérer images et nbres de commentaires sans champ supplémentaire :
SELECT ALL image_id, image_titre, COUNT(commentaire_id) AS commentaires_nb
FROM images LEFT OUTER JOIN commentaires ON image_id = commentaire_image
GROUP BY image_id

15/05/2011, 10h26
Séb.

SELECT ALL *, COUNT(idimg) AS n
FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg
GROUP BY idimg ORDER BY i.id ASC

Ici il faut compter les commentaires (comme je l'ai fait plus haut) et pas les images, autrement t'auras toujours au moins 1.

J'obtiens que les images qui ont un commentaires dont le nombre est unique.

Toutes les images présentes dans la base :
aaa_0000.jpg 5
qqq_5555.jpg 5
sdf_3522.jpg 5
hau_2233.jpg 5
lar_8322.jpg 5
ppp_3829.jpg 5
sup_8392.jpg 5
aaa_0002.jpg 5

ici,il va afficher que le dernier nombre qui est le 5.

Le 5 est si je fais la requête comme ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 $res = "SELECT ALL *, COUNT(idimg) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY idimg ORDER BY i.id ASC"; $resu = mysqli_query($connexion,$res); while ($ligne = mysqli_fetch_assoc($resu)){ //echo $ligne['n'].'<br>'; $in=$ligne['n']; } $resultat = "SELECT * FROM ori_images ORDER BY id ASC"; $result = mysqli_query($connexion,$resultat); while ($ligne = mysqli_fetch_assoc($result)){ echo $ligne['images'].' '.$in.'<br>'; }
J'obtiens ceci :
aaa_0000.jpg 8 vrai
qqq_5555.jpg 2 vrai
sdf_3522.jpg 0 faux il affiche seulement une fois pour les 0
hau_2233.jpg 5 vrai

Si je fais ma requête ainsi :
Code:

1 2 3 4 5 6 7 8 9 $res = "SELECT ALL *, COUNT(idimg) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY idimg ORDER BY i.id ASC"; $resu = mysqli_query($connexion,$res); while ($ligne = mysqli_fetch_assoc($resu)){ echo $ligne['images'].' '.$ligne['n'].'<br>'; }
Il ne veut pas afficher toutes les images si le nombre est identique d'une image à l'autre.

Je veux afficher toutes les images avec leur nombre de commentaire
même si c'est 0.

Autrement dit, si j'ai 2 images qui ont le même nombre de commentaires,
il va en affiché seulement une image avec le nombre.

Citation:

Envoyé par Séb.

SELECT ALL *, COUNT(idimg) AS n
FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg
GROUP BY idimg ORDER BY i.id ASC

Ici il faut compter les commentaires (comme je l'ai fait plus haut) et pas les images, autrement t'auras toujours au moins 1.

15/05/2011, 11h25
Séb.

Citation:

Envoyé par dancom5

Je veux afficher toutes les images avec leur nombre de commentaire même si c'est 0.

Il faut donc faire la même requête que celle que je t'ai indiquée. La tienne ne reprend pas le même principe, le idimg que j'avais écrit ci-dessus en rouge est incorrect.

Remplace COUNT(idimg) par COUNT(<identifiant du commentaire>)
15/05/2011, 11h37
dancom5
dans phpmyadmin, ça indique ceci si je remplace par id:
#1052 - Column 'id' in field list is ambiguous

en php ça indique ceci :
Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in...

si je mets ceci qui me renvoi que 4 résultat :
Code:

1 2 3 (SELECT ALL *, COUNT(id) AS n FROM ori_images AS i LEFT JOIN ori_comen AS c ON i.id = c.idimg GROUP BY idimg ORDER BY i.id ASC)
Je donne les champs générés:
id
img_id
titre
images
desc
id
idimg
name
desc
date
n

peut-être un conflit avec les id?
15/05/2011, 11h42
Séb.

Oui. Préfixe id avec le nom de la table des commentaires, comme tu l'as fait dans la jointure.

ça indique ceci :
mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given

Code:

1
2
3
4
5
6
7
8
9
		$res = "SELECT ALL *, COUNT(ori_images.id) AS n
FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg
GROUP BY idimg ORDER BY i.id ASC";
 
		$resu = mysqli_query($connexion,$res);
 
		while ($ligne = mysqli_fetch_assoc($resu)){
			echo $ligne['images'].' '.$ligne['n'].'<br>';
		}

Je dois l'avoir mal écrit le préfix.

15/05/2011, 11h47
Séb.

COUNT(ori_images.id)

Il faut mettre l'identifiant des commentaires de la table des commentaires, pas un identifiant de la table des images.
15/05/2011, 11h50
dancom5

J'ai essayé les deux. même problème.

ori_images.id est le id des images
ori_comen.id est celui des commentaires.
15/05/2011, 11h52
Séb.

$resu vaut probablement FALSE.
Fais un mysqli_error( ) pour connaître l'origine du pb.
15/05/2011, 11h54
dancom5

Warning: mysqli_error() expects exactly 1 parameter, 0 given in 32

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in 34

même dans phpmyadmin, ça fait ca.
15/05/2011, 11h56
Séb.

http://php.net/mysqli-error
15/05/2011, 12h04
dancom5

Pas d'erreur pour cette information
$resu = mysqli_query($connexion,$res) or mysqli_error($connexion);

mais le problème persiste.

Et en dehors de php aussi avec phpmyadmin

#1054 - Unknown column 'ori_images.id' in 'field list'
15/05/2011, 12h05
Séb.

Montre la requête.

Citation:

Et en dehors de php aussi avec phpmyadmin

Quel message de phpMyAdmin ?

Code:

1
2
3
SELECT ALL *, COUNT(ori_images.id) AS n
FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg
GROUP BY idimg ORDER BY i.id ASC

Erreur : #1054 - Unknown column 'ori_images.id' in 'field list'

La base comme elle est :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
CREATE TABLE IF NOT EXISTS `ori_comen` (
  `id` int(4) NOT NULL auto_increment,
  `idimg` int(4) NOT NULL,
  `name` varchar(12) NOT NULL,
  `desc` text NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=134 ;
 
INSERT INTO `ori_comen` (`id`, `idimg`, `name`, `desc`, `date`) VALUES
(133, 57, 'fgjsdfgh', 'gjsdfhgfghfgh.', '2011-05-12'),
(132, 59, 'ghsgh', 'jsfghsdfghssdfgh.', '2011-05-12'),
(131, 59, 'fdfg', 'hasdfg sdg asdgf asdgs.', '2011-05-12'),
(130, 59, 'dfsdfg.', 'dfhasd sdfgasdfg  asdfg.', '2011-05-12'),
(129, 59, 'asdfasd.', 'le dernier.', '2011-05-12'),
(128, 57, 'sdfgsdfg', 'sdfghsfdgts 57.', '2011-05-12'),
(127, 59, 'sdfgsdfg', 'sdfgsdfgg.', '2011-05-12'),
(126, 56, 'asdfasdf', 'asdgf sd fas df asdf asd fas df.', '2011-05-12'),
(125, 56, 'fasdf', 'asdfasdf fasdfasdf asdfasdf titepucecom.', '2011-05-12'),
(124, 56, 'asdfasdfa.', 'asdfasdf asdfasdf.', '2011-05-12'),
(123, 56, 'asdfasdfasdf', 'asdfasdfasdf.', '2011-05-12'),
(122, 56, 'asdfasdfasdf', 'asdfasdfasdf.', '2011-05-12'),
(121, 56, 'asdfasdf.', 'asdfasdf é asdfasdfasdf.', '2011-05-12'),
(120, 56, 'deuxieme.', '.', '2011-05-12'),
(119, 56, 'premier.', 'je suis le premier.', '2011-05-12');
 
-- --------------------------------------------------------
 
CREATE TABLE IF NOT EXISTS `ori_images` (
  `id` int(4) NOT NULL auto_increment,
  `img_id` int(4) NOT NULL,
  `titre` varchar(100) NOT NULL,
  `images` varchar(30) NOT NULL,
  `desc` varchar(500) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=64 ;
 
INSERT INTO `ori_images` (`id`, `img_id`, `titre`, `images`, `desc`) VALUES
(56, 0, 'jkjkfdj fasd fsd f asd f asd f asdf a sdf as df a sdf asdf', 'aaa_0000.jpg', 'd f asdf '),
(57, 0, 'Une photo de qui porte mon chapeau', 'qqq_5555.jpg', 'Un suphile avec. '),
(58, 0, 'Un autre petit test d''affichage.', 'sdf_3522.jpg', 'Ce test sans bien fonctionner. '),
(59, 0, ' jkjkfdj fasd fsd f asd f asd f asdf a sdf as df a sdf asdfjkjkfdj fasd fsd f asd f asd f asdf a sdf', 'hau_2233.jpg', 'jkjsdf a sdf a...'),
(60, 0, ' jkjkfdj fasd fsd f asd f asd f asdf a sdf as df a sdf asdfjkjkfdj fasd fsd f asd f asd f asdf a sdf', 'lar_8322.jpg', 'jkjkfdj df a...'),
(61, 0, 'ggg', 'ppp_3829.jpg', 'jkjkfdj fasd fsd f asd f asd f asdf a sdf as df a sdf asdfjkjkfdj fasd fsd f asd f asd f asdf a sdf as df a sddf a'),
(62, 0, 'asdf ''t', 'sup_8392.jpg', 'asdfsdfs ''jjj'),
(63, 0, 'asdfasdfasdf', 'aaa_0002.jpg', 'asdfasdfasdf');

Je pense que ça va aller mieux pour la suite.

15/05/2011, 12h24
Séb.

Ta requête est fausse. Il faut compter les commentaires, pas les images :

SELECT ALL *, COUNT(ori_images.id) AS n <= Ce qui est en rouge est faux.

Citation:

Erreur : #1054 - Unknown column 'ori_images.id' in 'field list'

Peut-être à cause des AS dans la jointure, essaie avec c à la place de ori_images dans le COUNT.
15/05/2011, 12h27
dancom5

Il n'y a plus d'erreur mais le résultat est de quatre seulement.
15/05/2011, 12h28
Séb.

Peux-tu montrer la requête stp ?

Code:

1 2 3 4 5 SELECT ALL * , COUNT( c.id ) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY idimg ORDER BY i.id ASC
résultat :
aaa_0000.jpg 8
qqq_5555.jpg 2
sdf_3522.jpg 0
hau_2233.jpg 5

15/05/2011, 12h38
Séb.

GROUP BY idimg <= Ceci est faux, il faut grouper par image, donc sur i.id
15/05/2011, 12h42
dancom5
Ça sort tous les résultats :
Code:

1 2 3 4 5 SELECT ALL * , COUNT( c.id ) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY i.id ORDER BY i.id ASC
Vraiment MERCI Séb! Je suis content. Vous avez eu une patience énorme pour moi!

ça sort tout:
Code:

1 2 3 4 5 SELECT ALL * , COUNT( c.id ) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY i.id ORDER BY i.id ASC
Merci Séb, vous avez été très généreux avec moi. MERCI!

Je vais intégrer ça dans ma page.. je vais espérer que ça va
bien rouler étant donné que j ai aussi le page par page dans
la requête.
ça devrait aller ca fonctionnait avant.

La requête qui était ça

Code:

SELECT * FROM ori_images ORDER BY id ASC LIMIT $ledebut,$nb_affichage_par_page

est devenu ça :
Code:

1 2 3 4 5 SELECT ALL * , COUNT( c.id ) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY i.id ORDER BY i.id ASC $ledebut,$nb_affichage_par_page
ça changé de quoi dans mon affichage.

Le ID, le TITRE et la DESC des images ont changés pour ceux
des commentaires. Je tente les ajustement. Si c'est concluant, je mets en résolu après.

Si je pourrais mettre comme ceci :
Code:

1 2 $ligne['ori_image.titre']
?
15/05/2011, 13h12
Séb.
Citation:

Code:

ORDER BY i.id ASC $ledebut,$nb_affichage_par_page

Il manque la clause LIMIT.

Citation:

Le ID, le TITRE et la DESC des images ont changés pour ceux
des commentaires. Je tente les ajustement.

Parce que tes tables ont des champs de mêmes noms, ce qui n'est pas une bonne pratique.
Regarde dans l'exemple donné dans mon 1er message, chaque champ a un nom unique ;)
Citation:
Si je pourrais mettre comme ceci :

Code:

1 2 $ligne['ori_image.titre']

?
Plutôt $ligne['i.titre'] à cause des AS de la jointure.
15/05/2011, 21h21
dancom5
La solution des jointure fonctionnerait si mes champs des tables aurait été tous différents. J'ai en commun (id, titre et desc) dans mes deux tables. Il faudrait que je change à pas mal de place le code.

Alors, je tests la première solution:
Code:

1 2 3 4 5 SELECT COUNT( c.id ) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY i.id ORDER BY i.id ASC
Affiche bien tous les nombre de commentaires.

Mais, si j'ajoute :

Code:

UPDATE ori_images SET img_id =

qui devient :
Code:

1 2 3 4 5 6 7 UPDATE ori_image SET idimg = (SELECT COUNT( c.id ) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY i.id ORDER BY i.id ASC)
J'obtiens le message suivant dans phpmyadmin:

Code:

#1093 - You can't specify target table 'ori_images' for update in FROM clause
15/05/2011, 21h39
Séb.
Citation:

Envoyé par dancom5

La solution des jointure fonctionnerait si mes champs des tables aurait été tous différents. J'ai en commun (id, titre et desc) dans mes deux tables. Il faudrait que je change à pas mal de place le code.

Tu ne devrais pas avoir tant de scripts que ça à corriger.

Citation:

Alors, je tests la première solution:

Mauvaise idée.
Citation:
Code:

UPDATE ori_images SET img_id =

qui devient :

Code:

1 2 3 4 5 6 7 UPDATE ori_image SET idimg = (SELECT COUNT( c.id ) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY i.id ORDER BY i.id ASC)
Cela ne respecte pas ce que je t'avais donné dans mon 1er message.
Il faut que tu reprennes ma requête et que tu l'adaptes à ta BdD, il n'y a rien (et surtout pas de jointure) à ajouter.
15/05/2011, 21h46
dancom5
J'ai beaucoup de fichier qui utilise les mêmes tables.

J'essais ceci :
Code:

1 2 3 4 5 UPDATE ori_images SET img_id = (SELECT COUNT(*) FROM ori_comen WHERE idimg = id)
mais je suis un peu perdu avec la clause where

Le seul moyen d'afficher le nombre de commentaires avec les 0 :
Code:

1 2 3 4 5 SELECT COUNT( c.id ) AS n FROM ori_images AS i LEFT OUTER JOIN ori_comen AS c ON i.id = c.idimg GROUP BY i.id ORDER BY i.id ASC

Code:

1
2
3
4
5
UPDATE ori_images
SET img_id = # Pourquoi changer l'identifiant de l'image ?
(SELECT COUNT(*)
FROM ori_comen
WHERE idimg = id) # id est ambigu

Essaie ceci :

Code:

1
2
3
4
5
6
7
UPDATE ori_images
SET nb_commentaires = # Champ nb_commentaires à créer
(
    SELEC COUNT(*)
    FROM ori_commen
    WHERE ori_commen.idimg = ori_images.id
)

15/05/2011, 22h48
dancom5
Dans phpmyadmin, ça fonctionne:
Code:

1 2 3 4 5 6 7 UPDATE ori_images SET img_id = ( SELECT COUNT(*) FROM ori_comen WHERE ori_comen.idimg = ori_images.id )
J'ai laissé img_id étant donné que ce champ est justement pour stocker les nombres.

Je vais faire la version en PHP.. Merci Séb! :merci:
15/05/2011, 22h48
Séb.

Citation:

Envoyé par dancom5

J'ai laissé img_id étant donné que ce champ est justement pour stocker les nombres.

Ah, pas sûr que le nom du champ soit bien choisi :mrgreen:
15/05/2011, 22h59
dancom5

F:lefou::resolu::merci: Séb!

Fantastique!

En plus, cette solution est simple. Rien à changer.

J'ai appris quelque chose et ça va me servir dans le future.