[Résolu] [HELP] Connexions entre tables ingérables

Question

crying_psyco

19 Dec 2013 à 10:09

Bonjour à tous!

J'ai grandement besoin d'aide et surtout de cerveaux affûtés. Je me suis récemment lancé dans php/mysql pour les besoins de ma société. Dans le projet en cours de réalisation, voici mon problème:
l'utilisateur peut créer un nouveau projet (nommé ici PX). Quand on consulte PX (localisation etc...) on peut aussi connaitre les "clients" en relation avec le projet. C'est là que ça se corse...
Sur PX, il peut y avoir un client à l'origine, puis d'autres qui viennent se greffer au fur et à mesure, que l'on doit donc pouvoir rajouter et, par extension, consulter leur propre fiche, disponible dans la base de donnée.
J'avoue que la gymnastique me démonte le cerveau. Si quelqu'un peut m'aider à trouver une solution propre pour ma BDD, ce serait un énorme soulagement.
Merci par avance!
P.S. je vous joins un petit diagramme qui sera peut-être plus compréhensible pour les personnes visuelles Smiley smile

anima

19 Dec 2013 à 10:25

Salut,

Tu sépares tes identités beaucoup trop. Un projet, que ce soit projet X, projet Y, projet Z, projet ABCDEF, reste un projet, avec ses définitions propres à lui, mais partagant une structure de base (qu'on va s'amuser à explorer ensemble). Il me semble nécessaire donc d'en faire une entité bien concrète dans ta BDD.

De même, tes clients sont, comme tu l'as dit, joints aux projets, et peuvent sûrement faire partie de plusieurs projets. D'où une 2e entité bien séparée.
La relation entre les clients et les projets est sûrement la suivante:
- Un client peut faire partie de plusieurs projets
- Un projet peut avoir plusieurs clients

Il convient donc d'utiliser une table de pivot pour le lien entre les deux. Soit quelque chose comme ceci:

J'ai rajouté une table de config pour les projets. Le code complet sur MySQL5.x pour générer le set de tables:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`projets`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`projets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`clients`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`clients` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`projets_clients`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`projets_clients` (
  `idRelation` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `idProjet` INT UNSIGNED NULL,
  `idClient` INT UNSIGNED NULL,
  PRIMARY KEY (`idRelation`),
  INDEX `c_link_idx` (`idClient` ASC),
  INDEX `p_link_idx` (`idProjet` ASC),
  CONSTRAINT `c_link`
    FOREIGN KEY (`idClient`)
    REFERENCES `mydb`.`clients` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `p_link`
    FOREIGN KEY (`idProjet`)
    REFERENCES `mydb`.`projets` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`projets_config`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`projets_config` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `idProjet` INT UNSIGNED NULL,
  `key` VARCHAR(45) NULL,
  `value` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `c_p_link_projet_idx` (`idProjet` ASC),
  CONSTRAINT `c_p_link_projet`
    FOREIGN KEY (`idProjet`)
    REFERENCES `mydb`.`projets` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Si quelque chose n'est pas clair, n'hésite pas à me demander des renseignements supplémentaires! Smiley smile

crying_psyco

19 Dec 2013 à 10:40

Pour détailler, actuellement ma BDD est constituée effectivement d'une table client et une table projet (en gros, je les ai allégées en scindant certaines infos)...

Après, l'idée d'une table pivot je n'y avait pas pensé mais je dois t'avouer que j'ai un peu de mal à comprendre sa fonction pure ainsi que ta syntaxe MySQL concernant cette table. Il en est de même pour la table config, pour laquelle je ne comprends pas trop sa fonction.

J'ai encore beaucoup de mal pour la logique développement, je te prie de m'excuser mais en tout cas, un GRAND MERCI pour cette piste Smiley smile

anima

19 Dec 2013 à 10:51

crying_psyco a écrit :
Pour détailler, actuellement ma BDD est constituée effectivement d'une table client et une table projet (en gros, je les ai allégées en scindant certaines infos)...

Après, l'idée d'une table pivot je n'y avait pas pensé mais je dois t'avouer que j'ai un peu de mal à comprendre sa fonction pure ainsi que ta syntaxe MySQL concernant cette table. Il en est de même pour la table config, pour laquelle je ne comprends pas trop sa fonction.

J'ai encore beaucoup de mal pour la logique développement, je te prie de m'excuser mais en tout cas, un GRAND MERCI pour cette piste

Une table pivot permet de faire une jointure indirecte entre deux types d'entités dans un schéma. Elle est utilisée 100% dans un seul cas: quand deux entités peuvent appartenir à un nombre n>1 d'autres entités (ici, un projet peut avoir plusieurs clients - d'ou le n>1 client->projet - et un client peut faire partie de plusieurs projets - d'ou le n>1 projet->client).

L'idée est simple. Pour faire la jointure, tu rajoutes une ligne dans ta table pivot avec l'ID du projet et l'ID du client. Du genre, si ton projet A (id=1) a un client B (clientId=1), tu auras une entrée dans ta table de pivot (AUTOINCREMENT_PK, 1, 1). Si d'un seul coup ton client C arrive sur le projet, tu auras aussi (AUTOINCREMENT_PK, 1, 2).

La structure de table a deux clés de type FOREIGN KEY sur la table de pivot. Ceci te permet d'éviter des erreurs au niveau de l'entrée d'IDs, te permet aussi de supprimer automatiquement les lignes lors du suppression de projet ou de client (désactivé dans mon schéma), et te permet enfin d'indexer les champs (= requètes rapides sur SELECT d'ID, idClient ou idProjet).

La table de config est 100% optionnelle. Elle t'aurait permis de stocker des infos sur chaque projet de façon plus ou moins variable.

crying_psyco

19 Dec 2013 à 11:29

D'accord!
Une dernière chose me chiffone... Dans tes requête MySQL, tu contraints 'c_link' et 'p_link'. Tu évoques aussi 'c_link_ipx' et 'p_link_ipx'. A quoi correspondent ces valeurs? Je n'arrive pas à saisir. Smiley sweatdrop

Autrement, tes explications sont claires. Je t'en remercie!

anima

19 Dec 2013 à 12:01

crying_psyco a écrit :
D'accord!
Une dernière chose me chiffone... Dans tes requête MySQL, tu contraints 'c_link' et 'p_link'. Tu évoques aussi 'c_link_ipx' et 'p_link_ipx'. A quoi correspondent ces valeurs? Je n'arrive pas à saisir.
Autrement, tes explications sont claires. Je t'en remercie!

Ce sont justement les FOREIGN KEYs! Je contrains idProjet et idClient dans la table de pivot, et je rend nécessaire l'existence de ces valeurs dans les tables clients et projets. C'est tout! Ca me permet de garantir l'état complet et sain de toutes mes données Smiley smile

crying_psyco

19 Dec 2013 à 12:07

Oh mon dieu je me sens bête x) ça coule de source mais ayant une logique très... différente, je doutais horriblement, étant donné que les termes ne sont pas exactement les même. Je pense avoir compris du coup.
Je vais tenter de lui faire interpréter les bonnes choses côté formulaire php et requêtes. Je sens que ça va être épique Smiley smile

Je reviendrais vers le post pour t'informer de l'évolution de la chose. En tout cas, merci d'avoir pu me permettre d'avancer dans mon projet! (et bonnes fêtes si je suis trop lent à développer tout ça Smiley langue

)

crying_psyco

20 Dec 2013 à 10:19

Erreur SQL !INSERT INTO projets_clients(relationID, projetID, clientID) VALUES ('', LAST_INSERT_ID(), '3')
Cannot add or update a child row: a foreign key constraint fails (`manage_airx`.`projets_clients`, CONSTRAINT `p_link` FOREIGN KEY (`projetID`) REFERENCES `projet` (`projetID`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Il n'est vraiment pas gentil avec moi...
Bonjour! Suite à mon avancée sur le formulaire, lors de l'enregistrement d'un projet, je reçois le message d'erreur ci-dessus.
Pour être précis, j'ai cherché à avoir des tables lights et sans redondances et donc les infos sont découpées. Pour l'enregistrement sur chaque table, je récupère l'ID "attribuée" à mon projet par LAST_INSERT_ID(). Mais là, à priori, la contrainte que tu as proposé pour la table pivot bloque un peu on dirait Smiley ohwell

Aurais-je raté quelque chose? Smiley sweatdrop

EDIT: Bon, en fait si j'ai bien compris mon erreur, je tente d'appeler un id inséré précédemment alors que sur cette même ligne, on en crée un, forcément ça fait désordre, j'ai légèrement modifié le code mais je ne suis pas encore arrivé à tout faire fonctionner.

EDIT2: Finalement, je me suis excité pour faire rentré cet ID dans une variable, et ça a fonctionné une fois le bon code inséré ^^" Smiley biggrin

(mysql_insert_id())
Modifié par crying_psyco (20 Dec 2013 - 12:07)

crying_psyco

20 Dec 2013 à 10:30

Je joins le code (qui pour le moment, ne permet pas encore la récupération multiple pour les champs SELECT)

Le formulaire PHP

<?php
/*
* Page création nouveau projet
*/

// connexion à MySQL

$sql_be = "SELECT Type, contact.clientID, raison_sociale, nom FROM type, contact WHERE contact.clientID = type.clientID AND Type = 'BE'";

?>
<!DOCTYPE html>
<html> 
	<head>
		<title>AirXpert Manager</title>
		<link rel="stulesheet" href="http://localhost/api_crm/css/reset.css">
		<link rel="stylesheet" href="http://localhost/api_crm/css/style-crea.css">
		<script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.min.js"></script> 
	</head>
	<body>
		<h1>Nouveau projet</h1>
		<form id="crea_projet" action="http://localhost/api_crm/fonctions/register-projet.php" method="post">
			<label for="be">Bureau(x) d'&eacute;tude(s) li&eacute;(s) au projet</label><br>
			<select id="be" name="be">
				<option value="0" multiple>-- Choisissez --</option>
				<?php
					$res_be = mysql_query($sql_be) or die('Erreur SQL !'.$sql_be.'<br>'.mysql_error());
					while( $post = mysql_fetch_object( $res_be ))
					{
						?>
						<option value="<?php echo($post->clientID); ?>"><?php echo($post->raison_sociale); ?>&nbsp;-&nbsp;<?php echo($post->nom); ?></option>
						<?php
					}
				?>
			</select><br>
			<label for="archi">Architecte li&eacute; au projet</label><br>
			<select id="archi" name="archi">
				<option value="0">-- Choisissez --</option>
				<?php
					$sql = "SELECT Type, contact.clientID, raison_sociale, nom FROM type, contact WHERE contact.clientID = type.clientID AND Type = 'Archi'";
					$res = mysql_query($sql) or die('Erreur SQL !'.$sql.'<br>'.mysql_error());
					while( $post = mysql_fetch_object( $res ))
					{
						?>
						<option value="<?php echo($post->clientID); ?>"><?php echo($post->raison_sociale); ?>&nbsp;-&nbsp;<?php echo($post->nom); ?></option>
						<?php
					}
				?>
			</select><br>
			<label for="installateur">Installateur li&eacute; au projet</label><br>
			<select id="installateur" name="installateur" multiple>
				<option value="0">-- Choisissez --</option>
				<?php
					$sql = "SELECT Type, contact.clientID, raison_sociale, nom FROM type, contact WHERE contact.clientID = type.clientID AND Type = 'Installateur'";
					$res = mysql_query($sql) or die('Erreur SQL !'.$sql.'<br>'.mysql_error());
					while( $post = mysql_fetch_object( $res ))
					{
						?>
						<option value="<?php echo($post->clientID); ?>"><?php echo($post->raison_sociale); ?>&nbsp;-&nbsp;<?php echo($post->nom); ?></option>
						<?php
					}
				?>
			</select><br>
			<label for="nom_projet">Nom du projet</label><br><input type="text" name="nom_projet" id="nom_projet" value=""><br>
			<label for="adresse_projet">Adresse du projet</label><br><input type="text" name="adresse_projet" id="adresse_projet" value=""><br>
			<label for="zipcode_projet">Code postale</label><br><input type="number" name="zipcode_projet" id="zipcode_projet" value="" max="99999"><br>
			<label for="ville_projet">Ville</label><br><input type="text" name="ville_projet" id="ville_projet" value=""><br>
			<label for="observations_projet">Observations</label><br><textarea rows="10" cols="50" id="observations_projet" name="observation_projet"></textarea><br>
			<label for="correspondances_projet">Correspondances</label><br><textarea rows="10" cols="50" id="correspondances_projet" name="correspondances_projet"></textarea><br>
			<input type="submit" value="Enregistrer">
		</form>
		<div id="results_be">
		</div>
	</body>
</html>

et le fichier PHP pour la récupération

<?php

// On commence par récupérer les champs 
if(isset($_POST['be']))      $be=$_POST['be'];
else      $be="";
if(isset($_POST['archi']))      $archi=$_POST['archi'];
else      $archi="";
if(isset($_POST['installateur']))      $installateur=$_POST['installateur'];
else      $installateur="";
if(isset($_POST['nom_projet']))      $nom_projet=$_POST['nom_projet'];
else      $nom_projet="";
if(isset($_POST['adresse_projet']))      $adresse_projet=$_POST['adresse_projet'];
else      $adresse_projet="";
if(isset($_POST['zipcode_projet']))      $zipcode_projet=$_POST['zipcode_projet'];
else      $zipcode_projet="";
if(isset($_POST['ville_projet']))      $ville_projet=$_POST['ville_projet'];
else      $ville_projet="";
if(isset($_POST['observation_projet']))      $observation_projet=$_POST['observation_projet'];
else      $observation_projet="";
if(isset($_POST['correspondances_projet']))      $correspondances_projet=$_POST['correspondances_projet'];
else      $correspondances_projet="";

// On vérifie si les champs sont vides 
if(empty($be) OR empty($archi) OR empty($installateur) OR empty($nom_projet) OR empty($adresse_projet) OR empty($zipcode_projet) OR empty($ville_projet)) 
    { 
    echo '<font color="red">Attention, seuls les champs <b>observations et correspondances/b> peuvent rester vide !</font><br>'; 
	include ('newprojet.php');
    } 
	
// Aucun champs n'est vide, on enregistre
else 
	{
		// connexion à MySQL
		
		//Controle des doublons
		$sql = "SELECT nom FROM projet WHERE `nom` = '" . $_POST['nom_projet']."'";
		$req = mysql_query($sql) or die('Erreur SQL !'.$sql.'<br>'.mysql_error());
		// on compte le nombre de résultats 
		$res = mysql_num_rows($req);
	
		if($res!=0)  // l'url existe déjà, on affiche un message d'erreur 
			{ 
			echo '<font color="red">D&eacute;sol&eacute;, mais ce projet existe d&eacute;j&agrave; dans notre base.</font><br>';
			include ('newprojet.php');
			} 
		else  // L'url n'existe pas, on insère les informations du formulaire dans la table 
			{
			// Ecriture dans la base
			// ci-dessous on insère les infos dans PROJET
			$sql = "INSERT INTO projet(projetID, nom) VALUES('', '$nom_projet')"; 
			mysql_query($sql) or die('Erreur SQL !'.$sql.'<br>'.mysql_error()); 
			// ci-dessous on insère les infos dans PROJETS_CLIENTS
			$sql = "INSERT INTO projets_clients(relationID, projetID, clientID) VALUES ('', LAST_INSERT_ID(), '$be')";
			mysql_query($sql) or die('Erreur SQL !'.$sql.'<br>'.mysql_error());
			// ci-dessous on insère les infos dans PROJETS_CLIENTS
			$sql = "INSERT INTO projets_clients(relationID, projetID, clientID) VALUES ('', LAST_INSERT_ID(), '$archi')";
			mysql_query($sql) or die('Erreur SQL !'.$sql.'<br>'.mysql_error());
			// ci-dessous on insère les infos dans PROJETS_CLIENTS
			$sql = "INSERT INTO projets_clients(relationID, projetID, clientID) VALUES ('', LAST_INSERT_ID(), '$installateur')";
			mysql_query($sql) or die('Erreur SQL !'.$sql.'<br>'.mysql_error());
			// ci-dessous on insère les infos dans PROJET_ADRESSE 
			$sql = "INSERT INTO projet_adresse(projetID, adresse, code_postal, ville) VALUES(LAST_INSERT_ID(), '$adresse_projet', '$zipcode_projet', '$ville_projet')"; 
			mysql_query($sql) or die('Erreur SQL !'.$sql.'<br>'.mysql_error());
			// ci-dessous on insère les infos dans PROJET_OBSERVATION 
			$sql = "INSERT INTO projet_observation(projetID, observation, correspondance) VALUES(LAST_INSERT_ID(), '$observation_projet', '$correspondances_projet')"; 
			mysql_query($sql) or die('Erreur SQL !'.$sql.'<br>'.mysql_error());
			
			echo ('Projet enregistr&eacute; avec succ&egrave;s<br>');
			echo ('<a class="button" href="http://localhost/api_crm/projet/newprojet.php"><input type="button" value="Cr&eacute;er un autre projet" /></a><br>');
			echo ('<a class="button" href="http://localhost/api_crm/home.php"><input type="button" value="retour &agrave; l\'accueil" /></a>');
			
			mysql_close($db);  // on ferme la connexion 
			}
	}

?>

crying_psyco

20 Dec 2013 à 12:06

Bon du coup tout est bon pour mon problème, je passe la chose en résolu! Merci pour l'aide et Bonnes Fêtes!

anima

20 Dec 2013 à 13:20

J'en profite pour te signaler un point important: ton script peut être très facilement victime de ce qu'on appelle dans le jargon "SQL injection" (SQLi en court), et ce, contrairement aux mythes et aux rumeurs, [u]même si magic quotes est on[/u]!

Tu ne fais aucun traitement ou préprocessing sur les valeurs envoyées par un utilisateur. En fait, dans tes requètes, tu utilises direct les valeurs $_POST (après un petit changement de nom de var).

Prenons ta requète:

$sql = "SELECT nom FROM projet WHERE `nom` = '" . $_POST['nom_projet']."'";

Avec un $_POST["nom_de_projet"] = "' OR ''='", ta requète devient:

SELECT nom FROM projet WHERE `nom` = '' OR ''=''

Ce qui sera toujours le cas.

La solution? Plusieurs, en fait:
- PDO ( http://www.php.net/pdo ). La meilleure solution, te permettra de paramétrer tes requètes
- MySQLi. Pareil
- utiliser mysql_real_escape_string. Pas recommandé, surtout que l'extension MySQL est en fin de vie (deprecated)

crying_psyco

20 Dec 2013 à 17:11

Oui, effectivement j'en ai entendu parlé mais je ne me suis pas encore penché sur le sujet, comme il s'agit d'une application à usage interne pour la société pour laquelle je travaille. Mais je vais m'y pencher afin d'avoir tout mon code "propre" une fois les fonctions principales en place Smiley smile

Merci beaucoup pour ton aide!

Sujet clos