UPDATING a table in MySQL containing a foreign key column

Question

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();
    }
}
?>

What are the queries you are trying with your form? Post them up. — Treffynnon, Apr 05 '11 at 13:07
Update your question with the code. Comments are not long enough. — Treffynnon, Apr 05 '11 at 13:25

score 3 · Answer 1 · answered Apr 05 '11 at 13:27

3

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;

answered Apr 05 '11 at 13:27

Marc B

356,200
43
426
500

i think this is what am doing, am using UPDATE products SET PRODUCT_NAME='$product_name' WHERE PID=1; – henry Apr 05 '11 at 13:34
1

Shouldn't be anything wrong with that, unless $product_name hasn't been properly escaped. If it has a quote in it, it'll "break" the query. Always check if your queries succeed. Even a basic `$result = mysql_query(...) or die(mysql_error());` will save you a LOT of time wondering why things aren't working. – Marc B Apr 05 '11 at 13:37
so to use $product_name I have to escape it properly – henry Apr 05 '11 at 13:44
You have to escape ANY data you insert into a query, otherwise you're wide open to SQL injection attacks, of which you can find a few bazillion examples on this site. – Marc B Apr 05 '11 at 13:46
but subcat_id(FK) on products table, how do i update that, for eg if I want to change a subcategory of a product, how will i do that? – henry Apr 05 '11 at 13:56
am not able to get that what exactly should I do use SET PRODUCT_NAME='$product_name' – henry Apr 05 '11 at 14:09

UPDATING a table in MySQL containing a foreign key column

1 Answers1