Afficher les éléments de droite avec RIGHT JOIN

Bonjour à tous

J'ai une grosse requete avec plein de jointure qui fonctionne très bien
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 $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

Citation:

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

Citation:

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

Citation:

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)

Citation:

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:

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:

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