nchalasa062698
asked on
Navigating to next record in Cold Fusion
I have a list of results that come from a access database and I display them sorted by the Last Name. And when they
click on one name they go the details page of that record. What I would like to do is give a "Next" and "Previous"
links on that details page so they can
go to the next persons details.
Any ideas on how to do it?
click on one name they go the details page of that record. What I would like to do is give a "Next" and "Previous"
links on that details page so they can
go to the next persons details.
Any ideas on how to do it?
ASKER
bigbadb,
Thanks for responding so fast, but I am not sure this works for me or may be I didn't get it as to how it works.
when you say rec_id I am assuming this
is the primary key on my table.
I thought I will take an example and go by that.
table : rec_id , last_name
1 Hong
2 davis
Now in the showallpeople.cfm
the list would show up as
davis (rec_id =2)
hong (rec_id =3)
WHen I go to davis's details and do the next according to the code it goes to rec_id=3, instead of going to rec_id=1 which is what I need.
Thanks for responding so fast, but I am not sure this works for me or may be I didn't get it as to how it works.
when you say rec_id I am assuming this
is the primary key on my table.
I thought I will take an example and go by that.
table : rec_id , last_name
1 Hong
2 davis
Now in the showallpeople.cfm
the list would show up as
davis (rec_id =2)
hong (rec_id =3)
WHen I go to davis's details and do the next according to the code it goes to rec_id=3, instead of going to rec_id=1 which is what I need.
You will have to call this page now with the following like
<a href="showoneperson.cfm?sh owthispers on=#showal l.rec_id#& originalid =#showall. rec_id#>Vi ew person</a>
Lets start over:
select *
from table
where rec_id = #showthisperson#
to show the next person you would do the following
<cfif http_referer contains "showoneperson.cfm">
<cfset showthisperson = #showthisperson# + 1>
<cfif originalid eq #showthisperson#>
<cfset showthisperson = #showthisperson# + 1>
</cfif>
<cfelse>
<cfset showthisperson = 1>
</cfif>
<a href="showoneperson.cfm?sh owthispers on=#showal l.rec_id#& originalid =#orginali d#>Next person</a>
pervious would work much the same. You will always have to pass the orginalid and then subtract 2 if you encouter this one
Let me know if you have any ?s hopefully this is not too confusion
<a href="showoneperson.cfm?sh
Lets start over:
select *
from table
where rec_id = #showthisperson#
to show the next person you would do the following
<cfif http_referer contains "showoneperson.cfm">
<cfset showthisperson = #showthisperson# + 1>
<cfif originalid eq #showthisperson#>
<cfset showthisperson = #showthisperson# + 1>
</cfif>
<cfelse>
<cfset showthisperson = 1>
</cfif>
<a href="showoneperson.cfm?sh
pervious would work much the same. You will always have to pass the orginalid and then subtract 2 if you encouter this one
Let me know if you have any ?s hopefully this is not too confusion
Are you talking about from a Query results?
Where maybe the next person may not be personID + 1?
Where maybe the next person may not be personID + 1?
ASKER
nathans,
that's right. I need to get to next
record which may not be ID+1.
that's right. I need to get to next
record which may not be ID+1.
the way i commented above should take care of this
ex
say you choose rec_id 4
then click next
you will see rec_id 1
then click next
you will see rec_id 2
then click next
you will see rec_id 3
then click next
you will see rec_id 5
if you hit previous you will see rec_id 3
the above code will work you just need to implement it in your page
ex
say you choose rec_id 4
then click next
you will see rec_id 1
then click next
you will see rec_id 2
then click next
you will see rec_id 3
then click next
you will see rec_id 5
if you hit previous you will see rec_id 3
the above code will work you just need to implement it in your page
ASKER
bigbadb,
I don't think this code will do what
I need. I want the next and previous
to always follow the order of my
results and not my id at anytime.
So depending on the last name , the
order for ID could be like
3, 69, 52, 1, 10.....so on.
SO what I need is code where when I
go to one person then I should some
how know the id of the person whose
last name comes after him. I hope
this clarifies my original question.
I don't think this code will do what
I need. I want the next and previous
to always follow the order of my
results and not my id at anytime.
So depending on the last name , the
order for ID could be like
3, 69, 52, 1, 10.....so on.
SO what I need is code where when I
go to one person then I should some
how know the id of the person whose
last name comes after him. I hope
this clarifies my original question.
ok now i see this is how you do it:
page one
<cfquery name="showall" datasource="whatever"
select *
from table
order by last_name
</cfquery>
<cfoutput query="showall">
<a href="showone.cfm?showone_ recid=#sho wall.rec_i d#>show this one</a>
</cfoutput>
page 2
<cfquery name="showall" datasource="whatever"
select *
from table
order by last_name
</cfquery>
<cfset count_loop = 0>
<cfset count_init_rec = 0>
<cfoutput query="showall">
<cfif showall.rec_id = #showone_recid#>
<cfset count_init_rec = #count_loop#
</cfif>
<cfset id_count(count_loop) = #showall.rec_id#>
<cfset count_loop = #count_loop# + 1>
</cfoutput>
Ok so now you have your rec_id in id_count(count_init_rec) if you want to view the next it will be
id_count(count_init_rec + 1)
view the previous it will be
id_count(count_init_rect - 1)
You stored all rec_ids in an array when you loop through the query you can stop when you reach the rec_id you are interested in and write whatever info you need
Now this will work for
3, 29, 4, 1,etc....
page one
<cfquery name="showall" datasource="whatever"
select *
from table
order by last_name
</cfquery>
<cfoutput query="showall">
<a href="showone.cfm?showone_
</cfoutput>
page 2
<cfquery name="showall" datasource="whatever"
select *
from table
order by last_name
</cfquery>
<cfset count_loop = 0>
<cfset count_init_rec = 0>
<cfoutput query="showall">
<cfif showall.rec_id = #showone_recid#>
<cfset count_init_rec = #count_loop#
</cfif>
<cfset id_count(count_loop) = #showall.rec_id#>
<cfset count_loop = #count_loop# + 1>
</cfoutput>
Ok so now you have your rec_id in id_count(count_init_rec) if you want to view the next it will be
id_count(count_init_rec + 1)
view the previous it will be
id_count(count_init_rect - 1)
You stored all rec_ids in an array when you loop through the query you can stop when you reach the rec_id you are interested in and write whatever info you need
Now this will work for
3, 29, 4, 1,etc....
I will work on it when I get off of work. What your looking for is..
Current Record
#myquery.CurrentRow#
This way you get the records in order
1 to
Total Records:
#myquery.recordcount#
Current Record
#myquery.CurrentRow#
This way you get the records in order
1 to
Total Records:
#myquery.recordcount#
Hi nchalasa,
The following code provides a way to navigate through a query resultset by clicking on next and previous buttons. It doesn't do exactly what you've asked for but it should give you a place to start.
The code I've listed below sort or stands on it's own in that it simply displays the first contact and lets you navigate forward and backward through the query results. It won't allow you to specify which contact to start at by clicking a hyperlinked contact in a list.
It's easy to do each one in isolation: EITHER
1. select a record for which you want to view the details
OR
2. navigate through a list of records, one by one, backwards or forwards, starting at the beginning.
Selecting a particular record to display on the details page is fairly straight forward: you just pass the primary key value in a hidden form field or on the URL and query the database to retrieve only one record based by adding that primary key value in a WHERE clause.
And I think you'll see that navigating through a list of records is also pretty straight forward: simply retrieve ALL records and control which ones are displayed with the StartRow and MaxRows attributes of the CFOUTPUT tag (taken straight from the Ben Forta book, the coldfusion web application construction kit, p 498).
Combining these two together is more difficult because the first action (picking the initial record to view) retrieves only one record and the second action (navigating through a list of records) inherently requires a list of records to start with.
So if you can think of a way to retrieve ALL records in step 1 and only display the one you've chosen from the list, then you should be in good shape to start working that code into the code below.
<!--- set the maximum number of rows to display (this can be modified to display however many rows you want at once)--->
<cfset MaxRows = 1>
<!--- and set the default record to start at (if this is the first time through) --->
<cfparam name="START" default="1">
<!--- Do the query to get the required details --->
<cfquery name="getContact" datasource="[your datasource]">
SELECT *
FROM Contacts
ORDER BY LastName
</cfquery>
<!--- output the query results for the record in --->
<cfoutput query="getContact" startrow=#start# maxrows=#MaxRows#>
<b>#getContact.FIRSTNAME# #getContact.LASTNAME#</b>< br>
<a href="mailto:#getContact.E MAIL#">#ge tContact.E MAIL#</a>< br>
#getContact.HomePhone#<br>
#getContact.WorkPhone#<br>
#getContact.CellPhone#<br>
</cfoutput>
<!--- Used to determine which row to output from the query on the next request --->
<cfset PrevStart = Start - MaxRows>
<cfset NextStart = Start + MaxRows>
<table><tr><td>
<!--- Previous button is displayed only if we're not on the first record --->
<cfif PrevStart GTE 1>
<cfoutput>
<form action="ContactDetails.cfm " method="post">
<input type="hidden" name="start" value="#PrevStart#">
<input type="image" src="../prev.gif">
</form>
</cfoutput>
</cfif>
</td><td>
<!--- Next Button is displayed only if we're not on the last record --->
<cfif NextStart LTE getContact.RecordCount>
<cfoutput>
<form action="ContactDetails.cfm " method="post">
<input type="hidden" name="start" value="#NextStart#">
<input type="image" src="../next.gif">
</form>
</cfoutput>
</cfif>
</td></tr></table>
Of course, you'll have to modify the SQL query, CFQuery and CFoutput tags to reflect your database column names but the rest should be as easy as cut and paste.
Good Luck,
Rod
The following code provides a way to navigate through a query resultset by clicking on next and previous buttons. It doesn't do exactly what you've asked for but it should give you a place to start.
The code I've listed below sort or stands on it's own in that it simply displays the first contact and lets you navigate forward and backward through the query results. It won't allow you to specify which contact to start at by clicking a hyperlinked contact in a list.
It's easy to do each one in isolation: EITHER
1. select a record for which you want to view the details
OR
2. navigate through a list of records, one by one, backwards or forwards, starting at the beginning.
Selecting a particular record to display on the details page is fairly straight forward: you just pass the primary key value in a hidden form field or on the URL and query the database to retrieve only one record based by adding that primary key value in a WHERE clause.
And I think you'll see that navigating through a list of records is also pretty straight forward: simply retrieve ALL records and control which ones are displayed with the StartRow and MaxRows attributes of the CFOUTPUT tag (taken straight from the Ben Forta book, the coldfusion web application construction kit, p 498).
Combining these two together is more difficult because the first action (picking the initial record to view) retrieves only one record and the second action (navigating through a list of records) inherently requires a list of records to start with.
So if you can think of a way to retrieve ALL records in step 1 and only display the one you've chosen from the list, then you should be in good shape to start working that code into the code below.
<!--- set the maximum number of rows to display (this can be modified to display however many rows you want at once)--->
<cfset MaxRows = 1>
<!--- and set the default record to start at (if this is the first time through) --->
<cfparam name="START" default="1">
<!--- Do the query to get the required details --->
<cfquery name="getContact" datasource="[your datasource]">
SELECT *
FROM Contacts
ORDER BY LastName
</cfquery>
<!--- output the query results for the record in --->
<cfoutput query="getContact" startrow=#start# maxrows=#MaxRows#>
<b>#getContact.FIRSTNAME# #getContact.LASTNAME#</b><
<a href="mailto:#getContact.E
#getContact.HomePhone#<br>
#getContact.WorkPhone#<br>
#getContact.CellPhone#<br>
</cfoutput>
<!--- Used to determine which row to output from the query on the next request --->
<cfset PrevStart = Start - MaxRows>
<cfset NextStart = Start + MaxRows>
<table><tr><td>
<!--- Previous button is displayed only if we're not on the first record --->
<cfif PrevStart GTE 1>
<cfoutput>
<form action="ContactDetails.cfm
<input type="hidden" name="start" value="#PrevStart#">
<input type="image" src="../prev.gif">
</form>
</cfoutput>
</cfif>
</td><td>
<!--- Next Button is displayed only if we're not on the last record --->
<cfif NextStart LTE getContact.RecordCount>
<cfoutput>
<form action="ContactDetails.cfm
<input type="hidden" name="start" value="#NextStart#">
<input type="image" src="../next.gif">
</form>
</cfoutput>
</cfif>
</td></tr></table>
Of course, you'll have to modify the SQL query, CFQuery and CFoutput tags to reflect your database column names but the rest should be as easy as cut and paste.
Good Luck,
Rod
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also on Rod's Code add the check to see if previous is a neg number or zero since when you select 1 it will be 1 - 1 and if you decide to do 5 records at a time 1 will be -4.
have you tried my last suggestion. It will work
ASKER
I am sorry but we had some other production problems and I didn't get a
chance to try it. THanks for your response and I will get back ASAP.
chance to try it. THanks for your response and I will get back ASAP.
When you first do the initial query you could then store the rec_id order into an array you pass from page to page along with with where you are in the recordset. EG
<cfparam name="rec_pos" default="0">
<cfif rec_pos IS 0>
<cfquery name="getContact" datasource="[your datasource]">
SELECT *
FROM Contacts
ORDER BY LastName
</cfquery>
<cfset thearray=arraynew(1)>
<cfset counter=1>
<cfloop query="getContact">
<cfset thearray(counter)=getConta ct.rec_id>
<cfset counter=counter+1>
</cfloop>
<cfset rec_pos=1>
</cfif>
<cfquery name="getSpecificContact" datasource="[your datasource]">
SELECT *
FROM Contacts
WHERE rec_id=#thearray(rec_pos)#
</cfquery>
....the output all the data, then put in hidden fields for thearray and for rec_pos. Thats the idea anyway. Good Luck.
:) dapperry
<cfparam name="rec_pos" default="0">
<cfif rec_pos IS 0>
<cfquery name="getContact" datasource="[your datasource]">
SELECT *
FROM Contacts
ORDER BY LastName
</cfquery>
<cfset thearray=arraynew(1)>
<cfset counter=1>
<cfloop query="getContact">
<cfset thearray(counter)=getConta
<cfset counter=counter+1>
</cfloop>
<cfset rec_pos=1>
</cfif>
<cfquery name="getSpecificContact" datasource="[your datasource]">
SELECT *
FROM Contacts
WHERE rec_id=#thearray(rec_pos)#
</cfquery>
....the output all the data, then put in hidden fields for thearray and for rec_pos. Thats the idea anyway. Good Luck.
:) dapperry
myglg is wrong you can do it with cf outout
forexamplre you run your query
then it display it into cfoutput
so u write as <cfoutput query="qry1" startrow="2" maxrow="1">
so you can easly show record by this method it is so easier then asp programming
forexamplre you run your query
then it display it into cfoutput
so u write as <cfoutput query="qry1" startrow="2" maxrow="1">
so you can easly show record by this method it is so easier then asp programming
ASKER
Bigbadbe,
I need to give you points for your
good answers. I am not sure how the
answer from myglG shows as accepted b'caz I don't think I accepted it. Let me know how I can give you the points.
Do I have to post another question to do that.
I need to give you points for your
good answers. I am not sure how the
answer from myglG shows as accepted b'caz I don't think I accepted it. Let me know how I can give you the points.
Do I have to post another question to do that.
@ rod_nolan
This worked GREAT for me... thanks.
This worked GREAT for me... thanks.
<cfquery name="showall" datasource="whatever">
select *
from table
</cfquery>
<cfoutput query="showall">
#showall.name#
<a href="showoneperson.cfm?sh
</cfoutput>
Now you click on view person
on this page you have a query similar to the following
select *
from table
where rec_id = #showthisperson#
to show the next person you would do the following
<cfset showthisperson = #showthisperson# + 1>
<a href="showoneperson.cfm?sh
This would advance you to the next person. To do previous you would simply - 1 from showthisperson
Hopw this helps