CODEWITHSUNDEEP
phpsql-update
Kr4ckl3s
Kr4ckl3s

Reputation: 45

PHP update via textboxes

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;

Upvotes: 4

Views: 1562

Answers (2)

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

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;
    }
}

Upvotes: 3

Brian
Brian

Reputation: 1025

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
  }
}

Upvotes: 0

Related Questions