Mysql Update of empty columns only [closed]

0

There is some way to do an Update in mysql where you only let update the data if the column is empty.

  • Imagine 4 input radio (the radios are marking 1 at a time)

  • If a user (x) chooses radio 1 and saves, and user (y) chooses radio 2 and saves

  • What happens to the input radio 1 ???? the post comes empty consequently erasing the data in mysql and this I do not want to happen

Example

Data table

         __________________________________________
        | id |  valor1  | valor2 | valor3 | Valor4 | 
        |  1 |    20    |        |   20   |        |

    $valor1 = $_post['valor1']; //post vasio
    $valor2 = $_post['valor2']; //post vasio
    $valor3 = $_post['valor3']; //post vasio
    $valor4 = $_post['valor4']; //post vasio

    $sql = mysql_query ("INSERT INTO dados(valor1,valor2,valor3,valor4)
    VALUES('$valor1','$valor2','$valor3','$valor4',)", 
    $conexao) or die( mysql_error());

    se os post vierem vazios, vão apagar os dados que já estão na base

In the value1 column and value3 is entered values, I would like a way to lock those values and not to leave the

    
asked by anonymous 17.01.2016 / 01:01

3 answers

2

Let's rephrase your question:

I want to keep a fixed data and only change if there is a POST. Is that it?

Use something similar to this:

<?php

// Pega os  dados já existentes baseado no ID, que deve pelo menos haver alguma colisão com o usuário logado, se não nada faz sentido.

$fixo = mysql_query("SELECT * FROM dados WHERE id = '$id'");
$fixo = mysql_fetch_array($fixo);

// Se houver post
if($_POST['valor1']){$valor1 = $_POST['valor1'])else{$valor1 = $fixo['valor1']};
if($_POST['valor2']){$valor2 = $_POST['valor2'])else{$valor2 = $fixo['valor2']};
if($_POST['valor3']){$valor3 = $_POST['valor3'])else{$valor3 = $fixo['valor3']};
if($_POST['valor4']){$valor4 = $_POST['valor4'])else{$valor4 = $fixo['valor4']};

// Desta forma sempre irá ser o valor já existente como padrão, se houver um POST será o valor a ser inserido.

// Exemplo:
// Se houver POST valor1 de 100 será 100.
// Se não houver POST valor2 irá pegar o valor do banco de dados, assim irá mante-lo.

mysql_query ("UPDATE dados SET valor1 = $valor1, valor2 = $valor2, valor3 = $valor3, valor4 = $valor4 WHERE id = '$id'");
// Irá atualizar onde o id for  igual ao id.
// Se preferir utilize INSERT:
// mysql_query ("INSERT INTO dados(valor1,valor2,valor3,valor4)    VALUES('$valor1','$valor2','$valor3','$valor4')");
?>

If you just want to use the POST if the flock data is empty use:

if($fixo['valor1'] == ''){$valor1 = $_POST['valor1'])else{$valor1 = $fixo['valor1']};
if($fixo['valor2'] == ''){$valor2 = $_POST['valor2'])else{$valor2 = $fixo['valor2']};
if($fixo['valor3'] == ''){$valor3 = $_POST['valor3'])else{$valor3 = $fixo['valor3']};
if($fixo['valor4'] == ''){$valor4 = $_POST['valor4'])else{$valor4 = $fixo['valor4']};

This way when the query is formed it will be: SET valor1 = valor1 for example, which will not change.

Notes:

  

mysql_ * is obsolete, if you are learning or at the beginning of development start using mysqli (or PDO, but mysqli would suffice!).

     

There's no reason to have multiple columns for this type of input , in my opinion.

     

If you are inserting a new line (INSERT), how are you "deleting"?!

    
17.01.2016 / 08:03
2

By default, MySQL detects an existing value. If it finds, it does not modify it. So there is no need to do a SELECT to search where there are empty fields.

You can check this out on the return of mysql_affected_rows () .

Take a simple test trying to update with existing data and you will see mysql_affected_rows () returns empty.

What could be done to optimize is simply to avoid sending data that has not been modified. To do this, create some client-side control, with JavaScript, for example. There are several techniques. You may use cookies, for example. The idea is to save the original form values as soon as you enter the page. When the user submits the form, before submitting, a verification of what was modified will be done. Whatever is not modified, remove from the form and submit only what was anted. The interesting thing about this is, in case nothing is changed, interrupt the sending and display a message to the user saying that nothing has been modified and, therefore, the data will not be sent.

So you'll avoid cost with data traffic, which is great already. And by the way, it still "relieves" MySQL from having to check whether a value already exists or not in that particular column.

Additional note, not related to question

Avoid deprecated functions. Change mysql_ functions by mysqli_

    
17.01.2016 / 11:25
2

1) Do not use the mysql() functions of PHP. They have been obsolete since version 5.5.X and have been removed from PHP 7, so do not use them. Use mysqli .

2) If you do not want to enter the values, then do not send them to the database, either.

INSERT INTO dados(valor2, valor4) VALUES('$valor2','$valor4')

The same goes for UPDATE . If you do not want to change a value, do not change:

UPDATE dados SET value2 = '$valor2', value4 = '$valor4'

3) Radio-type input will only be exclusive (brand or one or the other) if it has the same name.

<form name="exemplo" action="pagina.php" method="POST">
  <!-- selecione um genero -->
  <p>Qual o seu gênero? </p>
  <input type="radio" name="genero" value="masculino"> Masculino<br>
  <input type="radio" name="genero" value="feminino"> Feminino<br><br>
  <!-- selecione somente um -->
  <p>Gosta de matemática? </p> 
  <input type="radio" name="math" value="sim"> Sim<br>
  <input type="radio" name="math" value="nao"> Não<br>
</form>
<!-- \ fim do form -->

4) You can query the database to check if the fields are empty:

<?php
//abra a conexao com banco de dados
$conexao = new mysqli('host', 'usuario', 'senha', 'banco_de_dados');

//faca uma consulta
$consulta = "SELECT 'valor1', 'valor3' FROM 'dados' WHERE 'id' = '$id' LIMIT 1";
//execute sua query
$executar = $conexao->query($consulta);
//retornar o resultado da execucao
while ($dados = $executar->mysqli_fetch_array()) {
    $valor1 = $dados['valor1'];
    $valor3 = $dados['valor3'];
}
//confere se os valores estao vazios
if($valor1 == '' AND $valor3 == ''){
    //atualiza os dois campos aqui
}

Obviously you have two more conditions: if the value1 is empty and the 3 is not and if the value3 is empty and the value is not 1.

    
17.01.2016 / 02:47