Pagination with ColdFusion and MySQL

I find that web development with MySQL to be so much more pleasurable than with MS SQL Server. Here is a great example to prove my point. In another post I showed how to pull off pagination with MS SQL and ColdFusion. Lots of SQL code. And it required running the query twice. Once to count the entire result set, and another time for the results to display.
This version however, is very slim. It makes use of MySQL's SQL_CALC_FOUND_ROWS, FOUND_ROWS(), and LIMIT.

SQL_CALC_FOUND_ROWS(): SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result.

FOUND_ROWS(): A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECTFOUND_ROWS() afterward.

LIMIT: The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

So let's go take a look at this beauty...

<!--- CURRENT PAGE --->
<cfparam name="url.page" default="1">
<!--- RECORDS PER PAGE (ON ALL PAGES) --->
<cfparam name="variables.allperpage" default="6">
<!--- RECORDS PER PAGE (ON FIRST PAGE) --->
<cfparam name="variables.firstperpage" default="3">
<!--- NUMBER OF PAGE LINKS AT A TIME --->
<cfparam name="variables.blocksof" default="4">

<!--- WE RUN THE QUERY WITH SQL_CALC_FOUND_ROWS --->
<cfquery name="latest" datasource="MagLibrary">
SELECT SQL_CALC_FOUND_ROWS, articles.title, articles.articleid, 
	publications.title as pubtitle, publications.rank
FROM articles INNER JOIN publications ON 
	articles.publicationID = publications.publicationID
WHERE articles.active = 1
ORDER BY publications.rank, pubtitle, articles.title, articles.articleid
<!--- ONLY RETURN THE AMOUNT OF RECORDS WE NEED FOR THIS PAGE --->
LIMIT #firstperpage+(allperpage*(page-1))-allperpage#, 
<cfif url.page is 1>
	#firstperpage#
<cfelse>
	#allperpage#
</cfif>
</cfquery>

<!--- NOW WE QUERY HOW MANY ROWS WERE FOUND --->
<Cfquery name="articlecount" datasource="#session.datasource#">
SELECT FOUND_ROWS() AS articlecount
</cfquery>

<!--- 'TOP' IS THE LAST PAGE NUMBER LINK --->
<Cfset top = blocksof * ceiling((url.page+1)/blocksof)>

<cfoutput>

<!--- IF WE'RE PAST THE FIRST BLOCK OF PAGES, SHOW THE PREV LINK --->
<cfif url.page gt blocksof>
	<a href="?page=#top-blocksof#" class="fullstorylink" style="text-decoration:underline;">« Prev</a>
</cfif>

<!--- LOOP THROUGH THE PAGES IN THIS BLOCK --->
<cfloop from="#top-blocksof+1#" to="#top#" index="a">
<cfif url.page is not a>
	<a href="?page=#a#" class="fullstorylink" style="text-decoration:underline;">#a#</a>
<Cfelse>
	<b>[#a#]</b>
</cfif>
<!--- IF WE'RE ON THE LAST BLOCK AND DON'T HAVE ENOUGH RECORDS TO COMPLETE THE BLOCK,
	STOP CREATING THE LINKS FOR MORE PAGES, AND SET A FLAG TO NOT SHOW THE LINK FOR THE NEXT BLOCK --->
<cfif ((a-1) * allperpage) + firstperpage gte articlecount.articlecount>
	<Cfset noNext=1><cfbreak>
</cfif>
</cfloop>

<!--- IF WE RAN OUT OF RECORDS, DO NOT SHOW THE LINK FOR THE NEXT BLOCK OF PAGES --->
<cfif not isdefined("noNext")>
	<a href="?page=#top+1#" class="fullstorylink" style="text-decoration:underline;">Next »</a>
</cfif>

</cfoutput>

<!--- NOW IT'S A SIMPLE MATTER OF SHOWING THE RECORDS --->
<br><br>

<!--- WE'LL GROUP THE ARTICLES UNDER THEIR PUBLICATION --->
<cfoutput query="latest" group="pubtitle">
<h2>#pubtitle#</h2>
<cfoutput>
<a href="details.cfm?articleid=#articleid#">#title#</a>
</cfoutput>
</cfoutput>

For those of you counting lines, we went from 94 (MS SQL) down to 70 (MySQL). That's 24 less lines of SQL code. Not too shabby. And just knowing your programming is more efficient is a good feeling =)