Query updating all records rather than record which matches input?

Question

Apologies for vague title. I currently have an edit form in which a product can be selected and the details are displayed in a form where they can be edited. Unfortunately, when edited every product in the product table is edited rather than just the product selected. To select the product i'm using where productname = productnameinput etc etc (Code below)

Database: http://prnt.sc/f4tf5g (Before an edit)

Tried adding the following WHERE statements at the end of the update query:

UPDATE product SET CategoryID = :newCatId,  ProductName = :newProdName, ProductDescription = :newProdDesc, stockCount = :newStock WHERE product.ProductName = :prodname

UPDATE product SET CategoryID = :newCatId,  ProductName = :newProdName, ProductDescription = :newProdDesc, stockCount = :newStock WHERE ProductName = :prodname

PHP:

// edit product in database
$query="
  SELECT * FROM category
";
$result = $DBH->prepare($query);
$result->execute();
$categories = $result->fetchAll();
//we need to select all products frist
$query3 = "
  SELECT * FROM product
";
$result3 = $DBH->prepare($query3);
$result3->execute();
$allProducts = $result3->fetchAll();

//When the Product is selected this function is run
  if (isset($_POST['choose'])) {
    $query2 = "
      SELECT product.*, category.* FROM product LEFT JOIN category ON category.CategoryID = product.CategoryID WHERE product.ProductName = :prodname
    ";
    $result2 = $DBH->prepare($query2);
    $result2->bindParam(':prodname', $_POST['product_name']);
    $result2->execute();
    $product = $result2->fetch();
  }
  //When the Update button is Pressed
    if (isset($_POST['update'])) {
      $query4 = "
        UPDATE product SET CategoryID = :newCatId,  ProductName = :newProdName, ProductDescription = :newProdDesc, stockCount = :newStock WHERE product.ProductName = :prodname
      ";
      $result4 = $DBH->prepare($query4);
      $result4->bindParam(':newCatId', $_POST['newcategory']);
      $result4->bindParam(':newProdName', $_POST['productName']);
      $result4->bindParam(':newProdDesc', $_POST['productDescription']);
      $result4->bindParam(':newStock', $_POST['stockCount']);
      $result4->bindParam(':prodname', $_POST['product_name']);

      $result4->execute();

    }

Trying that now, thank you. Will update the question if unsuccessful but I think you're right — Xander, May 06 '17 at 15:48
@chris85 Pretty sure you're right but i'm still getting errors, I have updated my question. Should I include $post product name etc? — Xander, May 06 '17 at 15:54
What is the error, did you bind the new `where` column's value? — chris85, May 06 '17 at 15:59
@chris85 I have yeah, and now no error appears but the info isn't being updated, I forgot to add the bind into the question, i'll add it now (this is using WHERE product.ProductName = :prodname) — Xander, May 06 '17 at 16:07
http://php.net/manual/en/pdostatement.rowcount.php returns 0? It sounds like the name isn't matching, the `product.` shouldn't be needed. Do you have error reporting enabled, http://stackoverflow.com/a/32648423/4333555? — chris85, May 06 '17 at 16:19
@chris85 Error reporting is enabled, and I also tried removing .product but same result. I've added the rowCount you suggested but 0 being edited. — Xander, May 06 '17 at 16:27
Okay, so for some reason the value in the DB doesn't match the value you are passing. Try running just a `select` do you get results? — chris85, May 06 '17 at 16:35
@chris85 I've got it working now after playing around with the binds for a while, would you like to submit an answer detailing adding the where statement so I can accept it — Xander, May 06 '17 at 16:56
It seems like just a typo. I think you could just delete the question — chris85, May 06 '17 at 18:19
@chris85 Ah I tried but it wont let me becuase it has two answers — Xander, May 06 '17 at 19:06

score -1 · Answer 1 · answered May 06 '17 at 15:46

-1

You are updating entire table, there is not filter in your update

$query4 = "
    UPDATE product SET CategoryID = :newCatId,  ProductName = :newProdName, ProductDescription = :newProdDesc, stockCount = :newStock
  ";

answered May 06 '17 at 15:46

Fran Cerezo

940
3
8
19

Query updating all records rather than record which matches input?

1 Answers1