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:\[code\]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;\[/code\]and here is my edit query:\[code\]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");}\[/code\]please help me with my problem. i need to solve these asap.and here is my products.php page in case you need it\[code\]<!--- 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_idFROM 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="http://stackoverflow.com/questions/3845460/forms.php?product_id=<? echo $row['product_id']?>&mode=editproduct">Edit</a></td></tr><? } ?></table> </form><!--- END -->\[/code\]and heres my editform\[code\]########### EDIT PRODUCT FORMif($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="http://stackoverflow.com/questions/3845460/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="http://stackoverflow.com/questions/3845460/<? echo $product_id ;?>" /></td></tr><tr><td>Product Name</td><td><input type="text" name="product_name" value="http://stackoverflow.com/questions/3845460/<? echo $product_name ;?>" /></td></tr><tr><td>Color</td><td><input type="text" name="product_color" value="http://stackoverflow.com/questions/3845460/<? 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=http://stackoverflow.com/questions/3845460/$nt[$size_id]>$nt[size_name]</option>"; } echo "</select>";?></td></tr><tr><td>Description</td><td><input type="text" name="product_description" value="http://stackoverflow.com/questions/3845460/<? 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=http://stackoverflow.com/questions/3845460/$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=http://stackoverflow.com/questions/3845460/$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=http://stackoverflow.com/questions/3845460/$nt[supplier_id]>$nt[supplier_name]</option>"; } echo "</select>";?></td></tr><tr><td>Standard Price</td><td><input type="text" name="product_standardPrice" value="http://stackoverflow.com/questions/3845460/<? echo $product_standardPrice ;?>"/></td></tr><tr><td>Unit Price</td><td><input type="text" name="product_unitPrice" value="http://stackoverflow.com/questions/3845460/<? echo $product_unitPrice ;?>" /></td></tr><tr><td><input type="submit" name="editproduct" value="http://stackoverflow.com/questions/3845460/Save" /></td></tr></table></form><? }\[/code\]
edit query error with foreign key constraints
- Thread starter Darko
- Start date