PHP update via textboxes

Question

Right now the update only works if all textboxes are filled out, so the user can't just update productName for example. There isn't an error, but if the other textboxes are left blank then the database is updated with blanks and 0's. I want this to update whatever textboxes receive input, be it one or all, and leave the rest of the info alone if nothing is entered.

If productName for that row is Samsung, description is 'A phone' wholesalePrice is 179.99 and I just update the productName textbox only I still want the description and wholesalePrice to stay the same. Right now if I just update the productName only then the wholesalePrice shows as 0.00 and the description is blank. I tried using OR statements rather than commas in the query and whatever textbox I entered info in returned a 0.

if(isset($_POST['id'])) {
    try {
        $query = "UPDATE products SET productName = :productName, description = :description, wholesalePrice = :wholesalePrice,
    retailPrice = :retailPrice, category = :category, quantityOnHand = :quantityOnHand
    WHERE productID = :productID";
        $statement = $db->prepare($query);
        $statement->bindValue(':productID', $_POST['id']);
        $statement->bindValue(':productName', $productName);
        $statement->bindValue(':description', $description);
        $statement->bindValue(':wholesalePrice', $wholesalePrice);
        $statement->bindValue(':retailPrice', $retailPrice);
        $statement->bindValue(':category', $category);
        $statement->bindValue(':quantityOnHand', $quantityOnHand);
        $statement->execute();
        $statement->closeCursor();

//reload page after data is entered into the table and display a message if successful for 3 seconds before redirect
        $page = $_SERVER['PHP_SELF'];
        header('Location: ' . $_SERVER["HTTP_REFERER"] );
        exit;

Only update the columns if the value is not empty MySQL update column only if value not empty where — Mazaka
– Mazaka, Commented Nov 25, 2015 at 19:45

Kostas Mitsarakis · Accepted Answer · 2015-11-25 20:38:22Z

3

You can use a helper array for your columns to bind values dynamically if each $_POST value is set. Then you can create the update query for only those values.

$fields = array('productName', 'description', 'wholesalePrice', 'retailPrice', 'category', 'quantityOnHand');
$values = array();
$binds = array();

foreach($fields as $key => $value) {
    if (isset($_POST[$value])) {
        $values[] = $value.' = :'.$value;
        $binds[':'.$value] = $_POST[$value];
    }
}

if (!empty($values)) {
    $query = "UPDATE products SET ";
    $query .= implode(', ', $values);
    $query .= " WHERE productID = :productID";
    $binds[':productID'] = $_POST['id'];
    $statement = $db->prepare($query);
    $statement->execute($binds);
    $statement->closeCursor();
}

EDIT:

If you have the values stored in variables then you can use variable variables:

foreach($fields as $key => $value) {
    if (isset($$value)) {
        $values[] = $value.' = :'.$value;
        $binds[':'.$value] = $$value;
    }
}

edited Nov 25, 2015 at 20:38

answered Nov 25, 2015 at 19:51

Kostas Mitsarakis

4,7473 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kr4ckl3s Over a year ago

I'm not sure how that would work. The textboxes are filled out on another page. search.php -> update.php -> updateFunct.php -> update.php The data is obtained on search.php and it takes that specific row's data through the update link to the update.php page. The updateFunct.php handles the processing.

Kostas Mitsarakis Over a year ago

Then you can use variable variables. Check the updated answer.

Brian · Accepted Answer · 2015-11-25 20:02:24Z

0

You need to pass all existing values to form fields so existing data is passed to the sql update if nothing changes. On submit validate the data, then do the update.

<textarea name="description">'.$row['description'].'</textarea>

if(isset($_POST['id'])) {
  $productname = $_POST['productname'];
  $description = $_POST['description'];
  // etc ....
  try {
    // sql
  }catch{
    // error
  }
}

answered Nov 25, 2015 at 20:02

Brian

1,0357 silver badges14 bronze badges

4 Comments

tftd Over a year ago

You need to specify WHAT you're trying to catch. You can't just put catch{} and expect it to work. php.net/manual/en/…

Kr4ckl3s Over a year ago

The form is on a separate page. update.php contains the textboxes and updateFunct.php has the above code. The way the page works, a user can enter something into a search box like "Samsung" and the database lists all the info about every available Samsung found along with an update and delete button next to each row. When update is clicked it goes to the update form on which half displays the current info and the other half of the page allows input for updating. Once submit is clicked it updates and refreshes with the new input shown on the disabled half of the page.

Kr4ckl3s Over a year ago

The catch statement I have isn't the problem. It catches and handles errors. Nothing more.

Brian Over a year ago

My example is basic. It isn't meant to be a copy paste solution and anyone working with exceptions understands you need to catch the (Exception $e). @Kr4ckl3s You need to pass those fields on the form regardless. Never have any empty form field if data already exists. A user may think they can merely add to the existing via the blank form and or retype the whole thing.

Collectives™ on Stack Overflow

PHP update via textboxes

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related