Freelance Web Development

I created a new compressed file for Prototype 1.6.1 and Scriptaculous 1.8.3. Included is my modified sound.js.

The combined file is 268KB. The load order is as follows:

• prototype
• builder
• effects
• dragdrop
• controls
• slider
• sound

It includes all comments and credits. I gzipped it down to 60KB.

For your convenience in the attached zip contains both versions and a ColdFusion file to serve the correct one. You’d load it like so:

<script type="text/javascript" charset="ISO-8859-1" src="/includes/js/scriptaculous/scriptaculous.cfm"></script>

Well, what are you waiting for? wv-scripty-183.zip

JavaScript, Scriptaculous

I manage a site with nearly 50GB of small PDFs. Thousands of directories and files. This makes CFFM super duper slow. I know it sounds silly, but when staring at a blank window, 15 seconds feels like 15 minutes. The problem is that with every page load, the entire directory tree below your initial directory is read. Crazy. Here is the fix…

At around line 648 of cffm.cfm, only turn on recursion if this particular action requires it:

<cfset variables.listAllFiles = cffm.directoryList(cffm.includeDir, (isdefined("url.action") and url.action eq 'copymoveForm'))>

… which is, when moving or copying. In my application, speed increased 10x.
And to get a little more speed when listing a directory with images…

At around line 67 of cffm.cfm, turn off image sizes:

<cfinvokeargument name="enableImageDimensionsInDirList" value="true">

ColdFusion cffm, cms, recursion, slow

CFSelect is nice because it can help you write out some code quicker. Just like the rest of ColdFusion. But I never used it because so many times I needed to display two columns in the <option>, such as “#lastName#, #firstName#”.

While working on a site I fell into the same routine. I start typing out CFSelect then grit my teeth when I get to the Display attribute. Being stubborn about it I stumbled upon a nice MySQL function called CONCAT(). Using that one can merge two columns in the query to be output as a single column name. Like so:

<cfquery name="emps" datasource="#application.datasource#">
SELECT
 id, CONCAT(lastname, ', ', firstname) AS fullName
FROM
 employees
ORDER BY
 lastname, firstname
</cfquery>

Then simply use that column name for the display (or elsewhere needed):

<cfselect name="employeeid" query="emps" selected="" value="id" display="fullName"/>

ColdFusion, MySQL, Web Coding cfquery, cfselect, column, concat(), display, option, query, select

Sometimes it is useful to show in a list the numbers of children each object has. For instance how many Products are in each Category.

That’s simple enough to do when only one child type is used:

SELECT
 categories.title, categories.id,
 count(products.id) as productCount
FROM
 categories
   INNER JOIN products ON categories.id = products.categoryid

However, if you wanted to count two separate children types, the same method of querying will give you very odd results:

SELECT
 categories.title, categories.id,
 count(products.id) as productCount, count(images.id) as imagesCount
FROM
 categories
   INNER JOIN products ON categories.id = products.categoryid
   INNER JOIN images ON categories.id = images.categoryid

The lazy programmer would get around this by using the server language – looping through a simple query of the category, and with each row run another query to count the products (and another for the images). Yuck.

Instead, I found this method to work quite well:

SELECT
 cats.title AS title, cats.ID AS id,
 COALESCE(prods.howmany,0) AS productCount,
 COALESCE(imgs.howmany,0) AS imageCount
FROM (
 SELECT title, ID
 FROM categories
) AS cats
LEFT JOIN (
 SELECT
   count(products.id) as howmany,
   products.categoryid as categoryid
 FROM products
   LEFT JOIN categories ON products.categoryid = categories.id
 GROUP BY products.categoryid
) AS prods ON cats.id=prods.categoryid
LEFT JOIN (
 SELECT
   count(images.id) as howmany,
   images.categoryid as categoryid
 FROM images
   LEFT JOIN categories ON images.categoryid = categories.id
 GROUP BY images.categoryid
) AS imgs ON cats.id=imgs.categoryid

Note the use of the ‘COALESCE()’ function so that nulls are returned as a 0 (zero).
As you can see the database is still running three queries. But they are all within one connection, and the server language didn’t do any work.  Then MySQL joins those queries together into one result, easily parsed by your server language of choice. Here, ColdFusion:

<table>
 <tr>
   <td>Category</td>
   <td>Products</td>
   <td>Images</td>
 </tr>
 <cfoutput query="read">
 <tr>
   <td>#title#</td>
   <td>#productCount#</td>
   <td>#imageCount#</td>
 </tr>
 </cfoutput>
</table>

MySQL, Web Coding coalesce(), count(), inner join, left join

A big thank to Mr. Camden for giving me a few minutes of his time! With his insight I was able to remove the required Vscope argument. Instead, multiple instances per page are tracked through Request. No more required argument, and it’s backwards compatible again.

Grab the update here: http://github.com/WebVeteran/cf-tinymce/

CF TinyMCE, ColdFusion, TinyMCE cfargument, Request, vScope

I have made quite a few Clips in Coda to make development a little speedier. Hopefully you can get some use out of them too.

Preview of Coda Clips - keep in mind the clips window only shows the first line of each clip.

Download the Clips here:
ColdFusion Coda Clips

Coda, ColdFusion clips

I updated CF tinyMCE with a small nicety. It now automatically handles multiple instance per page. Previously, it would have to be invoked like so:

<cfoutput>
<tr>
	<td>
		#application.includes.tinymce(
			content=read.en,
			element="en",
			csswidth="444px",
			resizing=true,
			theme='advanced',
			theme_advanced_buttons1 = "bold,italic,underline,|,justifyleft,justifycenter,justifyright,justifyfull,|,formatselect,|,bullist,numlist,|,undo,redo,|,code,cleanup",
			theme_advanced_buttons2 = "",
			theme_advanced_buttons3 = ""

		)#
	</td>
	<td>
		#application.includes.tinymce(
			content=read.es,
			element="es",
			csswidth="444px",
			resizing=true,
			theme='advanced',
			loadbase='0',
			language='es',
			theme_advanced_buttons1 = "bold,italic,underline,|,justifyleft,justifycenter,justifyright,justifyfull,|,formatselect,|,bullist,numlist,|,undo,redo,|,code,cleanup",
			theme_advanced_buttons2 = "",
			theme_advanced_buttons3 = "",
			gecko_spellcheck = "false"
		)#
	</td>
</tr>
</cfoutput>

Notice the ‘loadbase=0′ argument on the second invocation. That told the previous version of CF TinyMCE to not all of TinyMCE again – just load the settings instead.

The new version automatically handles multiple instances and not loading TinyMCE all over again for subsequent invocations on the same page. Therefore, the new syntax example is:

<cfoutput>
<tr>
	<td>
		#application.includes.tinymce(
			VScope=Variables,
			content=read.en,
			element="en",
			csswidth="444px",
			resizing=true,
			theme='advanced',
			theme_advanced_buttons1 = "bold,italic,underline,|,justifyleft,justifycenter,justifyright,justifyfull,|,formatselect,|,bullist,numlist,|,undo,redo,|,code,cleanup",
			theme_advanced_buttons2 = "",
			theme_advanced_buttons3 = ""

		)#
	</td>
	<td>
		#application.includes.tinymce(
			VScope=Variables,
			content=read.es,
			element="es",
			csswidth="444px",
			resizing=true,
			theme='advanced',
			language='es',
			theme_advanced_buttons1 = "bold,italic,underline,|,justifyleft,justifycenter,justifyright,justifyfull,|,formatselect,|,bullist,numlist,|,undo,redo,|,code,cleanup",
			theme_advanced_buttons2 = "",
			theme_advanced_buttons3 = "",
			gecko_spellcheck = "false"
		)#
	</td>
</tr>
</cfoutput>

No more loadbase! The key is the argument “Vscope=Variables”. That lets CF pass the Variables scope back and forth between a page and a function. It is a required argument which I am not happy about. But since it’s value never changes it’s not so bad.

Because of this fundamental change, CF TinyMCE 090612 is not backwards compatible. If anyone knows how to pass the Variables scope back and forth between a page and a function, without an argument like I am here – please let me know!

You can download CF TinyMCE at github: http://github.com/WebVeteran/cf-tinymce/

CF TinyMCE, ColdFusion, TinyMCE CFC, coldfusion function, vScope

In previous posts I found a fix for an intermittent problem with IE6 not loading compressed javascripts. IE was loading the first zero bytes of the gzipped javascripts. But if you copy-paste the url of the javascript it loads fine. And then the pages with those javascripts work just fine from there on. That fix was to exclude IE6 from receiving the compressed version.

Well, this fix is much better. Just add blank Pragma and Cache-control headers. No need to exclude IE6 from receiving the compressed js.

<cfif cgi.HTTP_ACCEPT_ENCODING contains "gzip">
	<cfheader name="Content-Encoding" value="gzip" >
	<cfheader name="Content-Disposition" value="inline; filename=""protoaculous182min.jgz""">
	<cfheader name="Content-Type" value="application/x-javascript; charset=ISO-8859-1">
	<cfif cgi.HTTP_USER_AGENT contains 'MSIE 6.0; Windows'>
		<cfheader name="Pragma" value="">
		<cfheader name="Cache-control" value="">
	</cfif>
	<cfcontent deletefile="no" file="#expandpath('./protoaculous182min.jgz')#" type="application/x-javascript; charset=ISO-8859-1">
<cfelse>
	<cfheader name="Content-Disposition" value="inline; filename=""protoaculous182min.js""">
	<cfheader name="Content-Type" value="application/x-javascript; charset=ISO-8859-1">
	<cfinclude template="protoaculous182min.js">
</cfif>

ColdFusion, JavaScript, Scriptaculous cfcontent, cfheader, HTTP_USER_AGENT, ie6

Recently I was working on a project where the store gets data files from a vendor. XLS files! Just horrid. After going through the hoops for XLS-to-MySQL it was time to churn the records into the store’s actual database tables for xCart. It turns out there were duplicate SKUs in the vendor’s data files. So that had to be remedied, by giving my client a XLS of the 700+ duplicated records for evaluation.

The following will find all skus that are used more than once. You only get a single record for each sku.

SELECT sku
FROM 2009_product_catalog
GROUP BY sku
HAVING count(sku) > 1

Example output:

10150	PSE Hunter Hip Quiver
16294	CAP No Snow Water Repellent
16701	Vista Rustler Quiver
16714	Allen Broadhead Hip Quiver

You can then use those results in a WHERE IN clause to get all the records based on duplicated skus:

SELECT *
FROM 2009_product_catalog
WHERE sku IN (
	SELECT sku
	FROM 2009_product_catalog
	GROUP BY sku
	HAVING count(sku) > 1
	)
ORDER BY sku

Example output:

10150	PSE Hunter Hip Quiver
10150	PSE Hunter Hip Quiver
16294	CAP No Snow Water Repellent
16294	CAP No Snow Water Repellent
16701	Vista Rustler Quiver
16701	Vista Rustler Quiver
16714	Allen Broadhead Hip Quiver
16714	Allen Broadhead Hip Quiver

MySQL excel, xcart, xls

<cfif cgi.HTTP_ACCEPT_ENCODING contains "gzip" and cgi.HTTP_USER_AGENT does not contain 'MSIE 6.0; Windows'>
	<cfheader name="Content-Encoding" value="gzip" >
	<cfheader name="Content-Disposition" value="inline; filename=""protoaculous182min.jgz""">
	<cfheader name="Content-Type" value="application/x-javascript; charset=ISO-8859-1">
	<cfcontent deletefile="no" file="#expandpath('./protoaculous182min.gz')#" type="application/x-javascript; charset=ISO-8859-1">
<cfelse>
	<cfheader name="Content-Disposition" value="inline; filename=""protoaculous182min.js""">
	<cfheader name="Content-Type" value="application/x-javascript; charset=ISO-8859-1">
	<cfinclude template="protoaculous182min.js">
</cfif>

ColdFusion, JavaScript, Scriptaculous cfcontent, cfheader, Content-Disposition, Content-Type, gz, gzip, HTTP_ACCEPT_ENCODING, HTTP_USER_AGENT, safari