Creating a list of record data from a join
Very interesting post here. Only works in MS-SQL but it returns a list of data in a column in your query. I quite often need to do this but unfortunately it does create a function in the database itself. Still a great way of cutting down on queries on the page.
If you are wondering how you would use this. Well say you had a table of products and a table of categories and you wanted to retrieve the related categories in a separate column as a list, this function would allow you to do this. I can already think of many uses!
Farleigh Hospice – Lets see if we can hit James’ £5000 target!
James Frost (my boss) is having an amazing 40th Birthday party on Saturday. Rather than get him a gift he is asking everyone to donate to Farleigh Hospice. Farleigh Hospice provide care to families who have been touched by cancer or other life limiting illnesses. they offer a huge range of services which are free.
You can donate here: http://www.justgiving.com/jamesfrost1969
Go nuclear with Reactor for Coldfusion
I’m sure, like me you get extremely bored of doing add,edit,delete,add,edit,delete,add,edit,delete. Well i’ve always been looking for a quicker way to develop this sort of code, yes you can create templates where you just fill in all the variables, but more often than not you end up re-writing the whole thing anyway. Well finally, bingo! i’ve found out what i was trying to do is called an “Inline Dynamic Database Abstraction” API. The API is called the reactor framework. It basically allows you to create a table in your database and from that table you can perform all your database queries on-the-fly. Another great feature is that you can create relationships between tables via a simple XML file with this easy syntax:
<object name=”mRegion”>
<hasMany name=”sys_countries”>
<link name=”mRegionCountry” />
</hasMany>
</object>
<object name=”sys_countries”>
<hasMany name=”mRegion”>
<link name=”mRegionCountry” />
</hasMany>
</object>
<object name=”mRegionCountry”>
<hasOne name=”sys_countries”>
<relate from=”country_id” to=”country_id” />
</hasOne>
<hasOne name=”mRegion”>
<relate from=”regionID” to=”regionID” />
</hasOne>
</object>
This bit of code basically says a region has many countries and they link via a table called mRegionCountry.
It’s a lot more powerful than this but so far this as deep as i’ve gone. I’ve started using it in one of my applications and so far it’s saved me bags of time. I won’t go into details but after lots of googling i’ve found some very good documents the Introduction to Reactor for Coldfusion is great starter guide and is explained very simply with good examples. Also the .doc file that comes with the download is very good. Take a look at the examples too.
I’ll be posting more code in the future as I go along.
We’ve moved offices!
You’ll find more info about our move here
Remove carriage returns from a string
Quite often I need to remove carriage returns and usually I use the following code:
Replace(myvar,”#chr(13)#”,”",”ALL”);
However I have found this doesn’t always remove the carriage returns.
How did I fix it? I used the following code:
REReplace(myvar,”#chr(13)#|\n|\r”,”",”ALL”);
This works everytime, its very handy for writing html with javascript
for example:
<cfsavecontent variable=”thecode”>
<cfinclude template=”#dynamicCFMfile#”>
</cfsavecontent>
<cfscript>
newDefaultCode = Replace(thecode ,”"”",”\”"”,”ALL”);
newDefaultCode = REReplace(newDefaultCode ,”#chr(13)#|#chr(9)#|\n|\r”,”",”ALL”);
</cfscript>
<cfoutput>theHTML = “#newDefaultAdCode#”;</cfoutput>
document.write(theHTML);
This allows me to dynamically write a coldfusion template to the page via javascript.
Coast Digital antics!
We’ve just signed up with Flickr (about time!) and we are going to be posting examples of our work, cool photos and us just generally having fun! Come and have a look
New site goes live!
We have just launched our new website.
We’ve updated our branding and website to reflect how Coast Digital has evolved.
Check it out
http://www.coastdigital.co.uk
I love XSL!
I have been fighting with recursion for ages now and have found it to always be a real problem effecting performance, quite often you have to use complicated caching techniques, until now!
Using XSL you can perform recursion techniques on flat XML data. It sounds complicated but really it isn’t.
Say you have node data like the following (in a flat structure).
<tree>
<node icon="" id="1" objectID="9" parentID="0" recordID="15" ref="sites" title="Sites"/>
<node icon="" id="2" objectID="9" parentID="0" recordID="28" ref="media" title="Template Media"/>
<node icon="" id="3" objectID="9" parentID="0" recordID="31" ref="templates" title="Templates"/>
<node icon="site.png" id="4" objectID="11" parentID="1" recordID="1" ref="refresh--test" title="Refresh"/>
<node icon="page.png" id="5" objectID="1" parentID="4" recordID="134" ref="home" title="Home"/>
<node icon="form.png" id="6" objectID="12" parentID="5" recordID="60" ref="enq_form" title="Enquiry Form"/>
<node icon="form.png" id="7" objectID="12" parentID="5" recordID="61" ref="site_review" title="Site Review"/>
<node icon="menu.png" id="11" objectID="5" parentID="4" recordID="8" ref="topnav" title="Top Nav 2"/>
<node icon="" id="12" objectID="9" parentID="0" recordID="33" ref="users" title="Users"/>
</tree>
Using XSL you can build a hierarchical tree from this data:
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"> <xsl:template match="tree">
<!-- Starts the tree -->
<ol>
<xsl:apply-templates/>
</ol>
</xsl:template>
<!-- Selects root nodes -->
<xsl:template match="//node[@parentID=0]"> <xsl:call-template name="process-branch">
<xsl:with-param name="id"><xsl:value-of select="@id"/></xsl:with-param>
<xsl:with-param name="parentID"><xsl:value-of
select="@parentID"/></xsl:with-param>
</xsl:call-template> </xsl:template> <!-- Recursive function -->
<xsl:template name="process-branch">
<xsl:param name="id"/>
<xsl:param name="parentID"/> <xsl:choose>
<!-- If element has no sub-nodes -->
<xsl:when test="count(//node[@parentID=current()/@id])=0">
<!-- Writes the node -->
<li>
<a href="" mce_href=""><xsl:value-of select="@title" /></a>
</li>
</xsl:when>
<!-- If element has sub-nodes-->
<xsl:otherwise>
<!-- Starts a node with children element -->
<li>
<a href="" mce_href=""><xsl:value-of select="@title" /></a>
<ol>
<!-- For every sub-nodes of current node -->
<xsl:for-each select="//node[@parentID=current()/@id]">
<!-- Recurse the branch processing -->
<xsl:call-template name="process-branch">
<xsl:with-param name="id"><xsl:value-of
select="@id"/></xsl:with-param>
<xsl:with-param name="parentID"><xsl:value-of
select="@parentID"/></xsl:with-param>
</xsl:call-template>
</xsl:for-each>
</ol>
</li>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>
Obviously you can replace the html with whatever elements you wish, even build hierarchical XML.
You then use the (rather handy) coldfusion function
#XMLTransform(XMLParse(xmldata), xslSheet)#
And there you have it, no long loops with recurring queries!
Finding an e-commerce package
About 4 months ago we purchased a license for a coldfusion e-commerce software package, but only now am I finding functionality missing that I assumed would be there. For example the system allows you to enter discounts, but it doesn’t cater for “buy x get x free” and today a customer wanted to know how many customers they had. I went to reporting and couldnt find a report for this simple piece of information. I had to do a search on the customers and read how many results were returned. Not ideal.
I think it’s very hard to think about all the functionality you require when you are buying an e-commerce solution. It’s also hard to find something that does everything you want but doesn’t cost too much per-license. Most people who are looking to start a shop don’t often have a lot to spend and they see advertisements like “E-commerce solution £9.99 a month” which makes them question why they have to pay such a hefty price tag for your solution.
I’d love to hear your thoughts and If you have any recommendations for any other software packages.
Blog CFC customization
I don’t know if any of you have used Blog CFC but it’s a great little app. I have been implementing it into our CMS. I have found a few problems with it though, for example if you want the admin completely seperate in a different folder structure it’s very hard to get the links to work.
I also didnt really like the calendar aspect of the tool so I have written my own “pod” for just showing the months and years with posts.
in the blog.cfc i have written the following function:
<cffunction name="getActiveMonths" returnType="query" output="false" hint="Returns a query of months and years with Entries.">
<cfargument name="year" type="numeric" required="false">
<cfset var months = "">
<cfset var posted = ""> <cfif instance.blogDBType is "MSSQL">
<cfset posted = "dateAdd(hh, #instance.offset#, tblblogentries.posted)">
<cfelseif instance.blogDBType is "MSACCESS">
<cfset posted = "dateAdd('h', #instance.offset#, tblblogentries.posted)">
<cfelseif instance.blogDBType is "MYSQL">
<cfset posted = "date_add(posted, interval #instance.offset# hour)">
<cfelseif instance.blogDBType is "ORACLE">
<cfset posted = "tblblogentries.posted + (#instance.offset#/24)">
</cfif>
<cfquery datasource="#instance.dsn#" name="months" username="#instance.username#" password="#instance.password#">
select distinct
<cfif instance.blogDBType is "MSSQL">
datepart(mm, #preserveSingleQuotes(posted)#)
<cfelseif instance.blogDBType is "MYSQL">
extract(month from #preserveSingleQuotes(posted)#)
<cfelseif instance.blogDBType is "MSACCESS">
datepart('m', #preserveSingleQuotes(posted)#)
<cfelseif instance.blogDBType is "ORACLE">
to_char(#preserveSingleQuotes(posted)#, 'mm')
</cfif> as posted_month,
<cfif instance.blogDBType is "MSSQL">
datepart(yyyy, #preserveSingleQuotes(posted)#)
<cfelseif instance.blogDBType is "MYSQL">
extract(year from #preserveSingleQuotes(posted)#)
<cfelseif instance.blogDBType is "MSACCESS">
datepart('y', #preserveSingleQuotes(posted)#)
<cfelseif instance.blogDBType is "ORACLE">
to_char(#preserveSingleQuotes(posted)#, 'yyyy')
</cfif> as posted_year
from tblblogentries
where
blog = <cfqueryparam value="#instance.name#" cfsqltype="CF_SQL_VARCHAR" maxlength="50">
and #preserveSingleQuotes(posted)# < <cfqueryparam cfsqltype="cf_sql_timestamp" value="#blogNow()#">
and released = 1
ORDER BY posted DESC
</cfquery> <cfreturn months>
</cffunction>
Then in the front end in the calendar.cfm pod I commented out the calendar and put in the following code:
<cfscript>
monthsQuery=application.blog.getActiveMonths();
</cfscript>
<ol>
<cfoutput query="monthsQuery">
<li><a href="#application.rooturl##posted_year#/#posted_month#" mce_href="#application.rooturl##posted_year#/#posted_month#">#monthAsString(posted_month)# #posted_year#</a></li>
</cfoutput>
</ol>
It seems to work a treat!
