Re: Next Record / Previous Record
On Jan 20, 6:22 pm, "davella" <webforumsu...@xxxxxxxxxxxxxx> wrote:
> I created a message board time system where a user can click a message and
> 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
> #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#
> <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
> 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
> 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?
SELECT * FROM Messages
Where id >= #URL.ID#
BTW: <cfqueryparam .../> is a very good feature...