FRWD/BACK Only showing 6 records per page 1

[bluesauceuk]

Hiya

I have written a page that list all items - i would like only show six items per page

I reember the maxrows bit - but how do you do it the easiest way.

I want to show the first six items then forward and back it, except no forward on the last page and no before on the first.

Help


Cheers in advance


Mark ;-)

 

[GunJack]

Hey Mark,

You're going to have to obviously keep track of where you're at in the result set as you move between result pages. If you chose &quot;count&quot; for this variable, I would put a <cfparam name=&quot;count&quot; default=&quot;1&quot;> at the top which sets the starting record at 1. I would then put links back to the same page similar to this:

<cfif count gt 1>
<cfset prev=count - 6>
<cfif prev lt 1><cfset prev=1></cfif>
<a href=&quot;startPage.cfm?count=#prev#>Previous Page</a>
</cfif>
<cfif count lt ( query.recordcount - 5)>
<cfset next=count + 6>
<cfif next gte query.recordcount><cfset next=query.recordcount-1></cfif>
<a href=&quot;startPage.cfm?count=#next#>Next Page</a>
</cfif>

This will allow you to jump between result sets. To just show the six records, I would use a cfloop like this. I'm showing the cfparam and your main query as well.

<cfparam name=&quot;count&quot; default=&quot;1&quot;>
<cfquery name=&quot;query&quot; ....> select .......</cfquery>

<cfset start=count>
<cfif start lt 1> <cfset start=1></cfif>
<cfif start gt query.recordcount> <cfset start=query.recordcount></cfif>

<cfset end=count+5>
<cfif end gt query.recordcount> <cfset end=query.recordcount></cfif>

<cfloop from=#start# to=#end# query=&quot;query&quot;>

</cfloop>

This is how I usually do this but if someone knows a way to select records from a database with a starting and ending point, I'd love to hear it as my SQL knowledge is minimal. Also, I would recommend using a variable in place of all the &quot;6&quot;s and &quot;5&quot;s so you can easily adjust the number or records returned. IMO, hard coded values should be avoided.

GJ

 

[MarcDePoe]

GunJack..

I've never had to do this, but I would think that in a constantly changing database, the results of re-querying may be confusing. (if records were added in between browsing, it is possible to go forward or back and see the same 6 results) That is of course if I understand you correctly.

I would session the query on the 'first page' (whatever that might be) and use those results for the counting code you posted to insure no duplicates and run the query again only upon initialization of this ... whatever it is. (when the criteria changes)

if the database very seldomly changes, depending on how complex the criteria is in the query, you could use a unique field in the database to calculate starting points and throw LIMIT 6 in at the end of the query.

cheers!

 

[GunJack]

While your point is valid, I think you talking about the extreme case and not the norm. It's true that any changes in the database will potentially affect your positioning but trying to store an entire result set in a session variable is unwise IMO. Unless you have a very small result set, you'll be using a lot of memory to store the session variable. Since session variables are stored in the server's memory, you're putting the server at risk of low resources if traffic is moderate and/or the result set is large.

If you do cache the result set on the first page, you introduce another scenario where the visitor browses through a result set and sees records which may have been deleted and doesn't see records that have been added.

I think it would take a database that fluctuates rapidly for this to ever cause any real problem though. The method I outlined will also auto-adjusts itself for variances in the result set. This ensures you won't ever get an error like trying to display the last record if the result set suddenly became smaller.

If this were being used in a situation where the records changed rapidly and it was imperative that the order not get mixed, I would take steps to ensure the exact start record is maintained and take steps to compensate if that start record was deleted or if records were added, before, after it, etc... That type of solution would really be in the extreme case and what I was posting was for the average scenario.

Take care,
GJ

 

[MarcDePoe]

GunJack,
indeed you are wise