NextnPrevious pages and database update

Report · Dec 09, 2010

Hi Folks,

Am hoping someone can point me in the right direction on figuring this out...

I have 120 individuals in the database with related fields. Instead of displaying 120 rows when I pull the data from the database, I've built Next/Previous pages for displaying them 30 to a page.

Here's the thing: This is an admin section, pulling names and IDs and fields (text, radio buttons, dropdowns, etc.) for data entry, or rather, database update.

So, after displaying 30 on the page you have a link on the bottom of the page to display the next 30. This page refreshes itself...

Either I have to cache the update info from each page and put a submit button for updating on the last page or, submit each page (30 at a time) until each succeeding page is done, one at a time. See what I'm getting at? I think I'd rather be able to submit 30 at a time instead of trying to cache all the data as you move from page to page but am having a hard time trying to figure this out.

I'm not sure what is best or just how to do the database insert/update since the Next/Previous CF code depends on the same page refreshing itself each time for the next pages.

Also, the first page of this 'group of NextPrevious pages' is dependant on it's previous page of "select school", so that the first NextPrevious page (and following ones) displays the individuals from that particular school - 30 at a time.

Can anyone point me in the right direction on this kind of database insert/update when used in conjunction with Next/Previous page links?

Thanks!

- ed

Report · Dec 09, 2010

First of all, I'd advice you never take shortcuts when it comes to usability, i.e. the user experience.

What you _want_ is a submit button which will save all changes to the database, once you've edited all the records on multiple pages. That's what you basically said, and even if you hadn't, I'd convince you adopt the thought 😉

While I haven't actually ever pulled such a trick myself, a couple of ways came to my mind, of which I had to shoot most down after some thinking.

I'd definitely want to use ColdFusion's cflayoutarea for this, or iframes in emergency.

This will give you the generic idea (although you have to read after the steps, because this is NOT the way to go exactly)

1. Load up the list if user IDs from SQL.

2. Construct a copy of form fields of every record you're going to be able to edit. Place them in a hidden div, and/or make the form fields of type "hidden".

3. Display the user records and form options normally in a layout container, and let user edit them.

4. Make onchange for every form field to trigger a javascript function which copies the content of the form field into the hidden copy of the same field.

5. When submitting, post the hidden form.

6. Parse and analyze which form fields have been changed, and update the data accordingly for only those records and fields.

Cons: If you end up having Many user records, this is a nightmare on client side - a document structure with thousands of extra form fields is a no-no.

So, I'd go like stated above, but finding out a way Not to copy the form fields anywhere.

Instead, I'd use either one big hidden textarea OR Javascript variable (string, structure or array) to track down the changes of the user forms, and track the changes in that one variable.

Finally, I'd post the one javascript array/structure/textarea, and parse the content server-side, and update the changed fields accordingly.

You would have to do a bit of brainwork for this to work properly, I admit, but it shouldn't be rocket science. Just a lot of thinking and testing. How you form the temporary data which holds the form changes, is yet another matter. For example, if user changes one form field value several times, how do you check that the temporary data is updated correctly, etc. We're talking some serious javascript coding here too. jQuery or similar frameworks could help you here considerably. You could easily extend/update a javascript object, and finally at form submit, convert that to text and submit the changes.

Any way you decide, client-side javascripting and using layoutarea for prev/next page is what I'd do.

Never let coding challenges affect your GUI usability/functionality.

-Fernis

Report · Dec 09, 2010

Another way that came to my mind:

Hate javascript? Just fetch all the user records at once, and display them in groups of 30 by switching <div> visibility. That is, arrange them in groups of 30 in a number of divs, or use some javascript tabbing gadget to organize the result.

Someone might argue that using a session variable on the background would solve this problem more easily. This'll work too, if you're ok submitting the records in groups of 30... but then you'd have a submit button on each page separately, and man, I hate that idea.

With cfajaxproxy you could also probably update a session variables in the background whenever any form field onchange() triggers, but having CF do a server-side process each time a client-side form field is touched... I'm not a fan of that idea either - really bad...

Yet another way is to re-think your GUI. Could you just click a record, and side-bar (cflayoutarea with bind attribute) displays the form for the userid, which you can edit and submit manually if you want? Of course, if this increases the amount of mouse clicks on a regularly executed GUI task, i.e., if most user records are always edited, it's not a wise option.

I'm interested in seeing what other say about this kind of GUI challenge.

Report · Dec 09, 2010

Hi,

I would suggest you to have a "Save" button instead of submit button in each page. When they click save, have this data temporarily in some tables. After the next page, next page.. at the end of your final page, have your "Submit" button.

This is my idea. Instead of having submit button in each page, we have only one "Submit" in the last page.

Report · Dec 10, 2010

Thank ya'll for the ideas...

You know, before posting this I googled and googled and was surprised to find hardly anything addressing this. I would have thought it would be a fairly common way to perform edits on a large number of table rows - when you only want to display, say, 30 per page instead of 1200 records on one web page for admin edits.

With Meensi's and Furnis' suggestions and, a bit more thought... I kept thinking... It's pretty much like a shopping cart - holding a variable(s) from page to page. I know there's many ways to do this but the problem here lies in the page refresh holding variables with each refresh.

Seems logical to update All edits at one time - submit the form on the last page. I'm wondering, say as in the case you may have 1200 NextPrevious pages (!) - You'd want the user to do it in bits, page per page.

Since a clicked link clears session variables... maybe make the page refresh link (Next Page) a submit button and, on refresh, submit just "this particular page variables" and at the same time, the refresh loads the Next Page variables - updating the database in increments and display the next batch to edit.(?)

Does this make sense? I do want to make this a 'no-brainer' for the user - an admin section being most intuative is foremost to me...

The following is (stripped down) code I'm using to run the NextPrevious pages. I think maybe if I add the database insert somewhere in the code at the top of the page, coming from a form submit button that refreshes the page at the same time - maybe this would be the way to do it.

I'm not sure if there will be a problem wrapping the <form></form> around just the current variables on This Page. Maybe not. I think I may experiment with this, though...

Thank for your input on this.

Does seem like something like this, updates within NextPrevious pages would be a common practice and an easy fix doesn't it?

- ed

***************

<CFPARAM name="start" default="1">

<CFPARAM name="disp" default="40">

<cfquery DATASOURCE="#application.dsn#" name="search">
etcetera
</cfquery>

</head>

<BODY>
<html>
        <cfset bgcolor = background_table_color_1>
<cfoutput query="search" startrow="#start#" maxrows="#end#">
   <cfif bgcolor eq background_table_color_2>
    <cfset bgcolor = background_table_color_1>
   <cfelse>
    <cfset bgcolor = background_table_color_2>
   </cfif>

      #TRIM(variable01)#
      #TRIM(variable02)#
        <input type="radio" name="Consent" value="Yes" <cfif Consent IS "Yes">checked</cfif>> Yes
        <input type="radio" name="Consent" value="No" <cfif Consent IS "No">checked</cfif>>No
        <input type="radio" name="Assent" value="Yes" <cfif Consent IS "Yes">checked</cfif>> Yes
        <input type="radio" name="Assent" value="No" <cfif Assent IS "No">checked</cfif>>No
        </cfoutput>

Report · Dec 10, 2010

This was quite an interesting challenge, so I thought I'd really figure out how I'd do it.

I'm repeating myself again and again, but I always make GUI dictate how the technolgy is implemented, not vice versa.

Imho it is WRONG to make a GUI like this: User edits values, saves, goes to next page, saves, goes to next page, etc.

Even worse is this: User edits values, applies, goes to next page, applies, finally saves "applied" changes.

Multi-page implementation should not increase the complexity of the gui in any way. Also, any GUI buttons such as "submit" and "back" have to be _always_ visible in STATIC positions, not scrolling with the editable content. This is a thumb rule I also have. Scrolling back/save buttons is bad user interface design - ok for public pages, but not for professional administrative interface.

Thus, I made a nearly-half functional example of how I would do this:

File: TEST.CFM

--------------------------------

<cfajaximport tags="cfdiv,cfform">
<cfscript>
recordcount = 1200;
myQuery = queryNew("studentId,prop1,prop2,prop3","integer,varchar,varchar,varchar");
QueryAddRow(myQuery,recordcount);
for (i = 1; i lt 1200; i++)
{
    querySetCell(myQuery,'studentId',i,i);
    querySetCell(myQuery,'prop1',1,i);
    querySetCell(myQuery,'prop2',0,i);
    querySetCell(myQuery,'prop3','property#i#',i);
}
</cfscript>
<cfset session.myQuery = myQuery>

<html>
<head>
<script type="text/javascript" src="/CFIDE/scripts/wddx.js"> </script>
<style type="text/css">
#studentlistdiv {width:700px;height:500px;border:1px inset gray;overflow:auto;}
#studentlisttable TD {padding:0px 5px 0px 5px;}
</style>

<cfform name="pagingform">
    <a href="javascript:changepage(-1)">prev page</a>
    <cfinput type="text" name="page" id="page" value="1" size="3">
    <a href="javascript:changepage(1)">next page</a>
    <a href="##">submit</a>
    <cfinput type="hidden" name="other"><br>
</cfform>

</body>
</html>
</cfoutput>

---------------------------

FILE: test.showrecords.cfm

---------------------------

<cfparam name="startpage" default="1">
<cfform name="myform">
<table border="0" cellpadding="0" cellspacing="0" id="studentlisttable">
<tr><td>Student Id</td>
    <td>Prop 1</td>
    <td>Prop 2</td>
    <td>Prop 3</td>
</tr>
<cfset startrow = (startpage-1)*20+1>
<cfoutput query="session.myQuery" startrow="#startrow#" maxrows="20">
<tr><td>#studentid#</td>
    <td><cfinput id="prop1_#studentid#" name="prop1_#studentid#" type="checkbox"></td>
    <td><cfinput id="prop2_#studentid#" name="prop2_#studentid#" type="checkbox"></td>
    <td><cfinput id="prop3_#studentid#" name="prop3_#studentid#" type="text"></td>
</cfoutput>
</table>
</cfform>

I'll explain that a bit more in my next reply.

Report · Dec 10, 2010

First, I'm just generating a dumb query object and assigning it into a session variable. In the final version, we wouldn't need even that.

Soon after that, I'm converting the query object into a WDDX Javascript object with ToScript().

Next up are javascript functions which change the listing page, and populate the form fields on each listing page.

Note that the javscript function is a stub, and only updates the text field "prop3". It doesn't touch the checkboxes.

I've also taken a shortcut, having the studentid being same as the currentrow of the query.

Next up, we define the CFDIV which outputs the listing pages. The bind attribute does not work when we're changing the page value dynamically with javascript, but it works the first time when the page loads. There's probably a way/hack to make this work better.

Each time we change the page, Javascript function updates the page listing, and uses getField() (in wddx.js) to retrieve the form field values.

What's NOT included in the example:

I didn't code the way we update the WDDX object values when you change the form fields. You will need an onchange trigger for each form field, which calls a javascript function, passing its form field name and value. The function then parses the row and column from the form field, and updates the appropriate WDDX object's row and column. You could probably also update the WDDX object right before the list page changes, going through every form field - that way you wouldn't need to declare onchange trigger for each form field (might be better for client-side speed).

I also didn't investigate the exact method how to save the WDDX object into database. It certainly has to do something with WDDXSerializer, WDDXRecordset objec or CFWDDX tag. Look at those in ColdFusion helps.

About the second file (test.showrecords.cfm)

This page shows up n rows of the query, which is stored in a session variable. In a final version, we would just probably render the whole output with Javascript, using the WDDX object we already have. But it was quicker to code it this way. 🙂

----

Hope this helps (and not only confuses) a bit further 😉

-Fernis

There's a typo (extra space in "varchar") in the code example, I can't edit that article myself any more.