Updates involving long list of records

I am displaying a long list of records from a mysql database in a kind
of spreadsheet format with records making up the rows and fields the
columns. The reason for this is that users have a need to scroll
through a lot of records and edit a thing here or there in a text
field, checkbox, etc.

The way I have it set up is that when they are through, they perform a
submit and all records are updated. There are some problems with
this, including the fact that if someone gets to this page in a way
that no data is displayed and hits update, they could erase the whole
database.

Is there any way to update only the records that have been changed?
As this is kind of a client side thing, I'm suspecting that javascript
may be the tool here; I know that it would be possible using AJAX.
But I'm wondering if there is some way to only select the $_POST
elements that have been changed. I can think of some complicated ways
like storing the orignal data in session variables and comparing the
new POST data with the old session data, or having the user check a
box next to the changed records (they could easily miss a record and
lose changes) or check a set of records and move them to a new page
where they alone are updated (this is less convenient for the user).
I'm wondering if there is any simpler, more elegant way.

Any ideas?

Thanks,

--Kenoli

Re: Updates involving long list of records

On 8 Mar 2007 19:04:22 -0800, "Kenoli" wrote:

>I am displaying a long list of records from a mysql database in a kind
>of spreadsheet format with records making up the rows and fields the
>columns. The reason for this is that users have a need to scroll
>through a lot of records and edit a thing here or there in a text
>field, checkbox, etc.
How many records?

How often does this happen?
>
>The way I have it set up is that when they are through, they perform a
>submit and all records are updated. There are some problems with
>this, including the fact that if someone gets to this page in a way
>that no data is displayed and hits update, they could erase the whole
>database.
I don't understand how that could happen... "someone gets to thispage
in a way that no data is dispayed"???
>
>Is there any way to update only the records that have been changed?
>As this is kind of a client side thing, I'm suspecting that javascript
>may be the tool here; I know that it would be possible using AJAX.
>But I'm wondering if there is some way to only select the $_POST
>elements that have been changed. I can think of some complicated ways
>like storing the orignal data in session variables and comparing the
>new POST data with the old session data, or having the user check a
>box next to the changed records (they could easily miss a record and
>lose changes) or check a set of records and move them to a new page
>where they alone are updated (this is less convenient for the user).
>I'm wondering if there is any simpler, more elegant way.
I do this all the time in the various databases I run -
1 - display all the records, allowing inspection for errorneous data,
2 - let the user choose which record needs updated by clicking on an
id number (or such), which then...
3 - bring the selected record(s) to screen in a web form which
displays all the data and allows correcting of any and every field,
4 - store the result back in the table, allowing either the edited
record to overwrite the original or mking the visitor having to delete
the original record manually.

Ok, so it's a bit naive and a bit more laboured than your way, but it
doesn't allow the nightmare of anybody deleting all the records in
one fell swoop(!).

Re: Updates involving long list of records

Kenoli wrote:

> The way I have it set up is that when they are through, they perform a
> submit and all records are updated. There are some problems with
> this, including the fact that if someone gets to this page in a way
> that no data is displayed and hits update, they could erase the whole
> database.

1. See to that you can't get to the page in a such way.
2. Check the data before committing anything, things that erase you could
ignore and then ask the user if they are sure about that on another page,
where the user can select yes or no, if selecting yes, first then delete.


> Is there any way to update only the records that have been changed?

You could make an update where you compare the md5 sums, for the whole row in
the database against the md5 fro the data you would be updating.

Using javascript, you could have a hidden filed which you set if a value has
been updated for a row, then on submit you could select to remove those fields
not changed from be sent, or use the hidden field in the php to not use those
unset in your update statement. Javascript always requires that the client has
it enabled, today you can't count on it as javascript can be used to bad
thing, so there are quite many who disables it.


--

//Aho

Re: Updates involving long list of records

On Mar 8, 11:53 pm, fox.whis...@virgin.net wrote:
> On 8 Mar 2007 19:04:22 -0800, "Kenoli" wrote:
>

>
> How many records?

In this case, not many, only about 400.

>
> How often does this happen?

This is the main thing it it used for, to go down the list and check
checkboxes that say things like received such and such a response,
inquiry sent, etc. The user needs to go through and change things
like this on maybe 20 records our of the 400.

>
> >The way I have it set up is that when they are through, they perform a
> >submit and all records are updated. There are some problems with
> >this, including the fact that if someone gets to this page in a way
> >that no data is displayed and hits update, they could erase the whole
> >database.
>
> I don't understand how that could happen... "someone gets to thispage
> in a way that no data is dispayed"???

If the user has done some other stuff and then gets there using
browser history, I was able to reproduce a way of getting there that
had no data in the records.
>
> I do this all the time in the various databases I run -
> 1 - display all the records, allowing inspection for errorneous data,
> 2 - let the user choose which record needs updated by clicking on an
> id number (or such), which then...
> 3 - bring the selected record(s) to screen in a web form which
> displays all the data and allows correcting of any and every field,
> 4 - store the result back in the table, allowing either the edited
> record to overwrite the original or mking the visitor having to delete
> the original record manually.

Yeah, I do this all the time, too, in fact this script has the
funcationality to do this when the user needs to upgrade fields that
don't show up in the list view. You can see that if they are
upgrading many records it would be convenient to not have to keep
going back and forth between windows.

>
> Ok, so it's a bit naive and a bit more laboured than your way, but it
> doesn't allow the nightmare of anybody deleting all the records in
> one fell swoop(!).

Absolutely!

Re: Updates involving long list of records

On 9 Mar 2007 16:48:24 -0800, "Kenoli" wrote:

>On Mar 8, 11:53 pm, fox.whis...@virgin.net wrote:
>> On 8 Mar 2007 19:04:22 -0800, "Kenoli" wrote:
>>
>
>>
>> How many records?
>
>In this case, not many, only about 400.
>
>>
>> How often does this happen?
>
>This is the main thing it it used for, to go down the list and check
>checkboxes that say things like received such and such a response,
>inquiry sent, etc. The user needs to go through and change things
>like this on maybe 20 records our of the 400.
>
>>
>> >The way I have it set up is that when they are through, they perform a
>> >submit and all records are updated. There are some problems with
>> >this, including the fact that if someone gets to this page in a way
>> >that no data is displayed and hits update, they could erase the whole
>> >database.
>>
>> I don't understand how that could happen... "someone gets to thispage
>> in a way that no data is dispayed"???
>
>If the user has done some other stuff and then gets there using
>browser history, I was able to reproduce a way of getting there that
>had no data in the records.
>>
>> I do this all the time in the various databases I run -
>> 1 - display all the records, allowing inspection for errorneous data,
>> 2 - let the user choose which record needs updated by clicking on an
>> id number (or such), which then...
>> 3 - bring the selected record(s) to screen in a web form which
>> displays all the data and allows correcting of any and every field,
>> 4 - store the result back in the table, allowing either the edited
>> record to overwrite the original or mking the visitor having to delete
>> the original record manually.
>
>Yeah, I do this all the time, too, in fact this script has the
>funcationality to do this when the user needs to upgrade fields that
>don't show up in the list view. You can see that if they are
>upgrading many records it would be convenient to not have to keep
>going back and forth between windows.
>
>>
>> Ok, so it's a bit naive and a bit more laboured than your way, but it
>> doesn't allow the nightmare of anybody deleting all the records in
>> one fell swoop(!).
>
>Absolutely!
>

Well, there's your answer - take the option you've already given
yourself.

Re: Updates involving long list of records

On Mar 10, 5:25 am, fox.whis...@virgin.net wrote:

> Well, there's your answer - take the option you've already given
> yourself.

Always a good recommendation, though it is also good to get some
confirmation now and then. I appreciate, in particular, help I have
gotten from this list to see things in new ways.

--Kenoli