Freelance Web Development

Fast answer: use IFNULL() in your select statement.

Lets say you have some products that have a length attribute which is recorded in two different ways: inches and feet. In your CMS, the client enters EITHER inches OR feet. When generating a list of products sorted by length, you’d want the ‘inch’ products up top and the ‘feet’ products under them. At first, you’d probably try this:

SELECT id, title, lenIN, lenFT
FROM products
ORDER BY lenIN, lenFT

But that would give you the ‘feet’ products first since the inch values would be null. A second attempt:

SELECT id, title, lenIN, lenFT
FROM products
ORDER BY lenIN DESC, lenFT

That of course puts the products in the wrong ordering. Here is the fix:

SELECT id, title, IFNULL(lenIN, 1000) as tempIN, lenIN, lenFT
FROM products
ORDER BY tempIN, lenIN, lenFT

It comes from the magic of:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2. So when inches is null, a value of 1000 is used. I picked ‘1000′ because I’m sure no product will be 1000 inches long (feet would surely be used at that point). The result is the product which have inches filled will be put at the top of the list, ordered correctly, followed by the products where inches were blank (and are now 1000 inches).

MySQL

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

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

Inspired by posts by Ray Camden and then Adrian J. Moreno, I’ve come up with the same solution for MySQL:

SELECT *
FROM (
	SELECT
		@row := @row +1 AS rownum, noun
	FROM (
		SELECT @row :=0) r, nouns
	) ranked
WHERE rownum %4 =1 

The table name is ‘nouns’. The column with data we want is ‘noun’.

As you can see from the return, it is working. This table has over 752 rows, but MySQL gave us every 4th:

MySQL

Using WordPresses’ RSS import, I was able to copy every post from blogCFC easily. WordPress expects a local file when doing so. And blogCFC’s rss feed is limited to 15. To override that, edit file ‘blog.cfc’. It will be in the following path under your blogCFC installation: /org/camden/blog/. Just set arguments.params.maxEntries to something very very high. If you have less than 1500 posts, the following will do (starting at around line 742):

<!--- Right now, we force this in. Useful to limit throughput of RSS feed. I may remove this later. --->
<cfif (structKeyExists(arguments.params,"maxEntries") and arguments.params.maxEntries gt 15) or not structKeyExists(arguments.params,"maxEntries")>
	<cfset arguments.params.maxEntries = 15>
</cfif>
<cfset arguments.params.maxEntries = 1500>

Then, reinit your session to refresh the blog’s cache. Then go to your feed’s url with your web browser. You should have every single article there. Save that rss as a local xml file. Upload that file into WordPress. Great!

But comments are not copied. So I made and used the below script to copy all post comments from blogCFC to WordPress. Not too difficult! 10 minutes tops.

First, prep the blogCFC database. In the tblblogentries table (where blogcfc keeps the blog posts), add a column named ‘wpid’. For each row, enter WordPresses’s ID of the same post. I had 43 entried so it did not take a heck of a long time. Just sort both tables by title and do it. I suppose you can write a script for this too, but not worth the effort for 43 entries.

With that done, here is the CF code:

<cfquery name="read" dataSource="your-blogcfc-datasource">
SELECT tblblogcomments.*, tblblogentries.wpid
FROM tblblogcomments INNER JOIN tblblogentries ON tblblogcomments.entryidfk = tblblogentries.id
</cfquery>

<cfoutput query="read">
	#wpid# #name# #email# #posted# #website#<br>

	<cfquery name="ins" dataSource="your-wordpress-datasource">
	INSERT INTO wp_comments
	(comment_post_ID, comment_author, comment_author_email, comment_author_url, comment_date, comment_content, comment_approved)
	VALUES
	('#wpid#', '#name#', '#email#', '#website#', '#posted#', '#comment#', 1)
	</cfquery>
</cfoutput>

Run that page in a browser and all comments are imported! Nice. Then one more step. For each WP post, you have to update the post count. Again, not a big deal. Sort your WP comments table (wp_comments) by comment_post_id and do some simple math.

ColdFusion, MySQL, News WordPress

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

SOURCE: Emergent Success, Inc.

Sep 26, 2007 14:32 ET

MOUNTAIN VIEW, CA–(Marketwire – September 26, 2007 ) – Emergent Success Inc. today announced the
launch of ES-Extranet a proprietary web-based collaboration tool for use by
their clients, partners and consultants. Since the email "inbox" was not
designed as a collaborative work environment, this technological solution
creates the open space where discussion, calendaring, asset sharing and
other collaborative activities are easily practiced and navigated.

Emergent Success, Inc. is a collaborative consulting company that
facilitates dialogues to assist clients in solving their real-time
problems. Even though the preference is to do this work in vivo, the
addition of the ES-Extranet will allow anyone involved in a current
collaboration to participate in an asynchronous manner. The principals at
Emergent Success believe that in the same way that there is enormous value
in gathering people together for in-person dialogues, there is also
significant value to "virtual" dialogues. Principal Kevin Buck explains
"Experience has shown that to engage people with an online tool once you
have engaged them in person maximizes any collective effort — it is not an
either/or, but a both/and."

With an eye to collaborative integrity, Emergent Success engaged with David
Muro, designer, and Jules Gravinese, web developer, to co-create this new
tool. Each brought the best of their knowledge and experience to bear as
they developed the intuitive feel and ease of use of this online workspace.
Since collaborations are an iterative process, we look forward to the
ongoing learning for our company, consultants and clients.

About Emergent Success, Inc.

Emergent Success assists clients to solve their real-time issues by
liberating the collective wisdom, talent and energy from within their
organization for the emergence of strategic success. Its senior
consultants facilitate collaborative dialogues that create systemic
integration amongst the unintended silos present in most organizations.
The Company is headquartered in Mountain View, CA with consultants located
across the United States. www.emergentsuccess.com

ColdFusion, JavaScript, MySQL, News, Scriptaculous, Web Coding Front End Development

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)/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 =)

ColdFusion, MySQL, Web Coding

Using ColdFusion I look for the referring web page that sent a viewer to my site. Then I split that traffic up into two categories: 1) Searches 2) Links. If a similar search term was seen before, I incriment it’s weight. Same goes for domains of links. The result is a Referer cloud, very similar in look to a Tag Cloud:

But I find this MUCH more interested than a tag cloud…
This shows me what terms people are searching for in order to find me. Also lets me know who is linking to me (if the link was clicked on). I find it REALLY damn intersting and fun to watch. And since it’s sorted by the last hit time descending, I get up to the instant information.

Look at how weighted ‘php reference pdf’ is… and I only have two pages on my site that mention php. It just goes to show you how popular php is, and the need for the decent PDF reference that I posted.

And notice how many of those search term referrals are from Google (mouse over the terms for info). It shows how dominant they really are.

Comparison

I did a little poking around online and found other search/referer clouds. But they didn’t split the search terms out. And they counted www.domain.com as sepearate from domain.com, whereas my program knows to combine them as domain.com.

I’ve looked at the source code of other referer cloud programs… they are up in 300 lines of code, and use server log files (not real time). My program is only 150 lines of code and uses real time cgi variables and a MySQL database.

Others: Google only. Mine: All search engines.

Less code, more functionality, better performance. =)

ColdFusion, MySQL, Web Coding Google, Search Engine Optimization, Tag Cloud, Yahoo