在MySQL中更新包含外键列的表

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.

我正在处理与外键链接的3个表,并设置为ON UPDATE CASCADE和ON DELETE CASCADE。这些表是类别,子类别和产品。

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

产品表 - PID(PK),productname,subcatid(FK)

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

子类别表 - subcatid(PK),子类别名称,catid(FK)

category table - catid(PK), categoryname

类别表 - 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?

当我想更改产品名称时,对UPDATE产品表的正确查询是什么?另外,如果我想通过使用表单更改产品的子类别?

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

1 个解决方案

#1

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

会做的伎俩。同样适用于产品中的subcatid。只要子子类表中存在新的子域ID,您就可以通过简单的方式将products.subcatid更改为您想要的任何内容。

UPDATE products SET subcatid=New_ID where PID=XXX;

#1