[MySQL] Requete SQL INNER JOIN

Requete SQL INNER JOIN

Bonjour,

je réalise un panier de commande

J'ai 2 table

Table shop_panier et table shop_format.

La table shop_panier contient les colonne suivante: (dans l'ordre)
id, client, album, photo, quantite, format

La table shop_format contient les colonnes:
id, format, prix

Le but etant d'afficher le prix unitaire en fonction du format choisi par le client puis de le mutiplier par la quantité le tout pour une session.(pas simple la vie)

Voici la requette actuel qui ne me donne aucun resultat
Code:

1 2 3 SELECT shop_panier.*, shop_format.format, shop_format.prix, (shop_format.prix*shop_panier.quantite) AS total_ttc FROM shop_panier INNER JOIN shop_format ON shop_panier.format=shop_format.id WHERE client = 'paramclient'
le resultat de la requete est vide. meme sans 'paramclient'

Bref je m'en sort pas depuis 2 jours

Merci de votre aide

Bonjour,

il me semble que tu fais une erreur dans ta jointure :

Code:

shop_panier.format=shop_format.id

devrait plutôt être ça non ?

Code:

shop_panier.format=shop_format.format

Bonjour,

On peut avoir un jeu de données pour faire le test, ainsi que les scripts de création des tables ?

ced

Mon probleme de jointure est resolu ,merci beaucoup mais j'ai toujour un probleme d'affichage des ligne.

ca ne m'affiche que la derniere ligne rentrer dans la table shop_panier.

Voici mon code

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
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
<?php require_once('Connections/newtrip.php'); ?>
<?php require_once('Connections/newtrip.php'); ?>
<?php
session_start();
$MM_authorizedUsers = "";
$MM_donotCheckaccess = "true";
 
// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) { 
  // For security, start by assuming the visitor is NOT authorized. 
  $isValid = False; 
 
  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username. 
  // Therefore, we know that a user is NOT logged in if that Session variable is blank. 
  if (!empty($UserName)) { 
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login. 
    // Parse the strings into arrays. 
    $arrUsers = Explode(",", $strUsers); 
    $arrGroups = Explode(",", $strGroups); 
    if (in_array($UserName, $arrUsers)) { 
      $isValid = true; 
    } 
    // Or, you may restrict access to only certain users based on their username. 
    if (in_array($UserGroup, $arrGroups)) { 
      $isValid = true; 
    } 
    if (($strUsers == "") && true) { 
      $isValid = true; 
    } 
  } 
  return $isValid; 
}
 
$MM_restrictGoTo = "login.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {   
  $MM_qsChar = "?";
  $MM_referrer = $_SERVER['PHP_SELF'];
  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
  if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0) 
  $MM_referrer .= "?" . $QUERY_STRING;
  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
  header("Location: ". $MM_restrictGoTo); 
  exit;
}
?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
 
  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
 
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
 
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "quantite")) {
  $updateSQL = sprintf("UPDATE shop_panier SET client=%s, article=%s, quantite=%s WHERE id=%s",
                       GetSQLValueString($_POST['client'], "text"),
                       GetSQLValueString($_POST['article'], "int"),
                       GetSQLValueString($_POST['quantite'], "int"),
                       GetSQLValueString($_POST['id'], "int"));
 
  mysql_select_db($database_newtrip, $newtrip);
  $Result1 = mysql_query($updateSQL, $newtrip) or die(mysql_error());
 
  $updateGoTo = "panier.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}
 
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "validation")) {
  $insertSQL = sprintf("INSERT INTO shop_commande (client, `date`, total_ht, total_ttc) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['client'], "text"),
                       GetSQLValueString($_POST['date'], "date"),
                       GetSQLValueString($_POST['total_ht'], "double"),
                       GetSQLValueString($_POST['total_ttc'], "double"));
 
  mysql_select_db($database_newtrip, $newtrip);
  $Result1 = mysql_query($insertSQL, $newtrip) or die(mysql_error());
 
  $insertGoTo = "traitement_commande.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
 
$paramclient_panier = "1";
if (isset($_SESSION[ 'MM_Username' ])) {
  $paramclient_panier = (get_magic_quotes_gpc()) ? $_SESSION[ 'MM_Username' ] : addslashes($_SESSION[ 'MM_Username' ]);
}
mysql_select_db($database_newtrip, $newtrip);
$query_panier = sprintf("SELECT shop_panier.*, shop_format.format, shop_format.prix, (shop_format.prix*shop_panier.quantite) AS total_ttc FROM shop_panier INNER JOIN shop_format ON shop_panier.format=shop_format.format WHERE client = '%s' ", $paramclient_panier);
$panier = mysql_query($query_panier, $newtrip) or die(mysql_error());
$row_panier = mysql_fetch_assoc($panier);
$totalRows_panier = mysql_num_rows($panier);
 
 
 session_start();?>
<!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">
<title>Document sans titre</title>
</head>
<?php require 'header.inc.php' ?>
<?php require("conf.php"); ?>
<body>
<p>Bonjour </p>
<p>Votre adresse mail est : <?php echo $_SESSION['MM_Username']; ?></p>
<table width="61%" border="1">
  <tr>
 
    <td width="16%"><div align="center">Vignette</div></td>
    <td width="14%"><div align="center">Album</div></td>
    <td width="14%"><div align="center">N&deg; de photo </div></td>
    <td width="14%"><div align="center">Format</div></td>
    <td width="15%"><div align="center">Quantit&eacute;</div></td>
    <td width="12%"><div align="center">Prix unitaire </div></td>
    <td width="15%"><div align="center">Sous-Total</div></td>
  </tr>
  <tr>
    <td height="69"><div align="center"><img src="<?php echo PHOTOS_DIR."/" . rawurlencode($photodir) . "/" . THUMBS_DIR . "/__".$listvalidimg[$i] ?>" border="0" alt="<?php echo $listvalidimg[$i]; ?>" class="photo">&nbsp;</div></td>
	 <td><div align="center"><span class="Style1"><?php echo $row_panier['album']; ?></span></div></td>
    <td><div align="center"><span class="Style1">N&deg;<?php echo $row_panier['photo']; ?><br />(<a href="suppr_panier.php?id=<?php echo $row_panier['id']; ?>">Supprimer</a>)</span></div></td>
        <td><div align="center"><span class="Style1"><?php echo $row_panier['format']; ?></span></div></td>
        <td><form action="<?php echo $editFormAction; ?>" method="POST" name="quantite" id="quantite">
 
          <div align="center">
            <input name="quantite" type="text" id="quantite" value="<?php echo $row_panier['quantite']; ?>" size="3" maxlength="3">
            <input name="id" type="hidden" id="id" value="<?php echo $row_panier['id']; ?>">
            <input name="client" type="hidden" id="client" value="<?php echo $row_panier['client']; ?>">
            <input name="photo" type="hidden" id="photo" value="<?php echo $row_panier['photo']; ?>">
            <input type="hidden" name="MM_update" value="quantite">
            <input type="submit" name="Submit" value="Modifier">        
          </div>
        </form>          
          <div align="center"><span class="Style1"></span></div></td>
					<td><div align="center"><?php echo $row_panier['prix']; ?>&euro;</div></td>
					<td><div align="center"><span class="Style1"><?php echo $row_panier['total_ttc']; ?> &euro; </span></div></td>
  </tr>
</table>
<p>&nbsp;</p>
</body>
</html>
<?php
mysql_free_result($panier);
?>
<?php require 'footer.inc.php' ?>