Requête calcul moyenne sans les valeurs NULL

**Delba146** · 07/12/2023, 14h19

Bonjour,

Je travaille sur un projet de station meteo des capteurs branchés qui remontent des données vers une base SQL via un ESP32 et ensuite j'exploite cela pour un affichage sur une page html.

Je reçois des infos de la température pression humidité toutes les 10mn et je peux donc faire une moyenne calculer les max les min, ça marche
Je reçois également des valeurs de particules d'air mais toutes les 30mn ce qui signifie que dans ma BD j'ai 2 enregistrements sur 3 sans valeurs

Donc ma moyenne des particules se fait sur les valeurs réelles (un nombre) mais prend en compte les valeurs null qui sont considérées à 0 j'ai donc essayé avec IS NOT NULL mais maintenant le resultat de la moyenne est à 0.

Voici la fonction :

Code :

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
function avgReadingpm($value) {
  global $servername, $username, $password, $dbname;
  $value = (float)$value;
  var_dump($value);
   // Create connection
   $conn = new mysqli($servername, $username, $password, $dbname);
   // Check connection
   if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
   }
 
   $sql = "SELECT AVG(CAST(" . $value . " AS SIGNED)) AS avg_amount FROM SensorData WHERE (" . $value . "  IS NOT NULL ) AND (reading_time BETWEEN NOW() - INTERVAL 6 HOUR AND NOW()) ";
 
 
    if ($result = $conn->query($sql)) {
     return $result->fetch_assoc();
   }
   else {
     return false;
   }
   $conn->close();
 }

Pour info le retour de var_dump($value) est float(0) lorsqu'il n'y a pas de valeurs
Petite info supplémentaire j'ai suivi un tuto pour la création de ma BD et les valeurs sont en varchar.

Merci

**escartefigue** · 08/12/2023, 08h48

Bonjour,

Les occurrences de colonnes marquées "null" ne sont pas prises en compte dans les calculs d'agrégat

La preuve, avec ce jeu d'essais :

Code SQL :

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
create table T1
      (  T1IDT     integer       primary key
       , T1VAL     decimal(7,2)  
      )
;
insert into T1(T1IDT, T1VAL)
values (1, 200)
     , (2, 100)
     , (3, null)
     , (4, 300)
;
select * from T1
;
select avg(T1VAL)                as MOY
     , avg(coalesce(T1VAL, 0))   as MOY0
     , max(T1VAL)                as MX
     , MIN(T1VAL)                as MN
from T1

Le résultat est :

Nom : Sans titre.png
Affichages : 234
Taille : 3,9 Ko

On voit bien la différence entre MOY (calcul d'agrégat qui ne tient pas compte des "null") et MOY0 (calcul d'agrégat, dans lequel les colonnes marquées "null" sont remplacées par la valeur zéro)

Par ailleurs, des valeurs issues de capteurs ne devraient pas être de type FLOAT, encore moins de type VARCHAR

!
Si les valeurs sont toujours entières, alors de l'INTEGER (small, int ou big selon le max possible), sinon, il faut utiliser du DECIMAL.
Le FLOAT présente le défaut d'avoir un montant décimal approximatif, alors que le VARCHAR est inadapté pour les calculs, à réserver pour des chaînes de caractères !

**Delba146** · 09/12/2023, 10h04

Bonjour escartefigue,

Merci pour ta réponse à te lire je me demande si le fond de mon pb ce n'est pas la construction de ma BD, à l'origine j'ai suivi un tuto que voici :

https://randomnerdtutorials.com/clou...esp32-esp8266/
Avec un BD construite comme cela :

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE SensorData (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sensor VARCHAR(30) NOT NULL,
    location VARCHAR(30) NOT NULL,
    value1 VARCHAR(10),
    value2 VARCHAR(10),
    value3 VARCHAR(10),
    reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Etant débutant j'ai suivi le tuto à la lettre, ma première question dois je créer une autre BD avec des données en DECIMAL, ou puis je continuer ainsi?

J'ai essayé de modifier la BD et passer de varchar à Decimal mais j'ai ce message :
Erreur de requête:
#1264 - Out of range value for column 'pm25' at row 534

Ligne 533 ||NULL ||2023-11-05 14:17:26
Ligne 534 || 701.30 ||730.70 ||2023-11-05 14:17:58

A partir de la ligne 534 j'ai commencé à recevoir des valeurs avant c'était NULL, c'est une impresssion ou je vais devoir supprimer ma BD et la refaire en decimal???

**Séb.** · 09/12/2023, 13h14

Etant débutant j'ai suivi le tuto à la lettre, ma première question dois je créer une autre BD avec des données en DECIMAL, ou puis je continuer ainsi?

Il faut typer correctement tes données. C'est un prérequis. Sinon tu auras toujours des problèmes, et plus tu avanceras, plus ce sera difficile à redresser.

J'ai essayé de modifier la BD et passer de varchar à Decimal mais j'ai ce message :
Erreur de requête:
#1264 - Out of range value for column 'pm25' at row 534

Ligne 533 ||NULL ||2023-11-05 14:17:26
Ligne 534 || 701.30 ||730.70 ||2023-11-05 14:17:58

Donne ta requête et un échantillon de données.

A partir de la ligne 534 j'ai commencé à recevoir des valeurs avant c'était NULL, c'est une impresssion ou je vais devoir supprimer ma BD et la refaire en decimal???

Surtout ne supprime rien. Il faut simplement correctement typer ta colonne et éventuellement préparer la conversion.

**Delba146** · 09/12/2023, 17h37

Bonjour Seb
Je ne sais pas tu me parles de la requête pour modifier ma BD dans phpmyadmin ou ma requête php qui fait le calcul de la moyenne ??

Pour la requête pour modifier la BD je fais simplement structure/modifier et je change varchar(10) par DECIMAL(5,2) que phpmyadmin traduit en

Code :

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

ALTER TABLE `SensorData` CHANGE `pm25` `pm25` DECIMAL(5,2) NULL DEFAULT NULL;

avec ce retour :
Erreur de requête:
#1292 - Truncated incorrect DECIMAL value: ''

----------------------------------------------------------
Pour la partie php que me fait les moyennes voici

La structure de la BD :
Base de données id21478987_bddatameteo
Structure de la table SensorData
Colonne Type Null Valeur par défaut Commentaires
id int(6) Non
sensor varchar(30) Non
location varchar(30) Non
value1 varchar(10) Oui NULL
value2 varchar(10) Oui NULL
value3 varchar(10) Oui NULL
value4 varchar(10) Oui NULL
pm25 varchar(10) Oui NULL
pm10 varchar(10) Oui NULL
reading_time timestamp Non current_timestamp()
Déchargement des données de la table SensorData

Un échantillon de données :

Id sesnor location Value1 Value2 Value3 Value4 Pm25 Pm10 Reading_time
14084 BME280DHTAIR Office 7.91 89.88 1003.19 7.03 271.30 280.40 2023-12-09 08:08:52
14085 BME280DHTAIR Office 7.92 89.89 1003.19 7.03 2023-12-09 08:08:54
14086 BME280DHTAIR Office 7.91 89.93 1003.10 7.03 2023-12-09 08:10:55
14087 BME280DHTAIR Office 7.91 89.93 1002.96 7.03 269.82 278.87 2023-12-09 08:13:56

Les valeurs pm25 et pm10 sont reçue toutes les 30mn les autres toutes les 10mn

Dans l'ordre en premier je récupère toutes les données de la façon suivante :

Code :

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

1
2
3
4
5
 if (isset($_GET["readingsCount"])){
      $data = $_GET["readingsCount"];
      $data = trim($data);
      $data = stripslashes($data);
      $data = htmlspecialchars($data);

Ensuite je lance une onction pour le calcul de la moyenne :

Code :

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

 $avg_pm25 =  avgReadingpm('pm25');

Et voici la fonction avec la requête :

Code :

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
function avgReadingpm($value,) {
  global $servername, $username, $password, $dbname;
  $value = (float)$value;
  var_dump($value);
 
   $conn = new mysqli($servername, $username, $password, $dbname);
 
   if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
   }
       $sql = "SELECT AVG(CAST(" . $value . " AS SIGNED)) AS avg_amount FROM SensorData WHERE (" . $value . "  IS NOT NULL ) AND (reading_time BETWEEN NOW() - INTERVAL 6 HOUR AND NOW()) ";
    if ($result = $conn->query($sql)) {
     return $result->fetch_assoc();
   }
   else {
     return false;
   }
   $conn->close();
 }

Dis moi si je t'ai donné les bonnes infos

**laurentSc** · 09/12/2023, 18h57

Bonjour,
je pense que Séb parlait au post #4 de requête SQL. Tu as donné la DDL de la table au post #3. Mais elle n'est pas cohérente avec la structure que tu donnes au post #5. Par contre, pour faciliter les tests, pourrais-tu donner l'échantillon de données sous forme SQL : INSERT INTO SensorData (id, sensor, location, value1, value2, value3, value4, pm25, pm10, reading_time) VALUE("14084", "BME280DHTAIR", "Office", "7.91", "89.88", "1003.19", "7.03", "271.30", "280.40", "2023-12-09", "08:08:52"); etcMon exemple va pas vu qu'il compte 10 colonnes et 11 valeurs. A toi de corriger...

Je suis plus là ce soir...

**Delba146** · 09/12/2023, 19h13

Voici comme demandé :

Code :

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

1
2
3
4
5
6
7
8
9
INSERT INTO `SensorData` (`id`, `sensor`, `location`, `value1`, `value2`, `value3`, `value4`, `pm25`, `pm10`, `reading_time`) VALUES
(14133, 'BME280DHTAIR', 'Office', '12.07', '77.02', '995.51', '11.34', '370.38', '383.05', '2023-12-09 16:54:32'),
(14134, 'BME280DHTAIR', 'Office', '11.49', '76.71', '996.51', '10.68', '', '', '2023-12-09 17:32:22'),
(14135, 'BME280DHTAIR', 'Office', '11.49', '76.71', '996.52', '10.68', '349.03', '360.67', '2023-12-09 17:34:25'),
(14136, 'BME280DHTAIR', 'Office', '11.51', '76.23', '996.65', '10.67', '', '', '2023-12-09 17:34:28'),
(14137, 'BME280DHTAIR', 'Office', '11.51', '76.20', '996.52', '10.66', '', '', '2023-12-09 17:36:32'),
(14138, 'BME280DHTAIR', 'Office', '11.51', '76.20', '996.57', '10.66', '393.72', '407.13', '2023-12-09 17:39:32'),
(14139, 'BME280DHTAIR', 'Office', '11.51', '76.36', '996.51', '10.66', '', '', '2023-12-09 17:39:33'),
(14140, 'BME280DHTAIR', 'Office', '11.51', '76.28', '996.63', '10.66', '', '', '2023-12-09 17:41:36'),

**Séb.** · 09/12/2023, 21h22

Tu donnes des bouts de SQL, on ne peut pas t'aider là.

CREATE TABLE SensorData (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sensor VARCHAR(30) NOT NULL,
    location VARCHAR(30) NOT NULL,
    value1 VARCHAR(10),
    value2 VARCHAR(10),
    value3 VARCHAR(10),
    reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO `SensorData` (`id`, `sensor`, `location`, `value1`, `value2`, `value3`, `value4`, `pm25`, `pm10`, `reading_time`) VALUES
(14133, 'BME280DHTAIR', 'Office', '12.07', '77.02', '995.51', '11.34', '370.38', '383.05', '2023-12-09 16:54:32'),
(14134, 'BME280DHTAIR', 'Office', '11.49', '76.71', '996.51', '10.68', '', '', '2023-12-09 17:32:22'),
(14135, 'BME280DHTAIR', 'Office', '11.49', '76.71', '996.52', '10.68', '349.03', '360.67', '2023-12-09 17:34:25'),
(14136, 'BME280DHTAIR', 'Office', '11.51', '76.23', '996.65', '10.67', '', '', '2023-12-09 17:34:28'),
(14137, 'BME280DHTAIR', 'Office', '11.51', '76.20', '996.52', '10.66', '', '', '2023-12-09 17:36:32'),
(14138, 'BME280DHTAIR', 'Office', '11.51', '76.20', '996.57', '10.66', '393.72', '407.13', '2023-12-09 17:39:32'),
(14139, 'BME280DHTAIR', 'Office', '11.51', '76.36', '996.51', '10.66', '', '', '2023-12-09 17:39:33'),
(14140, 'BME280DHTAIR', 'Office', '11.51', '76.28', '996.63', '10.66', '', '', '2023-12-09 17:41:36')
;

**Delba146** · 10/12/2023, 01h07

Bonsoir,

Je suis novice il y a encore 3 mois je n'y connaissais rien je fais cela comme un loisirs, donc si parfois je ne donne pas les bonnes infos c'est uniquement pas manque de compréhension.
Quand tu dis " Tu donnes des bouts de SQL, on ne peut pas t'aider là" est ce tu souhaites avoir l'intégralité du fichier comme suit?

Code :

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
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
-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Hôte : localhost:3306
-- Généré le : sam. 09 déc. 2023 à 18:11
-- Version du serveur : 10.5.20-MariaDB
-- Version de PHP : 7.3.33
 
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
 
--
-- Base de données : `id21478987_bddatameteo`
--
 
-- --------------------------------------------------------
 
--
-- Structure de la table `SensorData`
--
 
CREATE TABLE `SensorData` (
  `id` int(6) UNSIGNED NOT NULL,
  `sensor` varchar(30) NOT NULL,
  `location` varchar(30) NOT NULL,
  `value1` varchar(10) DEFAULT NULL,
  `value2` varchar(10) DEFAULT NULL,
  `value3` varchar(10) DEFAULT NULL,
  `value4` varchar(10) DEFAULT NULL,
  `pm25` varchar(10) DEFAULT NULL,
  `pm10` varchar(10) DEFAULT NULL,
  `reading_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Déchargement des données de la table `SensorData`
--
 
INSERT INTO `SensorData` (`id`, `sensor`, `location`, `value1`, `value2`, `value3`, `value4`, `pm25`, `pm10`, `reading_time`) VALUES
(14133, 'BME280DHTAIR', 'Office', '12.07', '77.02', '995.51', '11.34', '370.38', '383.05', '2023-12-09 16:54:32'),
(14134, 'BME280DHTAIR', 'Office', '11.49', '76.71', '996.51', '10.68', '', '', '2023-12-09 17:32:22'),
(14135, 'BME280DHTAIR', 'Office', '11.49', '76.71', '996.52', '10.68', '349.03', '360.67', '2023-12-09 17:34:25'),
(14136, 'BME280DHTAIR', 'Office', '11.51', '76.23', '996.65', '10.67', '', '', '2023-12-09 17:34:28'),
(14137, 'BME280DHTAIR', 'Office', '11.51', '76.20', '996.52', '10.66', '', '', '2023-12-09 17:36:32'),
(14138, 'BME280DHTAIR', 'Office', '11.51', '76.20', '996.57', '10.66', '393.72', '407.13', '2023-12-09 17:39:32'),
(14139, 'BME280DHTAIR', 'Office', '11.51', '76.36', '996.51', '10.66', '', '', '2023-12-09 17:39:33'),
(14140, 'BME280DHTAIR', 'Office', '11.51', '76.28', '996.63', '10.66', '', '', '2023-12-09 17:41:36'),
(14141, 'BME280DHTAIR', 'Office', '11.51', '76.28', '996.78', '10.66', '357.62', '370.10', '2023-12-09 17:44:35'),
(14142, 'BME280DHTAIR', 'Office', '11.50', '76.27', '996.75', '10.66', '', '', '2023-12-09 17:44:37'),
(14143, 'BME280DHTAIR', 'Office', '11.49', '76.35', '996.84', '10.66', '', '', '2023-12-09 17:46:40'),
(14144, 'BME280DHTAIR', 'Office', '11.49', '76.35', '996.85', '10.66', '380.52', '394.07', '2023-12-09 17:49:39'),
(14145, 'BME280DHTAIR', 'Office', '11.48', '76.27', '996.86', '10.66', '', '', '2023-12-09 17:49:40'),
(14146, 'BME280DHTAIR', 'Office', '11.48', '76.24', '997.00', '10.66', '', '', '2023-12-09 17:51:44'),
(14147, 'BME280DHTAIR', 'Office', '11.48', '76.24', '996.99', '10.66', '365.25', '377.48', '2023-12-09 17:54:44'),
(14148, 'BME280DHTAIR', 'Office', '11.47', '75.99', '997.06', '10.66', '', '', '2023-12-09 17:54:47'),
(14149, 'BME280DHTAIR', 'Office', '11.49', '75.72', '996.96', '10.65', '', '', '2023-12-09 17:56:50'),
(14150, 'BME280DHTAIR', 'Office', '11.49', '75.72', '996.88', '10.65', '364.48', '377.33', '2023-12-09 17:59:51'),
(14151, 'BME280DHTAIR', 'Office', '11.48', '75.60', '996.91', '10.64', '', '', '2023-12-09 17:59:54'),
(14152, 'BME280DHTAIR', 'Office', '11.48', '75.06', '996.97', '10.64', '', '', '2023-12-09 18:01:58'),
(14153, 'BME280DHTAIR', 'Office', '11.48', '75.06', '996.97', '10.64', '377.93', '390.67', '2023-12-09 18:04:56'),
(14154, 'BME280DHTAIR', 'Office', '11.49', '74.28', '996.97', '10.62', '', '', '2023-12-09 18:04:58'),
(14155, 'BME280DHTAIR', 'Office', '11.49', '74.04', '996.97', '10.60', '', '', '2023-12-09 18:07:01'),
(14156, 'BME280DHTAIR', 'Office', '11.48', '74.40', '997.11', '10.61', '', '', '2023-12-09 18:10:10');
 
--
-- Index pour les tables déchargées
--
 
--
-- Index pour la table `SensorData`
--
ALTER TABLE `SensorData`
  ADD PRIMARY KEY (`id`);
 
--
-- AUTO_INCREMENT pour les tables déchargées
--
 
--
-- AUTO_INCREMENT pour la table `SensorData`
--
ALTER TABLE `SensorData`
  MODIFY `id` int(6) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14157;
COMMIT;
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

**Séb.** · 10/12/2023, 09h43

est ce tu souhaites avoir l'intégralité du fichier comme suit?

Oui voilà.

Selon le paramétrage de ton serveur, la modification de la définition de la table peut être rejetée s'il y a incohérence. Ici le serveur ne sait pas comment convertir une chaîne vide '' en DECIMAL, il faut alors préparer la redéfinition.

Par exemple en marquant NULL les valeurs '' :

update SensorData set pm25 = null where pm25 = '';
update SensorData set pm10 = null where pm10 = '';

Bien sûr, il faudra veiller à insérer les nouvelles lignes correctement, avec le marqueur NULL et non une chaîne vide lorsque la valeur est inconnue.

Ensuite tu peux modifier ta table :

alter table SensorData
modify column value1 decimal(5, 2), -- 5 chiffres max, dont 2 décimales, plage -999,99 à 999,99
modify column value2 decimal(5, 2),
modify column value3 decimal(5, 2),
modify column value4 decimal(5, 2),
modify column pm25 decimal(5, 2),
modify column pm10 decimal(5, 2)
;

Ex. moyenne par tranche horaire :

select all
    timestamp(date(reading_time), maketime(hour(reading_time), 0, 0)),
    avg(value1), avg(value2), avg(value3), avg(value4), avg(pm25), avg(pm10)
from SensorData
group by 1
order by 1 desc
;

**laurentSc** · 10/12/2023, 13h01

En appliquant à la lettre les conseils de séb, impec...

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `sensordata` (
  `id` int unsigned NOT NULL,
  `sensor` varchar(30) NOT NULL,
  `location` varchar(30) NOT NULL,
  `value1` decimal(5,2) DEFAULT NULL,
  `value2` decimal(5,2) DEFAULT NULL,
  `value3` decimal(5,2) DEFAULT NULL,
  `value4` decimal(5,2) DEFAULT NULL,
  `pm25` decimal(5,2) DEFAULT NULL,
  `pm10` decimal(5,2) DEFAULT NULL,
  `reading_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

**Delba146** · 10/12/2023, 15h42

Bonjour,

Tout d'abord merci à vous, avec mes galéres j'ai appris beaucoup de choses sur la manipulation des données, je peux nettoyer ma BD mais comme le dit justement seb " il faudra veiller à insérer les nouvelles lignes correctement, avec le marqueur NULL et non une chaîne vide lorsque la valeur est inconnue." donc j'ai fait des essais.

Mon ESP envoie toutes les 2 mn un message avec les Températures .. sans données de particules pm (MESSAGE 1) et toutes les 5mn ESP envoie toutes les infos (MESSAGE 2).
Désormais en BD DECIMAL le message sans les données pm25 et pm10 n'est plus enregistré uniquement le message MESSAGE 2 est enregistré avec toutes les colonnes pour revoir les données du MESSAGE 1 je dois repasser pm25 et pm10 en varchar pour trés certainement accepter les vides enfin je suppose

Ci-dessous les messages ESP et les fonctions qui traitent les messages

Selon vous quelle stratégie adoptée?? modifier le message ESP ou autre??

MESSAGE 1 : Toutes les 2 mn mon ESP envoie le message suivant :

Code :

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

1
2
3
httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName
                        + "&location=" + sensorLocation + "&value1=" + TEMPMOY
                        + "&value2=" + HUMMOY + "&value3=" + String(bme.readPressure() / 100.0F) + "&value4=" + temperatureRessentieEnCelsius;

MESSAGE 2 : Toutes les 5mn mon ESP envoie le message suivant :

Code :

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

1
2
3
httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName
                        + "&location=" + sensorLocation + "&value1=" + TEMPMOY
                        + "&value2=" + HUMMOY + "&value3=" + String(bme.readPressure() / 100.0F) + "&value4=" + temperatureRessentieEnCelsius + "&pm25=" + Moy_Pm25 + "&pm10=" + Moy_Pm10;

Pour info les messages sont interprétés par les lignes suivantes :

Code :

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
include_once('esp-database.php');
  error_log('test');
  // Keep this API Key value to be compatible with the ESP code provided in the project page. If you change this value, the ESP sketch needs to match
  $api_key_value = "123";
  $api_key= $sensor = $location =  $pm25 = $pm10 = $value1 = $value2 = $value4 = $value3 =  "";
 
  if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $api_key = test_input($_POST["api_key"]);
    if($api_key == $api_key_value) {
      $sensor = test_input($_POST["sensor"]);
      $location = test_input($_POST["location"]);
      $value1 = test_input($_POST["value1"]);
      $value2 = test_input($_POST["value2"]);
      $value3 = test_input($_POST["value3"]);
      $value4 = test_input($_POST["value4"]);
      $pm25 = test_input($_POST["pm25"]);
      $pm10 = test_input($_POST["pm10"]);
 
      $result = insertReading($sensor, $location, $value1, $value2, $value3, $value4, $pm25, $pm10);
 
      echo $result;
    }
    else {
      echo "Wrong API Key provided.";
    }
  }
  else {
    echo "No data posted with HTTP POST.";
  }
 
  function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
  }

Et ensuite le insertreading :

Code :

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
function insertReading($sensor, $location, $value1, $value2, $value3, $value4, $pm25, $pm10) {
    global $servername, $username, $password, $dbname;
 
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }
 
    $sql = "INSERT INTO SensorData (sensor, location, value1, value2, value3, value4, pm25, pm10)
 
       VALUES ('" . $sensor . "', '" . $location . "', '" . $value1 . "', '" . $value2 . "', '" . $value3 . "','" . $value4 . "', '" . $pm25 . "', '" . $pm10 ."')";
 
   if ($conn->query($sql) === TRUE) {
      return "New record created successfully";
 
    }

**Séb.** · 10/12/2023, 19h50

je dois repasser pm25 et pm10 en varchar pour trés certainement accepter les vides enfin je suppose

Non, tu manipules des numériques il faut donc un type SQL DECIMAL (ou INTEGER si tu as seulement des entiers), le VARCHAR sert uniquement pour du texte.
Les difficultés autour de cela viennent d'une méconnaissance et pas du type de données utilisé.

Selon vous quelle stratégie adoptée?? modifier le message ESP ou autre??

Insérer les données correctement. Quand la valeur est absente tu utilises le marqueur SQL NULL => La fonction SQL NULLIF() sera utile

NULLIF(param1, param2) -- Renvoie NULL si param1 est égal à param2, sinon renvoie param1

https://dev.mysql.com/doc/refman/8.0...unction_nullif

Le script PHP est une vraie horreur.

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
function insertReading($sensor, $location, $value1, $value2, $value3, $value4, $pm25, $pm10) {
    global $servername, $username, $password, $dbname;
 
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }
 
    $sql = "INSERT INTO SensorData (sensor, location, value1, value2, value3, value4, pm25, pm10)
 
       VALUES ('" . $sensor . "', '" . $location . "', '" . $value1 . "', '" . $value2 . "', '" . $value3 . "','" . $value4 . "', '" . $pm25 . "', '" . $pm10 ."')";
 
   if ($conn->query($sql) === TRUE) {
      return "New record created successfully";
 
    }
}

En le remaniant un minimum :

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
function insertReading($sensor, $location, $value1, $value2, $value3, $value4, $pm25, $pm10)
{
    global $servername, $username, $password, $dbname;
 
    // La connexion devrait être un paramètre de la fonction
    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }
 
    $sensor = $conn->real_escape_string($sensor);
    $location = $conn->real_escape_string($location);
    $value1 = $conn->real_escape_string($value1);
    $value2 = $conn->real_escape_string($value2);
    $value3 = $conn->real_escape_string($value3);
    $value4 = $conn->real_escape_string($value4);
    $pm25 = $conn->real_escape_string($pm25);
    $pm10 = $conn->real_escape_string($pm10);
 
    // Syntaxe INSERT/SET, propriétaire MySQL mais tellement plus lisible...
    // Je te mets du NULL partout si chaîne de valeur vide, à adapter
    $sql = <<<SQL
        INSERT INTO SensorData
        SET
            sensor = '{$sensor}',
            location '{$location}',
            value1 = NULLIF('{$value1}, ''),
            value2 = NULLIF('{$value2}, ''),
            value3 = NULLIF('{$value3}, ''),
            value4 = NuLLIF('{$value4}, ''),
            pm25 = NULLIF('{$pm25}', ''),
            pm10 = NULLIF('{$pm10}', '')
         SQL;
 
    if ($conn->query($sql) !== TRUE) {
        return "INSERT failed";
    }
 
    return "New record created successfully";
}

**escartefigue** · 11/12/2023, 09h07

Bonjour Delba

Envoyé par Delba146

Bonjour escartefigue,

Merci pour ta réponse à te lire je me demande si le fond de mon pb ce n'est pas la construction de ma BD, à l'origine j'ai suivi un tuto que voici :

https://randomnerdtutorials.com/clou...esp32-esp8266/
Avec un BD construite comme cela :

Ce genre de tuto contient tout ce qu'il ne faut pas faire en matière de modélisation d'une BDD !
D'une part, comme je l'ai mentionné plus haut, le typage des colonnes est inadapté, une mesure météorologique dans une colonne de type varchar est une hérésie.
D'autre part, mettre dans une même table 4 occurrences de valeurs est une autre hérésie : ça ne convient que si tout capteur effectue systématiquement 4 et seulement 4 types de mesures.
Et comme dans le script fourni, il y a 4 mesures pour un même timestamp, il faut en plus que ces 4 mesures soient faites exactement à la même nanoseconde...
Or, un capteur de température ne mesurera que des températures, un capteur de pression que des pressions, un anémomètre que des vitesses...
Une base de données relationnelle n'est pas un tableau excel, les colonnes ne doivent pas être répétées, s'il y a plusieurs mesures, on multiplie les lignes, pas les colonnes.

**Delba146** · 11/12/2023, 15h15

Bonjour,
Merci je vais intégrer vos propositions mais j'ai besoin de temps pour tout comprendre, effectivement les tutos sont loin d'être parfait mais bon ça me permet d'appendre au fur et à mesure.
Par contre je reviens sur la remarque de escartefigue :

"Une base de données relationnelle n'est pas un tableau excel, les colonnes ne doivent pas être répétées, s'il y a plusieurs mesures, on multiplie les lignes, pas les colonnes."
Je ne suis pas certain de comprendre aujourd'hui j'ai une table qui contient plusieurs natures de mesures certaines faites toutes les 2mn certaines toutes les 5mn mais à chaque fois je crée une nouvelle ligne, est ce une erreur?

**laurentSc** · 11/12/2023, 15h36

Bonjour,
je ne sais pas ce que pensent les experts (les vrais

), mais je pense qu'il faut déjà mettre au point le modèle de bdd...A mon avis, cela va conduire à la création de 2 tables (et pas une seule), une pour la température pression humidité et une autre pour les valeurs de particules d'air...

**escartefigue** · 11/12/2023, 16h40

Envoyé par Delba146

Bonjour,
Merci je vais intégrer vos propositions mais j'ai besoin de temps pour tout comprendre, effectivement les tutos sont loin d'être parfait mais bon ça me permet d'appendre au fur et à mesure.
Par contre je reviens sur la remarque de escartefigue :

"Une base de données relationnelle n'est pas un tableau excel, les colonnes ne doivent pas être répétées, s'il y a plusieurs mesures, on multiplie les lignes, pas les colonnes."
Je ne suis pas certain de comprendre aujourd'hui j'ai une table qui contient plusieurs natures de mesures certaines faites toutes les 2mn certaines toutes les 5mn mais à chaque fois je crée une nouvelle ligne, est ce une erreur?

Pour bien modéliser une base de données, il faut tout d'abord prendre en compte les règles de gestion.

Si les règles de gestion sont les suivantes (suppositions qui me semblent vraisemblables, à vérifier et corriger si besoin dans votre contexte) :

R001a : une station météo se compose d'au moins un capteur
R001b : un capteur compose une et une seule station météo

R002a : un capteur peut effectuer plusieurs mesures
R002b : une mesure est effectuée par un et un seul capteur

R003a : chaque mesure est associée à un type de mesure (température, hygrométrie, pression, luminosité, vitesse du vent...)
R003b : chaque type de mesures peut être associé à plusieurs mesures

Alors le MCD qui en découle est le suivant (types d'entités entre crochets, associations entre parenthèses) :

[STATION] 1,n --- (composer) --- 1,1 [CAPTEUR] 0,n --- (effectuer) --- 1,1(R) [MESURE] 1,1 --- (typer) --- 0,n [TYPE_MESURE]

De ce MCD, tout logiciel de modélisation dérive automatiquement le modèle tabulaire suivant (PK soulignées, FK suffixées par #) :

ST_STATION (ST_ident, ST_nom...)
CA_CAPTEUR (CA_ident, CA_reference, ST_ident#...)
YM_TYPE_MESURE (YM_ident, YM_code, YM_libelle, YM_unite...)
ME_MESURE (CA_ident#, ME_sequence, ME_dateheure, ME_valeur...) <-- la mesure est une entité-type faible du capteur, d'où l'identification de la mesure relativement au capteur, ce qui explique que la PK du capteur participe à la PK de la mesure, PK complétée par un numéro de séquence pour obtenir l'unicité

Ceci n'est bien sûr qu'une ébauche à compléter en fonction des règles de gestion

Envoyé par laurentSc

Bonjour,
je ne sais pas ce que pensent les experts (les vrais

), mais je pense qu'il faut déjà mettre au point le modèle de bdd...A mon avis, cela va conduire à la création de 2 tables (et pas une seule), une pour la température pression humidité et une autre pour les valeurs de particules d'air...

Oui, il faut d'abord mettre au point la BDD, mais non pas en réfléchissant aux tables, mais aux règles de gestion, puis au MCD. Les tables ne doivent être qu'une conséquence des règles de gestion et donc du MCD, pas un but en soi !

**laurentSc** · 11/12/2023, 21h46

Envoyé par escartefigue

Oui, il faut d'abord mettre au point la BDD, mais non pas en réfléchissant aux tables, mais aux règles de gestion, puis au MCD. Les tables ne doivent être qu'une conséquence des règles de gestion et donc du MCD, pas un but en soi !

C'est bien ce que je disais :

Envoyé par laurentSc

je ne sais pas ce que pensent les experts (les vrais

)

**Delba146** · 13/12/2023, 18h10

Envoyé par escartefigue

Pour bien modéliser une base de données, il faut tout d'abord prendre en compte les règles de gestion.

Quelle claque je viens de prendre en fait je n'y connaissais rien, bon je reviens de déplacement je vais bosser tout cela car ça me semble super intéressant.
En plus ça me permettra d'avoir une approche plus structurée.
Donc je bosse et je reviens (enfin j'espère) avec des questions moins bêtes.

Merci

**escartefigue** · 14/12/2023, 07h41

Excellente initiative

Il existe un forum schema consacré à la modélisation des données, c'est le bon endroit pour y poser les questions sur ce sujet
J'y ai d'ailleurs retrouvé un fil de discussion semblable sur des stations de relevés météorologiques dans un contexte agricole, voyez ICI

Requête calcul moyenne sans les valeurs NULL [MySQL]

PHP & Base de données

Discussions similaires

Partager

Partager