Simple pagination

Question

Lately I've been messing around with a ColdFusion forum script, and I'm trying to add pagination to it so that it only displays 10 comments per page instead of all of them. Sadly, I've only found complicated solutions to do this and no simple solution like I was used to in PHP. Currently, this is my code for getting the comments out of my database:

<cfquery name = "comments" datasource = "#DSN#">
SELECT * 
FROM  `forum_comments` 
WHERE topicid = #id#
</cfquery>

Because I don't want to overload my processor while fetching 1000+ comments per topic, I want to seperate all the comments into pages of 10 comments per page. Is this possible with just some little modifications to my page? My best guess to do this is by using the URL.page statement and the LIMIT function in MySQL, but I have no idea how.

Update

All of my current code:

<cfif IsDefined('URL.page')> // Pagination
  <cfset page = URL.page>
<cfelse>
  <cfset page = 1>
</cfif>

<cfset howManyRecsToShow = 10>
<cfset startRec = page*howManyRecsToShow>


<cfquery name = "comments" datasource = "#DSN#"> // Get all comments
SELECT * 
FROM  `forum_comments` 
WHERE topicid = #id#
LIMIT #startRec#, #howManyRecsToShow#
</cfquery>




                <cfset colour ="post_uneven"> // Required for CSS

  <cfloop query="comments"> // Loop over comments


    <div id="post_text">

        <div id="post_text_edit" title="Edit"></div>
        <div id="post_text_delete" title="Delete"></div>

        <div id="post_text_title">
        RE: #gettopic.title#
        </div>

        <div id="post_text_date">
        #DateFormat(dateAdd("s", comments.timestamp, "01/01/1970"))#       #TimeFormat(dateAdd("s", comments.timestamp, "01/01/1970"))#
        </div>

        <div id="post_text_text">
        #comments.text#

        </div>

    </div>

</div>
                    </cfloop>

    </cfoutput>



    <cfif colour is "post_uneven"><cfset colour="post_even"><cfelse><cfset colour="post_uneven"></cfif> // Required for CSS




<div id="topic_info_balk">
<div id="forum_paginas">
   <cfif page gt 1>
    <a href="pageTest.cfm?page=#page-1#">PREVIOUS 10</a>&nbsp;|&nbsp;
  </cfif>
  <a href="?page=<cfoutput>#page+1#</cfoutput>">NEXT 10</a>
</div>

Answer 1

You said you want a SIMPLE example so here it is. By SIMPLE I mean this will just show NEXT and PREVIOUS links. If you want slightly more complicated pagination that shows the number of pages then you have to get the total record count before hand. (I quickly free-hand typed this, not on my CF server right now, so not sure if this is 100% accurate)...

(pageTest.cfm)

<cfif IsDefined("url.page")>
  <cfset page = url.page>
<cfelse>
  <cfset page = 1>
</cfif>

<cfset howManyRecsToShow = 10>
<cfset startRec = page*howManyRecsToShow>

<cfquery name="q1">
  select id, username, email
  from users
  order by id limit #startRec# , #howManyRecsToShow#
</cfquery>

<cfoutput>
  <table border="1">
    <cfloop query="q1">
        <tr>
            <td>#q1.id#</td>
            <td>#q1.username#</td>
            <td>#q1.email#</td>
        </tr>
    </cfloop>
  </table>
  <cfif page gt 1>
    <a href="pageTest.cfm?page=#page-1#">PREVIOUS 10</a>&nbsp;|&nbsp;
  </cfif>
  <a href="pageTest.cfm?page=#page+1#">NEXT 10</a>
</cfoutput>