CODEWITHSUNDEEP
phpmysqlforeign-keyssql-update
henry
henry

Reputation: 21

PHP, MySQL Table UPDATE issue

Am trying to UPDATE a innodb table "products" by using a form of which last column is subid(fk) reference to table "subcategory" subid(PK), but i only want to update the "products" table without making any changes to subid(fk) column in "products" table, here is my full code 

<?php   

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']);
// See if that product name is an identical match to another product in the system
$sql = mysql_query("UPDATE products SET CATALOG_NO='$catalog_no', PRODUCT_NAME='$product_name', PRICE='$price', COMPOSITION='$composition', SIZE='$size' WHERE PID='$pid'") or die(mysql_error());
header("location: inventory_list.php"); 
exit();  
}
?> 

<?php 
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 FROM products WHERE PID='$targetID' LIMIT 1") or die(mysql_error());
$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"];
    }
} else {
    echo "You dont have that product";
    exit();
}
}
?>

The form uses the following code

<form action="inventory_edit.php" enctype="multipart/form-data" name="myForm" id="myform" method="post">
<table width="90%" border="0" cellspacing="0" cellpadding="6">
  <tr>
    <td width="20%" align="right">Product Name</td>
    <td width="80%"><label>
      <input name="product_name" type="text" id="product_name" size="64" value="<?php echo $product_name; ?>" />
    </label></td>
  </tr>
  <tr>
    <td align="right">Product Price</td>
    <td><label>
      $
      <input name="price" type="text" id="price" size="12" value="<?php echo $price; ?>" />
    </label></td>
  </tr>
  <tr>
    <td align="right">Composition</td>
    <td><label>
      <textarea name="composition" id="composition" cols="64" rows="5"><?php echo $composition; ?></textarea>
    </label></td>
  </tr>
  <tr>
    <td align="right">Size</td>
    <td><label>
      <input type="text" name="size" id="size" value="<?php echo $size; ?>" />
    </label></td>
  </tr>      
  <tr>
    <td>&nbsp;</td>
    <td><label>
      <input name="thisID" type="hidden" value="<?php echo $targetID; ?>" />
      <input type="submit" name="button" id="button" value="Make Changes" />
    </label></td>
  </tr>
</table>
</form>

The form does nothing (it just refreshes the page), it does not UPDATE the table. How to solve this problem?

Here is my table structure: table name - "products" set to ON UPDATE and ON DELETE CASCADE

PID(PK) CATALOG_NO  PRODUCT_NAME PRICE COMPOSITION SIZE SUBCAT_ID(FK)
  1         bbp2         NO2      $45    1% NO     10ml      7

Upvotes: 0

Views: 1350

Answers (4)

Laurence Moroney
Laurence Moroney

Reputation: 1263

It's been a while since I did a mysql_query, but is this line correct? 

$sql = mysql_query("UPDATE products SET CATALOG_NO='$catalog_no', PRODUCT_NAME='$product_name', PRICE='$price', COMPOSITION='$composition', SIZE='$size' WHERE PID='$pid'") or die(mysql_error());

THis looks like it is writing the SQL as this

"UPDATE products SET CATALOG_NO='$catalog_no'"

instead of the value of $catalog_no.

Should the line not be constructed like this:

"UPDATE products SET CATALOG_NO='" . $catalog_no . "', PRODUCT_NAME='" . $product_name . '"...

etc..?

Upvotes: 0

Marc B
Marc B

Reputation: 360702

Since the query doesn't seem to be doing anything, but isn't triggering an error condition (or you'd see it via the die() call, you should check to see exactly what the generated query string looks like:

$sql = "UPDATE products SET CATALOG_NO='$catalog_no', PRODUCT_NAME='$product_name', PRICE='$price', COMPOSITION='$composition', SIZE='$size' WHERE PID='$pid'";
$result = mysql_query($sql) or die(mysql_error());

echo $sql;

Just because the query call didn't do the die() doesn't mean the query is valid. Examine the generated query, try to run it manually, see what happens then.

Upvotes: 0

Priyabrata
Priyabrata

Reputation: 649

$pid = mysql_real_escape_string($_POST['thisPID']);

Here you have missed out the <input name="thisID" type="hidden">.It should be thisPID.

Upvotes: 1

user692487
user692487

Reputation: 1

Debug your code like this submit your form & check each value

// See if that product name is an identical match to another product in the system echo $sql = "UPDATE products SET CATALOG_NO='$catalog_no', PRODUCT_NAME='$product_name', PRICE='$price', COMPOSITION='$composition', SIZE='$size' WHERE PID='$pid'";

exit;

Upvotes: 0

Related Questions