title: UPDATING a table in MySQL containing a foreign key column
body: <p>I am working on 3 tables linked with foreign keys and also set to <code>ON UPDATE CASCADE</code> and <code>ON DELETE CASCADE</code>. These tables are <code>category</code>, <code>subcategory</code> and <code>products</code>.  </p>
  
  <p><code>products</code> table - PID(PK), productname, subcatid(FK)  </p>
  
  <p><code>subcategory</code> table - subcatid(PK), subcategoryname, catid(FK)  </p>
  
  <p><code>category</code> table - catid(PK), categoryname</p>
  
  <p>What is the right query to <code>UPDATE</code> 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?</p>
  
  <p>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.</p>
  
  <p>Any help is appreciated.</p>
  
  <p>am using the following code</p>
  
  <pre><code>&lt;?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();
  }
  ?&gt;
  </code></pre>
  
  <hr>
  
  <pre><code>&lt;?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 &gt; 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();
      }
  }
  ?&gt;
  </code></pre>