How to update only one column of the record?

Asked

Viewed 188 times

2

I made a code to update some columns of my record when needed. There are 7 columns listed on array, but usually I will only update 1 or 2 columns. With this code I can update my record, but I have to put data in all the inputs, in case I could not write something on input name="keywords" and click on SUBMIT and leave the others inputs blank, because if I do this will only update the column keywords and leave the rest of the columns blank.

So I wanted to know: How do I update one 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>
<div>
    <input type="text" name="img"> 
    <span data-placeholder="img"></span>          
</div>
.
.
.
<button type="submit" name="updateBTN">Send</button>
</form>
php sql database form sql-update

  • I found a way, I don’t know if it’s the best way but it’s a way that with me turned round!!! :-)

  • LET ME ETENDER FRIEND, YOU WANT TO UPDATE ONLY THE COLUMN THAT WAS WRITTEN. IS IT ? Without having to fill everything. Just to fill in?

  • @Scratched and scratched Yes

  • LEO JA ANSWERED FRIEND. COPY HIS CODE!

  • 1

    Find it interesting with jquery can post on the reply. http://kithomepage.com/sos/foreach-colunas-valores-para-insert-jquery.php

2 answers

1


You have to mount the query according to the non-null values of the array, see how:

Comments on the code itself.

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

    $conn = new PDO('mysql:host=localhost;dbname=NOME_DB', 'USUARIO', 'SENHA', array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));

    $sets="";
    $title="";
    $keywords="";
    $img="";
    $widht="";
    $status="";
    $name="";
    $height="";

      $update = array(
        'title'            => $_POST['title'],
        'keywords'         => $_POST['keywords'],
        'img'              => $_POST['img'],
        'widht'            => $_POST['widht'],
        'status'           => $_POST['status'],
        'name'             => $_POST['name'],
        'height'           => $_POST['height']
      );

        //percorre o array
        foreach ($update as $column => $value) {

            //verifica se não há valores nulos
            if ($value!=""){
                //constroi a variável para usar na declaração UPDATE
                $sets .= $column." = :".$column.", ";

                /**********************************************
                    no caso dos names dos inputs iguais
                    aos nomes das colunas da tabela do banco
                    cria as variáveis com os names dos inputs
                ***********************************************/
                $$column=$value;

            }
        }

         //retira a ultima virgula
         $sets = rtrim($sets, ', ');

         //monta a query 
         $query = "UPDATE nomeTabela SET $sets WHERE title = :title";

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

    $statement->bindValue(":title", $title, PDO::PARAM_STR);

    if ($keywords!=""){
        $statement->bindValue(":keywords", $keywords, PDO::PARAM_STR);
    }   
    if ($img!=""){
        $statement->bindValue(":img", $img, PDO::PARAM_STR);
    }
    if ($widht!=""){
        $statement->bindValue(":widht", $widht, PDO::PARAM_STR);
    }
    if ($status!=""){
        $statement->bindValue(":status", $status, PDO::PARAM_STR);
    }
    if ($name!=""){
        $statement->bindValue(":name", $name, PDO::PARAM_STR);
    }
    if ($height!=""){
        $statement->bindValue(":height", $height, PDO::PARAM_STR);
    }

    $statement->execute(); 

}

  • Notice: Undefined variable: sets is pointing to $sets that’s inside the if. Will you forget something?

0

In a very simple way you can go checking your variables and concatenating in your SQL statement if it is not empty.

Example:

$query = "UPDATE TABELA SET ". (empty($variavel) ? "" : "CAMPO = $variavel") ." WHERE TITLEID = $TITLE";

I hope it helps.

Browser other questions tagged php sql database form sql-update

You are not signed in. Login or sign up in order to post.