lulu50
asked on
(gdemaria) I need to do pagination to my list
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
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>
ASKER
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.
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.
ASKER
gdemaria,
where are you?
Did you forget about me today? :-(
where are you?
Did you forget about me today? :-(
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
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>
ASKER
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.
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;"> </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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Excellent+++
:) you are very welcome
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.