CODEWITHSUNDEEP
phpmysqlforeign-keyssql-update
henry
henry

Reputation: 21

UPDATING a table in MySQL containing a foreign key column

I am working on 3 tables linked with foreign keys and also set to ON UPDATE CASCADE and ON DELETE CASCADE. These tables are category, subcategory and products.

products table - PID(PK), productname, subcatid(FK)

subcategory table - subcatid(PK), subcategoryname, catid(FK)

category table - catid(PK), categoryname

What is the right query to UPDATE products table when I want to change the name of the product? Also if I want to change the subcategory of a product by using a form?

I am using a form which successfully prepopulates with the fields - product name, subcategory name but it does not update the records in products table, means it does nothing and does not changes the product name and subcategory name.

Any help is appreciated.

am using the following code

<?php 
// Parse the form data and add inventory item to the system
if (isset($_POST['PRODUCT_NAME'])) {
    $pid = mysql_real_escape_string($_POST['thisPID']);
    $catalog_no = mysql_real_escape_string($_POST['CATALOG_NO']);
    $product_name = mysql_real_escape_string($_POST['PRODUCT_NAME']);
    $price = mysql_real_escape_string($_POST['PRICE']);
    $composition = mysql_real_escape_string($_POST['COMPOSITION']);
    $size = mysql_real_escape_string($_POST['SIZE']);
    $subcat = mysql_real_escape_string($_POST['SUBCAT_ID']);
    // See if that product name is an identical match to another product in the system
    $sql = mysql_query("UPDATE products SET CATALOG_N0='$catalog_no', PRODUCT_NAME='$product_name', PRICE='$price', COMPOSITION='$composition', SIZE='$size', SUBCAT_ID='$subcat' WHERE PID='$pid'");
    header("location: inventory_list.php"); 
    exit();
}
?>

<?php 
// Gather this product's full information for inserting automatically into the edit form below on page
if (isset($_GET['pid'])) {
    $targetID = $_GET['pid'];
    $sql = mysql_query("SELECT products.PID, products.CATALOG_NO, products.PRODUCT_NAME, products.PRICE, products.COMPOSITION, products.SIZE, products.SUBCAT_ID, subcategory.SUBCAT_ID, subcategory.SUBCATEGORY_NAME FROM (products, subcategory) WHERE subcategory.SUBCAT_ID=products.SUBCAT_ID AND PID='$targetID' LIMIT 1");
    $productCount = mysql_num_rows($sql); // count the output amount
    if ($productCount > 0) {
        while($row = mysql_fetch_array($sql)){ 

             $catalog_no = $row["CATALOG_NO"];
             $product_name = $row["PRODUCT_NAME"];
             $price = $row["PRICE"];
             $composition = $row["COMPOSITION"];
             $size = $row["SIZE"];
             $subcat = $row["SUBCAT_ID"];
        }
    } else {
        echo "You dont have that product";
        exit();
    }
}
?>

Upvotes: 2

Views: 4468

Answers (1)

Marc B
Marc B

Reputation: 360682

You're not using the product name as a foreign key, so a simple standard

UPDATE products SET productname='New Name of Product' where PID=XXX;

would do the trick. Same goes for the subcatid in products. As long as the new subcat ID exists in the subcategory table, you can change products.subcatid to whatever you want, again via a simple 

UPDATE products SET subcatid=New_ID where PID=XXX;

Upvotes: 3

Related Questions