Pagination with ColdFusion and MSSQL (faux MySQL’s LIMIT X,Y)

Pagination is something that has always been somewhat difficult for
me. Especially optimizing it for large sites. To make it even harder,
the customer wants the first page to show three items, and every
subsequent page to show ten. Not hard enough? OK, they want page number
links instead of simple left/right arrow links, such as:

1 2 3 [4] Next »

Still Easy?

  • Only show 4 page links at a time
  • When you click ‘next’, show the next block of 4 pages
  • When you click ‘prev’, show the previous block of 4 pages
  • If you are on the first block of links, do not show the ‘prev’ link
  • If you run out of records, only show the correct number of page links in that block
  • If you run out of records, do not show the ‘next’ link

Remember, page 1 has 3 items. Page 2 has 10. So the math gets kinda
tricky when you don’t want to do ‘what page are we on’ statements all
over the place.

Here is my solution. All you have to do is enter your records per
page (“allperpage”), number of page links at a time (“blocksof”), and
the number of items on the first page (“firstperpage”).

The SQL query has been optimized to get as close to MySQL’s ‘LIMIT’
function as possible. We’re doing an INNER JOIN here, and sorting on
columns from both tables. I haven’t seen any examples of this
optimization technique with an inner join, or even sorting on more than
one column. We pull back the max rows we’d need, then trim the top with
a reverse sort, finally un-reverse the sort for the web page. WHEW.

Onto the code…

In this example, we’re going to show page blocks of 4. The first page has 3 items. Every other page has 6. The example uses articles which are in publications.
It looks like a monster. But half of it is comments.

<!--- 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">

<!--- COUNT NUMBER OF RECORDS TOTAL --->
<cfquery name="articlecount" datasource="MagLibrary">
SELECT count(articles.articleid) as articlecount
FROM articles INNER JOIN publications ON articles.publicationID = publications.publicationID
WHERE articles.active = 1
</cfquery>

<!--- RETURN ONLY THE NUMBER OF RECORDS WE NEED --->
<!--- THIS IS AS CLOSE TO MYSQL'S LIMIT X,Y AS WE'RE GONNA GET --->
<cfquery name="latest" datasource="#session.datasource#">
<!--- PAGE 1 IS SIMPLE. SUBSEQUENT PAGES GET COMPLICTED SINCE THE FIRST PAGE HAS A DIFFERENT NUMBER OF RECORDS --->
<!--- THIRD, ORDER OUR RESULTS --->
SELECT *
FROM (
	<!--- SECOND, ONLY RETURN THE AMOUNT OF RECORDS WE NEED FOR THIS PAGE --->
	SELECT TOP
	<!--- THIS STEP ENSURES THE LAST PAGE HAS THE CORRECT AMOUNT OF RECORDS.
		IGNOREING THIS CHECK ALWAYS GIVES THE LAST PAGE THE #PERPAGE# RECORDS
		SO YOU'D END UP WITH SOME OF THE PREVIOS PAGE'S RECORDS --->
	<cfif articlecount.articlecount lt (firstperpage + (allperpage * (url.page-1)))>
		#articlecount.articlecount - (firstperpage + (allperpage * (url.page-2)))#
	<Cfelse>
		<cfif url.page is 1>
			#firstperpage#
		<cfelse>
			#allperpage#
		</cfif>
	</cfif> *
	FROM (
		<!--- FIRST, GET UP TO THE AMOUNT WE NEED WITH CORRECT SORTING --->
		SELECT TOP #firstperpage + (allperpage * (url.page-1))#
			articles.title, articles.articleid, publications.title as pubtitle, publications.rank
		FROM articles INNER JOIN publications ON articles.publicationID = publications.publicationID
		WHERE articles.active = 1
		<!--- OUR PREFERED ORDERING --->
		ORDER BY publications.rank, pubtitle, articles.title, articles.articleid
	) as t1
	<!--- REVERSE THE ORDERING SINCE WE'RE SELECTING THE TOP --->
	ORDER BY rank DESC, pubtitle DESC, title DESC, articleid DESC
) as t2
<!--- AND REORDER FOR DISPLAYING --->
ORDER BY rank, pubtitle, title, articleid
</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>

Comments, suggestions, improvements are gladly welcomed.

SE results of new site launch

I was worried of the new site launch, hearing stories of site falling off of search engines because of the new navigation. Pages have move. To a spider, the page disappeared. And a new page exists. Therefore any benefit of that file over time is lost. So I made some provisions to my site to overcome this obstacle. And so far so good, webveteran.com is doing so much better than it ever has – in all major search engines.

Results

Using Advanced Web Ranking, I compared the night of the launch with the next days. The site went live on the February 18 2007. Here is the comparison to the February 21 2007.

      Advanced Web Ranking Report
           Project: MyProject
 Feb 21, 2007 (Compare to Feb 18, 2007)

freelance web development                  www.webveteran.com

Search Engine                  Position Previous  Change Page
AlltheWeb (12,400,000)                1        1       -    1
AlltheWeb (12,400,000)                -        1       x    -
Altavista (12,700,000)                1        1       -    1
AOL                                  25       33      +8    3
Google (5,940,000)                   25       33      +8    3
MSN (380,145)                         2        2       -    1
Search                                6        6       -    1
Yahoo                                 1        1       -    1

freelance web developer                    www.webveteran.com

Search Engine                  Position Previous  Change Page
AlltheWeb (5,070,000)                 2        2       -    1
Altavista (5,260,000)                 2        2       -    1
AOL                                  32        -       +    4
Google (571,000)                     32        -       +    4
MSN (207,852)                         -        -       ?    -
Search                                -        -       ?    -
Yahoo                                 2        2       -    1

freelance programming services             www.webveteran.com

Search Engine                  Position Previous  Change Page
AlltheWeb (3,400,000)                 2        2       -    1
Altavista (3,660,000)                 2        2       -    1
AOL                                   -        -       ?    -
Google (111,000)                      -        -       ?    -
MSN (191,352)                        34       35      +1    4
Search                                -        -       ?    -
Yahoo                                 2        2       -    1

freelance coldfusion programmer            www.webveteran.com

Search Engine                  Position Previous  Change Page
AlltheWeb (138,000)                  10       12      +2    1
AlltheWeb (138,000)                   -       12       x    -
Altavista (145,000)                  10       12      +2    1
AOL                                   -        -       ?    -
Google (14,200)                       -        -       ?    -
MSN (10,955)                          -        -       ?    -
Search                                -        -       ?    -
Yahoo                                 9       10      +1    1

freelance coldfusion                       www.webveteran.com

Search Engine                  Position Previous  Change Page
AlltheWeb (540,000)                   3        -       +    1
Altavista (553,000)                   3        -       +    1
AOL                                   9        -       +    1
Google (70,800)                       9        -       +    1
MSN (59,163)                         12        -       +    2
MSN (59,163)                         13        -       +    2
Search                                5        -       +    1
Yahoo                                 3        -       +    1

coldfusion freelance                       www.webveteran.com

Search Engine                  Position Previous  Change Page
AlltheWeb (540,000)                   1       14     +13    1
Altavista (553,000)                   1       14     +13    1
AOL                                  12       13      +1    2
Google (72,400)                      12       13      +1    2
MSN (59,815)                          8        8       -    1
Search                               22       22       -    3
Yahoo                                 1       12     +11    1

web2 demos                                 www.webveteran.com

Search Engine                  Position Previous  Change Page
AlltheWeb (482,000)                   -        -       ?    -
Altavista (548,000)                   -        -       ?    -
AOL                                   9        8      -1    1
Google (95,700)                       9        8      -1    1
MSN (29,631)                          1        1       -    1
Search                                2        2       -    1
Yahoo                                 -        -       ?    -

Lots of green plus signs in there =)
If you see a plus with no number, it means I didn’t exist for that search before. This is great.

JavaScript: Round to any multiple of a specific number

The Javascript Math.Round() function is used for rounding to whole numbers, or decimals:

Math.round(25.9) //returns 26
Math.round(25.2) //returns 25
Math.round(-2.58) //returns -3
Math.round(28.453*100)/100  //returns 28.45

But what if you want to round to the nearest multiple of 7? Not a problem! The solution is easier than the question sounds…

7 * Math.round(12 / 7); // returns 14
7 * Math.round(2 / 7); // returns 0

A new problem this poses is if someone enters 2, the result is 0. So lets expand on the above, making sure the lowest number we get is 7. Useful for whimsical shopping cart quantity entering. In this example we want to round to the nearest 25. The onBlur of the input element ‘amount’ fires off this function:

function RoundTo(X) {
	amount = 25 * Math.round(document.getElementById('amount').value / 25);
	if (amount == 0) {
		amount = 25;
	}
	document.getElementById('quantity').value = quantity;
}

OK great… but the customer entered a non numeric character. Also not a big deal. Simply wrap the whole thing in the numeric test “if (value == parseFloat(value))”:

function RoundTo(X) {
	if (document.getElementById('amount').value == parseFloat(document.getElementById('amount').value)) {
		amount = 25 * Math.round(document.getElementById('amount').value / 25);
		if (amount == 0) {
			amount = 25;
		}
	} else {
		amount = 25;
	}
	document.getElementById('quantity').value = quantity;
}

With that function, we have a form field where the user can enter in any number and the function will round to 25. If they enter a low number, we return 25. If they enter a non-numeric, we return 25

Jules’ front end development recognized by Yahoo!

I received an interesting contact form submission one evening, by a Yahoo! talent scout.

Date: Tue, 06 Feb 2007 20:53:26 -0600
Subject: Form Submission
To: [email protected]
From: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain; charset=”iso-8859-1″
Content-Transfer-Encoding: 7bit
——————————————————

Name: Teddi XXXXXXXX
Email: [email protected]
Phone: (408)XXX-XXXX
Company: Yahoo
Website: www.yahoo.com
City: Sunnyvale
State: CA
Question:

Hi Jules,
My name is Teddi XXXXXXXX and I’m a Talent Scout with Yahoo.
I would like to discuss Front End Engineering roles we have
with Yahoo. When might be a good time to speak?

Thanks,
Teddi

So… that was nice!

Later that night I spoke with Teddi on the phone. Being very good at her job, she knew right away that my resume was formatted from a Monster.com template. And then she gave me advice about resume writing.
I showed her some of my front end work, both of my own site and live examples of sites I’ve coded. She was very impressed.

I showed her that a search in Yahoo! for “freelance web development” shows me as the first result. And I’m number two for “freelance web developer”. I was surprised she didn’t know that. She asked “how did you pull that off?!” The reply: “It’s my job”.

 

Now, the problem is, that their development team is in sunny California. And I’m in NY. Yahoo! has offices in NY, but they are marketing teams. I asked what are the odds of me doing the development work for Yahoo! in NY. The reply: “Zero.”
I don’t know where it will lead. But at least it was nice to be recognized.

Post code snippets in WordPress

Here is the simplest way to easily add source code to WordPress with the built-in TinyMCE editor. There are a lot of posts of people looking for a simple solution like this. And Many people wrote plugins, that don’t really work. MoxieCode gets badmouthed for a poorly written editor. Well that’s all pretty silly. All we needed to do is read some of TinyMCE’s documentation and figure a clever CSS fix.

At the end, you’ll be able to visually embed code just like:

@ require('../../../wp-config.php');
 function wp_translate_tinymce_lang($text) {  if ( ! function_exists('__') ) {   return $text;  } else {   $search1 = "/^tinyMCELang[(['"])(.*)1]( ?= ?)(['"])(.*)4/Uem";   $replace1 = "'tinyMCELang[121]3'.stripslashes('4').__('5').stripslashes('4')";
   $search2 = "/ : (['"])(.*)1/Uem";   $replace2 = "' : '.stripslashes('1').__('2').stripslashes('1')";
   $search = array($search1, $search2);   $replace = array($replace1, $replace2);
   $text = preg_replace($search, $replace, $text);
   return $text;  } }

No plugins are needed. Minimal code changes are neccesary. And it works.

1. Edit tiny_mce_config.php:
/[wordpressInstallRoot]/wp-includes/js/tinymce/tiny_mce_config.php

1a. Add the Format select box:
Line 31, starts with “$mce_buttons = apply_filters(‘mce_buttons’, array(‘…”
Append to the end of that array, “, ‘formatselect’, ‘cleanup’, ‘removeformat'”. So you should now have that line as:

$mce_buttons = apply_filters('mce_buttons', array('bold', 'italic', 'strikethrough', 'separator', 'bullist', 'numlist', 'outdent', 'indent', 'separator', 'justifyleft', 'justifycenter', 'justifyright', 'separator', 'link', 'unlink', 'image', 'wp_more', 'separator', 'spellchecker', 'separator', 'wp_help', 'wp_adv_start', 'wp_adv', 'separator', 'formatselect', 'underline', 'justifyfull', 'forecolor', 'separator', 'separator', 'charmap', 'separator', 'undo', 'redo', 'wp_adv_end', 'formatselect', 'cleanup', 'removeformat'));

1b. Use your blog’s style:
Line 43, starts with “$mce_popups_css = get_option(‘siteurl’)…”
Replace lines 43 through 49 with:

 $mce_popups_css = '/[wordpressInstallRoot]/wp-includes/js/tinymce/plugins/wordpress/popups.css'; $mce_css = '/[wordpressInstallRoot]/wp-includes/js/tinymce/plugins/wordpress/wordpress.css, /[wordpressInstallRoot]/wp-content/themes/[yourCurrentTheme]/style.css'; $mce_css = apply_filters('mce_css', $mce_css); /*if ( $_SERVER['HTTPS'] ) {  $mce_css = str_replace('http://', 'https://', $mce_css);  $mce_popups_css = str_replace('http://', 'https://', $mce_popups_css); }*/

1c. Install the Block Format Menu:
In front of line 57, add:

theme_advanced_blockformats : "pre,p,div,h1,h2,h3,h4,h5,h6,blockquote,code",

1d. Modify TinyMCE’s line break behaviour:
Replace lines 72 through 75 with:

 force_p_newlines : false, force_br_newlines : false, convert_newlines_to_brs : false, remove_linebreaks : false,

1e. Modify TinyMce’s pasting behaviour:
Replace lines 76 through 78 with:

 paste_create_paragraphs : false, paste_create_linebreaks: true, paste_auto_cleanup_on_paste : true,

2. Edit your stylesheet for proper viewing in TinyMCE.
/[wordpressInstallRoot]/wp-content/themes/[yourCurrentTheme]/style.css’

By default TinyMCE will pick up the BODY style of your stylesheet. Pretty slick, except that most sites have content in a div or td of a different style. So lets fix that.

2a. Add a style for TinyMCE’s body:
This part really depends on your current wordpress theme. What you need to do is find the style of your content div or td, and apply that to TinyMCE’s window.
At the top of your stylesheet, add:

body.mceContentBody { text-align: left; background: #FFF;}

The contents of this should match the contents of your div or td.

2b. The style of our embedded code:
Add another section to style our blocks of code, similar to:

PRE { background-color:#EFEFEF; border-left:5px solid #DDDDDD; color:#000000; font-family:"Andale Mono","Courier New",fixed-width; font-size:11px; margin:15px 0px 15px 10px; overflow:auto; padding:5px 5px 20px 5px; line-height:1em; width: 530px;}

Of course, you style is to your own taste. But I would suggest only fixed width fonts, non breaking lines, and auto overflow for scrollbars. The bottom padding of 20px is important for the bottom scroll bar. It’s actually a workaround for a particular browser. What to guess which one? I set the width to the width of my div as well, so that in TinyMCE, I get the correct view of it – scrollbars and all.

Instructions

Enter your post as you normally would. Let TinyMCE go buck wild – making <p> tags all over the place.
For each block of code you pasted, select the block and use the format menu to select ‘Preformatted’. You should see that block of code in all its glory. “Hey, the line breaks are gone!” BloggerSayWha? Don’t fret. Save the post and the line breaks will magically appear.

Enjoy =)