Argthtjtr

I have this code to update a record from my database, usually i will update only one column, example: on the array i have 6 columns listed, i want to only update the column keywords, and the other columns should not me affected.

With this code i can update my records but not a especifc column, because if i write something on the input name="keywords" and writte nothing on the other inputs, the column values from these input will be replaced with none values on my database.

My question is:

1. 
   

   How to updade a column without affecting the others?

   
   
   

   if(isset($_POST["updateBTN"])){    
   
     $insert_data = array(
   
   
   
       ':title'            => $_POST['title'],
   
       ':keywords'         => $_POST['keywords'],
   
       ':img'              => $_POST['img'],
   
       ':widht'            => $_POST['widht'],
   
       ':status'           => $_POST['status'],
   
       ':name'             => $_POST['name'],
   
       ':height'           => $_POST['height']
   
   
   
     );
   
   
   
   $query = "UPDATE table SET keywords = :keywords, img = :img, widht = :widht, status = :status, name = :name, height = :height WHERE title = :title";
   
   $statement = $conn->prepare($query);
   
   $statement->execute($insert_data);
   
   
   
   }
   
   

   
   

html:

<form  method="post">

<div>

    <input type="text" name="title"> 

    <span data-placeholder="Title"></span>          

</div>

<div>

    <input type="text" name="keywords"> 

    <span data-placeholder="keywords"></span>          

</div>

.

.

.

<button type="submit" name="updateBTN">Send</button>

</form>

You can either change your SQL query to only update the columns for which you have data, or populate any undefined values in $insert_data using values retrieved from the database. The first is probably a better idea, but the second is easier to implement.

I haven't written PHP in a bit, but maybe something like:

    if(isset($_POST["updateBTN"])) {

      $query = "SELECT title, keywords, img, widht, staus, name, height FROM table WHERE title = :title";

      $stmt = $conn->prepare($query);

      $stmt->execute(array(":title" => $_POST['title']));

      $row = $stmt->fetch(PDO::FETCH_ASSOC);



      $insert_data = array(

        ':title'            => $_POST['title'] ? $_POST['title'] : $row['title'],

        ':keywords'         => $_POST['keywords'] ? $_POST['keywords'] : $row['keywords'],

        ...

      );



      $query = "UPDATE table SET keywords = :keywords, img = :img, widht = :widht, status = :status, name = :name, height = :height WHERE title = :title";

      $statement = $conn->prepare($query);

      $statement->execute($insert_data);

    }

Assuming you don't want to set any of the columns to NULL values, you can use COALESCE():

UPDATE table

    SET keywords = COALESCE(:keywords, keywords),

        img = COALESCE(:img, img),

        widht = COALESCE(:widht, widht),

        status = COALESCE(:status, status),

        name = COALESCE(:name, name),

        height = COALESCE(:height height)

WHERE title = :title;

see if this works

UPDATE table

SET keywords = :keywords,

    img = CASE WHEN img IS NOT NULL THEN img ELSE :img END,

    widht = CASE WHEN widht IS NOT NULL THEN widt ELSE :widht END,

    status = CASE WHEN status IS NOT NULL THEN status ELSE :status END,

    name = CASE WHEN name IS NOT NULL name ELSE :name END,

    height = CASE WHEN height IS NOT NULL THEN height ELSE :height END

 WHERE title = :title;