我的两个DB数组:
订单-->(id,customer_id)
客户-->(id,name)
他们有外键(order.customer_id和customer.id)。
我有一个带有文本字段和提交按钮的html表单,该用户给出了客户的名称,我想从查询中获取所选客户的订单。这是我的代码,SELECT语句中有一个错误。如果有人能帮忙。。。
<?php
$connect = mysql_connect("localhost", "root", "") or die("Unable to connect to MySQL");
$selected = mysql_select_db("eshop", $connect) or die("Could not select examples");
if (isset($_POST['name'])) {
$customerName = $_POST['name'];
}
$result = mysql_query("SELECT order.id
FROM order, customer
WHERE order.customer_id = customer.id
AND customer.name = $customerName
");
if($result === FALSE) {
die(mysql_error()); // TODO: better error handling
}
while($row = mysql_fetch_array($result))
{
echo $row['order.id'];
}
mysql_close($connect);
?>
order
是一个保留字,因此使用backticks来包装它。即:`order`
$result = mysql_query("SELECT `order`.id
FROM `order`, customer
WHERE `order`.customer_id = customer.id
AND customer.name = '$customerName'
");
您的代码:
if (isset($_POST['name'])) {
$customerName = trim($_POST['name']);
$result = mysql_query("SELECT `order`.id as OrderId
FROM `order`, customer
WHERE `order`.customer_id = customer.id
AND customer.name = '$customerName'
") or die(mysql_error());
while($row = mysql_fetch_array($result))
{
echo $row['OrderId'];
}
}
注意:使用mysqli_*函数或PDO而不是mysql_*函数(已弃用)
如果您的最终用户是超级可靠的,则只有最小的查询更改,(为变量字符串添加引号)
$result = mysql_query("SELECT order.id
FROM order, customer
WHERE order.customer_id = customer.id
AND customer.name = '$customerName'
");
无论如何,逃跑是非常重要的。
尝试
$result = mysql_query("SELECT order.id
FROM order, customer
WHERE order.customer_id = '$customer.id'
AND customer.name = '$customerName'
");
并尝试为客户id声明一个变量,然后插入双引号
感谢您的回答。我有点失望,因为我在大学里的老师对SQL使用了错误的方法,就像我发布的那个方法一样。这是学期作业。无论如何,谢谢!