inner join, passing data from table for edit

so i have now a working inner join table. my problem is i dont know how to pass the correct values for editing in the select area. after i update. its error is

Cannot add or update a child row: a foreign key constraint fails (`sadsystem/products`, CONSTRAINT `products_ibfk_3` FOREIGN KEY (`size_id`) REFERENCES `product_sizes` (`size_id`) ON DELETE CASCADE ON UPDATE CASCADE)

<!--- DISPLAY TABLE products.php -->
<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> 
<td><? echo $row['product_description']; ?></td>
<td><? echo $row['brand_id']; ?></td>  
<td><? echo $row['category_id']; ?></td>  
<td><? echo $row['supplier_id']; ?></td> 
<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 -->

<!--- DISPLAY ADD LINK-->
<table align="center">
<tr>
<td><a href="forms.php?mode=addproduct">Add Product</a></td>
</tr>
</table>
<!--- END -->

form.php >> the page where i should correctly pass the data from products.php

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

somewhere in the select area of sizes where i screw up. please help me. thank you.

here is my table structure

-- phpMyAdmin SQL Dump
-- version 3.3.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 28, 2010 at 02:44 AM
-- Server version: 5.0.51
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `sadsystem`
--

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

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 `size_id` (`size_id`),
  KEY `supplier_id` (`supplier_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

--
-- 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, 'aw', 'aw', 'aw', 0, 0, 3, 23, 1, 1),
(2, 'aw', 'aw', 'aw', 123, 4124, 3, 22, 1, 1),
(4, 'aw', 'aw', 'aw', 12, 12, 6, 27, 3, 3),
(5, 'asd', 'sadsad', 'asdasd', 1412412, 124124, 3, 22, 1, 1),
(6, 'dasd', 'asdasd', 'dasd', 0, 0, 3, 22, 3, 1),
(7, 'asd', 'asdasd', 'asdasdasd', 12, 1212, 3, 22, 5, 1),
(8, 'asd', 'asd', 'asd', 1, 1, 3, 22, 4, 1),
(9, '2', '2', '2', 2, 2, 5, 23, 2, 2);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `products_ibfk_4` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`supplier_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  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_3` FOREIGN KEY (`size_id`) REFERENCES `product_sizes` (`size_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ansaurus

tags:

views:

answers:

inner join, passing data from table for edit

related questions