Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
Pages :: 1
#1 27-05-2009 10:36:47
- Sq78
- Membre
- Inscription : 13-05-2009
- Messages : 47
Remplacer la fonction mysql GROUP_CONCAT
Bonjour,
Je reprends un ancien post de l'ancien forum ou je n'avais pas été assez précis.
Je cherche à remplacer GROUP_CONCAT car la version Mysql de mon hébergeur ne permet pas cette fonction.
Voila comment ça se présente
Une table proa_matchs
CREATE TABLE `proa_matchs` (
`id_match` int(11) NOT NULL auto_increment,
`id_club_dom` smallint(2) unsigned default NULL,
`id_club_ext` smallint(2) unsigned default NULL,
`date_reelle` datetime default NULL,
`journee` smallint(2) unsigned default NULL,
`score` char(20) NOT NULL,
`resultat` char(2) default NULL,
PRIMARY KEY (`id_match`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=65 ;
--
-- Contenu de la table `proa_matchs`
--
INSERT INTO `proa_matchs` (`id_match`, `id_club_dom`, `id_club_ext`, `date_reelle`, `journee`, `score`, `resultat`) VALUES
(1, 1, 2, '2009-06-06 18:00:00', 1, '', NULL),
(2, 3, 4, '2009-06-07 20:00:00', 1, '', NULL),
(3, 5, 6, '2009-06-07 20:00:00', 1, '', NULL),
(4, 7, 8, '2009-06-07 20:00:00', 1, '', NULL),
(5, 9, 10, '2009-06-07 20:00:00', 1, '', NULL),
(6, 11, 12, '2009-06-07 20:00:00', 1, '', NULL),
(7, 13, 14, '2009-06-07 20:00:00', 1, '', NULL),
(8, 15, 16, '2009-06-07 20:00:00', 1, '', NULL),
(9, 2, 3, '2009-06-13 20:00:00', 2, '', NULL),
(10, 4, 5, '2009-06-13 20:00:00', 2, '', NULL),
(11, 6, 7, '2009-06-13 20:00:00', 2, '', NULL),
(12, 8, 9, '2009-06-13 20:00:00', 2, '', NULL),
(13, 10, 11, '2009-06-13 20:00:00', 2, '', NULL),
(14, 12, 13, '2009-06-13 20:00:00', 2, '', NULL),
(15, 14, 15, '2009-06-13 20:00:00', 2, '', NULL),
(16, 16, 1, '2009-06-13 20:00:00', 2, '', NULL);
`id_match` int(11) NOT NULL auto_increment,
`id_club_dom` smallint(2) unsigned default NULL,
`id_club_ext` smallint(2) unsigned default NULL,
`date_reelle` datetime default NULL,
`journee` smallint(2) unsigned default NULL,
`score` char(20) NOT NULL,
`resultat` char(2) default NULL,
PRIMARY KEY (`id_match`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=65 ;
--
-- Contenu de la table `proa_matchs`
--
INSERT INTO `proa_matchs` (`id_match`, `id_club_dom`, `id_club_ext`, `date_reelle`, `journee`, `score`, `resultat`) VALUES
(1, 1, 2, '2009-06-06 18:00:00', 1, '', NULL),
(2, 3, 4, '2009-06-07 20:00:00', 1, '', NULL),
(3, 5, 6, '2009-06-07 20:00:00', 1, '', NULL),
(4, 7, 8, '2009-06-07 20:00:00', 1, '', NULL),
(5, 9, 10, '2009-06-07 20:00:00', 1, '', NULL),
(6, 11, 12, '2009-06-07 20:00:00', 1, '', NULL),
(7, 13, 14, '2009-06-07 20:00:00', 1, '', NULL),
(8, 15, 16, '2009-06-07 20:00:00', 1, '', NULL),
(9, 2, 3, '2009-06-13 20:00:00', 2, '', NULL),
(10, 4, 5, '2009-06-13 20:00:00', 2, '', NULL),
(11, 6, 7, '2009-06-13 20:00:00', 2, '', NULL),
(12, 8, 9, '2009-06-13 20:00:00', 2, '', NULL),
(13, 10, 11, '2009-06-13 20:00:00', 2, '', NULL),
(14, 12, 13, '2009-06-13 20:00:00', 2, '', NULL),
(15, 14, 15, '2009-06-13 20:00:00', 2, '', NULL),
(16, 16, 1, '2009-06-13 20:00:00', 2, '', NULL);
Une table proa_pronostics
CREATE TABLE `proa_pronostics` (
`id` int(11) NOT NULL auto_increment,
`id_match` smallint(3) NOT NULL,
`journee` smallint(2) unsigned default NULL,
`pronostic` char(9) NOT NULL,
`login` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=57 ;
--
-- Contenu de la table `proa_pronostics`
--
INSERT INTO `proa_pronostics` (`id`, `id_match`, `journee`, `pronostic`, `login`) VALUES
(1, 1, 1, '30', 'Equinoxe5'),
(2, 2, 1, '13', 'Equinoxe5'),
(3, 3, 1, '13', 'Equinoxe5'),
(4, 4, 1, '13', 'Equinoxe5'),
(5, 5, 1, '31', 'Equinoxe5'),
(6, 6, 1, '32', 'Equinoxe5'),
(7, 7, 1, 'undefined', 'Equinoxe5'),
(8, 8, 1, 'undefined', 'Equinoxe5'),
(49, 1, 1, '03', 'test2'),
(47, 7, 1, '03', 'kinou78'),
(48, 8, 1, '13', 'kinou78'),
(46, 6, 1, '31', 'kinou78'),
(45, 5, 1, '31', 'kinou78'),
(44, 4, 1, '31', 'kinou78'),
(43, 3, 1, '32', 'kinou78'),
(42, 2, 1, '31', 'kinou78'),
(41, 1, 1, '30', 'kinou78'),
(50, 2, 1, '23', 'test2'),
(51, 3, 1, '30', 'test2'),
(52, 4, 1, '23', 'test2'),
(53, 5, 1, '31', 'test2'),
(54, 6, 1, '03', 'test2'),
(55, 7, 1, '13', 'test2'),
(56, 8, 1, '23', 'test2');
`id` int(11) NOT NULL auto_increment,
`id_match` smallint(3) NOT NULL,
`journee` smallint(2) unsigned default NULL,
`pronostic` char(9) NOT NULL,
`login` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=57 ;
--
-- Contenu de la table `proa_pronostics`
--
INSERT INTO `proa_pronostics` (`id`, `id_match`, `journee`, `pronostic`, `login`) VALUES
(1, 1, 1, '30', 'Equinoxe5'),
(2, 2, 1, '13', 'Equinoxe5'),
(3, 3, 1, '13', 'Equinoxe5'),
(4, 4, 1, '13', 'Equinoxe5'),
(5, 5, 1, '31', 'Equinoxe5'),
(6, 6, 1, '32', 'Equinoxe5'),
(7, 7, 1, 'undefined', 'Equinoxe5'),
(8, 8, 1, 'undefined', 'Equinoxe5'),
(49, 1, 1, '03', 'test2'),
(47, 7, 1, '03', 'kinou78'),
(48, 8, 1, '13', 'kinou78'),
(46, 6, 1, '31', 'kinou78'),
(45, 5, 1, '31', 'kinou78'),
(44, 4, 1, '31', 'kinou78'),
(43, 3, 1, '32', 'kinou78'),
(42, 2, 1, '31', 'kinou78'),
(41, 1, 1, '30', 'kinou78'),
(50, 2, 1, '23', 'test2'),
(51, 3, 1, '30', 'test2'),
(52, 4, 1, '23', 'test2'),
(53, 5, 1, '31', 'test2'),
(54, 6, 1, '03', 'test2'),
(55, 7, 1, '13', 'test2'),
(56, 8, 1, '23', 'test2');
Dans la 1ere table, les id_club_dom et id_club_ext sont sans importance pour ma recherche. Il s'agit simplement de faire le lien entre l'id et le nom du club suivant une autre table.
Donc avec ces 2 tables, je souhaite afficher le nombre de pronostics par score et par rencontre.
Voilà ma requête
SELECT COUNT( t1.id_match ) AS nb, t1.id_club_dom, e1.club AS club_dom, t1.id_club_ext, e2.club AS club_ext, t2.id_match, t2.journee, t2.pronostic
FROM proa_matchs AS t1
INNER JOIN proa_clubs AS e1 ON e1.id_club = t1.id_club_dom
INNER JOIN proa_clubs AS e2 ON e2.id_club = t1.id_club_ext
LEFT JOIN proa_pronostics AS t2 ON t1.id_match = t2.id_match
WHERE t2.journee = '1'
GROUP BY t1.id_match, t2.pronostic
FROM proa_matchs AS t1
INNER JOIN proa_clubs AS e1 ON e1.id_club = t1.id_club_dom
INNER JOIN proa_clubs AS e2 ON e2.id_club = t1.id_club_ext
LEFT JOIN proa_pronostics AS t2 ON t1.id_match = t2.id_match
WHERE t2.journee = '1'
GROUP BY t1.id_match, t2.pronostic
Elle me rend ce résultat
nb id_club_dom club_dom id_club_ext club_ext id_match journee pronostic
1 1 Ajaccio 2 Alès 1 1 03
2 1 Ajaccio 2 Alès 1 1 30
1 3 Avignon 4 Beauvais 2 1 13
1 3 Avignon 4 Beauvais 2 1 23
1 3 Avignon 4 Beauvais 2 1 31
1 5 Cannes 6 Montpellier 3 1 13
1 5 Cannes 6 Montpellier 3 1 30
1 5 Cannes 6 Montpellier 3 1 32
1 7 Narbonne 8 Nice 4 1 13
1 7 Narbonne 8 Nice 4 1 23
1 7 Narbonne 8 Nice 4 1 31
3 9 Paris 10 Poitiers 5 1 31
1 11 Rennes 12 Saint-Quentin 6 1 03
1 11 Rennes 12 Saint-Quentin 6 1 31
1 11 Rennes 12 Saint-Quentin 6 1 32
1 13 Sète 14 Toulouse 7 1 03
1 13 Sète 14 Toulouse 7 1 13
1 13 Sète 14 Toulouse 7 1 undefined
1 15 Tourcoing 16 Tours 8 1 13
1 15 Tourcoing 16 Tours 8 1 23
1 15 Tourcoing 16 Tours 8 1 undefined
1 1 Ajaccio 2 Alès 1 1 03
2 1 Ajaccio 2 Alès 1 1 30
1 3 Avignon 4 Beauvais 2 1 13
1 3 Avignon 4 Beauvais 2 1 23
1 3 Avignon 4 Beauvais 2 1 31
1 5 Cannes 6 Montpellier 3 1 13
1 5 Cannes 6 Montpellier 3 1 30
1 5 Cannes 6 Montpellier 3 1 32
1 7 Narbonne 8 Nice 4 1 13
1 7 Narbonne 8 Nice 4 1 23
1 7 Narbonne 8 Nice 4 1 31
3 9 Paris 10 Poitiers 5 1 31
1 11 Rennes 12 Saint-Quentin 6 1 03
1 11 Rennes 12 Saint-Quentin 6 1 31
1 11 Rennes 12 Saint-Quentin 6 1 32
1 13 Sète 14 Toulouse 7 1 03
1 13 Sète 14 Toulouse 7 1 13
1 13 Sète 14 Toulouse 7 1 undefined
1 15 Tourcoing 16 Tours 8 1 13
1 15 Tourcoing 16 Tours 8 1 23
1 15 Tourcoing 16 Tours 8 1 undefined
Comme vous pouvez le voir, pour le match Ajaccio / Ales, on voit qu'il y a 2 pronostics à 30 (3/0 donc) et un à 03 (0/3)
Je cherche à faire afficher les 8 matchs de la rencontre avec une seule ligne par match comme je faisais avec GROUP_CONCAT ce qui donne ceci
SELECT DISTINCT t1.id_club_dom, e1.club AS club_dom, t1.id_club_ext, e2.club AS club_ext, t2.id_match, t2.journee, GROUP_CONCAT( t2.pronostic ORDER BY t2.pronostic), COUNT(t2.pronostic) AS nb_joueurs
FROM proa_matchs AS t1
INNER JOIN proa_clubs AS e1 ON e1.id_club = t1.id_club_dom
INNER JOIN proa_clubs AS e2 ON e2.id_club = t1.id_club_ext
LEFT JOIN proa_pronostics AS t2 ON t1.id_match = t2.id_match
WHERE t2.journee = '1'
GROUP BY t2.id_match
id_club_dom club_dom id_club_ext club_ext id_match journee GROUP_CONCAT( t2.pronostic ORDER BY t2.pronostic) nb_joueurs
1 Ajaccio 2 Alès 1 1 03,30,30 3
3 Avignon 4 Beauvais 2 1 13,23,31 3
5 Cannes 6 Montpellier 3 1 13,30,32 3
7 Narbonne 8 Nice 4 1 13,23,31 3
9 Paris 10 Poitiers 5 1 31,31,31 3
11 Rennes 12 Saint-Quentin 6 1 03,31,32 3
13 Sète 14 Toulouse 7 1 03,13,undefined 3
15 Tourcoing 16 Tours 8 1 13,23,undefined 3
FROM proa_matchs AS t1
INNER JOIN proa_clubs AS e1 ON e1.id_club = t1.id_club_dom
INNER JOIN proa_clubs AS e2 ON e2.id_club = t1.id_club_ext
LEFT JOIN proa_pronostics AS t2 ON t1.id_match = t2.id_match
WHERE t2.journee = '1'
GROUP BY t2.id_match
id_club_dom club_dom id_club_ext club_ext id_match journee GROUP_CONCAT( t2.pronostic ORDER BY t2.pronostic) nb_joueurs
1 Ajaccio 2 Alès 1 1 03,30,30 3
3 Avignon 4 Beauvais 2 1 13,23,31 3
5 Cannes 6 Montpellier 3 1 13,30,32 3
7 Narbonne 8 Nice 4 1 13,23,31 3
9 Paris 10 Poitiers 5 1 31,31,31 3
11 Rennes 12 Saint-Quentin 6 1 03,31,32 3
13 Sète 14 Toulouse 7 1 03,13,undefined 3
15 Tourcoing 16 Tours 8 1 13,23,undefined 3
Merci d'avance de votre aide
Hors ligne
#2 22-06-2009 15:54:51
- Maljuna Kris
- Infantimigulo
- Lieu : Douarnenez 29100 Breizh Izel
- Inscription : 08-05-2009
- Messages : 2 453
- Site Web
Re : Remplacer la fonction mysql GROUP_CONCAT
Saluton,
par exemple :
<?php
$sql= "SELECT t1.id_club_dom,
e1.club AS club_dom,
t1.id_club_ext,
e2.club AS club_ext,
t2.id_match,
t2.journee,
t2.pronostic
FROM proa_matchs AS t1
INNER JOIN proa_clubs AS e1 ON e1.id_club = t1.id_club_dom
INNER JOIN proa_clubs AS e2 ON e2.id_club = t1.id_club_ext
LEFT JOIN proa_pronostics AS t2 ON t1.id_match = t2.id_match
WHERE t2.journee = '1'
ORDER BY t1.id_club_dom,t1.id_club_ext3";
$liste=mysql_query($sql);
$c1=null;$c2=null;$joueurs=0;$group_concat='';$deb=''
while($ligne=mysql_fetch_assoc($liste){
if(($c1===null) || (($c1.' '.$c2) <> ($ligne['id_club_dom'].' '.$ligne['id_club_ext']))){
if($c1 !=null){echo $deb, ' ',substr($group_concat,0,-1),' ',$joueurs,'<br />'};
$c1=$ligne['id_club_dom'];
$c2=$ligne['id_club_ext'];
$deb= $c1.' '.$ligne['club_dom'].' '
.$c2.' '.$ligne['club_ext'].' '
.' '.$ligne['id_match'].' '
.' '.$ligne['journee'];
$joueurs=0;
}
$group_concat.=$ligne['pronostic'].',';
$joueurs++;
}
echo $deb, ' ',substr($group_concat,0,-1),' ',$joueurs;
$sql= "SELECT t1.id_club_dom,
e1.club AS club_dom,
t1.id_club_ext,
e2.club AS club_ext,
t2.id_match,
t2.journee,
t2.pronostic
FROM proa_matchs AS t1
INNER JOIN proa_clubs AS e1 ON e1.id_club = t1.id_club_dom
INNER JOIN proa_clubs AS e2 ON e2.id_club = t1.id_club_ext
LEFT JOIN proa_pronostics AS t2 ON t1.id_match = t2.id_match
WHERE t2.journee = '1'
ORDER BY t1.id_club_dom,t1.id_club_ext3";
$liste=mysql_query($sql);
$c1=null;$c2=null;$joueurs=0;$group_concat='';$deb=''
while($ligne=mysql_fetch_assoc($liste){
if(($c1===null) || (($c1.' '.$c2) <> ($ligne['id_club_dom'].' '.$ligne['id_club_ext']))){
if($c1 !=null){echo $deb, ' ',substr($group_concat,0,-1),' ',$joueurs,'<br />'};
$c1=$ligne['id_club_dom'];
$c2=$ligne['id_club_ext'];
$deb= $c1.' '.$ligne['club_dom'].' '
.$c2.' '.$ligne['club_ext'].' '
.' '.$ligne['id_match'].' '
.' '.$ligne['journee'];
$joueurs=0;
}
$group_concat.=$ligne['pronostic'].',';
$joueurs++;
}
echo $deb, ' ',substr($group_concat,0,-1),' ',$joueurs;
Gloire à qui n'ayant pas d'idéal sacro-saint,
Se borne à ne pas trop emmerder ses voisins. G. Brassens Don Juan 1976.
Avĉjo MoKo kantas
La chaîne YouTube MoKo Papy
Hors ligne
Pages :: 1
Édité par WDMédia - Hébergement : WDMédia-hébergement