Probleme de requete avec jointure entre 2 tables

**floflo38100** · 01/04/2016, 11h57

Bonjour,

J ai deux tables:

1 table mould

mould_Id
type
code
date

---------------
1 tables events

id_events
id_mould
date_events
status_events

--------------------

j aimerai affiché l ensemble des des infos liés a chaque mould (mould_id) et le dernier evenement ajouté dans la table events( cette table contient plusieurs evenements "historique du mould" donc

J aimerai recup lensemble des info de chaque mould de la table mould et le dernier element ajouter de la table events associé au mould

je vous joints ma requete pour essayer de comprendre.

merci
voici ma page de traitement

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
<?php
if((isset($_POST["so"]))&&(isset($_POST["tri"])))
{
	//&&(isset($_POST["choix"]))
	$so = $_POST["so"];
	$tri = $_POST["tri"];
	//$id_mould = $_POST["choix"];
 
 
	//var_dump($so);
	//var_dump($tri);
	//include_once("_secure.php");
	include_once("_secure2.php");
 
	if($so == 'all')
	{
		switch($tri)
		{
 
			case'1':
			$query = $mysqli -> query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY tool_number ASC");
			//$query = $mysqli->query("SELECT * FROM mould WHERE actif=0  ORDER BY tool_number ASC");
			break;
			case'2':
			$query = $mysqli -> query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY tool_number DESC");
			//$query = $mysqli->query("SELECT * FROM mould WHERE actif=0  ORDER BY tool_number DESC");
			break;
			case'3':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY type ASC");
			break;
			case'4':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY type DESC");
			break;
			case'5':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY part_number ASC");
			break;
			case'6':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY part_number DESC");
			break;
			case'7':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY place ASC");
			break;
			case'8':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY place DESC");
			break;
			case'9':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND events_add_date=MAX(events_add_date) ORDER BY status ASC");
			break;
			case'10':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND events_add_date=MAX(events_add_date) ORDER BY status DESC");
			break;
			case'11':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY last_use ASC");
			break;
			case'12':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY last_use DESC");
			break;
			case'13':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY cycle_quantity ASC");
			break;
			case'14':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0  ORDER BY cycle_quantity DESC");
			break;
 
		}
	}
	//else if (is_numeric($id_mould)) {	
		//$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND id_mould = $id_mould ORDER BY type ASC");
		//}
 
	else
		{
		switch($tri)
			{
			case'1':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0   
			AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR part_number LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')			
			ORDER BY tool_number ASC");
			break;
			case'2':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR part_number LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')			
			ORDER BY tool_number DESC");
			break;
			case'3':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR part_number LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')
			ORDER BY type ASC");
			break;
			case'4':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND status AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR part_number LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')			
			ORDER BY type DESC");
			break;
			case'5':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mouldSELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR part_number LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')			
			ORDER BY part_number ASC");
			break;
			case'6':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mouldSELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR part_number LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')
			ORDER BY part_number DESC");
			break;
 
			case'7':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mouldSELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR part_number LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')		
			ORDER BY place ASC");
			break;
			case'8':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mouldSELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR part_number LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')
			status
			ORDER BY place DESC");
			break;
			case'9':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mouldSELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR part_number LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')
			ORDER BY status ASC");
			break;
			case'10':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR part_number LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')			
			ORDER BY status DESC");
			break;
			case'11':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR custo LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')			
			ORDER BY last_use ASC");
			break;
			case'12':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR custo LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')			
			ORDER BY last_use DESC");
			break;			
			case'13':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND (tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR custo LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')			
			ORDER BY cycle_quantity ASC");
			break;
			case'14':
			$query = $mysqli->query("SELECT * FROM mould INNER JOIN events ON mould.id_mould = events.id_mould WHERE actif=0 AND(tool_number LIKE '%$so%'
			OR type LIKE '%$so%'
			OR custo LIKE '%$so%'
			OR place LIKE '%$so%'
			OR status LIKE '%$so%'
			OR cycle_quantity LIKE '%$so%'
			OR last_use LIKE '%$so%')			
			ORDER BY cycle_quantity DESC");
			break;
			}
		}
 
 
	//var_dump($query);
	$nb=$query->num_rows;
	if($nb >0)
	{
		while($data = $query->fetch_array())
		{
		$id_mould = $data["id_mould"];	
 
			echo "<tr>";
				echo "<td>".utf8_decode($data["tool_number"])."</td>";
				echo "<td>".$data["type"]."</td>";
				?>
				<td><a href='#' onClick="window.open('mould_edit.php?id_mould=<?php echo $id_mould;?>','Edit Mould','width=1280, height=980, top=30, left=50')"><img src='img/quickedit.png' title='Edit'></a></td>
				<?php
				/*echo"<td><a href=\"#\" onClick=\"window.open('mould_edit.php?id_mould=$id_mould','Modify Mo','width=800,height=900,left=20,top=30')\">
					<img src=\"img/quickedit.png\" title=\"Mould Edit\"></a></td>";*/
 
				/*echo "<td class=\"center\">
				<a href=\"mould_edit.php?id_mould=$id_mould\" title=\"Editer\">
				<img src=\"img/quickedit.png\" >
				</a>
				</td>";*/
				if (isset($data["part_number"]) AND (empty($data["part_number2"]))) 
				{
					echo "<td>".$data["part_number"]."</td>";
				}
				else if (isset($data["part_number"])AND(isset($data["part_number2"]))AND(isset($data["part_number3"])))
				{
					echo "<td>".$data["part_number"]."<br>".$data["part_number2"]."<br>".$data["part_number3"]."</td>";
				}
				else if (isset($data["part_number"])AND(isset($data["part_number2"]))AND(isset($data["part_number3"]))AND(isset($data["part_number4"]))AND(empty($data["part_number5"])))
				{
					echo "<td>".$data["part_number"]."<br>".$data["part_number2"]."<br>".$data["part_number3"]."<br>".$data["part_number4"]."</td>";
				}	
 
				/*else if (isset($data["part_number3"])) 
				{
					echo "<td>".$data["part_number"]."<br>".$data["part_number2"]."<br>".$data["part_number3"]."<br>".$data["part_number4"]."<br>".$data["part_number5"]."</td>";
				}*/
 
 
				echo "<td>".utf8_decode($data["place"])."</td>";
				echo "<td>".utf8_decode($data["status"])."</td>";
				if ($data["last_use"]=="0000-00-00") {
					echo "<td></td>";
 
				}else
				{
					echo "<td>".$data["last_use"]."</td>";
				}
 
				echo "<td>".$data["cycle_quantity"]."</td>";
 
		}
		echo "</tr>";
	}
}	
?>

et voici mon autre page qui affiche le tableau de bord

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
<?php
include_once("_secure2.php");
if (isset($_POST["search"])) 
{
	//2 je stock
	$search=$_POST["search"];
}
?>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD Xhtml 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="style/style.css"/>
<link rel="stylesheet" type="text/css" href="style/style.css"/>
<script src="http://code.jquery.com/jquery-1.11.1.min.js"></script>
    <script src="http://cdn.datatables.net/1.10.2/js/jquery.dataTables.min.js"></script>
 
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/jquery-migrate-1.1.0.js"></script>
<script type="text/javascript">
$(function(){
	//Variable pour le switch
	var parite = 0;
 
	//Variable pour la recherche (initialement on veut tout)
	var quoi = 'all';
	//variable pour le select
	//var selection;+ "choix=" + selection
 
	//requete ajax
	function query(par,cherche){
		$.ajax({
			type:"POST",
			url:"mould_list.php",
			//On passe deux arguments
			data:"tri=" + par + "&so=" + cherche,
			success:function(data){
				$('#resultat').html(data);
			}
		});
	}
 
	//Premier appel, au chargement de la page
	query(1,quoi);
 
	//Si saisie dans le champ de recherche
	$('#search').keyup(function(){
		//on passe la valeur saisie à la variable quoi
		quoi = $(this).val();
		//Et on relance la recherche
		query(1,quoi);
	});
 
	$("#customer_code").change(function(){
		selection 	= $(this).val();
	});
 
	//Le switch
	function switchons(asc,desc,cherche){
		if(parite == 0){
			query(asc,cherche);
			parite++;
		}else{
			query(desc,cherche);
			parite--;
		}
	}
	//appel au click des th
	$('#btnTool').click(function(){
		switchons(1,2,quoi);	
	});
	$('#btnType').click(function(){
		switchons(3,4,quoi);	
	});
	$('#btnPartnumber').click(function(){
		switchons(5,6,quoi);	
	});	
	$('#btnPlace').click(function(){
		switchons(7,8,quoi);	
	});
	$('#btnStatus').click(function(){
		switchons(9,10,quoi);	
	});
	$('#btnLastuse').click(function(){
		switchons(11,12,quoi);	
	});
	$('#btnCyclequantity').click(function(){
		switchons(13,14,quoi);	
	});
 
 
 
});
$(function(){
	var selection;
	var query;
 
	$("#customer_code").change(function(){
		selection 	= $(this).val();
		query		= $.ajax({
			type:"POST",
			url:"mould_list.php",
			data:"choix=" + selection,
			success: function(data) {
				$("#resultat").html(data);	
			}
		});	
	});
});
</script>
</head>
<body onLoad="trier('5')">
<div id="superglobal">
<div id="global">
	<?php
		include_once("_menu_mould.php");
		?>
		<div id="titre">
			<div id="titre2">
				<h2>MOULD LIST</h2>
 
				<ul id="sous_menu">
					<li id="recherche">
					<form action="" method="post">
					<p>
						<label for="search">Search : </label>
						<input type="text" name="search" id="search"<?php if (isset($search)) echo "value=\"$search\"";?>>
					</p>
					</form>
					</li>
				</ul>
			</div>	
		</div>
 
<table class="status">
 
 
		<thead>
    <th><a href="#"title="tri" id="btnTool">Tool Number</a></th>
    <th><a href="#"title="tri" id="btnType">Type #</a></th>
    <th>Edit</th>
    <!--<th>Edit</th>-->
	<th><a href="#"title="tri" id="btnPartnumber">Part Number #</a></th>
    <th><a href="#"title="tri" id="btnPlace">Storage place</a></th>
	<th><a href="#"title="tri" id="btnStatus">Mould Status</a></th>  
    <th><a href="#"title="tri" id="btnLastuse">Last Use</a></th>
    <th><a href="#"title="tri" id="btnCyclequantity">Cycle Quantity</a></th>
    </thead>
    <form action="moul_list_status.php" method="post">
   <thead>
 
   	<th>
 
	<!--ici mes 2 selects mon probleme c est que je ne sais pas comment les traiter en jqueri 
	et ou placer ma requete dans mould_list afin d avoir lorque je selectionne une option l'ensemble des resultat associé-->	
	<select name="option_tri" id="option_tri" onChange="trier(this.value)">
		<option value="ras">Select Customer Code</option>
		<?php
		$query	=	$mysqli	->	query("SELECT customer_code FROM mould WHERE actif = 0 GROUP BY customer_code");
		$nb		=	$query	->	num_rows;
		if ($nb > 0) 
 
		{
			while($row	=	$query	->	fetch_array())
			{
				$id_mould = $row["id_mould"];
				$customer_code = $row["customer_code"];
 
			echo "<option value=\"$id_mould\">".utf8_encode($customer_code)."</option>";
			}
		echo "</select></p>";
		}
 
		?>
	</th>
	<th>
 
 
		<select name="option_tri" id="option_tri">
		<option value="ras">Select Type</option>;
		<?php
		$query	=	$mysqli	->	query("SELECT type FROM mould WHERE actif = 0 GROUP BY type");
		$nb		=	$query	->	num_rows;
		if ($nb > 0) 
		{
			while($row	=	$query	->	fetch_array())
			{
				$id_mould = $row["id_mould"];
				$type = $row["type"];
 
			echo "<option value=\"$id_mould\">".utf8_encode($type)."</option>";
			}
		echo "</select></p>";
		}
 
 
		?>
	</th>
		</thead>
	</form>
 
 
		<tbody id="resultat">
 
		</tbody>
 
</table>
</div>
</div>
</div>
 
</body>
</html>
 
</body>
</html>

**Amélie Ladoque** · 06/04/2016, 10h10

Ce n'est pas un problème de PHP mais de requête avec MySQL.
Le plus simple est de te servir de l'interface PhpMyAdmin pour tester ton SQL

Probleme de requete avec jointure entre 2 tables

PHP & Base de données

Discussions similaires

Partager

Partager