Regrouper données mySQL pour graphique multi séries

**makimax** · 15/02/2021, 19h24

Bonjour,
Je tente de réaliser un graphique "highcharts" qui affiche la consommation de plusieurs compteurs d'eau sur le même graphique.
J'ai réussi pour un seul compteur, mais dans ce cas avec plusieurs je dois récupérer non pas une série mais autant qu'il y a de compteurs dans la table. Le code de mon graphique appelle les données vers un PHP qui fait les requêtes et formate les données en JSON :

Formatage attendu pour un graphique avec une série :
[{"name":Compteur1,"data":[[date1, valeur1],[date2, valeur2],[date3, valeur3]}]

Ce que j'arrive à faire avec le code suivant ou l'ID est récupéré dans l'URL :

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
<?php
//Récupérer le numéro d'id dans l'url pour lancer la requête sur ce device
$id = isset($_GET['id']) ? $_GET['id'] : NULL;
// Inclure la page de connexion à la base de données
include_once 'database.php';
// Connect to MySQL database
$pdo = pdo_connect_mysql();
// IMPORTANT : Pour la requête qui va suivre , il est nécessaire de créer la VIEW mySQL :
/*
    CREATE or REPLACE VIEW V_CONSO_DAY as
          (SELECT meter_devlog_id  as V_CONSO_meter_devlog_id
                 , max(meter_value)    as V_CONSO_meter_value
                 , date(meter_logdate) as V_CONSO_meter_logdate
            FROM `meter` 
            GROUP BY meter_devlog_id 
                   , date(meter_logdate)
            ORDER BY meter_devlog_id
                   , date(meter_logdate)
           )
    ;
*/
// REQUETE pour la consommation par heure des 7 derniers jours, résultat de la différence entre chaque index max du jour J - index max de J-1
// UNIX_TIMESTAMP car Highcharts nécessite la date au format UNIX, *1000 pour être en millisecondes, CONVERT_TZ(ma_variable_datetime, '+00:00', @@session.time_zone) pour que la réponse reste dans le bon fuseau horaire. 
// Prepare the SQL statement :
$stmt = $pdo->prepare("
    SELECT 
        D0.V_CONSO_meter_devlog_id as 'meter_devlog_id',
        UNIX_TIMESTAMP(CONVERT_TZ(D0.V_CONSO_meter_logdate, '+00:00', @@session.time_zone)) * 1000 as 'meter_unix_logdate',
        D0.V_CONSO_meter_value as 'Index_AD',
        D1.Index_PD as 'Index_PD',
        D0.V_CONSO_meter_value - D1.Index_PD as 'conso_day'
    FROM V_CONSO_DAY D0
    LEFT JOIN
        (SELECT 
            V_CONSO_meter_devlog_id,
            V_CONSO_meter_logdate,
            V_CONSO_meter_value as Index_PD
        FROM V_CONSO_DAY
        ) D1
        ON D1.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
        AND D1.V_CONSO_meter_logdate = ( SELECT max(D2.V_CONSO_meter_logdate)
                            FROM V_CONSO_DAY D2
                            WHERE D2.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
                            AND D2.V_CONSO_meter_logdate < D0.V_CONSO_meter_logdate
        )
    WHERE D0.V_CONSO_meter_devlog_id = '{$id}' AND D0.V_CONSO_meter_logdate > (NOW() - INTERVAL 7 DAY)  
    ");
$stmt->execute();
$json = [];
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    extract($row);
    $json[]= [$meter_unix_logdate, (int)$conso_day];
}
echo json_encode($json, JSON_NUMERIC_CHECK);
?>

Pour plusieurs compteur, il faut empiler les autres séries et le formatage doit être celui-ci :

[{"name":Compteur1,"data":[[date1, valeur11],[date2, valeur12],[date3, valeur13]},{"name":Compteur2,"data":[[date1, valeur21],[date2, valeur22],[date3, valeur23]},{"name":Compteur3,"data":[[date1, valeur31],[date2, valeur32],[date3, valeur33]}]

Je n'ai pas réussi à le faire, n'étant pas du tout programmeur, je me débrouille en lisant et en essayant, mais là, je bloc, j'en suis ici :

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
<?php
include_once 'database.php';
$pdo = pdo_connect_mysql();
//$json = array();
$datas = array();
$names = array();
$series = array();
//Retrieve the list of used devices.
$request_used_devices = $pdo->prepare("SELECT * FROM devicestatus WHERE dev_used = 1 ORDER BY dev_id");
$request_used_devices->execute();
$used_devices = $request_used_devices->fetchAll(PDO::FETCH_ASSOC);
 
foreach ($used_devices as $used_device):
 
$stmt = $pdo->prepare("
    SELECT 
        devicestatus.dev_id,
        devicestatus.dev_name,
        UNIX_TIMESTAMP(CONVERT_TZ(D0.V_CONSO_meter_logdate, '+00:00', @@session.time_zone)) * 1000 as 'meter_unix_logdate',
        D0.V_CONSO_meter_value - D1.Index_PD as 'conso_day'
    FROM `devicestatus`
    LEFT JOIN V_CONSO_DAY D0 
      ON D0.V_CONSO_meter_devlog_id = devicestatus.dev_id
    LEFT JOIN
        (SELECT 
            V_CONSO_meter_devlog_id,
            V_CONSO_meter_logdate,
            V_CONSO_meter_value as Index_PD
        FROM V_CONSO_DAY
        ) D1
        ON D1.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
        AND D1.V_CONSO_meter_logdate = ( SELECT max(D2.V_CONSO_meter_logdate)
                            FROM V_CONSO_DAY D2
                            WHERE D2.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
                            AND D2.V_CONSO_meter_logdate < D0.V_CONSO_meter_logdate
        )
    WHERE D0.V_CONSO_meter_devlog_id = '{$used_device['dev_id']}' AND D0.V_CONSO_meter_logdate > (NOW() - INTERVAL 7 DAY) 
    ");
$stmt->execute();
$names[] = $used_device['dev_id'];
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    extract($row);
    $datas[]= [$meter_unix_logdate, (int)$conso_day];
}
 $series['name'] = $names;
 $series['data'] = $datas;
endforeach ;
echo json_encode($series, JSON_NUMERIC_CHECK);
?>

Le résultat n'est pas comme il faut, les "Wagons" sont rangés par Type
[{"name":[Compteur1,Compteur2,Compteur2],"data":[[date1, valeur11],[date2, valeur12],[date3, valeur13],[date1, valeur21],[date2, valeur22],[date3, valeur23],[date1, valeur31],[date2, valeur32],[date3, valeur33]]}]

Je ne sait pas si j'ai le droit de poster ce lien :
https://stackoverflow.com/questions/...sql-highcharts
C'est le seul endroit avec un problème similaire, mais je ne comprends pas le code pour l'appliquer à mon utilisation...

Ma base de données 'database' est composé d'une table 'devicestatus' qui contient les informations propre aux compteurs 'dev_id', 'dev_name', ... et une seconde table 'meter' qui contient les relevés des compteurs ('meter_devlog_id' qui est la clé commune avec le dev_id, meter_logdate, meter_value).

Espérant être clair, si une âme charitable veux bien me guider...
Merci.

**CosmoKnacki** · 15/02/2021, 19h45

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
<?php
// on sort la requête préparée de la boucle (c'est tout l'intérêt de la préparation, on exécute la 
// même requête mais avec des paramètres différents.
$stmt = $pdo->prepare("
SELECT 
    devicestatus.dev_id,
    devicestatus.dev_name,
    UNIX_TIMESTAMP(CONVERT_TZ(D0.V_CONSO_meter_logdate, '+00:00', @@session.time_zone)) * 1000 as meter_unix_logdate,
    D0.V_CONSO_meter_value - D1.Index_PD as conso_day
FROM devicestatus
LEFT JOIN V_CONSO_DAY D0 
    ON D0.V_CONSO_meter_devlog_id = devicestatus.dev_id
LEFT JOIN
    (
        SELECT 
            V_CONSO_meter_devlog_id,
            V_CONSO_meter_logdate,
            V_CONSO_meter_value as Index_PD
        FROM V_CONSO_DAY
    ) D1
    ON D1.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
    AND D1.V_CONSO_meter_logdate = (
        SELECT max(D2.V_CONSO_meter_logdate)
        FROM V_CONSO_DAY D2
        WHERE D2.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
        AND D2.V_CONSO_meter_logdate < D0.V_CONSO_meter_logdate
    )
WHERE D0.V_CONSO_meter_devlog_id = :used_dev_id
  AND D0.V_CONSO_meter_logdate > (NOW() - INTERVAL 7 DAY) 
"); // On utilise un placeholder ----^ (ici :used_dev_id) ...
 
foreach ($used_devices as $used_device):
 
    $stmt->execute([ ':used_dev_id' => $used_device['dev_id'] ]); // ... qu'on renseigne là.
    $datas = [];
 
    while ( $row = $stmt->fetch(PDO::FETCH_ASSOC) ):
        // extract($row); Éviter extract(), c'est une mauvaise pratique qui risque
        // d'écraser silencieusement toutes les variables qui auraient le même nom.
        $datas[] = [$row['meter_unix_logdate'], (int)$row['conso_day']];
    endwhile;
 
    $series[] = [
        'name' => $used_device['dev_id'],
        'data' => $datas
    ];
 
endforeach;
 
echo json_encode($series, JSON_NUMERIC_CHECK);
?>

Je suis persuadé qu'il est possible de parvenir au même résultat uniquement avec MySQL (si ça peut te donner des idées).

**makimax** · 15/02/2021, 21h38

Oh my god !, ca paraît tellement si simple ! respect et robustesse car ce n'est plus de l'aide c'est carrément servi sur un plateau.

Le plus cool c'est que je comprends en grande partie (et je ne suis pas du métier) grâce à vos commentaires.

j'ai rajouté le début :

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
<?php
// Inclure le fichier de connexion à la base
include_once 'database.php';
 
// connection à la base
$pdo = pdo_connect_mysql();
 
// Lancer la requête pour récupérer les devices utilisés
$request_used_devices = $pdo->prepare("SELECT * FROM devicestatus WHERE dev_used = 1 ORDER BY dev_id");
$request_used_devices->execute();
$used_devices = $request_used_devices->fetchAll(PDO::FETCH_ASSOC);
 
// On prépare la requête qui servira à récupérer les consommations pour chaque used_devices dans la boucle qui suit 
// on sort la requête préparée de la boucle (c'est tout l'intérêt de la préparation, on exécute la 
// même requête mais avec des paramètres différents.
$stmt = $pdo->prepare("
SELECT 
    devicestatus.dev_id,
    devicestatus.dev_name,
    UNIX_TIMESTAMP(CONVERT_TZ(D0.V_CONSO_meter_logdate, '+00:00', @@session.time_zone)) * 1000 as meter_unix_logdate,
    D0.V_CONSO_meter_value - D1.Index_PD as conso_day
FROM devicestatus
LEFT JOIN V_CONSO_DAY D0 
    ON D0.V_CONSO_meter_devlog_id = devicestatus.dev_id
LEFT JOIN
    (
        SELECT 
            V_CONSO_meter_devlog_id,
            V_CONSO_meter_logdate,
            V_CONSO_meter_value as Index_PD
        FROM V_CONSO_DAY
    ) D1
    ON D1.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
    AND D1.V_CONSO_meter_logdate = (
        SELECT max(D2.V_CONSO_meter_logdate)
        FROM V_CONSO_DAY D2
        WHERE D2.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
        AND D2.V_CONSO_meter_logdate < D0.V_CONSO_meter_logdate
    )
WHERE D0.V_CONSO_meter_devlog_id = :used_dev_id
  AND D0.V_CONSO_meter_logdate > (NOW() - INTERVAL 7 DAY) 
"); // On utilise un placeholder ----^ (ici :used_dev_id) ...
 
foreach ($used_devices as $used_device):
 
    $stmt->execute([ ':used_dev_id' => $used_device['dev_id'] ]); // ... qu'on renseigne là.
    $datas = [];
 
	// On transfert les données de date et consommation de chaque ligne de la réponse
	// du used_device concerné dans un array.
    while ( $row = $stmt->fetch(PDO::FETCH_ASSOC) ):
        $datas[] = [$row['meter_unix_logdate'], (int)$row['conso_day']];
        	// extract($row); Éviter extract(), c'est une mauvaise pratique qui risque
        	// d'écraser silencieusement toutes les variables qui auraient le même nom.
    endwhile;
 
 	// Transfert de la série du used_device 
    $series[] = [
        'name' => $used_device['dev_name'],
        'data' => $datas
    ];
 
endforeach;
 
echo json_encode($series, JSON_NUMERIC_CHECK);
?>

Du coup pas besoin de définir les array, ni de les purger ?

Code :

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

1
2
3
$datas = array();
$names = array();
$series = array();

J'ai du mal à saisir le "placeholder", c'est une variable éphémère qui existe seulement le temps de la requête et du foreach alors ?

Merci pour l'idée de le faire via mySQL, je vais jeter un coup d'oeil de suite !

Question bête mais on est d'accord que je dois bien refaire la connexion avec $pdo = pdo_connect_mysql(); même si elle est faite dans la page qui appelle le graphique. Ce graphique appelant le fichier data.php. Peut être que je devrais remplacer le fichier et mettre tout le code dans la même page.

J'ai fait mon premier site qui, à chaque page inclus un header.php, qui lui même à un navbar avec la liste des used_devices obtenu par une connexion et requête.
Ce qui veut dire qu'à chaque changement de page, ceci est répété et semble être assez lourd. J'imagine que ce n'est pas bien fait. Mais c'est du hors sujet.

**CosmoKnacki** · 15/02/2021, 22h37

Envoyé par makimax

Du coup pas besoin de définir les array, ni de les purger ?

Code :

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

1
2
3
$datas = array();
$names = array();
$series = array();

$datas est initialisé dans la boucle, et donc réinitialisé à chaque tour ($datas = []; c'est la même chose que $datas = array();).
$names ne sert plus.
Par contre, $series doit toujours être définie.

J'ai du mal à saisir le "placeholder", c'est une variable éphémère qui existe seulement le temps de la requête et du foreach alors ?

Le "placeholder" n'est pas une variable, c'est juste l'emplacement d'une valeur dans la requête (comme une croix sur un plan). D'ailleurs on le voit bien dans le paramètre passé à la méthode PDOStatement::execute() où on associe le nom du placeholder à une valeur via un tableau associatif.
Le fait de préparer une requête permet de la paramétrer. Ce que j'appelle "placeholder" marque l'emplacement du paramètre dans la requête en lui donnant un nom par la même occasion pour le script (on peut également utiliser des "placeholder"s sous forme de point d'interrogation mais c'est moins parlant). L'intérêt de la manœuvre, au delà du fait qu'on peut paramétrer une requête, est que dés l'exécution de la méthode PDO::prepare() la requête est envoyée au serveur MySQL qui va dés lors préparer son plan d'exécution (la manière dont il va s'y prendre pour répondre efficacement à ce qu'on lui demande) et attendre qu'on lui envoie des paramètres pour renvoyer les résultats correspondants. Avec la "préparation", la requête n'est envoyée qu'une seule fois au serveur MySQL, puis les paramètres, sans "préparation", il faut à chaque fois renvoyer le tout.
Cette méthode a aussi l'avantage qu'il n'y a plus à se soucier de quoter les valeurs ou d'échapper des caractères problématiques, c'est automatique.
La durée de vie de cette requête préparée ne dépend pas du foreach, mais de celle de $stmt, rien n'empêche d'utiliser la méthode PDOStatement::execute() pour cette variable après la boucle. Si la valeur de cette variable est remplacée ou si elle est collectée par le ramasse-miettes (garbage collector) parce quelle n'est plus utile dans la suite du script, j'imagine que soit le serveur MySQL en est avisé par php, soit celui-ci gère ça tout seul comme un grand, voire un mix des deux.

Question bête mais on est d'accord que je dois bien refaire la connexion avec $pdo = pdo_connect_mysql(); même si elle est faite dans la page qui appelle le graphique. Ce graphique appelant le fichier data.php. Peut être que je devrais remplacer le fichier et mettre tout le code dans la même page.

Une fois par script php en prenant en compte les includes.

Regrouper données mySQL pour graphique multi séries

PHP & Base de données

Discussions similaires

Partager

Partager