edit query error with foreign key constraints_问答_开发者

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;

and here is my edit query:

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.

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 -->

and heres my editform

########### 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:

Take away the quotes around the numerical variables in the Update string (e.g., change size_id='$size_id' to size_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] Change SELECT *, tab2.foo as bar,... to just SELECT * (while keeping the joins, etc).

Allowing edits on the labeled fields seems error-prone (spelling). If you have a manageable number of categories (eg, suppliers), you could use drop-down list with the readable names as the labels and id numbers as the values.