Discussion :

[cmascart]

J'ai une table qui contient des fruits, identifiés de 1 à 4
J'ai une autre table des achats, qui référence les fruits, avec contrainte de clef étrangère
Je tente ensuite d'insérer un bloc d'achats de fruits.
Problème, le bloc (de plusieurs centaines d'achats, en réalité) contient une référence d'un fruit inexistant.
Comment trouver, avec PHP et PDO, la clef étrangère incorrecte ?

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
 
DROP DATABASE IF EXISTS fruits;
CREATE DATABASE fruits CHARACTER SET 'utf8';
USE fruits;
CREATE TABLE t_fruits
(
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 nom VARCHAR(15) NOT NULL
)
ENGINE=InnoDB,
COMMENT 'Tous les fruits'
;
 
CREATE TABLE t_achats
(
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 jour DATE NOT NULL,
 fruit INT UNSIGNED NOT NULL,
 montant NUMERIC(8, 2) NOT NULL,
 CONSTRAINT achatFruit FOREIGN KEY(fruit) REFERENCES t_fruits(id) ON DELETE CASCADE
)
ENGINE=InnoDB,
COMMENT 'Achats de fruits'
;
 
SHOW TABLES;
 
INSERT INTO t_fruits VALUES
(NULL, 'Poire'),
(NULL, 'Pomme'),
(NULL, 'Cerise'),
(NULL, 'Fraise')
;
SELECT * FROM t_fruits;
 
INSERT INTO t_achats VALUES
(NULL, CURRENT_DATE, 1, 15.2),
(NULL, CURRENT_DATE, 2, 20.3),
(NULL, CURRENT_DATE, 3, 10.45),
(NULL, CURRENT_DATE, 5, 10.22), -- Fruit 5 n'existe pas
(NULL, CURRENT_DATE, 4, 17.25),
(NULL, CURRENT_DATE, 1, 8.52)
;
SELECT * FROM t_achats ORDER BY id;
 
SELECT 'Comment obtenir, en PHP avec PDO, la clef problématique (le fruit 5) ?' AS question;

Jusqu'à présent, PHP me permet d'obtenir le code d'erreur 1452, mais pas le fruit 5.
Si je pouvais l'obtenir, ça me permettrait d'éviter de programmer une solution lourde, telle que découper ma requête insert en autant de requêtes qu'elle contient de lignes.
Et tester le résultat après chaque mini-requête d'une ligne.

Car ma requête d'insertion d'achats provient d'une requête Ajax, depuis un client web, qui me balance, en un JSON, un tableau de tous ses achats de fruits.
Je convertis le tableau en une requête SQL, et l'exécute d'un bloc de plusieurs centaines d'achats.
Problème, si le tableau JSON contient un fruit 5 inconnu, toute la requête échoue. Aucun achat ne passe.

Si PDO pouvait me dire que la requête a échoué, à cause du fruit inconnu 5, je pourrais répondre toujours en Ajax, à mon client "fruit 5 inconnu"
Le client omettrait le fruit 5 de sa liste d'achats, et recommencerait la requête Ajax.
Et ce,en boucle, jusqu'à ce que sa liste d'achats ne contienne plus de fruits inconnus, et que mon INSERT multiple réussisse.
Auquel cas, je lui renvoie un code contenant le nombre d'achats insérés sur la BD du serveur.
Si le nombre d'achats insérés sur la BD du serveur correspond au nombre d'achats envoyés, alors, le client peut les effacer, le serveur les a bien reçus.

Sinon, c'est qu'il y a encore eu une erreur, par exemple sur un autre fruit inconnu t_achats.fruit=9
Auquel cas, on recommence la boucle: Fruit inconnu 9
Que le client est prié d'effacer de sa liste, avant de la renvoyer au serveur, etc...

Ma question est comment obtenir, en PHP, grâce à PDO, la valeur de la foreign key qui enfreint la contrainte ?

Merci,
Christian.

[Artemus24]

Salut à tous.

une référence d'un fruit inexistant.

Qu'est-ce que signifie, pour vous, une référence inexistante ?

D'après ce que j'ai compris, un client web va constituer une requête Ajax sous la forme d'un tableau JSON et vous la soumettre.
N'y a-t-il pas un contrôle pour vérifier l'existence de ce fuit N°5 avant de vous le transmettre pour traitement ?
N'y aurait-il pas un problème de mise à jour entre votre base de données et celui du client web ?

J'en déduis que vous n'avez jamais géré ce genre de problème avant d'avoir ce bug.
Comment faisiez-vous avant ?

Vous désirez une réponse interactive à votre demande.
La requête Ajax sera gérée par un script sql qui va insérer dans les tables, vos fruits et vos achats.
Je vois que vous utilisez la contrainte "Foreign Key" pour gérer l'intégrité de vos tables.
C'est bien, ce n'est pas ainsi que l'on gère, par la requête Ajax, les clefs étrangères.

Vous ne devez pas transmettre une clef étrangère, mais la valeur qui y est associée.
Par exemple :
--> "2021-11-15", "Poire", 15.2
--> "2021-11-15", "Pomme", 20.3
--> "2021-11-15", "Cerise", 10.45
--> "2021-11-15", "Banane", 10.22
--> "2021-11-15", "Fraise", 17.25
--> "2021-11-15", "Poire", 8.52

Pour le remplissage de la table des fruits, cela se fera ainsi :

Code :

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

1
2
Insert into Fruit (col_fruit) Values (le libelle de votre fruit)
  on duplicate key update col_fruit = values(`col_fruit`);

Ainsi pas d'erreur car la duplication sera une mise à jour.

Juste après l'insertion, vous devez récupérer l'identifiant, celui qui va vous servir comme clef étrangère.
--> last_insert_id()

Maintenant que tu as la clef étrangère, tu peux l'insérer dans la table achat.

Sinon, pour tester la présence ou l'absence de la valeur d'une clef étrangère dans une table, il suffit de faire ainsi :

Code :

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

select 1 from achat where id={votre clef étrangère à tester}

Si la requête vous retourne "1", la clef étrangère existe.
Si elle vous retourne NULL, la clef étrangère n'existe pas.

Si vous désirez un beau message d'erreur, voici comment faire.
1) Création d'un déclencheur pour analyser votre requête achat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
-- ==============
-- Trigger `test`
-- ==============
 
DROP TRIGGER IF EXISTS `test`;
 
DELIMITER $$
CREATE TRIGGER `test`
BEFORE INSERT ON `achat`
FOR EACH ROW BEGIN
    CALL `validate`(NEW.clef);
END$$
DELIMITER ;

Clef est le nom de la colonne qui sert comme clef étrangère dans votre table achat.
Et voici la procédure stockée :

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
-- ====================
-- Procedure `validate`
-- ====================
 
DELIMITER $$
DROP PROCEDURE IF EXISTS `validate`$$
 
CREATE PROCEDURE `validate` ( IN IN_Clef smallint,
                            )
DETERMINISTIC
NO SQL
BEGIN
  DECLARE msg varchar(255);
  If ((select 1 from achat where clef = IN_Clef) is NULL) THEN
    set msg = concat('Insertion interdite, clef étrangère "', cast(IN_Clef as char), '" inexistante');
    SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = msg, MYSQL_ERRNO = 7777;
  END IF;
END$$
DELIMITER ;

Ce n'est pas testé.
A vous de le mettre en forme selon vos besoin.

Cordialement.
Artemus24.
@+

[cmascart]

Merci Artemus24 pour ta réponse.
En rédigeant cette discussion, je pensais qu'il existait une instruction SQL ou PDO permettant d'obtenir l'Id de la clef étrangère problématique.
Un peu comme PDO délivre le lastInsertId() après une requête d'insert.

Dans l'exemple ci-dessous, $codeSql contient la requête INSERT INTO ... VALUES(...)

Code php :

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

1
2
3
4
5
6
7
8
9
10
11
   $bdd = new PDO('mysql:host=*****;dbname=maBase;charset=utf8', login, password);
   $monQuery = $bdd->prepare($codeSql);
   if($monQuery->execute())
   {
    echo(json_encode([0, $nbHistor, $monQuery->rowcount()])); // Ca marche
   }
   else
   {
    echo(json_encode([2, 0, f_mediaManquant($lesHistor, $bdd)])); // Ca ne marche pas, clef étrangère inconnue
   }
   $monQuery->closecursor();

Ça marche, ou ça ne marche pas, selon le résultat de $monquery->execute().
Si ça ne marche pas, on rend, en troisième item du tableau en réponse, l'Id du média (=le fruit, dans mon exemple simplifié) manquant.
Tel que le rend la fonction suivante:

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
 function f_mediaManquant($pHistor, PDO $pBdd)
 {
  $mediasId = []; // Seuls les Id m intéressent
  foreach($pHistor as $lHistor)
  {
   $mediaId = $lHistor[2]; // l item[2] d un historique (=l achat) est son Id.
   array_push($mediasId, $mediaId); // Je les assemble dans un tableau
  }
  $lesMedias = array_unique($mediasId); // J élimine les doublons, pour ne tester chaque Id qu une seule fois
 
  $leManquant = NULL;
  $testSql = 'SELECT COUNT(id) AS nb FROM t_medias WHERE id = ?';
  $queryTest = $pBdd->prepare($testSql);
  foreach($lesMedias as $leMedia)
  {
   $queryTest->execute([$leMedia]); // Je teste un Id
   $ligne = $queryTest->fetch(PDO::FETCH_ASSOC);
   if($ligne['nb'] == 0)
   {
    $leManquant = $leMedia; // Je ne l ai pas trouvé
   }
  }
  $queryTest->closecursor();
  return($leManquant);
 }

En finale, le script invoqué par la requête Ajax rend une chaîne JSON contenant un tableau [2, 0, 123]
Signifiant [Erreur, 0 éléments insérés, Le média 123 n'existe pas]
Le client émetteur de la requête Ajax reçoit cette chaîne en e.target.responseText, qu'il parse:

Code JavaScript :

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

1
2
3
4
5
6
7
8
9
10
 const
 xhrAchat = new XMLHttpRequest(),
 ecouteurAchat = (e)=>
 {
  const tableau : JSON.parse(e.target.responseText);
  // Traitement...
 }
 ;
 
xhrAchat.addEventListener('load', ecouteurAchat);

Il n'a plus qu'à l'effacer de sa liste

Code :

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

DELETE FROM t_achats WHERE fruit = 123;

Puis, à recommencer sa requête, jusqu'à ce qu'elle marche, ou qu'elle révèle un autre fruit (=le média) inconnu.
Auquel cas, on recommence, jusqu'à ce que la requête ne contienne plus aucun achat qui enfreigne la contrainte d'intégrité sur clef étrangère.

J'ai fait le test, en polluant volontairement la liste d'achats avec des fruits inconnus.
Ça marche. Je reçois autant d'erreurs en boucle que de fruits inconnus différents (peu importe le nombre d'achats).
À chaque itération, le client effectue bien un DELETE, jusqu'à ce que la liste d'achats ne contienne plus de fruit inconnu.
Puis, la dernière itération réussit l'insert.

Donc, pour moi, le problème est résolu.
Merci Artemus24.

[Artemus24]

Salut cmascart.

La résolution de ton problème n'est pas technique.
Il s'agit de trouver une solution fonctionnelle pour gérer les erreurs du client.

Tu as choisi de retourner une chaîne JSON donnant le code erreur, le résultat de l'action et un libellé de cet erreur.
Je suppose que cette chaîne JSON existait déjà mais que tu n'as fait que l'enrichir.

Il s'agit en fait de créer un dialogue entre le client et le serveur.
Dans ce dialogue, tu auras deux parties :
--> ce que le client envoie.
--> la réponse du serveur.

Le client demande au travers d'un code action soit :
--> une interrogation avec une clef
--> une suppression avec une clef
--> une création avec un tableau qui contient le nom de la table, avec les données à insérer.
--> une mise à jour avec un tableau contenant le nom de la table, le nom de la colonne et la donnée à mettre à jour

Le serveur répondra :
--> code retour.
--> libelle du code retour.

Il est inutile d'ajouter d'autres informations en retour du serveur.
Le mieux est de gérer tes données sous la forme d'une grappe.
J'entends par là, gérer l'intégrité de ta base de données en acceptant tout ou en rejetant tout.
Et non de faire des validations partielles qui ne fait que complexifier inutilement le traitement.

Cordialement.
Artemus24.
@+