4 Replies Latest reply on Jan 21, 2008 4:37 PM by Newsgroup_User

    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?
        • 1. Re: Next Record / Previous Record
          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>
          • 2. Re: Next Record / Previous Record
            Level 7
            @ 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
            • 3. Re: Next Record / Previous Record
              draves Level 1
              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?
              • 4. Re: Next Record / Previous Record
                Level 7
                right you are. i should have paid more attention to your code... sorry

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