使用外键约束编辑查询错误 [英] edit query error with foreign key constraints
本文介绍了使用外键约束编辑查询错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
好的，就是这样.我有一个表产品，其中包含来自 product_sizes、brands、categories 和供应商表的外键.当我将产品表显示为 html 时.我使用 JOIN 以便产品表中显示的数据不是外键的 id 而是其对应的名称..我对添加数据的查询没有问题.我的问题是当我尝试使用我的编辑查询编辑数据时.，它提示我关于外键约束的错误.我将发布我的产品表以及我的编辑查询.这是:
ok here it is. i have a table product with foreign keys from product_sizes,brands,categories, and suppliers table. when i display the products table to a html. i use JOIN so that the data displayed in the products table is not the id's of the foreign keys but rather its corresponding names.. i have no problem with the query in regards of adding data. my problem is when i try to edit the data with my edit query. , it prompts me an error about foreign key constraint. i will post my products table and also my edit query. here it is:
CREATE TABLE IF NOT EXISTS `products` (
`product_id` int(10) NOT NULL auto_increment,
`product_name` varchar(20) default NULL,
`product_color` varchar(20) default NULL,
`product_description` varchar(100) default NULL,
`product_standardPrice` double default NULL,
`product_unitPrice` double default NULL,
`category_id` int(10) default NULL,
`brand_id` int(10) default NULL,
`size_id` int(10) default NULL,
`supplier_id` int(10) default NULL,
PRIMARY KEY (`product_id`),
KEY `category_id` (`category_id`),
KEY `brand_id` (`brand_id`),
KEY `supplier_id` (`supplier_id`),
KEY `size_id` (`size_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `products`
--
INSERT INTO `products` (`product_id`, `product_name`, `product_color`, `product_description`, `product_standardPrice`, `product_unitPrice`, `category_id`, `brand_id`, `size_id`, `supplier_id`) VALUES
(1, '12', '12', '12', 212, 1, 3, 22, 3, 1),
(2, '2', '2', '2', 2, 2, 4, 23, 2, 2);
--
-- Constraints for dumped tables
--
--
-- Constraints for table `products`
--
ALTER TABLE `products`
ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `products_ibfk_2` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`brand_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `products_ibfk_4` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`supplier_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `products_ibfk_5` FOREIGN KEY (`size_id`) REFERENCES `product_sizes` (`size_id`) ON DELETE CASCADE ON UPDATE CASCADE;
这是我的编辑查询:
if(isset($_POST['editproduct'])){
$product_id=$_POST["product_id"];
$product_name=$_POST["product_name"];
$product_color=$_POST["product_color"];
$size_name=$_POST["size_name"];
$product_description=$_POST["product_description"];
$brand_name=$_POST["brand_name"];
$category_name=$_POST["category_name"];
$supplier_name=$_POST["supplier_name"];
$product_standardPrice=$_POST["product_standardPrice"];
$product_unitPrice=$_POST["product_unitPrice"];
$sql = "UPDATE products SET
product_name='$product_name'
,product_color='$product_color'
,size_id='$size_id'
,product_description='$product_description'
,brand_id='$brand_id'
,category_id='$category_id'
,supplier_id='$supplier_id'
,product_standardPrice='$product_standardPrice'
,product_unitPrice='$product_unitPrice'
WHERE
product_id='$product_id'";
$result=mysql_query($sql,$connection) or die(mysql_error());
header("location: products.php");
}
请帮助我解决我的问题.我需要尽快解决这些问题.
please help me with my problem. i need to solve these asap.
这里是我的 products.php 页面，以备不时之需
and here is my products.php page in case you need it
<!--- DISPLAY TABLE -->
<form method="post">
<table class="sortable" id="mytable" align="center">
<tr>
<th><strong>Product #</strong></th>
<th><strong>Product Name</strong></th>
<th><strong>Color</strong></th>
<th><strong>Size</strong></th>
<th><strong>Description</strong></th>
<th><strong>Brand</strong></th>
<th><strong>Category</strong></th>
<th><strong>Supplier</strong></th>
<th><strong>Standard Price</strong></th>
<th><strong>Unit Price</strong></th>
<th><strong>Action</strong></th>
</tr>
<?
include ("conn.php");
$sql="SELECT *
,product_sizes.size_name as size_id
,brands.brand_name as brand_id
,categories.category_name as category_id
,suppliers.supplier_name as supplier_id
FROM products
JOIN
product_sizes on product_sizes.size_id=products.size_id
JOIN
brands on brands.brand_id=products.brand_id
JOIN
categories on categories.category_id=products.category_id
JOIN
suppliers on suppliers.supplier_id=products.supplier_id
ORDER BY product_id";
$result=mysql_query($sql,$connection) or die(mysql_error());
while($row=mysql_fetch_array($result)) {
?>
<tr>
<td><? echo $row['product_id']; ?></td>
<td><? echo $row['product_name']; ?></td>
<td><? echo $row['product_color']; ?></td>
<td><? echo $row['size_id']; ?> </td> <!---- HOW TO DISPLAY NAME RATHER THAN ID? --->
<td><? echo $row['product_description']; ?></td>
<td><? echo $row['brand_id']; ?></td> <!---- HOW TO DISPLAY NAME RATHER THAN ID? --->
<td><? echo $row['category_id']; ?></td> <!---- HOW TO DISPLAY NAME RATHER THAN ID? --->
<td><? echo $row['supplier_id']; ?></td> <!---- HOW TO DISPLAY NAME RATHER THAN ID? --->
<td>P <? echo $row['product_standardPrice']; ?></td>
<td>P <? echo $row['product_unitPrice']; ?></td>
<td><a href="forms.php?product_id=<? echo $row['product_id']?>&mode=editproduct">Edit</a></td>
</tr>
<? } ?>
</table>
</form>
<!--- END -->
这里是我的编辑表单
########### EDIT PRODUCT FORM
if($mode=="editproduct")
{
$product_id=$_GET["product_id"];
$sql="SELECT * FROM products WHERE product_id='$product_id'";
$result=mysql_query($sql,$connection) or die(mysql_error());
while($row=mysql_fetch_array($result)) {
$product_id=$row['product_id'];
$product_name=$row['product_name'];
$product_color=$row['product_color'];
$size_id=$row['size_id'];
$product_description=$row['product_description'];
$brand_id=$row['brand_id'];
$category_id=$row['category_id'];
$supplier_id=$row['supplier_id'];
$product_standardPrice=$row['product_standardPrice'];
$product_unitPrice=$row['product_unitPrice'];
} ?>
<link href="default.css" rel="stylesheet" type="text/css">
<form method="post" action="forms.php">
<table align="center">
<tr>
<td><strong>Edit Product</strong></td>
<td><input type="hidden" name="product_id" value="<? echo $product_id ;?>" /></td>
</tr>
<tr>
<td>Product Name</td>
<td><input type="text" name="product_name" value="<? echo $product_name ;?>" /></td>
</tr>
<tr>
<td>Color</td>
<td><input type="text" name="product_color" value="<? echo $product_color ;?>" /></td>
</tr>
<tr>
<td>Size</td>
<td>
<?
$query="SELECT * FROM product_sizes ORDER BY size_id ASC";
$result = mysql_query ($query);
echo "<select name=size_id>";
while($nt=mysql_fetch_array($result))
{
echo "<option value=$nt[$size_id]>$nt[size_name]</option>";
}
echo "</select>";
?>
</td>
</tr>
<tr>
<td>Description</td>
<td><input type="text" name="product_description" value="<? echo $product_description ;?>" /></td>
</tr>
<tr>
<td>Brand</td>
<td>
<?
$query="SELECT * FROM brands ORDER BY brand_name ASC";
$result = mysql_query ($query);
echo "<select name=brand_id>";
while($nt=mysql_fetch_array($result))
{
echo "<option value=$nt[brand_id]>$nt[brand_name]</option>";
}
echo "</select>";
?>
</td>
</tr>
<tr>
<td>Category</td>
<td>
<?
$query="SELECT * FROM categories ORDER BY category_name ASC";
$result = mysql_query ($query);
echo "<select name=category_id>";
while($nt=mysql_fetch_array($result))
{
echo "<option value=$nt[category_id]>$nt[category_name]</option>";
}
echo "</select>";
?>
</td>
</tr>
<tr>
<td>Supplier</td>
<td>
<?
$query="SELECT * FROM suppliers ORDER BY supplier_name ASC";
$result = mysql_query ($query);
echo "<select name=supplier_id>";
while($nt=mysql_fetch_array($result))
{
echo "<option value=$nt[supplier_id]>$nt[supplier_name]</option>";
}
echo "</select>";
?>
</td>
</tr>
<tr>
<td>Standard Price</td>
<td><input type="text" name="product_standardPrice" value="<? echo $product_standardPrice ;?>"/></td>
</tr>
<tr>
<td>Unit Price</td>
<td><input type="text" name="product_unitPrice" value="<? echo $product_unitPrice ;?>" /></td>
</tr>
<tr>
<td><input type="submit" name="editproduct" value="Save" /></td>
</tr>
</table>
</form>
<? }
推荐答案
除了评论中正在进行的讨论之外，我想提出一些更一般的建议:
Besides the ongoing discussion in the comments, I'd like to suggest some more general advice:
- 去掉更新字符串中数值变量周围的引号(例如，将
size_id='$size_id'更改为size_id=$size_id). - 将 UPDATE 字符串打印到浏览器，以便您可以准确地看到您要求数据库执行的操作.
- 如果 JOINS 可能导致问题，请尝试对标签进行子查询甚至单独查询(至少作为测试).
只更新与保存的不同的字段.不应该真的这样做，但一个优点是您可以尝试仅更改本地列.
- Take away the quotes around the numerical variables in the Update string (e.g., change
size_id='$size_id'tosize_id=$size_id). - Print the UPDATE string to the browser so you can see exactly what you're asking the database to do.
- If the JOINS could be causing trouble, try subqueries or even separate queries for the labels (at least as a test).
Update only the fields that differ from the saved. Shouldn't really have to do this but one advantage is that you could try just changing the local columns.
[new] 将 SELECT *, tab2.foo as bar,... 更改为 SELECT * (同时保留连接等).
[new] Change SELECT *, tab2.foo as bar,... to just SELECT * (while keeping the joins, etc).
这篇关于使用外键约束编辑查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
