ColdFusion Ajax & JQuery ... Learn to Earn

Recently I decided that it's time to start paying a bit more attention to improving the visual presentation of one of my sites. The site's built in ColdFusion with lot's of nifty CF Ajax stuff thrown in. CFDiv's, CFWindows, and Pods here and there too. So, I figured since I'm not too bad at coding in CF - at least I can usually Google my way through most of the snags I run in to - I wanted to try challenging myself a bit by taking on something fairly foreign to me. Ajax.

I know the basics of Ajax. Asynchronous JavaScript with XML - Sending and receiving Http requests and responses to a server to return data to a site without refreshing the documents. However, I had little experience actually taking on an Ajax project. But if that wasn't difficult enough, I wanted to get really frustrated, so I decided to use a JavaScript library that I had little if any experience with - Entrance JQuery.

Now I've seen a lot of very groovy examples of the nifty UI tricks that can be done with JQuery. They are cool. But I really wanted to learn how to use JQuery for my specific needs. So, I did. I choose to launch my campaign with a real world example. So for my project, I mapped out what I wanted to do with ColdFusion, JQuery, and Some CFAjax stuff. Here's what I planned out ... First, I wanted to add a US database to my site, SEOMasterList.com, to build out some dynamic pages. The reasons for that should be quite apparent. I'm planning to obtain rankings and possibly monetize the site.

Next, I wanted to add some cool visual effects while providing improved functionality to the site, and possibly give visitors a better experience.

And finally, I just wanted to see if I could actually do it - two weeks in to it, I finished it. I have got to admit I spent a hell of a lot of time learning while trying to get the site done - someone with experience could have probably launched it in a matter of no time. But, I learned a lot.

Once I modified, installed, and built out the components to accommodate the addition of the database, I set up another component to dynamically bind the results from the database to two selects in a form. This was very challenging - I spent the better part of two days just trying to get the bindings to work. Luckily, I found a cool ColdFusion script and modified it to output my database queries to XML. That came in really handy. The first go at trying to output 80,000+ records to a single flat XML file didn't work out to well. I ended up rebuilding the data CFC's several times in order to get what I needed. I ended up with a 1.4 MB XML file that I'm now using to bind to the data in the selects.

After tackling that part of the project, I wanted to introduce some visually appealing effects to the site so I picked up the latest JQuery code and sent it up to my site. After some effort, I was successful in creating a pretty slick interface for my Ajax loaded form. Through the use of some nifty ('#div').show(),('#div').hide () functionality on the form page, I was able to add and remove elements of the page which weren't necessary at every stage of the form submission process. This proved to be tougher to implement than I had expected. Up to this point I have a form bound by CFC's to data, an asynchronous form handler to process a form submission, and some nifty JQuery going on to assist in dressing up my application modifications. All in all. I think it went really well - and I learned a whole hell of a lot doing it. If you want to see what I did ... have a heart ... I'm an Ajax nOOb. Check it out on SEOMasterList ...

SQL UDF to Convert Uppercase to Propercase

So, I figured that it would be a good idea to try and expand Florida Search Engine Optimization web coverage beyond the Miami, Fort Lauderdale areas. So, I started a project to build a quazi regional network in my site. At first, I tried to be creative and store all of the cities and counties of Florida in an array with the counties as an array of structs.

With some work, I was able to get this running. However, I realized that if and when I want to make changes or additions to the cities or counties, it was going to be quite a bit of work.

After some consideration I decided that it would be much easier to just use a database table with the cities and counties.

Now, everything was going quite well with the addition of the new database when I found that the city and county fields were all in uppercase ... I really didn't want to leave the data like that, so I decided to figure out how to change the 80,000 plus records to propercase, and I found just the thing ... a cool little T-SQL UDF ... that changes the data to propercase ... the code follows ...

view plain print about
1create FUNCTION PROPERCASE
2(
3--The string to be converted to proper case
4@input varchar(8000)
5)
6--This function returns the proper case string of varchar type
7RETURNS varchar(8000)
8AS
9BEGIN
10IF @input IS NULL
11BEGIN
12--Just return NULL if input string is NULL
13RETURN NULL
14END
15
16--Character variable declarations
17DECLARE @output varchar(8000)
18--Integer variable declarations
19DECLARE @ctr int, @len int, @found_at int
20--Constant declarations
21DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int
22
23--Variable/Constant initializations
24SET @ctr = 1
25SET @len = LEN(@input)
26SET @output = ''
27SET @LOWER_CASE_a = 97
28SET @LOWER_CASE_z = 122
29SET @Delimiter = ' ,-'
30SET @UPPER_CASE_A = 65
31SET @UPPER_CASE_Z = 90
32
33WHILE @ctr <= @len
34BEGIN
35--This loop will take care of reccuring white spaces
36WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) >
 0
37BEGIN
38SET @output = @output + SUBSTRING(@input,@ctr,1)
39SET @ctr = @ctr + 1
40END
41
42IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
43BEGIN
44--Converting the first character to upper case
45SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
46END
47ELSE
48BEGIN
49SET @output = @output + SUBSTRING(@input,@ctr,1)
50END
51
52SET @ctr = @ctr + 1
53
54WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
55BEGIN
56IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
57BEGIN
58SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
59END
60ELSE
61BEGIN
62SET @output = @output + SUBSTRING(@input,@ctr,1)
63END
64SET @ctr = @ctr + 1
65END
66
67END
68RETURN @output
69END
70
71GO
72SET QUOTED_IDENTIFIER OFF
73GO
74SET ANSI_NULLS ON
75GO

So all that was required to get this to work, was to call the UDF after it was created ...

view plain print about
1END
2RETURN @output
3END
4
5GO
6SET QUOTED_IDENTIFIER OFF
7GO
8SET ANSI_NULLS ON
9GO
10
11--Insert your call here ... 
12UPDATE TABLENAME 
13SET fieldname=dbo.PROPERCASE(fieldname)
14FROM DBNAME..TABLENAME

Now this saved me a hell of a lot of time and was a very simple and easy way to format the data the way that I wanted. Unfortunately, when I went to repeat the same action for the counties, I accidentally wrote over the city fields with the data in the counties field ... Smooth.

It would really come in handy if something like this could have been done in ColdFusion without having to create a UDF to do so ... maybe the guys over at Adobe will read this and decide that it merits inclusion into the next release.

BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Florida Search Engine Optimization L.L.C.
Search Engine Optimization Specialist || Web Designer || Web Developer || Edward J Beckett ||
Search Engine Optimization Company  || SEO Services || Internet Marketing Company || Search Engine Optimization Expert || Florida Search Engine Optimization LLC
Florida Search Engine Optimization || Search Engine Optimization || SEO Services || Florida SEO Blog
Florida Search Engine Optimization
Search Engine Optimization
SEO Services
Florida SEO Blog
February-04-2012
4:00 PM EST