We help IT Professionals succeed at work.

view x through x of x total records  <---anandkp help!!!

jguy07
jguy07 asked
on
182 Views
Last Modified: 2013-12-24
anandkp,
             I have one more question in reguards to a thread you helped me with at https://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20869008.html

I'm trying to display a "viewing 1 - 15 0f 150 total records" type message at the end of the recordset paging you assisted me with.  I keep bombing the code logic.  Here's what I have right now, but I know it's wrong.  Could you pls advise?  TIA!!!!

<cfparam name="n_pgno" default="1"> <!---Default Page number--->
<cfparam name="StartRow" default="1"> <cfparam name="MaxRows" default="15">

<cfquery name="getresults"> </cfquery>

<CFSET TOT_PGNO = CEILING(getresults.RECORDCOUNT / MAXROWS)><!---anandkp helped here--->

          <CFIF StartRow GTE MaxRows>              
          <!---Condition For 15 recs--->
          <a href="jcifl-getissues.cfm?N_PGNO=#Evaluate(N_PGNO-1)#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate(StartRow - MaxRows)#">&lt;&lt;Previous</A>
          </CFIF>
          &nbsp;&nbsp;
          <!---Nav numbers here--->
          <CFLOOP index="i" from="1" to="#tot_pgno#">
               <A href="jcifl-getissues.cfm?N_PGNO=#N_PGNO#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate((MaxRows*(i-1))+1)#">#i#</A>&nbsp;
          </CFLOOP>
          <CFIF (StartRow + MaxRows) LTE getresults.recordCount>
          <A href="jcifl-getissues.cfm?N_PGNO=#Evaluate(N_PGNO+1)#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate(StartRow + MaxRows)#">Next&gt;&gt;</A>
          </CFIF>
          </TD>
     </TR>
      

<!---MY MESSED UP LOGIC THAT I NEED HELP WITH --->

        <CFIF StartRow GTE MaxRows><tr><td>&nbsp;</td></tr><tr><td class="apptext">Viewing <strong>#startrow# -
        <cfset var1 = #Evaluate(getresults.RECORDCOUNT - MaxRows)#>
        <cfset var2 = #Evaluate(startrow - 1)#> #Evaluate(var1 + var2)#</strong> of <strong>#getresults.RECORDCOUNT#</strong> total records</td></tr>
        </CFIF>
        <CFIF StartRow LTE MaxRows><tr><td>&nbsp;</td></tr><tr><td class="apptext">Viewing <strong>#startrow# -
        #Evaluate(StartRow + (MaxRows - 1))#</strong> of <strong>#getresults.RECORDCOUNT#</strong> total records</td></tr>
        </CFIF>

<!---END MY MESSUP--->       
Comment
Watch Question

Commented:
hi jguy07,

Viewing #StartRow# - #StartRow+Maxrows# of #getresults.recordCount#

Commented:
or a better one wld be

Viewing #StartRow# - #n_pgno*Maxrows# of #getresults.recordCount#

Author

Commented:
anand,
         I can't get that to work.  Could I just show what page is being displayed?  (e.g. viewing page 2 of 15)  If so, how?  TIA!!

J.

Commented:
Viewing #StartRow# - #Evaluate(n_pgno*Maxrows)# of #getresults.recordCount# shld definately work !

let me know what ur getting with this ...

Author

Commented:
anand,
          Thanks for the quick reply.  However, I'm still getting the same output.  Here's some examples:

When I get the first page, all looks great!
Example: Viewing 1 - 15 of 161

However, when the pages climb, the Viewing of (x) climbs properly, but the (x) after the - isn't.  It's staying at 15.
Example:  click 2
Viewing 16 - 15 of 161

Example: click 4
Viewing 31 - 15 of 161

etc.

It's like it's stuck on #maxrows# value.

J.

Commented:
Feel free to pick and choose what ya need from this...I threw it together pretty quick..  Also, you may want to put in a stopper so once you get to the last page, you dont link for more beyond the scope of your query.

<cfif #isdefined('startrow')# IS FALSE><cfset #startrow# = 1></cfif>
<cfquery name="foo" datasource="books">
SELECT      *
FROM      books
</cfquery>

<cfif #startrow# + 15 GT #foo.recordcount#>
<cfset #maxrows# = #foo.recordcount# - #startrow#>
<cfelse>
<cfset #maxrows#=15>
</cfif>

<cfoutput>startrow - #startrow#....maxrows - #maxrows#<br></cfoutput>

<cfoutput query="foo" startrow=#startrow# maxrows=#maxrows#>
#coursecode#<br>
</cfoutput>

<cfoutput>
<cfset #endrow# = #startrow# + #maxrows#>
viewing results #startrow# - #endrow# of #foo.recordcount#<br><br>
<a href="counter.cfm?startrow=#endrow#">go</a>
</cfoutput>

Commented:
Sorry...hosed out the numbering just a tad...heres the fixed code:

<cfif #isdefined('startrow')# IS FALSE><cfset #startrow# = 1></cfif>
<cfquery name="foo" datasource="books">
SELECT      *
FROM      books
</cfquery>

<cfif #startrow# + 15 GT #foo.recordcount#>
<cfset #maxrows# = (#foo.recordcount# - #startrow#)+1>
<cfelse>
<cfset #maxrows#=15>
</cfif>

<cfoutput>startrow - #startrow#....maxrows - #maxrows#<br></cfoutput>

<cfoutput query="foo" startrow=#startrow# maxrows=#maxrows#>
#coursecode#<br>
</cfoutput>

<cfoutput>
<cfset #endrow# = #startrow# + #maxrows#>
viewing results #startrow# - #evaluate(endrow-1)# of #foo.recordcount#<br><br>
<a href="counter.cfm?startrow=#endrow#">go</a>
</cfoutput>
Here's a solution I've used that putls everyting into one neat rollup at the top of the page.

<CFSET CurrentPage=GetFileFromPath(GetTemplatePath())>
<CFPARAM NAME="PageNum_qryMyQuery" DEFAULT="1">
<CFSET MaxRows_qryMyQuery=15>
<CFSET StartRow_qryMyQuery=Min((PageNum_qryMyQuery-1)*MaxRows_qryMyQuery+1,Max(qryMyQuery.RecordCount,1))>
<CFSET EndRow_qryMyQuery=Min(StartRow_qryMyQuery+MaxRows_qryMyQuery-1,qryMyQuery.RecordCount)>
<CFSET TotalPages_qryMyQuery=Ceiling(qryMyQuery.RecordCount/MaxRows_qryMyQuery)>
<CFSET QueryString_qryMyQuery=Iif(CGI.QUERY_STRING NEQ "",DE("&"&CGI.QUERY_STRING),DE(""))>
<CFSET tempPos=ListContainsNoCase(QueryString_qryMyQuery,"PageNum_qryMyQuery=","&")>
<CFIF tempPos NEQ 0>
  <CFSET QueryString_qryMyQuery=ListDeleteAt(QueryString_qryMyQuery,tempPos,"&")>
</CFIF>

And then for the output you can just use...

Displaying <CFOUTPUT>#StartRow_qryMyQuery#</CFOUTPUT>&nbsp; through <CFOUTPUT>#EndRow_qryMyQuery#</CFOUTPUT>&nbsp; of  <CFOUTPUT>#qryMyQuery.RecordCount#</CFOUTPUT>&nbsp; total records.

And for paging navigation...

            <CFIF PageNum_qryHotfiles GT 1>
                 <A HREF="<cfoutput>#CurrentPage#?PageNum_qryMyQuery=1#QueryString_qryMyQuery#</cfoutput>">&lt;&lt;
                  FIRST</A>
                 <A HREF="<cfoutput>#CurrentPage#?PageNum_qryMyQuery=#Max(DecrementValue(PageNum_qryMyQuery),1)##QueryString_qryMyQuery#</cfoutput>">&lt;
                  PREV</A>
            </CFIF>
            <CFIF PageNum_qryMyQuery LT TotalPages_qryMyQuery>
                 <A HREF="<cfoutput>#CurrentPage#?PageNum_qryMyQuery=#Min(IncrementValue(PageNum_qryMyQuery),TotalPages_qryMyQuery)##QueryString_qryMyQuery# </cfoutput>">NEXT &gt;</A>              
                <A HREF="<cfoutput>#CurrentPage#?PageNum_qryMyQuery=#TotalPages_qryMyQuery##QueryString_qryMyQuery#</cfoutput>">LAST
                  &gt;&gt;</A>
            </CFIF>

Works same but with some different effects... allows movement through entire query result set and you can adjust only the number of records in the MaxRows_qryMyQuery to adjust the page... so if you define that as a variable... you can allow the user to dynamically choose how many results they want displayed on a page and everything else stays the same.

:)
errata to my code (I changed the query name to protect the guilty... and missed one)... :P

In page navigation area... <CFIF PageNum_qryHotfiles GT 1> should read <CFIF PageNum_qryMyQuery GT 1>

And although this part is obvious... you'd wrap the repeating table rows, divs, or whatever your record output is with the following:

<CFOUTPUT QUERY="qryMyQuery " STARTROW="#StartRow_qryMyQuery #" MAXROWS="#MaxRows_qryMyQuery #">

.... Actual output of query data ...

</CFOUTPUT>

Commented:
jguy -  id ont understand how this can be possible

as if u notice my code - u'll see ive written Viewing #StartRow# - #Evaluate(n_pgno*Maxrows)# of #getresults.recordCount#

It is displaying (n_pgno*maxrows) - so it has to increment with the page ur viewing

can i see ur code pls ... i am sure there is some typo somewhere ... let me know ...

K'Rgds
Anand

Author

Commented:
Here's my code:

<cfparam name="n_pgno" default="1"> <!---Default Page number--->
<cfparam name="StartRow" default="1"> <cfparam name="MaxRows" default="15">

<cfquery name="getresults" datasource="myds">
SELECT blah blah
FROM blah;
</cfquery>

<!---Here's the Paging Table--->
<table align="center" width="720" border="0" align="center" cellpadding="0" cellspacing="0" class="apptext">
      
<CFSET TOT_PGNO = CEILING(getresults.RECORDCOUNT / MAXROWS)><!---From the DB--->
        
                <CFOUTPUT>
        <tr><td>&nbsp;</td></tr>
        <tr><td>&nbsp;</td></tr>
     <TR>
          <TD class="apptext">
          <CFIF StartRow GTE MaxRows>              
          <!---Condition For 15 recs--->
          <a href="jcifl-getissues.cfm?N_PGNO=#Evaluate(N_PGNO-1)#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate(StartRow - MaxRows)#">&lt;&lt;Previous</A>
          </CFIF>
          &nbsp;
          <!---Nav numbers here--->
          <CFLOOP index="i" from="1" to="#tot_pgno#">
               <A href="jcifl-getissues.cfm?N_PGNO=#N_PGNO#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate((MaxRows*(i-1))+1)#">#i#</A>&nbsp;
          </CFLOOP>
          <CFIF (StartRow + MaxRows) LTE getresults.recordCount>
          <A href="jcifl-getissues.cfm?N_PGNO=#Evaluate(N_PGNO+1)#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate(StartRow + MaxRows)#">Next&gt;&gt;</A>
          </CFIF>
          </TD>
     </TR>
      <tr><td>Viewing #StartRow# - #Evaluate(n_pgno*Maxrows)# of #getresults.recordCount#</td></tr>
       <tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr>
      <tr><td><a href="search.cfm">&lt;&lt;Search Again</a></td></tr>
               
</CFOUTPUT>
            

Author

Commented:
Anand,
           I think I see what's going on, but I don't quite know how to fix it.  Seems that n_pgno keeps sending the value of 1 each time the page loads.  When I comment the <cfparam name="n_pgno" default="1"> out the paging blows away because it can't determine the value of #n_pgno#.  It's doing exactly like we're telling it, multiply 1 * 15(Maxrows) each time the page loads.  I did a <cfdump> to view the #n_pgno# and it in fact does pass "1" each and every time.  

J.
The output isn't stepping properly for the page... in the link that displays the page number... try changing this:

<CFLOOP index="i" from="1" to="#tot_pgno#">
               <A href="jcifl-getissues.cfm?N_PGNO=#N_PGNO#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate((MaxRows*(i-1))+1)#">#i#</A>&nbsp;
</CFLOOP>

To this:

<CFLOOP index="i" from="1" to="#tot_pgno#">
               <A href="jcifl-getissues.cfm?N_PGNO=#Evaluate(N_PGNO+i)#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate((MaxRows*(i-1))+1)#">#i#</A>&nbsp;
          </CFLOOP>

Commented:
Hmmmmm i got confused with ur code ...

Since u mentioned this section was working fine earlier - i didnt take a careful look at it

<!---Nav numbers here--->
          <CFLOOP index="i" from="1" to="#tot_pgno#">
               <A href="jcifl-getissues.cfm?N_PGNO=#N_PGNO#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate((MaxRows*(i-1))+1)#">#i#</A>&nbsp;
          </CFLOOP>


shld be

<!---Nav numbers here--->
          <CFLOOP index="i" from="1" to="#tot_pgno#">
               <A href="jcifl-getissues.cfm?N_PGNO=#i#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate((MaxRows*(i-1))+1)#">#i#</A>&nbsp;
          </CFLOOP>


that shld do it

pls keep the rest of teh code [viewing ...] as given in my last post [02/06/2004 12:47PM IST]

K'Rgds
Anand

Author

Commented:
Anand,
         Seems we may be getting somewhere now, but I'm still having a problem.  When a full 15 records (val of maxrows) is displayed on the first page, the viewing x of x looks good.  When you click page two and if it's full, it displays properly.  Now, however, say a query only returns 1 row of data instead of 15,   I get this "Viewing 1 - 15 of 1".  Now say I have a query that returns a full 40 rows of data.  There are 3 pages (1,2,3).  1 shows great (viewing 1-15 of 40), 2 shows great (16-30 of 40), but, when I click next or page 3, I get "viewing 31 - 46 of 40" instead of 31 - 40 of 40.  

Here's the code:
<!---Nav numbers here--->
          <CFLOOP index="i" from="1" to="#tot_pgno#">
               <A href="jcifl-getissues.cfm?N_PGNO=#i#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate((MaxRows*(i-1))+1)#">#i#</A>&nbsp;
          </CFLOOP>
          <CFIF (StartRow + MaxRows) LTE getresults.recordCount>
          <A href="jcifl-getissues.cfm?N_PGNO=#Evaluate(N_PGNO+1)#&partnumber=#partnumber#&trackingnumber=#trackingnumber#&StartRow=#Evaluate(StartRow + MaxRows)#">Next&gt;&gt;</A>
          </CFIF>
          </TD>
     </TR>
       <tr><td>&nbsp;</td></tr>
       <tr><td>Viewing #StartRow# - #Evaluate(n_pgno*Maxrows)# of #getresults.recordCount#</td></tr>  

I'm going to raise the points on this question because you've been helping me soooo much!!  Thank you for being so kind.  

J.

Commented:
yeah - u'll need to put up a small condition there

Viewing #StartRow# -
<cfif query.recordcount LTE Maxrows>
     #Query.Recordcount#
<cfelse>
     <cfif Evaluate(n_pgno*Maxrows) GT Query.RecordCount>
            #Query.RecorCount#
     <cfelse>
            #MAxrows#
     </cfif>
</cfif>
of #getresults.recordCount#

hope this does it ... let me know incase ive missed out something !

Author

Commented:
anand,
             Now the 1st page and last page (assuming I have 3 pages of 15 records) work perfectly!  However, on page 2, it's saying: "Viewing 16 - 15 of 40".  Anything in between the 1st and last pg of 15 maxrows will show the proper startrow number, but the startrow# of x (x is always 15) each step up until you reach the last page.  Any ideas?

J.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Anand,
           You did it yet agian!  You rock!!!!!!!!!!!!

J.

Commented:
been busy off late ... so i cldnt test things before posting them so many times ... i hope u wld understand & excuse me for that ...
its never taken so many tries at the same things .... lucky got it for u in the end :)

cheers
Anand
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.