How to update only one column of the record?

Question

How to update only one column of the record?

Navigation

#1 by (1 votes)
#2 by (0 votes)

3

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

So I wanted to know: How do I update one column without affecting 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 sql-update

asked by anonymous 21.11.2018 / 20:23

2 answers

0

In a very simple way you can go by 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.

21.11.2018 / 20:38

Lambda in Python - Doubt Problems with font-face

score 1 · Accepted Answer

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

Comments in 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(); 

}