Php, SQL et boucle while

Version imprimable

Bonjour à tous.
J'ai une table "matable" avec deux colonnes commune et population(type int), où, des groupes de communes ont même population .
Pour chaque valeur de population enregistrée dans matable, je veux tirer au hasard 3 communes et avoir les résultats pour toutes les autres valeurs en une seule requête.
Pour le cas de 3 valeurs de population par exemple, le code suivant a bien marché
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 <?php $requete= " SELECT * from ( ( SELECT * FROM `matable` WHERE population='nombre1' ORDER BY Rand(1) LIMIT 3 ) UNION ( SELECT * FROM `matable` WHERE population='nombre2' ORDER BY Rand(1) LIMIT 3 ) UNION ( SELECT * FROM `matable` WHERE population='nombre3' ORDER BY Rand(1) LIMIT 3 ) ) as Ech order by pays " ; ?>
mais pour un très grand nombre (100,500,1000 etc.) de valeurs de population, cette façon de faire ne sera pas du tout pratique et je pense plutôt à une boucle while;
mais toutes mes tentatives jusque là sont vaines; raison pour laquelle je viens solliciter votre aide.
Par avance, merci.
Cordialement,

Salut coliasso.

Vous avez une table contenant trois colonnes :
--> pays
--> nom de la commune
--> population dans cette commune.

Vous extrayez de votre table, un échantillon de commune ayant une valeur donnée.
Votre approche ne vous semble pas adapté à une volumétrie plus importante du nombre de communes.

La boucle while est une solution php, mais en mysql les boucles, ça n'existe pas !

Le mieux est de créer une table, contenant les différentes populations que vous recherchez.
De faire une jointure entre vos deux tables, ainsi que de numéroter chaque égalité afin d'extraire le nombre voulu de lignes.
Voici mon exemple :

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `referencement`
--------------
 
--------------
CREATE TABLE `referencement`
( `id`             integer unsigned not null auto_increment primary key,
  `pays`           integer unsigned not null,
  `commune`        varchar(255)     not null,
  `population`     bigint  unsigned not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `referencement` (`pays`,`commune`,`population`) values
  (25, 'Paris',      1000000),  (25, 'Lyon',       2000000),  (25, 'Lille',      3000000),  (25, 'Marseille',  1000000),
  (25, 'Nice',       3000000),  (25, 'Toulouse',   2000000),  (25, 'Strasbourg', 3000000),  (25, 'Bordeaux',   1000000),
  (75, 'Rome',       2000000),  (75, 'Venise',     3000000),  (75, 'Naple',      1000000),  (75, 'Turin',      2000000),
  (75, 'Milan',      1000000),  (75, 'Bologne',    3000000),  (75, 'Florence',   3000000),  (33, 'Madrid',     2000000),
  (33, 'Séville',    3000000),  (33, 'Grenande',   3000000),  (33, 'Barcelone',  1000000)
--------------
 
--------------
select * from `referencement`
--------------
 
+----+------+------------+------------+
| id | pays | commune    | population |
+----+------+------------+------------+
|  1 |   25 | Paris      |    1000000 |
|  2 |   25 | Lyon       |    2000000 |
|  3 |   25 | Lille      |    3000000 |
|  4 |   25 | Marseille  |    1000000 |
|  5 |   25 | Nice       |    3000000 |
|  6 |   25 | Toulouse   |    2000000 |
|  7 |   25 | Strasbourg |    3000000 |
|  8 |   25 | Bordeaux   |    1000000 |
|  9 |   75 | Rome       |    2000000 |
| 10 |   75 | Venise     |    3000000 |
| 11 |   75 | Naple      |    1000000 |
| 12 |   75 | Turin      |    2000000 |
| 13 |   75 | Milan      |    1000000 |
| 14 |   75 | Bologne    |    3000000 |
| 15 |   75 | Florence   |    3000000 |
| 16 |   33 | Madrid     |    2000000 |
| 17 |   33 | Séville    |    3000000 |
| 18 |   33 | Grenande   |    3000000 |
| 19 |   33 | Barcelone  |    1000000 |
+----+------+------------+------------+
--------------
drop table if exists `temp`
--------------
 
--------------
create table `temp`
( `id`             integer unsigned not null auto_increment primary key,
  `population`     bigint  unsigned not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `temp` (`population`) values  (1000000),(2000000)
--------------
 
--------------
select * from `temp`
--------------
 
+----+------------+
| id | population |
+----+------------+
|  1 |    1000000 |
|  2 |    2000000 |
+----+------------+
--------------
drop view if exits `vue`
--------------
 
ERROR 1064 (42000) at line 77: Erreur de syntaxe près de 'exits `vue`' à la ligne 1
--------------
create view `vue` as
    select  r1.population,
            r1.pays,
            r1.commune,
            count(case when r1.population = r2.population then 1 else 0 end) as rang
 
      from  `temp`          as t
 
inner join  `referencement` as r1
        on  r1.population = t.population
 
inner join  `referencement` as r2
        on  r2.population = t.population
       and  r2.id < r1.id
 
  group by  r1.population, r1.pays, r1.commune
  order by  r1.population, r1.pays, r1.commune
--------------
 
--------------
select    *
    from  vue
order by  population, rang
--------------
 
+------------+------+-----------+------+
| population | pays | commune   | rang |
+------------+------+-----------+------+
|    1000000 |   25 | Marseille |    1 |
|    1000000 |   25 | Bordeaux  |    2 |
|    1000000 |   75 | Naple     |    3 |
|    1000000 |   75 | Milan     |    4 |
|    1000000 |   33 | Barcelone |    5 |
|    2000000 |   25 | Toulouse  |    1 |
|    2000000 |   75 | Rome      |    2 |
|    2000000 |   75 | Turin     |    3 |
|    2000000 |   33 | Madrid    |    4 |
+------------+------+-----------+------+
--------------
select    *
    from  vue
   where  rang <= 3
order by  population, rang
--------------
 
+------------+------+-----------+------+
| population | pays | commune   | rang |
+------------+------+-----------+------+
|    1000000 |   25 | Marseille |    1 |
|    1000000 |   25 | Bordeaux  |    2 |
|    1000000 |   75 | Naple     |    3 |
|    2000000 |   25 | Toulouse  |    1 |
|    2000000 |   75 | Rome      |    2 |
|    2000000 |   75 | Turin     |    3 |
+------------+------+-----------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

22/02/2017, 13h57
coliasso

Salut, Artemus24.
Merci de votre réponse.
Mais pourriez-vous me proposer une solution php pour la boucle while?
Encore une fois, merci.
22/02/2017, 20h08
Artemus24

Salut coliasso .

Elle ne vous convient pas cette solution ?

Vous créez une table structurée avec une seule colonne, la population. Au départ, vous la vider.
Vous insérez autant de lignes que vous avez de demande à produire. Dans mon exemple, j'ai inséré que deux lignes.
Vous exécutez la requête qui va produire deux groupes de trois lignes.
Cela remplace votre requête basée sur l'union et c'est plus souple à l'usage, car vous pouvez insérer autant de lignes que vous désirez.

Une boucle en php n'est pas la solution car vous devez traiter selon la théorie des ensembles puisque MySql est un SGBD de type relationnel.

@+
23/02/2017, 10h17
coliasso

Salut, Artemus24.
Encore une fois, merci à toi.
Ce n'est pas qu'elle ne me convient pas, mais le problème est que je vais l'utiliser pour une application que des utilisateurs non avertis vont utiliser et auront juste à cliquer sur des liens pour exécuter des requêtes; or, cette solution demande des manip qui, à mon avis, ne pourront peut-être pas toutes être programmées ainsi.
Par ailleurs, j'ai quelques questions d'éclaircissement sur votre code:
1) je crois comprendre que r1 et r2 diffèrent seulement par le critère r2.id< r1.id; mais je comprends pas très bien l'action de ce critère dans ce contexte.
2) dans votre code, je ne vois pas encore très bien à quel niveau intervient le caractère aléatoire du choix des communes(mon objectif principal était de tirer au hasard 3 communes pour chaque valeur de population).
Par avance, merci.

Salut Coliasso.

Citation:

Envoyé par Coliasso

Encore une fois, merci à toi.

Pas de quoi ! :P
J'essaye tant bien que mal de résoudre votre problème.

Citation:

Envoyé par Coliasso

1) je crois comprendre que r1 et r2 diffèrent seulement par le critère r2.id < r1.id; mais je ne comprends pas très bien l'action de ce critère dans ce contexte.

Votre critère de sélection est la population. A l'affichage, j'ai fait en sorte de sélectionner seulement trois lignes, pour un même critère.
Le but de cette astuce est de numéroter (la colonne rang) toutes les lignes ayant le même critère (même valeur pour population).

La jointure r1 est la ligne qui sera affichée.
La jointure r2 va déterminer le nombre de lignes à partir de r1 en respectant le critère r2.id < r1.id.
Prenons un exemple qui sera plus parlant. Soit l'extraction suivante :
Code:

1 2 3 4 5 6 7 8 9 10 +----+------+------------+------------+ | id | pays | commune | population | +----+------+------------+------------+ | 1 | 25 | Paris | 1000000 | | 4 | 25 | Marseille | 1000000 | | 8 | 25 | Bordeaux | 1000000 | | 11 | 75 | Naple | 1000000 | | 13 | 75 | Milan | 1000000 | | 19 | 33 | Barcelone | 1000000 | +----+------+------------+------------+
Toutes ces lignes ont la même valeur pour la colonne population.
En pratiquant la jointure sur la même extraction et suivant le critère r2.id < r1.id, on demande à chacune des six lignes de l'exemple suivant, le nombre de lignes qui respecte le critère.
Le décompte donne :
--> id = 1, aucune ligne
--> id = 4, on trouve id = 1.
--> id = 8, on trouve id = 1 et id = 4.
--> id =11, on trouve id = 1 et id = 4 et id = 8.
--> id =13, on trouve id = 1 et id = 4 et id = 8 et id =11.
--> id =19, on trouve id = 1 et id = 4 et id = 8 et id =11 et id =13.

Or ce test est effectué par "count(case when r1.population = r2.population then 1 else 0 end) as rang".
En reprenant le décompte, et en l'appliquant au "case", on trouve :
--> id = 1, count() = 1
--> id = 4, count() = 2
--> id = 8, count() = 3
--> id =11, count() = 4
--> id =13, count() = 5
--> id =19, count() = 6

C'est juste une astuce pour comptabiliser le nombre de lignes selon un critère d'ordonnancement (la colonne id est la clef primaire).

Citation:

Envoyé par Coliasso

2) dans votre code, je ne vois pas encore très bien à quel niveau intervient le caractère aléatoire du choix des communes(mon objectif principal était de tirer au hasard 3 communes pour chaque valeur de population).

A vrai dire, je n'ai pas tenu compte de cette façon de sélectionner vos lignes, et je l'avais remplacé par la numérotation des lignes en cas d'égalité sur la population.
Ne tenez plus compte du paragraphe précédent, car j'ai procédé autrement pour utiliser la sélection aléatoire des lignes.

Le principe reste le même. On tri sur la colonne aléa, en numérotant les lignes, et l'on conserve que les trois premières lignes.
A chaque nouvelle exécution de cette requête, vous obtiendrez un affichage différent de vos lignes. Voici la requête :
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 -------------- select * from ( select @rang:=if(@prec=population, @rang+1, 1) as rang, @prec:=population as population, pays, commune from ( select r.population, r.pays, r.commune, cast(rand() as decimal(17, 17)) as alea from `critere` as t inner join `referencement` as r on r.population = t.population order by r.population, r.pays, r.commune ) as x cross join ( select @prec:='', @rang:=0 ) as y order by population, alea ) as z where rang <=3 -------------- +------+------------+------+-----------+ | rang | population | pays | commune | +------+------------+------+-----------+ | 1 | 1000000 | 33 | Barcelone | | 2 | 1000000 | 75 | Milan | | 3 | 1000000 | 75 | Naple | | 1 | 2000000 | 75 | Turin | | 2 | 2000000 | 33 | Madrid | | 3 | 2000000 | 25 | Toulouse | +------+------------+------+-----------+
J'ai renommé la table 'temp' en table 'critere'. C'est plus parlant.

Citation:

Envoyé par Coliasso

Ce n'est pas qu'elle ne me convient pas, mais le problème est que je vais l'utiliser pour une application que des utilisateurs non avertis vont utiliser et auront juste à cliquer sur des liens pour exécuter des requêtes; or, cette solution demande des manip qui, à mon avis, ne pourront peut-être pas toutes être programmées ainsi.

Vous désirez extraire de la table "référencement", les lignes ayant comme population des valeurs que vous allez saisir.
Votre contrainte est le nombre de ces critères qui peuvent être important, disons une centaine de critères, voire plus.
La solution que vous proposez fonctionne très bien pour deux, voire trois critères mais devient ingérable lorsque le nombre de critère explose.
Et pas question de faire une boucle où à chaque itération de celle-ci vous faites une saisie et un affichage du résultat.
Ce n'est pas propre comme façon de faire et en terme de performance, le résultat sera désastreux car autant il y aura de critères, vous ferez autant de balayage de votre table 'référencement'.

Pour résoudre ce problème, faites de l'habillage au sein de votre application php.

1) vous débutez une nouvelle saisie. La première des choses à faire est de remettre la table 'critère' à zéro. Pour ce faire, vous exécutez ceci :

Code:

truncate table `critere`;

2) comme vous désirez faire une boucle, autant la faire en php au moment de la saisie.
A chaque nouvelle saisie, vous remplissez la table 'critère', en faisant :

Code:

insert into `critere` (`population`) values (la_valeur_que_vous_saisissez);

3) quand la saisie est terminée, vous déclenchez alors la requête ci-dessus.
Le résultat produira pour chaque critère saisie, un paquet de trois lignes.
A chaque ré-exécution de cette requête, avec les mêmes critères, vous obtiendrez un affichage différent (aléatoire) comme vous le souhaitez.

@+

24/02/2017, 12h20
coliasso

Grand merci, Artemus24.
A+