Keeping track of updated values

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
iamsuperman
Forum Newbie
Posts: 1
Joined: Thu May 22, 2008 6:48 pm

Keeping track of updated values

Post by iamsuperman »

Dear all,

Hi, I have created a Book class which has the following structure:

class Book{
//some attributes
isbn
title
author
//database functions
insert()
update()
delete()
}


Imagine that there is a webpage for the users to edit a book record in the database.
The isbn is used as a primary key, therefore, only title and author can be edited by the users.
When a user clicks the update button, all values(isbn,title,author) would be POSTED to the server.
At the server side, these POSTED values can be retrieved.
I can think of two approaches which can persist the edited values to the database.

Approach1:
The POSTED isbn value is passed to the getBook($isbn) function of the DatabaseAccessor class which returns a book object with all its attributes values(isbn,title,author) loaded. As you know, this getBook($isbn) function should involve a SELECT statement. Then getter methods will be used to check whether a particular attribute value has been changed. For example:

Code: Select all

 If (book->getTitle() != $title)
{
book->setTitle($title);
$titleChanged=true; //titleChanged is a boolean value originally set to false,
}
Being able to know whether a value has been changed or not, inside the update() function, an update statement like this can therefore be generated with the help of boolean values for each attribute:

Code: Select all

UPDATE [book] SET title=$title WHERE isbn=$isbn;
Approach2:
It does not lookup the book object using the getBook($isbn) function.
All POSTED values are passed to the constructor to create a book object and then the update() function is called.
But this time, inside the update() function, since we do not know whether a particular value has been changed or not.
We have to use a universal UPDATE statement like this even if a user has only changed some but not all values of a book record:

Code: Select all

UPDATE [book] SET title=$title,author=$author WHERE isbn=$isbn;
The above statement is used when:
1. the user only changed the title value
2. the user only changed the author value
3. the user changed both title and author values (ideal case)
4. the user did not change anything but clicked the submit button

As you can see, approach1 involves an extra SELECT statement to lookup a book object, but then it can generate a tailor-made update statement because it knows which value(s) is/are changed.

Approach2 does not need that SELECT statement, but a universal UPDATE statement is used even if not all values are changed. This may also affect the performance because it may unneccessarily overwrite an unchanged value in the database.

So I would very much like to know which approach seems better to you.
Please feel free to give any comments.

Thanks so much.
Last edited by iamsuperman on Thu May 22, 2008 9:06 pm, edited 1 time in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Keeping track of updated values

Post by Christopher »

Just updated always is simpler. There are probably few times when someone with just hit update with all the same data.

There are some other alternatives though. You could create hidden inputs in the form to hold the original values and check against them to see if there has been a change. Alternatively you could store the original values in the session. Both avoid doing a select before updating.

I would ask if you actually have a performance problem that this extra code is solving? If not then just always update.
(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Keeping track of updated values

Post by califdon »

I agree with arborint. Unless you're trying to handle something that you didn't describe, I see no reason to "pile on" additional database or code operations, just do the update. Usually it makes no difference if anything or everthing or nothing was actually changed. If you have a particular reason that it does make a difference, please explain what that is.
Post Reply