Mysql update of Empty columns only

Asked

Viewed 890 times

0

Is there any way to do an Update in mysql where only let update the data if the column is empty.

  • Imagine 4 radio input (the radios will be 1 at a time is not)

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

  • Ai What happens with input radio 1 ???? the post is empty consequently erasing the data in mysql and this I do not want to occur

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 column value1 and Valor3 there are inserted values, I would like a way to lock these values and not let change

php mysql
  • 2

    Check if this is not a XY problem. It may be that the answers are more necessary by asking the initial problem to be solved.

  • 3

    As @Acco said in their comment, you are looking for an incorrect solution to your problem. Mysql does not allow this type of lock. You should validate this with php itself on screen. be updating the information when saving or restructuring the modeling of your database that seems confusing to me

  • 4

    The update slightly improved the question, but revealed a problem: storing a radio input in 4 different columns is probably unnecessary. Another problem is that you don’t test in your code whether a post was sent or not. Another problem is that your code is an INSERT, it doesn’t even make sense to talk in empty columns. Insert always creates a new row. I would suggest going back a few steps in its development and fixing these things (mastering the concepts) before proceeding, because the further it advances the more confusing the code becomes, and will increasingly require patches.

  • With the modification, my answer becomes meaningless. And so is the problem itself. It seems to be more of a business logic problem. Something +- as @Ari commented.

3 answers

2

1) Do not use functions mysql() PHP. They are 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, or.

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

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

UPDATE dados SETvalue2= '$valor2',value4= '$valor4'

3) Radio-type input shall only be excluded (tag 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 make a query in 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 will have two more conditions: if valu1 is empty and 3 is not, and if Valor3 is empty and 1 is not.

2

Let’s rephrase your question:

I want to keep a data fixed and only change if there is a POST. That’s 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 only want to use the POST if the batch 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, what will not change.

Notes:

mysql_* is obsolete, whether it is learning or at the beginning of development start using mysqli (or PDO, but mysqli would be enough!).

There is 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"?!

  • 2

    Just a hint: the ternary makes it much more legible: $valor1 = isset( $_POST['valor1'] ) ? $_POST['valor1'] : $fixo['valor1']; http://answall.com/questions/56812/uso-de-e-em-php

2

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

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

Do a simple test trying to update with existing data and you can see that mysql_affected_rows() returns empty.

What could be done to optimize is simply to avoid sending data that has not been modified. For this, create some client-side control, with Javascript, for example. There are various techniques. You can 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 check of what was modified would be done. What is not modified, remove from the form and send only what has been previewed. The interesting thing is, if it appears that nothing has been modified, stop sending and display a message to the user saying that nothing has been modified and, for this reason, the data will not be sent.

This way you will avoid cost with data traffic, which is already great. And breaking, it still "relieves" Mysql of having to check whether a value already exists or not in that specific column.

Additional note, unrelated to question

Avoid disused functions. Change functions mysql_ for mysqli_

Browser other questions tagged php mysql

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