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?
LVL 1
nchalasa062698Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bigbadbCommented:
SHowallpeople.cfm looks like the following:

<cfquery name="showall" datasource="whatever">
select *
from table
</cfquery>

<cfoutput query="showall">
#showall.name#

<a href="showoneperson.cfm?showthisperson=#showall.rec_id#>View person</a>

</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?showthisperson=#showall.rec_id#>Next person</a>


This would advance you to the next person.  To do previous you would simply - 1 from showthisperson


Hopw this helps
0
nchalasa062698Author Commented:
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.
   
             
0
bigbadbCommented:
You will have to call this page now with the following like
<a href="showoneperson.cfm?showthisperson=#showall.rec_id#&originalid=#showall.rec_id#>View 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?showthisperson=#showall.rec_id#&originalid=#orginalid#>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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Nathan Stanford SrSenior ProgrammerCommented:
Are you talking about from a Query results?

Where maybe the next person may not be personID + 1?
0
nchalasa062698Author Commented:
nathans,
     that's right. I need to get to next
 record which may not be ID+1.
0
bigbadbCommented:
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
0
nchalasa062698Author Commented:
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.
0
bigbadbCommented:
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=#showall.rec_id#>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....
0
Nathan Stanford SrSenior ProgrammerCommented:
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#
0
rod_nolanCommented:
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.EMAIL#">#getContact.EMAIL#</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
0
myqlGCommented:
I have done things similar to this..
I really dislike the way CF DOESNT let you navigate through recordsets as easy as ASP (.movenext .movefirst .absoluteposition) these are all godsends.. however when I return small numbers of records..I put them in a hidden listbox using <cf output> and then I can manipulate the hidden listbox to my hearts content with javascript.  This is the crappiest work aroudn I have found..but hte only one that gives me access to teh query info dynamically.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nathan Stanford SrSenior ProgrammerCommented:
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.
0
bigbadbCommented:
have you tried my last suggestion.  It will work
0
nchalasa062698Author Commented:
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.
0
dapperryCommented:
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)=getContact.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
0
FRehmanCommented:
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
0
nchalasa062698Author Commented:
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.
0
simonallenukCommented:
@ rod_nolan

This worked GREAT for me... thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.