Discussion :

[pierrot10]

Bonjour à tous

J'ai une grosse requete avec plein de jointure qui fonctionne très bien

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
$sql_select ='SELECT fi.field_name, st.id_station, st.station_name, st.station_longname, se.sensor_name, se.sensor_longname, se.chart_pointStyle_id_chart_pointStyle, se.chart_pointRadius, se.chart_fill, se.chart_showLine, se.chart_borderWidth, se.chart_pointHoverRadius, se.id_sensor, stype.measure_unit, stype.id_sensor_type, stype.sensor_type_name,m.value, c.id_collection,c.ttn_m_time,
		cs.chart_style_value, cp.chart_pointStyle_value,
		col_bg.color_name as bg_color,col_bg.color_value as bg_color_value,
		col_bd.color_name as bd_color,col_bd.color_value as bd_color_value
		FROM fields AS fi
		INNER JOIN stations AS st ON fi.id_field = st.fields_id_field
		INNER JOIN sensors AS se ON st.id_station = se.stations_id_station
		INNER JOIN sensor_types AS stype ON se.sensor_types_id_sensor_type = stype.id_sensor_type
		INNER JOIN measures AS m ON se.id_sensor = m.sensors_id_sensor
		INNER JOIN collections AS c ON m.collections_id_collection = c.id_collection
		INNER JOIN chart_backgroundColor AS cbgc on cbgc.id_chart_backgroundColor=se.chart_backgroundColor_id_chart_backgroundColor
		INNER JOIN colors AS col_bg on cbgc.colors_id_colors=col_bg.id_colors
		INNER JOIN chart_borderColor AS cbdc on cbdc.id_chart_borderColor=se.chart_borderColor_id_chart_borderColor
		INNER JOIN colors AS col_bd on cbdc.colors_id_colors=col_bd.id_colors
		INNER JOIN chart_style AS cs on se.chart_style_id_chart_style = cs.id_chart_style
		INNER JOIN chart_pointStyle AS cp on se.chart_pointStyle_id_chart_pointStyle = cp.id_chart_pointStyle
		WHERE fi.id_field = '.$field.' AND se.sensor_active = 1 AND st.station_active = 1
		'.$clauseAND2.'
		ORDER BY c.ttn_m_time, se.sensor_longname, c.id_collection  ASC';
 
	$sql_result = $connect->query($sql_select);
 
 
	if($debug==true)
	{
		if($sql_result->num_rows>0){
 
				$j=0;
 
				// Collect the time of measures and the id of the two sensors
				while($row = $sql_result->fetch_assoc())
				{
					echo '<pre>';
					echo print_r($row);
					echo '</pre>';
 
					$j++;
 
				}
		}
 
		$sql_result->data_seek(0);
		echo "<hr>";
	}

ceci m'affiche ( je ne mets pas tous, car il y en a 16

Array
(
[field_name] => hutins
[id_station] => 1
[station_name] => st-1
[station_longname] => Station 1
[sensor_name] => b1
[sensor_longname] => Température bas
[chart_pointStyle_id_chart_pointStyle] => 1
[chart_pointRadius] => 1
[chart_fill] => 0
[chart_showLine] => 1
[chart_borderWidth] => 1
[chart_pointHoverRadius] => 13
[id_sensor] => 1
[measure_unit] => C
[id_sensor_type] => 2
[sensor_type_name] => te
[value] => 18.0000
[id_collection] => 50
[ttn_m_time] => 2020-11-05 00:50:06
[chart_style_value] => line
[chart_pointStyle_value] => triangle
[bg_color] => Red
[bg_color_value] => 255, 99, 132
[bd_color] => Red
[bd_color_value] => 255, 99, 132
)
1

Array
(
[field_name] => hutins
[id_station] => 1
[station_name] => st-1
[station_longname] => Station 1
[sensor_name] => b4
[sensor_longname] => Température haut
[chart_pointStyle_id_chart_pointStyle] => 4
[chart_pointRadius] => 1
[chart_fill] => 0
[chart_showLine] => 1
[chart_borderWidth] => 1
[chart_pointHoverRadius] => 13
[id_sensor] => 4
[measure_unit] => C
[id_sensor_type] => 2
[sensor_type_name] => te
[value] => 18.0000
[id_collection] => 50
[ttn_m_time] => 2020-11-05 00:50:06
[chart_style_value] => line
[chart_pointStyle_value] => cicle
[bg_color] => Turquoise
[bg_color_value] => 75, 192, 192
[bd_color] => Turquoise
[bd_color_value] => 75, 192, 192
)
1

Le problème est qu'il va m'afficher les que si une station dont ses capteurs ont des mesures enregitrées dans la base de donnée

INNER JOIN stations AS st ON fi.id_field = st.fields_id_field

J'aimerais modifier ceci de manière que l'ID (st.id_station) soit aussi listé. J'ai 6 stations actifs pour le field qui a l'id 2 (fi.id_field) et trois stations ont des mesures.
J'aimerias que toutes les stations actives soient aussi listée même si il n'y a pas encore de capteurs enregistrés et associés aux stations qui ne sont pas (encore) listée

Par exemple

Array
(
[field_name] => hutins
[id_station] => 5
[station_name] => st-5
[station_longname] => Station 5
)

Ou s'il y a des capteurs associées mais pas encore de mesures, ceci devrait ressemblé à ceci (sans [value] => 18.0000)

Array
(
[field_name] => hutins
[id_station] => 1
[station_name] => st-1
[station_longname] => Station 1
[sensor_name] => b4
[sensor_longname] => Température haut
[chart_pointStyle_id_chart_pointStyle] => 4
[chart_pointRadius] => 1
[chart_fill] => 0
[chart_showLine] => 1
[chart_borderWidth] => 1
[chart_pointHoverRadius] => 13
[id_sensor] => 4
[measure_unit] => C
[id_sensor_type] => 2
[sensor_type_name] => te
[id_collection] => 50
[ttn_m_time] => 2020-11-05 00:50:06
[chart_style_value] => line
[chart_pointStyle_value] => cicle
[bg_color] => Turquoise
[bg_color_value] => 75, 192, 192
[bd_color] => Turquoise
[bd_color_value] => 75, 192, 192
)

J'ai essayé de modifier ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

RIGHT JOIN stations AS st ON fi.id_field = st.fields_id_field

sans succès.

Dans l'alternative, je me demande si je ne devrais pas faire une autre requête qui va uniqument listé les stations su du genre

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
$sql_select ='SELECT st.id_station FROM stations AS st
		WHERE st.station_active = 1
		ORDER BY st.id_station  ASC';
 
	$sql_result = $connect->query($sql_select);

Puis fusionner les résultats dans la construction de mon array que je vais devoir exploiter

La seconde alternatice me semble bien plus simple à appliquer, mais si je peux tout faire dans la meme requete, ca serait aussi bien