Next Record / Previous Record

[davella]

I created a message board type system where a user can click a message and once view the message has the option to click next or previous message to move directly to the next or previous message. I was using a pagination setup at first but essentially all it was doing was increasing or decreasing the current #ID# by 1. The one I was using was found in Ben Fortas book "Web Application constriction Kit Fifth Edition" This does not work however because in certain circumstances the following message could have been deleted. I changed the code a bit because I kept confusing myself but here it is...

<cfquery name="GetMessage"....>
SELECT * FROM Messages
Where id=#URL.ID#
</cfquery>
<a href="#CurrentPage#?id#URL.ID#-1=">Previous Message</a>

<a href="#CurrentPage#?id#URL.ID#+1=">Next Message</a>

As you can see, the code only changes the id by 1, which means if your viewing message 40 and 41 is deleted, when you click next message it will go to the deleted message (message 41) and not Message 42. So my question is, is there a Mysql trick that will allow me to go directly to the next available record, skipping any deleted non existing records? How did you guys handle this in different circumstances?

[draves]

If the table were small I might do the following in Oracle which you might have to change for Mysql.

<cfquery name="LIMITS"....>
select min(id) as firstid, max(id) as lastid
from Messages
</cfquery>

<cfquery name="IDS"....>
select
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
from Messages
where id <> #URL.ID#
</cfquery>
<a href="#CurrentPage#?id=#IDS.prevurl#">Previous Message</a>
<a href="#CurrentPage#?id=#IDS.nexturl#">Next Message</a>

[Newsgroup_User]

@ 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
problem...

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[draves]

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?

[Newsgroup_User]

right you are. i should have paid more attention to your code... sorry

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com