How to update data from a table only if the new values are different from the current values?

Navigation
1

How can I do an UPDATE only if the submitted data is different from the stored data?

I have the following: 

<?php

header('Content-type: application/json');
header('Access-Control-Allow-Origin: *');

include 'database.php';



$cod_oferta = $_GET['cod_oferta'];
//$cod_categoria_com = $_GET['cod_categoria_com'];
$titulo_promocao = utf8_decode($_GET['titulo_promocao']);
$descricao = utf8_decode($_GET['descricao']);
$igredientes = utf8_decode($_GET['igredientes']);
//$foto = $_GET['foto'];
$valor_sem_desconto = $_GET['valor_sem_desconto'];
$valor_com_desconto = $_GET['valor_com_desconto'];
$validade = $_GET['validade'];

$validade_date = strftime('%Y-%m-%d', strtotime($validade));

$estoque = $_GET['estoque'];
//$cod_fornecedor = $_GET['cod_fornecedor'];
//$cod_categoria = $_GET['cod_categoria'];
$imagem = $_GET['imagem'];
$desconto = $_GET['desconto'];


$query=" UPDATE cadastra_oferta SET titulo_promocao='$titulo_promocao', descricao='$descricao', foto='$imagem', valor_sem_desconto='$valor_sem_desconto', valor_com_desconto='$valor_com_desconto', desconto='$desconto', validade_oferta='$validade_date', igredientes='$igredientes', qtd_estoque='$estoque' 
WHERE cod_oferta='$cod_oferta' ";

        if($con->query($query) === TRUE)
        {
            echo "success";
        }
        else
        {
            echo "error";
        }
?>

It updates until it is empty.

    
asked by anonymous 12.05.2017 / 00:20

2 answers

2

You can do a SELECT before.

Ex: 

$verifica = mysql_query("SELECT cod_oferta, titulo FROM cadastra_oferta WHERE cod_oferta = '$cod_oferta' and titulo = '$titulo'") or die(mysql_error()); if(mysql_num_rows != 0){echo "Informações repetidas";} else {

$insere = mysql_query("INSERT INTO cadastra_oferta (cod_oferta, titulo) VALUES ('$cod_oferta', '$titulo')") or die(mysql_error());

if($insere) {echo "Sucesso ao cadastrar";} else {echo "Erro.";}

}
    
12.05.2017 / 00:32
4

MySQL does not update if the values are the same, according to the documentation itself:

  

If you set a column to the value it currently has, MySQL notices this and does not update it.

Link

For example: 

CREATE TABLE 'tabela' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'name' VARCHAR(255) NOT NULL DEFAULT '',
    'date' TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX 'ID_AUTO_INCREMENT' ('id')
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;

Assuming you make one: 

INSERT INTO tabela('name', 'date') VALUES ("Inkeliz", "1999-12-12 00:00:00")

It would save: 

1 | Inkeliz | 1999-12-12 00:00:00

If you run: 

UPDATE tabela SET name = 'Inkeliz' WHERE id = 1;

You can check that it will not affect any rows: 

/* Registros afetados: 0 */

When running a SELECT : 

SELECT * FROM tabela WHERE id = 1

Will return: 

1 | Inkeliz | 1999-12-12 00:00:00

Notice that it remains unchanged, not even date will be updated, remember that it has ON UPDATE CURRENT_TIMESTAMP . This is because the value of name saved is already equal to the value you want to update.

If you now run a name different then a SELECT : 

UPDATE tabela SET name = 'zileknI' WHERE id = 1;
SELECT * FROM tabela WHERE id = 1;

Will return: 

1 | zileknI | 2017-05-11 21:07:08

In this case, the UPDATE worked because it defined a different value from what was already in the database, this will update the date that visually allows to know that it worked.

    
12.05.2017 / 02:12
How to stop a run in Python? [duplicate] How to separate a digit from an integer?