Update DB with only fields that have changed

James0816

Is there an easy way to update a record with only the information that has changed? for example if my table has 10 fields and I display all ten fields on the page but only actually change two fields......I only need to update those two fields. To accomplish this, I can only think of individual "if" statements to test the variable against what is in the recordset. This could get quite ugly if the the table contains many fields. Luckily my tables are small. Just trying to find out an easier way of doing it.

Thanks,

Pig

First off, it should be said that the easiest way would be to just run the update, and not care about what has changed. There is no loss or detriment if you update a field with the same value it already had.

Anyway, the trick is to use arrays. query the database to get all the old values, and then load them into an array. Then loop through all the $_POST data and compare based on keys (you would have to have the form field names be the same as the database field names).

Hope that made sense.

mourisj

Why would you want to do that? If anyway the data are not changed, an update does not matter.

The "intelligence" you have to develop to achieve this will use more resources (i.e. spend more time to achieve, increase CPU load, memory, ...) than a complete update of your data will.

Could you explain in more detail why you want to do this?

Have a nice day,

JJ Mouris

James0816

yes....it would just be much easier to update everything in one lump sum. The reason that I was doing it that way is that I have several fields encrypted for security reasons of course. After testing the page out a few times, i noticed that the encrypted fields were somehow getting thrown off and after looking at the data in mysql using the aes_decrypt, they still showed as encrypted. I know it sounds strange but that is what happened....and yes i made sure i used the same key to decrypt. is very weird.

mourisj

What about setting a hidden field with an onUpdate() JavaScript function for the fields whit which you have your krypto-problem. Then update these fields separately if the hidden fields is set, and update all the rest in one single statement ?

Friendly,

JJ Mouris

Pig

Javascript is inherently flawed since it is client side. If this is important information, that would be a very bad idea. 11% of browsers do not have javascript enabled (at least that was the stats 5-6 months ago).

mourisj

What I meant was to flag the fields as "Modified", not do do any crypting/decrypting in JavaScript.

Anyway, if the information is "important" and is sent over an unsecure communication channel, the important data are exposed to anybody "listening" to it.