Freelance Web Development

After writing Pagination with ColdFusion and MySQL I tightened up the script some. And with Hatem’s suggestions, made it better. So, here is the new version. It’s a different application, so the query is slightly different.

Hatem’s added/modified:

• <cftransaction>
• #iif(url.page is 1,DE(firstperpage),DE(allperpage))#

I added/modified:

• <cfparam name=”variables.firstperpage” default=”#variables.allperpage#”>
• <cfparam name=”url.where” default=”ImportDownloaded IS NULL”>
• WHERE #preserveSingleQuotes(url.where)#

<!--- CURRENT PAGE --->
<cfparam name="url.page" default="1">
<!--- RECORDS PER PAGE (ON ALL PAGES) --->
<cfparam name="variables.allperpage" default="15">
<!--- RECORDS PER PAGE (ON FIRST PAGE) --->
<cfparam name="variables.firstperpage" default="#variables.allperpage#">
<!--- NUMBER OF PAGE LINKS AT A TIME --->
<cfparam name="variables.blocksof" default="10"><cfparam name="url.where" default="ImportDownloaded IS NULL">

<cftransaction>
	<cfquery name="orders" datasource="#session.datasource#">
	SELECT SQL_CALC_FOUND_ROWS orders.*, orderitems.itemname
	FROM orders LEFT JOIN orderItems ON orders.id = orderItems.orderid
	WHERE #preserveSingleQuotes(url.where)#
	ORDER by orders.datetime DESC, orderitems.itemname
	LIMIT #firstperpage+(allperpage*(page-1))-allperpage#, #iif(url.page is 1,DE(firstperpage),DE(allperpage))#
	</cfquery>
	<cfquery name="result_count" datasource="#session.datasource#">
	SELECT FOUND_ROWS() as howmany
	</cfquery>
</cftransaction>

<!--- 'TOP' IS THE LAST PAGE NUMBER LINK --->
<cfset top = blocksof * ceiling((url.page)/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#&where=#url.where#">« 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#&where=#url.where#">#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 result_count.howmany>
	<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#&where=#url.where#">Next »</a>
</cfif>

</cfoutput>

Some pretty good stuff I’d say

Thanks Hatem.

ColdFusion, MySQL, Web Coding