This content has been marked as final. Show 4 replies
If the table were small I might do the following in Oracle which you might have to change for Mysql.
select min(id) as firstid, max(id) as lastid
max(case when id < #URL.ID# then id else #LIMITS.firstid# end) prevurl,
min(case when id > #URL.ID# then id else #LIMITS.lastid# end) nexturl
where id <> #URL.ID#
<a href="#CurrentPage#?id=#IDS.prevurl#">Previous Message</a>
<a href="#CurrentPage#?id=#IDS.nexturl#">Next Message</a>
@ draves: your code still does not account for deleted records, when
there is no record in the db with next sequential id from current record
@ davella: using cftry/cfcatch around your query, and/or just checking
for query.recordcount and incrementing the id value if no records found
and running your select query again with new id value should solve your
The code does account for deleted records. PREVURL will contain the the highest id just below the ID selected for the current page and NEXTURL will contain the next id just above. I created a table in Oracle, deleted records and tested it before submitting the first response. The WHERE clause is unnecessary. To keep it from erroring if there is not previous id it chooses the first and if there is not a next id it chooses the last. It would have to be tested if two calls that scan the table is faster then thrashing through specific increments but how do you know when to stop incremental searching?