Données entre colonnes de différentes tables (PHP /MySQL)

Invité · 02/05/2023, 13h33

Bonjour.

Je sollicite votre aide au sujet d'une opération que je souhaiterais faire, concernant deux tables (operateurs, taches) d'une même BDD MySQL.

Chacune contient une colonne charge_hebdo.

taches.charge_hebdo est le résultat de l'addition de trois autres colonnes de cette même table : elle est ainsi auto-générée.

AInsi operateurs.charge_hebdo doit avoir, de façon automatique, le même résultat que taches.charge_hebdo, pour un même id représentant donc un même opérateur sur les deux tables. Seule la charge_hebdo change selon ces différents opérateurs.

Vu que j'affiche un tableau en PHP qui reprend toutes ces données, selon vous, comment puis-je procéder, pour recevoir automatiquement le résultat de taches.charge_hebdo dans ma colonne operateurs.charge_hebdo ?

Pour information, elles ne peuvent pas être placées dans la même table.

Pour plus d'informations, voici le code :
tableau pour la table operateurs

Code html :

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
<!-- Création de la vue du tableau -->
                        <div class="card-body">
                            <table id="table1" class="table table-bordered table-striped mx-auto text-center">
                                <thead>
                                    <tr>
                                        <th class="text-center">ID</th>
                                        <th class="text-center">Identité</th>
                                        (...)
                                        <th class="text-center">Charge hebdomadaire</th>
                                        <th></th>
                                        <th></th>
                                    </tr>
                                </thead>
 
                                <tbody>
                                    <?php
                                    // On récupère les données contenues dans la BDD
                                        $query = "SELECT * FROM operateurs ORDER BY id ASC";
                                        $statement = $conn->prepare($query);
                                        $statement->execute();
                                        $statement->setFetchMode(PDO::FETCH_OBJ);
                                        $result = $statement->fetchAll();
 
                                        if($result) {
                                            foreach($result as $row) {
                                    ?>
                                            <!-- On affiche les résultats BDD dans la table -->
                                            <tr>
                                                <td><?= $row->id; ?></td>
                                                <td><?= $row->operateurs_identite; ?></td>
                                                (...)
                                                <td><?= $row->charge_hebdo; ?></td>
 
                                                <td>
                                                    <!-- Bouton de MODIFICATION -->
                                                    <a href="operateur_edit.php?id=<?= $row->id; ?>" class="btn btn-warning rounded-pill"><i class="fa fa-edit"></i>Modifier</a>
                                                </td>
                                                <td>
                                                    <!-- Bouton de SUPPRESSION -->
                                                    <form action="code.php" method="POST">
                                                        <button type="submit" name="delete_operateur" value="<?=$row->id;?>" class="btn btn-danger rounded-pill"><i class="fa fa-trash"></i>Supprimer</button>
                                                    </form>
                                                </td>
                                            </tr>
                                    <?php
                                            }
                                        }
                                            else {     
                                    ?>
                                            <tr>
                                                <td colspan="3">Aucune donnée trouvée</td>
                                            </tr>
                                    <?php
                                        }
                                    ?>
                                </tbody>
                            </table>
                        </div>

tableau pour la table taches

Code html :

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
<!-- Création de la vue du tableau -->
                        <div class="card-body">
                            <table id="table1" class="table table-bordered table-striped mx-auto text-center">
                                <thead>
                                    <tr>
<th class="text-center"> ID </th>
<th class="text-center"> Identité de l'opérateur </th>
(...)
<th class="text-center"> Charge hebdomadaire </th>
                                        (...)
                                        <th class="w-50"></th>
                                        <th class="w-50"></th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <!-- Récupération des données à insérer dans le tableau -->
                                    <?php
                                        $query = "SELECT * FROM taches";
                                        $statement = $conn->prepare($query);
                                        $statement->execute();
 
                                        $statement->setFetchMode(PDO::FETCH_OBJ);
                                        $result = $statement->fetchAll();
 
                                        if($result) {
                                            foreach($result as $row) {
                                    ?>
                                            <!-- On affiche les résultats BDD dans la table -->
                                            <tr>
<td><?= $row->id; ?></td>
                                                <td><?= $row->identite_employe; ?></td>
(...)
 
<td><?= $row->charge_hebdo; ?></td>
 
                                                <td>
                                                    <!-- Bouton de MODIFICATION -->
                                                    <a href="tache_edit.php?id=<?= $row->id; ?>" class="btn btn-warning rounded-pill"><i class="fa fa-edit"></i>Modifier</a>
                                                </td>
                                                <td>
                                                    <!-- Bouton de SUPPRESSION -->
                                                    <form action="code.php" method="POST">
                                                        <button type="submit" name="delete_tache" value="<?=$row->id;?>" class="btn btn-danger rounded-pill"><i class="fa fa-trash"></i>Supprimer</button>
                                                    </form>
                                                </td>
                                            </tr>
                                    <?php
                                            }
                                        }
                                            else {     
                                    ?>
                                            <tr>
                                                <td colspan="3">Aucune donnée trouvée</td>
                                            </tr>
                                    <?php
                                        }
                                    ?>
                                </tbody>
                            </table>
                        </div>

Merci d'avance.

**Séb.** · 02/05/2023, 14h01

taches.charge_hebdo est le résultat de l'addition de trois autres colonnes de cette même table : elle est ainsi auto-générée.

Je n'aime pas particulièrement les colonnes autogénérées, mais soit.

AInsi operateurs.charge_hebdo doit avoir, de façon automatique, le même résultat que taches.charge_hebdo, pour un même id représentant donc un même opérateur sur les deux tables.
Seule la charge_hebdo change selon ces différents opérateurs.

Il ne faut pas dupliquer les données. C'est le meilleur moyen d'avoir des incohérences.

Si les valeurs sont identiques, et doivent le demeurer, alors 1 seule colonne doit être utilisée.
Fais une jointure pour récupérer taches.charge_hebdo au besoin.

comment puis-je procéder, pour recevoir automatiquement le résultat de taches.charge_hebdo dans ma colonne operateurs.charge_hebdo ?

Donne ton schéma, donne ton SQL actuel, donne le résultat attendu, autrement c'est difficile d'être plus précis.

Invité · 02/05/2023, 15h29

Pour l'heure, ce sont des données horaires fictives, surtout pour la charge_hebdo qui devra être comme suit : taches.charge_hebdo == operateurs.charge_hebdo

Voici le code de la table taches :

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
19
20
21
22
CREATE TABLE `taches` (
  `id` int(4) NOT NULL,
  `identite_employe` varchar(100) NOT NULL,
  `charge_s_fab` time NOT NULL,
  `charge_s_rect` time NOT NULL,
  `charge_s_mep` time NOT NULL,
  `charge_hebdo` time GENERATED ALWAYS AS (`charge_s_fab` + `charge_s_rect` + `charge_s_mep`) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
--
 
INSERT INTO `taches` (`id`, `identite_employe`, `charge_s_fab`, `charge_s_rect`, `charge_s_mep`) VALUES
(1, 'Homer', '11:06:06', '01:18:09', '08:07:08'),
(2, 'Marge', '08:06:57', '02:18:06', '14:08:26');
 
--
ALTER TABLE `taches`
  ADD PRIMARY KEY (`id`);
 
--
ALTER TABLE `taches`
  MODIFY `id` int(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

Et celui de la table operateurs :

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
19
20
21
22
CREATE TABLE `operateurs` (
  `id` int(4) NOT NULL,
  `operateurs_identite` varchar(100) NOT NULL,
  `semaine` int(2) NOT NULL,
  `charge_hebdo` time NOT NULL,
  `dispo_hebdo` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 
--
 
INSERT INTO `operateurs` (`id`, `operateurs_identite`, `semaine`, `charge_hebdo`, `dispo_hebdo`) VALUES
(1, 'Homer', 2, '07:07:17', '33:07:10'),
(2, 'Marge', 1, '05:07:57', '15:17:57');
 
--
ALTER TABLE `operateurs`
  ADD PRIMARY KEY (`id`);
 
--
ALTER TABLE `operateurs`
  MODIFY `id` int(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

D'après toi, est-ce possible, dans mon code PHP - structuré en MVC -, d'inclure une jointure entre ces deux tables, afin de générer automatiquement le résultat de charge_hebdo dans la table operateurs ?

**Séb.** · 02/05/2023, 18h02

1. Tu donnes le DDL et un échantillon, merci

Quelques commentaires :

Tu as 2 colonnes TEXT "identite_employe" et "operateurs_identite", c'est contreperformant et sujet à anomalies.
Il te faut une table de référence employés/utilisateurs et des clefs primaires/étrangères numériques assurant performance et intégrité.

`semaine` int(2) NOT NULL,

Si ceci représente un numéro de semaine et que tu comptes la coder sur 2 chiffres alors c'est insuffisant (et l'usage d'une longueur pour un INT, ici le "(2)", est déprécié)
Pour une semaine il faut YYYYWW, soit 6 chiffres => C'est le format d'un YEARWEEK(...) par exemple
Autrement tu ne passeras pas 1 année d'activité

2. Par contre tu ne donnes pas le résultat attendu

Et cette histoire de charge_hebdo reportée n'est pas claire pour moi.

Alors oui, bien sûr, tu pourrais lancer des calculs régulièrement, ou dès que ta table des tâches est modifiée, mais ce n'est sûrement pas une bonne solution.

Tu parles de charge_*hebdo*, mais que se passe-t-il une fois la semaine passée ? Tu repars à zéro ?
Tu ne gardes que la charge de la semaine courante ? Précédente ? Tu fais une moyenne ?
Sans exemple de ce que tu souhaites obtenir c'est difficile de te répondre.
De plus attention de ne pas mélanger les problématiques PHP/MVC et SQL / Modélisation, ça n'a rien à voir !

Si l'objectif est de distribuer des tâches avec un temps de travail estimé ou réalisé
et ensuite avoir un rapport par utilisateur de la charge de travail hebdomadaire
alors ceci suffit :

user (§id, name)
task (§id, finished_at DATE, workload TIME, #user_id(user.id))

task.finished_at est la date à laquelle la tâche est réalisée
task.workload est le temps de travail de réalisation de la tâche

Connaître le nombre d'heures par semaine par utilisateur :

Code SQL :

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

1
2
3
4
SELECT ALL u.name, YEARWEEK(t.finished_at, 3) AS period, SUM(t.workload) AS workload_sum
FROM user AS u
INNER JOIN task AS t ON u.id = t.user_id
GROUP BY 1, 2

Avec des CTE pour illustrer l'exemple :

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
19
20
WITH
	user (id, name) AS (
		VALUES
			ROW (10, 'Gaëlle'),
			ROW (20, 'Franck')
	),
	task (id, finished_at, workload, user_id) AS (
		VALUES
			ROW (1, DATE'2023-05-02', TIME'02:00:00', 20),
			ROW (2, DATE'2023-04-25', TIME'01:30:00', 20),
			ROW (3, DATE'2023-04-29', TIME'00:50:00', 10),
			ROW (4, DATE'2023-04-30', TIME'01:00:00', 20),
			ROW (5, DATE'2023-04-28', TIME'00:35:00', 10)
	)
SELECT ALL
	u.name, YEARWEEK(t.finished_at, 3) AS period,
	SEC_TO_TIME(SUM(TIME_TO_SEC(workload))) AS workload_sum
FROM task AS t
INNER JOIN user AS u ON t.user_id = u.id
GROUP BY 1, 2;

Donne :

Code :

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

1
2
3
4
name	period	workload_sum
Gaëlle	202317	01:25:00
Franck	202318	02:00:00
Franck	202317	02:30:00

Voilà, on calcule la charge de travail hebdomadaire à la réalisation à la volée. Libre à toi de placer cette requête dans une vue (CREATE VIEW), tu auras ainsi une ~table dynamique ne nécessitant pas d'entretien et sans risque d'incohérences, qu'il te suffira d'interroger pour sortir des rapports avec PHP.

Invité · 03/05/2023, 16h00

je trouve cette requête plutôt intéressante dans le cadre de mon projet mais ne comprends pas encore comment je puis l'adapter mon code et où la placer (modèle ?) pour que la charge_hebdo soit, in fine, automatiquement implémentée.

Invité · 17/05/2023, 11h58

Bonjour à tous.

Je n'ai toujours pas réussi à passer les valeurs de la colonne charge_hebdo (table taches) vers la colonne charge_hebdo (table operateurs) : y'a-t-il quelqu'un qui puisse m'aider à terminer cette quête ?!

Merci d'avance.

Données entre colonnes de différentes tables (PHP /MySQL)

PHP & Base de données

Vue hybride

Discussions similaires

Partager

Partager