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