CODEWITHSUNDEEP
phpmysql
newbieRB
newbieRB

Reputation: 133

Update MySQL db if value is changed and change another value

In my database I have a updateinfo with timestamp etc. which should updated every time the values has changes/update.

actually I check at first if there has changed value (old) and value (new) only if it is, I add the update value with new updateinfos.

Now I ask myself how I can do it easier and faster. Normally MySQL doesn't update something if the value has not changed. But what about my updateinfos. If I add them MySQL will update them every time. How can I do it that MySQL only sets the new updateinfos if there is really a changed value?

For Example

mysql SET a=1, b=2, Updateinfo='TIMESTAMP+IP'

What I looking for is a kind like this

mysql SET a=1, b=2, (Updateinfo='Timestamp+IP' ONLY IF a or b has updated)

So is there a way to add the field Updateinfo='Timestamp...' to the query if there is a change by one of the SET a=1, b=2?

Hope I could explain what I'm looking for.

Upvotes: -2

Views: 1820

Answers (2)

Oliver Sprites
Oliver Sprites

Reputation: 1

here my solution in php style

UPDATE software_config SET modifiedby=IF(STRCMP(main, '$main')=0 AND STRCMP(setting, '$setting')=0 AND STRCMP(value, '$value')=0, modifiedby, $new_value), main='$main', setting='$setting', value='$value' WHERE id=$id"

Upvotes: 0

Carl Binalla
Carl Binalla

Reputation: 5401

Something like this? It's only a pseudo-code, but you get the idea

run_query("UPDATE table SET a=1, b=2");
if(success == run_query){
    run_query("UPDATE table SET Updateinfo='Timestamp+IP' WHERE a=1 AND b=2");
} else {
    //some other things...
}

Revise #1

$a = 'somevalue';
$b = 'somevalue2';

$result = fetch -> run_query('SELECT a, b FROM table');
$existingA = $result["a"];
$existingB = $result["b"];

if(($existingA != $a) || ($existingB != $b)){
    run_query("UPDATE table SET a=$a, b=$b, Updateinfo='Timestamp+IP'");
}

Upvotes: 1

Related Questions