所以这是我第一次正确地使用INNER JOIN语句，我一直在尝试它，不用说它并不顺利。下面是我的页面代码:
<?php require_once('Connections/reps.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($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;
}
}
mysql_select_db($database_reps, $reps);
$query_orders = "SELECT * FROM orders";
$orders = mysql_query($query_orders, $reps) or die(mysql_error());
$row_orders = mysql_fetch_assoc($orders);
$totalRows_orders = mysql_num_rows($orders);
mysql_select_db($database_reps, $reps);
$query_staff = "SELECT * FROM staff";
$staff = mysql_query($query_staff, $reps) or die(mysql_error());
$row_staff = mysql_fetch_assoc($staff);
$totalRows_staff = mysql_num_rows($staff);
mysql_select_db($database_reps, $reps);
$query_products = "SELECT * FROM products";
$products = mysql_query($query_products, $reps) or die(mysql_error());
$row_products = mysql_fetch_assoc($products);
$totalRows_products = mysql_num_rows($products);
mysql_select_db($database_reps, $reps);
$join = "SELECT
orders.ID AS id,
orders.CustomerName AS customerName,
orders.StaffMember AS staffMember,
orders.SalesPrice AS salesPrice,
orders.ListPrice AS listPrice,
orders.AgeOfPayment AS productAge,
orders.Product AS productName,
orders.Quantity AS orderQuantity,
orders.DateOfOrder AS orderDate,
staff.Bonus AS staffBonus,
products.Price AS productPrice
FROM
orders AS orders
INNER JOIN staff AS staff ON orders.StaffMember = staff.StaffName
INNER JOIN products AS products ON orders.Product = products.ProductName
ORDER BY
orderDate ASC";
$joinresult = mysql_query($join);
?>
<!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" />
<title></title>
<?php
require_once("models/config.php");
if (!securePage($_SERVER['PHP_SELF'])){die();}
require_once("models/header.php");
?>
</head>
<body>
<div id='wrapper'>
<div id='top'>
<div id='logo'></div>
</div>
<div id='content'>
<h1></h1>
<h2>Change This</h2>
<div id='left-nav'>
<?php include("left-nav.php"); ?>
</div>
<div id='main'>
<div id ="orderstable">
<table border="1" cellpadding="1" cellspacing="3">
<tr>
<th>Order ID</th>
<th>Customer Name</th>
<th>Product</th>
<th>Quantity</th>
<th>Sales Price (€)</th>
<th>Total Sale (€)</th>
<th>List Price (€)</th>
<th>Price Difference (€)</th>
<th>% Difference</th>
<th>Sold By</th>
<th>Order Date</th>
<th>Age of Payment</th>
<th>Bonus %</th>
<th>Bonus Amount</th>
</tr>
<?php do { ?>
<?php do { ?>
<tr>
<td align="center" valign="middle"><?php echo $row_orders['ID']; ?></td>
<td align="center" valign="middle"><?php echo $row_join['customerName']; ?></td>
<td align="center" valign="middle"><?php echo $row_join['product']; ?></td>
<td align="center" valign="middle"><?php echo $row_orders['Quantity']; ?></td>
<td align="center" valign="middle"><?php echo $row_orders['SalesPrice']; ?></td>
<td align="center" valign="middle"><?php $first_multiply = $row_orders['Quantity'];
$second_multiply = $row_orders['SalesPrice'];
$multiply_total = $first_multiply * $second_multiply;
echo ($multiply_total); ?></td>
<td align="center" valign="middle"><?php echo $row_orders['Price']; ?></td>
<td align="center" valign="middle"><?php $first_number = $row_orders['Price'];
$second_number = $row_orders['SalesPrice'];
$sum_total = $first_number - $second_number;
echo ($sum_total); ?></td>
<td align="center" valign="middle">as</td>
<td align="center" valign="middle"><?php echo $row_join['staffMember']; ?></td>
<td align="center" valign="middle"><?php echo date('d/m/Y', $row_orders['orderDate']); ?></td>
<td align="center" valign="middle"></td>
<td align="center" valign="middle"><?php echo $row_orders['bonus']; ?>%</td>
<td align="center" valign="middle"> </td>
</tr>
<?php } while ($row_orders = mysql_fetch_assoc($orders));?>
<?php } while ($row_join = mysql_fetch_array($join));?>
</table>
</div>
</div>
<div id='bottom'></div>
</div>
</body>
</html><?php
mysql_free_result($orders);
mysql_free_result($staff);
mysql_free_result($products);
?>
我基本上想做的是:
- 在此之前的页面提交订单。数据存储在数据库中的表中并回显。
- 这三个表分别是员工表、订单表和产品表
- 我根据ID而不是值保存了表单。我在谷歌上搜索并研究了INNER JOIN，我尝试了下面的方法，但没有奏效。
有人能指出我做错了什么吗?
SELECT
o.ID,
o.CustomerName,
o.StaffMember,
o.SalesPrice,
o.ListPrice,
o.AgeOfPayment,
o.Product,
o.Quantity,
o.DateOfOrder,
s.Bonus,
p.Price
FROM
`orders` o
INNER JOIN staff AS s ON o.StaffMember = s.StaffName
INNER JOIN products AS p ON o.Product = p.ProductName
WHERE o.StaffMember = s.StaffName
AND o.Product = p.ProductName
ORDER BY
orderDate ASC";
然而，我有一种感觉，你的表不是很好。我建议使用id而不是Product = ProductName等
我将这样做:
$join = "SELECT o.ID id,
o.CustomerName customerName,
o.StaffMember staffMember,
o.SalesPrice salesPrice,
o.ListPrice listPrice,
o.AgeOfPayment productAge,
o.Product productName,
o.Quantity orderQuantity,
o.DateOfOrder orderDate,
s.Bonus staffBonus,
p.Price productPrice
FROM
orders o,
staff s,
products p
WHERE
o.StaffMember = s.StaffName
AND
o.Product = p.ProductName
ORDER BY
orderDate";
由于在连接3个表时有2个条件，因此您将获得所有结果。您可以放入另一个AND，在orders表中呈现一些独特的内容，并获得有关特定订单的信息。
需要注意的一点是mysql_函数现在在PHP中是不支持的。您可能希望切换到mysqli_(与mysql_类非常相似)或PDO。