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

Question

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

I’m with 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";
        }
?>

Which updates until it’s blank.

Answer 1

O Mysql does not update if the values are equal, 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
;

Supposing you make one:

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

He 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 line:

/* Registros afetados: 0 */

When to run a SELECT:

SELECT * FROM tabela WHERE id = 1

Will return:

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

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

If you run a name different and subsequently 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 set a different value than what was already in the database, this will update the date that visually lets you know that it worked.

Answer 2

You can make 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.";}

}