Skip to main content
N
Nessmuk
Inspiring
December 9, 2010
Question

NextnPrevious pages and database update

  • December 9, 2010
  • 3 replies
  • 966 views

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

    This topic has been closed for replies.

    3 replies

    M
    meensi
    Inspiring
    December 10, 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.

    N
    NessmukAuthor
    Inspiring
    December 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

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

    <!-- application with session variables and headersecure placed here -->

    <!-- Per above - Put code here for database insert - An "if this page comes from the submitted form on this page" -->

    <!-- The following is, more or less, ThisPage.cfm -->

    <!-- Start displaying with record 1 if not specified via url -->
    <CFPARAM name="start" default="1">

    <!-- Number of records to display on a page -->
    <CFPARAM name="disp" default="40">

    <CFSET SchoolNameDropdown_z=structNew() />

    <CFSET structAppend(#SchoolNameDropdown_z#, URL) />
    <CFSET structAppend(#SchoolNameDropdown_z#, Form) />

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

    <CFSET end=Start + disp>
    <CFIF start + disp GREATER THAN data.RecordCount>
      <CFSET end=999>
    <CFELSE>
      <CFSET end=disp>
    </CFIF>

    <head>
    <link href="global.css" rel="stylesheet" type="text/css" />

    </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>

    <CFOUTPUT>
    <!-- Display prev link -->
      <CFIF start NOT EQUAL 1>
      <CFIF start GTE disp>
        <CFSET prev=disp>
        <CFSET prevrec=start - disp>
      <CFELSE>
        <CFSET prev=start - 1>
        <CFSET prevrec=1>
      </CFIF>
      <a href="ThisPage.cfm?start=#prevrec#"><<< Previous #prev# </a>
      </cfif>
    <!-- Display next link -->
    <CFIF end LT data.RecordCount>
      <CFIF start + disp * 2 GTE data.RecordCount>
        <CFSET next=data.RecordCount - start - disp + 1>
      <CFELSE>
        <CFSET next=disp>
      </CFIF>   
        <a href="ThisPage.cfm?start=#Evaluate("start + disp")#">Next #next# >>> </a>
      </cfif>
    </CFOUTPUT>
    </BODY>
    </HTML>

    <!-- end -->

    Fernis
    Fernis
    Inspiring
    December 11, 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>

    <cfoutput>

    <script>
    #ToScript(myQuery,"jsMyQuery")#
    function changepage(pagevaluechange)
    {
        elem = document.getElementById('page');
        elemval = eval(elem.value);
        document.getElementById('page').value = elemval + pagevaluechange;
        ColdFusion.navigate('test.showrecords.cfm?startpage='+document.getElementById('page').value,'studentlistdiv',updatefields);
    }
    function updatefields()
    {
        startrow = (document.getElementById('page').value-1)*20+1;
        for (j = startrow; j <= startrow + 19; j++) {
            document.getElementById('prop3_'+j).value = jsMyQuery.getField(j-1,'prop3');
        }
    }
    </script>
    </head>
    <body onload="changepage(0)">
    <cfdiv id="studentlistdiv" bind="url:test.showrecords.cfm?startpage={pagingform:page}"></cfdiv>

    <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.

    Fernis
    Fernis
    Inspiring
    December 10, 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.

    Fernis
    Fernis
    Inspiring
    December 10, 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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices