Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

(gdemaria) I need to do pagination to my list

Posted on 2014-03-12
9
Medium Priority
?
211 Views
Last Modified: 2014-03-13
Hi,

I have a list of parent Issues and child Issues that I need to be able to display just 10 records on a time and do the pagination


<cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#'>
  SELECT case when ParentIssueID is null then IssueId
              else ParentIssueID
		 end  as TopIssueID
		 , IssueID
		 , ParentIssueID
		 , TicketNumberTxt
		 , TitleOfIssue
		 , Status
		 , DateCreated
   FROM LogEntry
  order by 1 desc, case when parentIssueID is null then 1 else 2 end
</cfquery>



<cfparam name="url.page" default="1">
<cfset page_links_shown = 5>
<cfset records_per_page = 10>

<cfset start_record = url.page * records_per_page - records_per_page>
<cfset total_pages = ceiling(QSearchResult.count/ records_per_page)>


<table>
<cfoutput query="QSearchResult">
<tr>
 <cfif val(QSearchResult.ParentIssueID)> 
   <td style="padding-left:20px;">
 <cfelse>
   <td> 
 </cfif>
   #QSearchResult.issueID#
 </td>
 <td>#QSearchResult.ticketNumberTxt#</td>
 <td>#QSearchResult.TitleOfIssue#</td>
 <td>#QSearchResult.Status#</td>
</tr>
</cfoutput>
</table>
  


<cfif url.page EQ 1>
   Prev Page
<cfelse>
   <a href="index.cfm?page=#url.page-1#">Prev Page</a>
</cfif>
<hr>


<cfif url.page * records_per_page LT QSearchResult.counts>
   <a href="pages.cfm?page=#url.page+1#">Next Page</a>
<cfelse>
   Next Page
</cfif>
<hr>

Open in new window

0
Comment
Question by:lulu50
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 39924393
A couple questions -

when counting the 10 records to show on a page.   Is that ten comprised of parent and child issues together?    Or are we counting only 10 parent issues and showing any number of child issues that may happen to belong?

I assume it is the first, that the total number of records will be 10.  But when you go to page two, you have to be aware that you may be showing a child issue with no parent on the same page.    For example, if you have a parent issue with 5 child issues, the parent issue could show as record 8 on the first page, then show 1 child issue on the first page, then on the second page would show just 3 child issues with no parent.   That is ok?

How many total records might there be?   For example, there may be about 100 total records which would be 10 pages of 10 records each?   Or are we talking thousands?    If the number if fairly small, we can do it the easy way.
0
 

Author Comment

by:lulu50
ID: 39926079
gdemaria,

I want to thank you first for all your help.

it's going to have thousands of records.

no, I can't have a child without a parent on the second page.  So,
I have to display 10 parent records regardless of how many child records it has.
0
 

Author Comment

by:lulu50
ID: 39926690
gdemaria,

where are you?

Did you forget about me today? :-(
0
How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

 
LVL 39

Expert Comment

by:gdemaria
ID: 39926778
sorry - doctor appointment :)

Check this one out - this is the straight-forward 10 records per page, I modified the start-record variable just a bit and added startRow and MaxRows to the cfoutput


<cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#'>
  SELECT case when ParentIssueID is null then IssueId
              else ParentIssueID
		 end  as TopIssueID
		 , IssueID
		 , ParentIssueID
		 , TicketNumberTxt
		 , TitleOfIssue
		 , Status
		 , DateCreated
   FROM LogEntry
  order by 1 desc, case when parentIssueID is null then 1 else 2 end
</cfquery>



<cfparam name="url.page" default="1">
<cfset page_links_shown = 5>
<cfset records_per_page = 10>

<cfset total_pages = ceiling(QSearchResult.count/ records_per_page)>

<cfif val(url.page) lt 1>
  <cfset url.page = 1>
<cfelseif val(url.page) gt total_pages>
  <cfset url.page = total_pages>
</cfif>
<cfset start_record = ((url.page - 1) * records_per_page ) + 1>


<table>
<cfoutput query="QSearchResult" startrow="#start_record#" maxrows="#records_per_page#">
<tr>
 <cfif val(QSearchResult.ParentIssueID)> 
   <td style="padding-left:20px;">
 <cfelse>
   <td> 
 </cfif>
   #QSearchResult.issueID#
 </td>
 <td>#QSearchResult.ticketNumberTxt#</td>
 <td>#QSearchResult.TitleOfIssue#</td>
 <td>#QSearchResult.Status#</td>
</tr>
</cfoutput>
</table>
  


<cfif url.page EQ 1>
   Prev Page
<cfelse>
   <a href="index.cfm?page=#url.page-1#">Prev Page</a>
</cfif>
<hr>


<cfif url.page * records_per_page LT QSearchResult.counts>
   <a href="pages.cfm?page=#url.page+1#">Next Page</a>
<cfelse>
   Next Page
</cfif>
<hr>

Open in new window

0
 

Author Comment

by:lulu50
ID: 39926873
gdemaria!!!!!!

oh, I hope everything is ok!!!!

I made a lots of changes to it so I posted my most recent changes.

I set it to display just 2 records on a time for testing purpose.

I got it to work, but is it possible to have it like this instead

Prev 3 4 5 6 7 Next

it will be nice to have some numbers between them.

Thank you so much for all your help.



 <cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#'>
  SELECT case when L.ParentIssueID is null then L.IssueId
              else L.ParentIssueID
		 end  as TopIssueID
		 , L.IssueID
		 , L.ParentIssueID
		 , L.TicketNumberTxt
		 , L.TitleOfIssue
		 , I.Status
		 , L.DateCreated
   FROM LogEntry L
   LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
  order by 1 desc, case when L.parentIssueID is null then 1 else 2 end
</cfquery>


<cfparam name="url.page" default="1">
<cfset page_links_shown = 5>
<cfset records_per_page = 2>

<cfset total_pages = ceiling(QSearchResult.RecordCount / records_per_page)>

<cfif val(url.page) lt 1>
  <cfset url.page = 1>
<cfelseif val(url.page) gt total_pages>
  <cfset url.page = total_pages>
</cfif>
<cfset start_record = ((url.page - 1) * records_per_page ) + 1>



<table style="width:100%;border: solid 1px #dddddd;" id="ResultListTbl">

<tr style="background-color:#d0d2d3;font-weight:bold;">
 <th style="width:80px;padding-left:3px;">Issue ID</th>
 <th style="width:100px;">Ticket#</th>
 <th style="width:300px;">Title</th>
 <th style="width:50px;">Status</th>
 <th style="width:100px;">Date</th>
  <th style="width:50px;">&nbsp;</th>
</tr>

<cfoutput query="QSearchResult" startrow="#start_record#" maxrows="#records_per_page#">

 <cfif val(QSearchResult.ParentIssueID)> 
 <!-- //Child Row -->
 <tr>
   <td style="padding-left:20px;">#QSearchResult.issueID#</td>
 <td>#QSearchResult.ticketNumberTxt#</td>
 <td>#QSearchResult.TitleOfIssue#</td>
 <td>#QSearchResult.Status#</td>
  <td>#DateFormat(QSearchResult.DateCreated, "m/d/yyyy")#</td>
    <td>Edit</td>
    </tr>
 <cfelse>
<!-- //Parent Row -->
 <tr class="bgrow">
   <td style="font-weight:bold;">#QSearchResult.issueID#</td>
 <td>#QSearchResult.ticketNumberTxt#</td>
 <td>#QSearchResult.TitleOfIssue#</td>
 <td>#QSearchResult.Status#</td>
  <td>#DateFormat(QSearchResult.DateCreated, "m/d/yyyy")#</td>
  <td>Edit</td>
  </tr>
 </cfif>
   

</cfoutput>
</table> 
   
   <cfoutput>
  <cfif url.page EQ 1>
   Prev Page
<cfelse>
   <a href="index.cfm?page=#url.page-1#">Prev Page</a>
</cfif>
<hr>


<cfif url.page * records_per_page LT QSearchResult.RecordCount >
   <a href="index.cfm?page=#url.page+1#">Next Page</a>
<cfelse>
   Next Page
</cfif>
<hr>
  
  </cfoutput>

Open in new window

0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 39926912
how about something like this?

<cfoutput>
<hr>
<cfif url.page EQ 1>
   &laquo; Prev
<cfelse>
   <a href="index.cfm?page=#url.page-1#">&laquo; Prev</a>
</cfif>
&nbsp;|&nbsp;
<cfloop index="pp" from="1" to="#total_pages#">
  <cfif pp eq url.page>
   <strong>#pp#</strong> <!--- current page ---->
  <cfelse>
   <a href="index.cfm?page=#pp#">#pp#</a>
  </cfif>&nbsp;|&nbsp;
</cfloop>
<cfif url.page lt total_pages>
   <a href="index.cfm?page=#url.page+1#">Next &raquo;</a>
<cfelse>
   Next &raquo;
</cfif>
<hr>
</cfoutput>

Open in new window

0
 

Author Comment

by:lulu50
ID: 39927001
gdemaria,

That is awesome!!!!!!!!!!!!!!!!!

I am very happy with it!!!!!!!!!!!!!!!!!!!1

It looks great!!!!!!!!!11

Can't thank you enough

Thank you for one million and one times lol

Thanks again and again

Lulu
0
 

Author Closing Comment

by:lulu50
ID: 39927003
Excellent+++
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 39927531
:)   you are very welcome
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question