test de jointure ou intersection entre 3 tables

if ($category == "Utilisateur") $req="(SELECT * FROM prestation WHERE $where ORDER BY $order_by $limit) INTERSECT (SELECT affectation FROM project) INTERSECT (SELECT pseudo FROM inscription where category = '".$category"') "; else $req="SELECT * FROM prestation WHERE $where ORDER BY $order_by $limit";

Structure de la table `inscription` -- CREATE TABLE IF NOT EXISTS `inscription` ( `firstname` varchar(25) NOT NULL, `lastname` varchar(25) NOT NULL, `pseudo` varchar(15) NOT NULL, `email` varchar(50) NOT NULL, `password` text NOT NULL, `phone` varchar(50) NOT NULL, `skype` varchar(25) NOT NULL, `category` varchar(20) NOT NULL, `birthday` date NOT NULL, `inscription` datetime NOT NULL, `actif` tinyint(1) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Structure de la table `project` -- CREATE TABLE IF NOT EXISTS `project` ( `name` varchar(20) NOT NULL, `encoDate` date NOT NULL, `dateStart` date NOT NULL, `nbrDay` int(5) NOT NULL, `dateEnd` date NOT NULL, `depassement` int(5) NOT NULL, `description` text NOT NULL, `priority` int(5) NOT NULL, `affectation` varchar(20) NOT NULL, `clientName` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Structure de la table `prestation` -- CREATE TABLE IF NOT EXISTS `prestation` ( `date` date NOT NULL, `client` varchar(50) COLLATE utf8_bin NOT NULL, `name` varchar(20) COLLATE utf8_bin NOT NULL, `tache` varchar(20) COLLATE utf8_bin NOT NULL, `debut` time NOT NULL, `fin` time NOT NULL, `rapport` text COLLATE utf8_bin NOT NULL, `selection` varchar(50) COLLATE utf8_bin NOT NULL, `note` varchar(5) COLLATE utf8_bin NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

En fait je vais le récupérer afin d'utiliser les variables pour afficher les statistiques projet/utilisateur

Code:

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
<?php
	include 'db.inc.php';
 
		$email = $_SESSION['email'];
		$req="select i.email, p.name from inscription i, project p where i.pseudo= p.affectation and i.email = '".$email."'";
		$conn = $connexion->prepare($req);
		$conn->execute();
 
	  $req1="select name from prestation where selection = 'Analyse'  and name='".$conn['name']."' order by note";
	  $req2="select name from prestation where selection = 'Recherche' and name='".$conn['name']."'  order by note";
	  $req3="select name from prestation where selection = 'Développement'  and name='".$conn['name']."'  order by note";
	  $req4="select name from prestation where selection = 'Débogage'    and name='".$conn['name']."' order by note";
 
		$conn1 = $connexion->prepare($req1);
		$conn1->execute();
		$d = $conn1->rowCount();
		$conn2 = $connexion->prepare($req2);
		$conn2->execute();
		$r = $conn2->rowCount();
		$conn3 = $connexion->prepare($req3);
		$conn3->execute();
		$c = $conn3->rowCount();
		$conn4 = $connexion->prepare($req4);
		$conn4->execute();
		$u = $conn4->rowCount();
 
?>

Code:

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
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
 
	  function drawChart() {
 
        var data = google.visualization.arrayToDataTable( <?php echo "[
          ['action', 'Note'],
          ['Analyse',     ".$d."],
          ['Recherche',     ".$r."],
          ['Développement',  ".$c."],
          ['Débogage', ".$u."]"; ?>
 
        ]);
 
        var options = {
          title: 'Projet / utilsateur'
        };
 
        var chart = new google.visualization.PieChart(document.getElementById('piechart'));
 
        chart.draw(data, options);
      }
    </script>

Code:

1
2
3
4
5
6
7
8
<div class="container-fluid" id="result">
			<h1 class="col-xs-12 col-md-4 col-md-offset-4 center">Statistique</h1>
			<div class="col-xs-12 col-md-12">
 
			<div id="piechart" style="width: 900px; height: 500px;"></div>
 
			</div>
        </div>