Nex/Prev buttons

Hey all,
I've got a quick question hopefully somebody can shove me in the right direction.

Alright, so i've basically got a master/details situation going on, click on a picture in one page and get details about it in the next page.

On the "details" page, I have a next and previous button that look like this...

<a href="show.cfm?portID=#prev#">
<a href="show.cfm?portID=#next#">

The "prev" and "next" variables are just taking the portID variable and either adding or subtracting 1 from it.

Problem is, when I get to row 1 and go previous, obviously it should validate and push me to row 10 or whatever the last row is.

Any ideas? Just to be clear, i'm not looking for a paging situation, this isn't a list of information, it is one page getting data called via the URL.ID.

Thanks!
Caden

I would pass the current id along with a command like "next" or "previous", and let the application decide what the actual next or previous record to display is. Simply adding or subtracting 1 is not a viable long term solution because what happens if you delete number 10? When you are on 9 and press next, the app will error out because there is no 10.

The other option is to determine what the next or previous record id really is and then use that as the URL variable instead of simply doing the add/subtract 1.

Both make sense, my problem is, i'm new to CF, so i'm unsure as to how to do thoes things. Everything i'm reading in relation to next or previous buttons are in referece to paging.

Are there built in functions for allowing the application to figuring out what the next record in the database is?

Caden

No, as it's really a database/persistence issue. And how difficult it is depends on how your database is set up. It might be as simple as a query that selects the next highest or lowest id value:

<!--- get the next lowest id --->
<cfquery name="myQuery" maxRows="1">
select id
from myTable
where id < <cfqueryparam value="#url.currentID#" cfSQLType="CF_SQL_INTEGER">
</cfquery>

Some databases also have functions or operators that get the next record, but that is specific to the database.

Alright, now i'm confused, this is what's happening.

I started messing around with your code, and ended up with trying to make it as simple as possible

<cfquery name="nextquery" datasource="omneraportfolio">
select ID
from portfolio
where ID > #URL.portID#
</cfquery>

The nextquery works, just fine, if i'm in ID 15, it'll send me to 16, and so on, except when I get to the end it just gives me a blank, obviously, I haven't told it to do anything when it reaches the end, so that's fine. But previous...

<cfquery name="prevquery" maxRows="1" datasource="omneraportfolio">
select id
from portfolio
where id < <cfqueryparam value="#url.portID#" cfSQLType="CF_SQL_INTEGER">
</cfquery>

I tried putting your code basically as in, into previous, but no matter what I try, it gives me the 1st record, always, which I suppose isn't a bad thing and if I figure out why that's happening i'll know how to validate for EOF and BOF...anyhow, that's what's happening so far...i'll keep messing with it

Caden

Of course its getting the first database entry, since its the first entry it finds, which meets the requirements (ID<portID) and you have maxrows=1 in your query.
Just add: ORDER BY id DESC

I would run a query for all items, ordering the results within the query. Then I would loop through the results making a list of ID numbers. So we would have 1,3,4,5,6,7,10 then I would figure out where in the list my current ID is by using listfind.

For Previous value:
Then, if our current position is the same as the length of the list, I would pull the first value of the list with a listgetat. Otherwise, I would just subtract one from the current position and use that in the listgetat.

Same thing, but in reverse for the next value.

That is a fine option, *if* you don't have too many records in the database. When you get a million rows, rerunning and reordering the query for every display gets pretty inefficient though. But for smaller sets, that solution would also work just fine.

Thanks for all your input, it will all be very helpful!

Being that the database will not be more then 50 records, I won't have to worry about it being sluggish in refrence to Bastions idea.

Thanks again, i'll post my solution once i've got it workin right.

Caden

Here's a question that'll help me along though.

Obviously when I am sitting at either the first or last record, either the previous or next respectively will show nothing, so, for example.

if I am sitting at this address and I scroll over previous,
http://localhost:8501/omnera/portfolio/show.cfm?portID=1
i'll get this.
http://localhost:8501/omnera/portfolio/show.cfm?portID=

Now, can I reference that in Coldfusion, such as "null" or something of the sort? If I can, I don't have to pull number of records or figure out where in reference to the database I am currently sitting, I can just validate for "null" or such.

Thanks
Caden

There is no null, but you could do <cfif not len( trim( url.portID ) )>...

Alright, I'm still having trouble, but being new causes these problems, so sorry for the frustration.

At this point, i'm doing a query loop, as suggested, and that works all fine. I can then output the findings of the loop to see the ID's of the rows.

At this point i'm at a loss though. I'm not sure how to pull variables out of that information, to be able to put them into a list, and then use all the fancy list items, like listfirst and listlast.

So, if somebody could tell me how from:
<CFLOOP query="nextquery">
<CFOUTPUT>
#ID#
</CFOUTPUT>
</CFLOOP>
I can grab the information, i'd be well on my way.

Thanks
Caden

valueList( id ) will give you a list of all the id values returned by the query.

I finally got it, and without doing anything fancy...

<CFSET next = #nextquery.ID#>
<CFIF #next# eq "">
<CFSET next=#URL.portID#>
</CFIF>

And then I run the next (or previous) button off of the next variable.

Although, it works, i'd still like to know how to get the other way I was working on to work.

If anyone could take a few minutes to show me a solution, i'd appreciate that. I was trying to use Bastion suggestion, but couldn't get it. I had the loop, but that's as far as I could get.

Anyhow, thanks for all the suggestions
Caden

also you dont need to use the code:

<CFLOOP query="nextquery">
<CFOUTPUT>
#ID#
</CFOUTPUT>
</CFLOOP>

u can use:

<CFOUTPUT query="nextquery">
#ID#
</CFOUTPUT>