Bet you thought I forgot about this series, eh? 🙂 Got caught up dealing with the SOAP stuff – that was simple to build with Zend but ended up not working with the legacy system we were working with so had to redo it to break out of Zend all together to get it working. Was two weeks of headache though before I finally got it all figured out.
Anyway, time to switch back to ColdFusion for a bit and finish this last entry in replacing CFGrid with DataTables! Alas during the intervening time, things changed in the DataTables front with the release of v1.10 (see upgrade note for major differences!). So what was a fairly straight apples-to-apples comparison (CFGRID w/ certain flags set vs DataTables + 1 extension) isn't as easy to do. Still, I wanted to at least cover a really basic way of doing some functions so time to pull out the custom coding, yeah! Which is one beauty of using it with DataTables and jQuery, so much easier to customize!
For these examples I'm using the video game tables from part 1, but updated to use the same sort of Ajax loading as part 2. For simplicity's sake, this example just deals with a single table of data rather than a properly normalized set of tables. I'm also using just the core default features of CFGrid, without doing any additional JS to augment functionality.
So first our view code. For visual niceness, I included Pure CSS and some really basic CSS stylings because plain HTML tends to be ugly :-P. Oh and in both examples, I'm utilizing CDNs for jQuery, Pure, and DataTables rather than local hosted and HTML 5 rather than XHTML 4.01.
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="description" content="Blahdy blah blah" />
<meta name="robots" content="index, follow" />
<title>CF Grid - Editing In Grid Example</title>
<link rel="stylesheet" href="http://yui.yahooapis.com/pure/0.5.0/pure-min.css">
<link href="/cfgridtodt.css" rel="stylesheet" type="text/css">
<!--[if lte IE 8]>
<link rel="stylesheet" href="https://yui-s.yahooapis.com/pure/0.5.0/grids-responsive-old-ie-min.css">
<![endif]-->
<!--[if gt IE 8]><!-->
<link rel="stylesheet" href="https://yui-s.yahooapis.com/pure/0.5.0/grids-responsive-min.css">
<!--<![endif]-->
</head>
<body>
<div id="layout">
<h1>CF Grid - Editing In Grid Example</h1>
<cfform>
<cfgrid
name="VideoGameList"
align="Top"
autoWidth="yes"
bgColor="FFF"
colHeaderBold="yes"
format="html"
gridDataAlign="left"
gridLines="yes"
bind="cfc:cfgrid.getVideoGamesForTable({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
sort="yes"
stripeRowColor="FC6"
stripeRows="yes"
selectmode="edit"
onChange="cfc:cfgrid.updateVideoGames({cfgridaction},{cfgridrow},{cfgridchanged})"
insert="yes"
delete="yes"
>
<cfgridcolumn name="videogameid" display="no" />
<cfgridcolumn name="title" header="Title" target="_blank" width="300" />
<cfgridcolumn name="dateacquired" header="Acquired" dataalign="center" type="date" width="100" />
<cfgridcolumn name="genre" header="Genre" width="200" />
<cfgridcolumn name="num_discs" header="## Discs" width="50" />
<cfgridcolumn name="esrb_rating" header="ESRB" width="50" />
<cfgridcolumn name="publisher" header="Publisher" width="200" />
<cfgridcolumn name="platform" header="Platform" width="200" />
<cfgridcolumn name="seriesname" header="Series Name" width="200" />
</cfgrid>
</cfform>
</div>
</body>
</html>
Now our component. It's similar to the one for Part 2 except an additional function has been added to process the grid updates.
<cfcomponent>
<!--- Our function to populate our grid --->
<cffunction name="getVideoGamesForTable" access="remote" returntype="struct" output="false">
<cfargument name="Page" type="string" required="false" default="" />
<cfargument name="PageSize" type="string" required="false" default="" />
<cfargument name="GridSortColumn" type="string" required="false" default="dateacquired" />
<cfargument name="GridSortDirection" type="string" required="false" default="DESC" />
<cfset var startRow = (Arguments.Page-1) * Arguments.PageSize />
<cfset var endRow = Arguments.Page * Arguments.PageSize />
<cfset var qVideoGames = "" />
<cfif NOT LEN(TRIM(Arguments.GridSortColumn))>
<cfset Arguments.GridSortColumn = "dateacquired" />
<cfset Arguments.GridSortDirection = "DESC" />
</cfif>
<!--- obviously in a real-world use case, we'd have some validations here to be sure the sort and page stuff were valid entries; and a try/catch in case of query failure --->
<cfquery name="qVideoGames" datasource="#application.dsn#">
SELECT videogameid, title, dateacquired, num_discs, esrb_rating, publisher, genre, platform, seriesname
FROM videogames
ORDER BY #Arguments.GridSortColumn# #Arguments.GridSortDirection#
</cfquery>
<cfreturn QueryConvertForGrid(qVideoGames, Arguments.Page, Arguments.PageSize) />
</cffunction>
<!--- the function called by the grid when a change is made --->
<cffunction name="updateVideoGames" access="remote" returntype="void" output="false">
<cfargument name="CFGridAction" type="any" required="false" default="" />
<cfargument name="CFGridRow" type="any" required="false" default="" />
<cfargument name="CFGridChanged" type="any" required="false" default="" />
<cfset var sVideoGame = Arguments.CFGridRow />
<!--- in a real world scenario - these actions would be passed to a service layer to handle things! --->
<cfset var lFields = "title,dateacquired,genre,num_discs,esrb_rating,publisher,platform,seriesname" />
<cfswitch expression="#Arguments.CFGridAction#">
<!--- Values updated --->
<cfcase value="u">
<cfset var sqlType = "cf_sql_varchar" />
<cfset var IsValidRequest = true />
<!--- we use a loop even though it only does one change at a time in case we do add the multi option --->
<cfloop collection="#Arguments.CFGridChanged#" item="changedColumn">
<!--- we can't tell them something went wrong, but we can at least protect our database --->
<cfif Not ListFindNoCase(lFields, changedColumn)>
<cfset IsValidRequest = false />
</cfif>
<cfif IsValidRequest>
<cfset sqlType = "cf_sql_varchar" />
<!--- some basic checks --->
<cfswitch expression="#changedColumn#">
<cfcase value="num_discs">
<cfset sqlType = "cf_sql_integer" />
<cfif Not IsValid("integer", NewValue) OR NewValue LTE 0 OR NewValue GTE 5>
<cfset IsValidRequest = false />
</cfif>
</cfcase>
<cfcase value="dateacquired">
<cfset sqlType = "cf_sql_date" />
<cfif Not IsValid("date", NewValue) OR NewValue GT NOW()>
<cfset IsValidRequest = false />
</cfif>
</cfcase>
</cfswitch>
<cfif IsValidRequest>
<cftry>
<cfquery datasource="#application.dsn#">
UPDATE videogames
SET #changedColumn# = <cfqueryparam value="#TRIM(Arguments.CFGridChanged[changedColumn])#" cfsqltype="#sqlType#" />
WHERE videogameid = #sVideoGame.videogameid#
</cfquery>
<!--- keep our aJax from throwing back a too detailed error --->
<cfcatch><cfthrow message="Unable to save, check results and try again" /></cfcatch>
</cftry>
</cfif>
</cfif>
</cfloop>
</cfcase>
<!--- inserted new row --->
<cfcase value="i">
<cfquery datasource="#application.dsn#">
INSERT INTO videogames (title, dateacquired, num_discs, esrb_rating, publisher, genre, platform, seriesname)
VALUES (
<cfqueryparam value="#TRIM(sVideoGame['title'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['title']))#" />,
<cfqueryparam value="#DateFormat(LEFT(sVideoGame['dateacquired'], 10), "yyyy-mm-dd")#" cfsqltype="cf_sql_date" null="#NOT LEN(TRIM(sVideoGame['dateacquired']))#" />,
<cfqueryparam value="#TRIM(sVideoGame['num_discs'])#" cfsqltype="cf_sql_integer" null="#NOT LEN(TRIM(sVideoGame['num_discs']))#" />,
<cfqueryparam value="#TRIM(sVideoGame['esrb_rating'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['esrb_rating']))#" />,
<cfqueryparam value="#TRIM(sVideoGame['publisher'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['publisher']))#" />,
<cfqueryparam value="#TRIM(sVideoGame['genre'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['genre']))#" />,
<cfqueryparam value="#TRIM(sVideoGame['platform'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['platform']))#" />,
<cfqueryparam value="#TRIM(sVideoGame['seriesname'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['seriesname']))#" />
)
</cfquery>
</cfcase>
<!--- deleted one or more rows --->
<cfcase value="d">
<cfquery datasource="#application.dsn#">
DELETE FROM videogames
WHERE videogameid = #sVideoGame['videogameid']#
</cfquery>
</cfcase>
</cfswitch>
</cffunction>
</cfcomponent>
Since these are pretty dynamic things, for this post I did little video snippets showing their functions.
It "works" but as is/out of the box. With a few modifications to the settings and CFC you can also allow editing, inserting, deleting of multiple rows (I think). However…
- The table doesn't auto refresh after updating – but it does on insert and delete; there is a refresh button to the footer though that isn't the most user friendly way to handle it, IMHO, if it isn't in multiple edit mode
- No built in way to pass back results to tell if the action succeeded or not – have to hand roll your own JS
- If an insert does fail, you can't just hit save again, have to do a whole new insert and copy/paste or type it back in – not user friendly!
- You can make a cfgridcolumn offer a drop list of options, but there isn't built in way to load those options from Ajax call
- And as with all the CFGRID stuff – the libraries are already dated out of the box and isn't likely to be updated for months/years if ever and the generated code is clunky
- If breaks if you have newer versions of Firebug running in Firefox
- The auto width doesn't really work well, it still squishes stuff if you don't set sizes on most columns, and table is not responsive
On the pro side, for very basic table editing, it is fast enough to get going once you actually can translate the abysmal Adobe docs (or just Google and glean from Stack Overflow like I did LOL).
So how about DataTables? As I mentioned in the beginning, the core DataTable functions don't include editing but there is an extension available to do it. Or at least, there was. Unfortunately, the free jEditable jQuery plug in used to play nicely with DataTables, but it now appears to be an abandoned project so I wouldn't recommend it. The official Editor extension for and from DataTables looks awesome but it has a huge con: it is NOT free and it is NOT open source. There is a 15 day free trial, then you have to buy a licenses. Those licenses are nicely done per developer rather than site/user, but still, that's $119 for a single developer (with multi-developer licenses available for businesses).
Fortunately, we can still get the same functionality for free by writing our own code, and it still has some nice improvements to the CFGRID code.
<!doctype html> <html> <head> <title>DataTables - Editing In Grid Example</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <meta name="description" content="Blahdy blah blah" /> <meta name="robots" content="index, follow" /> <!--- Pure CSS using Yahoo CDNs ---> <link rel="stylesheet" href="http://yui.yahooapis.com/pure/0.5.0/pure-min.css"> <!--[if lte IE 8]> <link rel="stylesheet" href="http://yui.yahooapis.com/pure/0.5.0/grids-responsive-old-ie-min.css"> <![endif]--> <!--[if gt IE 8]><!--> <link rel="stylesheet" href="http://yui.yahooapis.com/pure/0.5.0/grids-responsive-min.css"> <!--<![endif]--> <!--- jQuery using Google CDNs ---> <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/jquery-ui.min.js"></script> <link rel="stylesheet" href="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/themes/flick/jquery-ui.css"> <!--- Data Tables stuff: using DT's CDN and the new way of doing UI intergration ---> <link type="text/css" href="//cdn.datatables.net/plug-ins/28e7751dbec/integration/jqueryui/dataTables.jqueryui.css" rel="stylesheet" /> <script type="text/javascript" src="//cdn.datatables.net/1.10.0/js/jquery.dataTables.js"></script> <script type="text/javascript" src="//cdn.datatables.net/plug-ins/be7019ee387/integration/jqueryui/dataTables.jqueryui.js"></script> <!--- Block UI for avoiding double submissions ---> <script type="text/javascript" src="jquery.blockUI-2.64.0.js"></script> <!--- custom stuff ---> <link href="cfgridtodt.css" rel="stylesheet" type="text/css"> <script type="text/javascript" src="datatables.js"></script> </head> <body> <div id="layout"> <h1>DataTables - Editing In Grid Example</h1> <div id="resultBox" class="message hideOnLoad"></div> <table id="videoGameList" class="sortedTable pure-form display"><!--- pure-form is for Pure, display is for DataTables ---> <thead> <tr> <th>Title</th> <th>Acquired</th> <th>Genre</th> <th># Discs</th> <th>ESRB</th> <th>Publisher</th> <th>Platform</th> <th>Series Name</th> <th><!--- delete icon column ---></th> </tr> </thead> <tbody></tbody> <tfoot> <tr id="addRow" class="hideOnLoad"> <th><input name="title" id="title" type="text" class="pure-input-1" placeholder="Game Title" /></th> <th><input name="dateacquired" id="dateacquired" type="text" class="pure-input-1" placeholder="Acquired" value="<cfoutput>#DateFormat(Now(), "mm/dd/yyyy")#</cfoutput>" /></th> <th><input name="genre" id="genre" type="text" class="pure-input-1" placeholder="Genre" /></th> <th><input name="num_discs" id="num_discs" type="text" class="pure-input-1" placeholder="# Discs" maxlength="1" /></th> <th><input name="esrb_rating" id="esrb_rating" type="text" class="pure-input-1" placeholder="ESRB" maxlength="1" /></th> <th><input name="publisher" id="publisher" type="text" class="pure-input-1" placeholder="Publisher" /></th> <th><input name="platform" id="platform" type="text" class="pure-input-1" placeholder="Platform" /></th> <th><input name="seriesname" id="seriesname" type="text" class="pure-input-1" placeholder="Series Name" /></th> <th><!--- no delete on add row, use the Cancel button! ---></th> </tr> <tr> <th colspan="9"> <div id="refreshButton" class="tableButton">Refresh</div> <div id="insertButton" class="tableButton">Insert</div> <div id="saveButton" class="tableButton">Save</div> <div id="cancelButton" class="tableButton">Cancel</div> </th> </tr> </tfoot> </table> </div> <!--- Simple dialog box for deletions ---> <div id="deleteConfirm" title="Delete Game" class="dialogBoxes"> <p>This game will be <strong style="color: #600;">deleted permanently</strong>; <strong>are you sure you want to delete <span id="gameName"></span></strong>?</p> </div> </body> </html>
In our CFC we have our function for loading the table, then the functions for saving edits to fields, adding new records, and deleting records.
<cfcomponent>
<cfsetting showdebugoutput="false" />
<cffunction name="getVideoGamesForTable" access="remote" returnformat="JSON" returntype="any" output="false">
<cfargument name="sEcho" type="numeric" required="true" default="1" hint="DataTables: sEcho counter" />
<cfargument name="iDisplayStart" type="numeric" required="false" default="0" hint="DataTables: page iDisplayStart indicator" />
<cfargument name="iDisplayLength" type="numeric" required="false" default="10" hint="DataTables: how many records per page" />
<cfargument name="sSearch" type="string" required="false" default="" />
<cfset var sortField = '' />
<cfset var sortFieldValue = '' />
<cfset var sortFieldDirValue = '' />
<cfset var sortFieldDir = '' />
<cfset var orderFields = "" />
<cfset var cleanSearchCriteria = TRIM(Arguments.sSearch) />
<cfset var FilterOrderBy = "dateacquired DESC" />
<cfset var aColumnArray = ListToArray("title,dateacquired,genre,num_discs,esrb_rating,publisher,platform,seriesname") />
<cfset var sGridData = StructNew() />
<cfset var aData = arrayNew(1) />
<cfset var qAllVideoGames = "" />
<cfset var qVideoGames = "" />
<cfset var qFilteredVideoGames = "" />
<!--- If we have a sort argument, loop through and build out or order by --->
<cfif IsDefined("Arguments.iSortCol_0")>
<cfloop from="0" to="#Arguments.iSortingCols#" index="x">
<cfset sortField = 'iSortCol_#x#' />
<cfset sortFieldDir = 'sSortDir_#x#' />
<cfif IsDefined("Arguments.#sortField#") EQ "YES">
<cfset sortFieldValue = Arguments[#sortField#] + 1 />
<cfset sortFieldDirValue = Arguments[#sortFieldDir#] />
<cfset orderFields = ListAppend(orderFields, aColumnArray[sortFieldValue] & " " & sortFieldDirValue) />
</cfif>
</cfloop>
<cfset FilterOrderBy = orderFields />
</cfif>
<cfquery name="qAllVideoGames" datasource="#application.dsn#">
SELECT COUNT(DISTINCT videogames.videogameid) AS allRecordCount
FROM videogames
</cfquery>
<cfset var startRow = 1 />
<cfset var endRow = qAllVideoGames.RecordCount />
<cfif Arguments.iDisplayLength NEQ -1>
<cfset startRow = Arguments.iDisplayStart + 1 />
<cfset endRow = startRow + Arguments.iDisplayLength />
</cfif>
<cfquery name="qFilteredVideoGames" datasource="#application.dsn#">
SELECT DISTINCT videogameid, title, dateacquired, genre, num_discs, esrb_rating, publisher, platform, seriesname
FROM videogames
WHERE 1=1
<cfif cleanSearchCriteria NEQ "">
AND (
title LIKE <cfqueryparam value="%#cleanSearchCriteria#%" cfsqltype="cf_sql_varchar" />
OR dateacquired LIKE '%#cleanSearchCriteria#%'
OR esrb_rating LIKE '%#cleanSearchCriteria#%'
OR publisher LIKE '%#cleanSearchCriteria#%'
OR platform LIKE '%#cleanSearchCriteria#%'
OR seriesname LIKE '%#cleanSearchCriteria#%'
)
</cfif>
<cfif FilterOrderBy NEQ "">ORDER BY #FilterOrderBy#</cfif>
</cfquery>
<cfif Arguments.iDisplayLength NEQ -1>
<cfset startRow = Arguments.iDisplayStart + 1 />
<cfset maxRows = Arguments.iDisplayLength />
</cfif>
<!--- Loop our results, implementing our filtering and pagination and putting into an array to be returned --->
<cfoutput query="qFilteredVideoGames" startrow="#startRow#" maxrows="#maxRows#">
<cfset sRowData = StructNew() />
<cfset sRowData["pkey"] = qFilteredVideoGames.videogameid />
<cfset sGridData = structNew() />
<cfset sGridData["DT_RowId"] = qFilteredVideoGames.videogameid />
<cfset sGridData["DT_RowData"] = sRowData />
<cfset sGridData["title"] = qFilteredVideoGames.title />
<cfset sGridData["dateacquired"] = DateFormat(qFilteredVideoGames.dateacquired, 'mm/dd/yyyy') />
<cfset sGridData["genre"] = qFilteredVideoGames.genre />
<cfset sGridData["num_discs"] = qFilteredVideoGames.num_discs />
<cfset sGridData["esrb_rating"] = qFilteredVideoGames.esrb_rating />
<cfset sGridData["publisher"] = qFilteredVideoGames.publisher />
<cfset sGridData["platform"] = qFilteredVideoGames.platform />
<cfset sGridData["seriesname"] = qFilteredVideoGames.seriesname />
<cfset sGridData["delete"] = '<img class="deleteButton" src="delete.png" alt="delete' & qFilteredVideoGames.title & '" />' />
<cfset ArrayAppend(aData, sGridData) />
</cfoutput>
<cfset sGridReturn["sEcho"] = JavaCast("int", Arguments.sEcho) />
<cfset sGridReturn["recordsTotal"] = qAllVideoGames.allRecordCount />
<cfset sGridReturn["recordsFiltered"] = qFilteredVideoGames.RecordCount />
<cfset sGridReturn["data"] = aData />
<cfreturn sGridReturn />
</cffunction>
<!--- function for processing data change--->
<cffunction name="updateGameData" access="remote" returnformat="JSON" returntype="struct" output="false">
<cfargument name="VideoGameID" type="numeric" required="false" default="" />
<cfargument name="ChangedField" type="string" required="false" default="" />
<cfargument name="NewValue" type="string" required="false" default="" />
<cfset var lFields = "title,dateacquired,genre,num_discs,esrb_rating,publisher,platform,seriesname" />
<cfset var validESRBs = "E,E10+,T,M,AO" />
<cfset var validPlatforms = "Sony Playstation,Sony PS2,Sony PS3,Nintendo Wii,PC" />
<cfset var sqlType = "cf_sql_varchar" />
<cfset var sResults = StructNew() />
<cfset sResults['ResultCode'] = 200 />
<cfset sResults['ResultMessages'] = ArrayNew(1) />
<!---
Again, in a real world scenario - these actions would be passed to a service layer to handle things and there would be better data validation!
For now, just doing just really basic validations to demonstration being able to pass back messages!
--->
<cfif Not ListFind(lFields, Arguments.ChangedField)>
<cfset sResults['ResultCode'] = 310 />
<cfset ArrayAppend(sResults['ResultMessages'], Arguments.ChangedField & " is not an editable field") />
<cfelseif Arguments.ChangedField NEQ "seriesname" AND TRIM(Arguments.NewValue) EQ "">
<cfset sResults['ResultCode'] = 310 />
<cfset ArrayAppend(sResults['ResultMessages'], Arguments.ChangedField & " is required") />
</cfif>
<!--- If we pass the very core checks, continue --->
<cfif sResults['ResultCode'] EQ 200>
<!--- checks for specific fields --->
<cfswitch expression="#Arguments.ChangedField#">
<cfcase value="num_discs">
<cfset sqlType = "cf_sql_integer" />
<cfif Not IsValid("integer", Arguments.NewValue) OR Arguments.NewValue LTE 0 OR Arguments.NewValue GTE 5>
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "Number of discs must be a valid integer between 1 and 5") />
</cfif>
</cfcase>
<cfcase value="dateacquired">
<cfset sqlType = "cf_sql_date" />
<cfif Not IsValid("date", Arguments.NewValue) OR Arguments.NewValue GT NOW()>
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "Date acquired must be a valid date and cannot be in the future") />
</cfif>
</cfcase>
<cfcase value="esrb_rating">
<cfif Not ListFind(validESRBs, Arguments.NewValue)>
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "ESRB does not have that rating") />
</cfif>
</cfcase>
<cfcase value="platform">
<cfif Not ListFind(validPlatforms, Arguments.NewValue)>
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "I don't own that platform") />
</cfif>
</cfcase>
</cfswitch>
<!--- still good? do our update and if it fails, return the error --->
<cfif sResults['ResultCode'] EQ 200>
<cftry>
<cfquery datasource="#application.dsn#">
UPDATE videogames
SET #Arguments.ChangedField# = <cfqueryparam value="#TRIM(Arguments.NewValue)#" cfsqltype="#sqlType#" />
WHERE videogameid = #Arguments.VideoGameID#
</cfquery>
<cfcatch>
<cfset sResults['ResultCode'] = 300 />
<cfset ArrayAppend(sResults['ResultMessages'], "An error occurred: " & cfcatch.Message) />
</cfcatch>
</cftry>
</cfif>
</cfif>
<cfreturn sResults />
</cffunction>
<!--- process our inserted rows --->
<cffunction name="insertNewGame" access="remote" returnformat="JSON" returntype="struct" output="false">
<cfargument name="title" type="string" required="false" default="" />
<cfargument name="dateacquired" type="string" required="false" default="" />
<cfargument name="num_discs" type="string" required="false" default="" />
<cfargument name="esrb_rating" type="string" required="false" default="" />
<cfargument name="publisher" type="string" required="false" default="" />
<cfargument name="genre" type="string" required="false" default="" />
<cfargument name="platform" type="string" required="false" default="" />
<cfargument name="seriesname" type="string" required="false" default="" />
<cfset var validESRBs = "E,E10+,T,M,AO" />
<cfset var validPlatforms = "Sony Playstation,Sony PS2,Sony PS3,Nintendo Wii,PC" />
<cfset var sResults = StructNew() />
<cfset sResults['ResultCode'] = 200 />
<cfset sResults['ResultMessages'] = ArrayNew(1) />
<!--- check data --->
<cfif TRIM(Arguments.title) EQ "">
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "Title is required") />
</cfif>
<cfif TRIM(Arguments.dateacquired) EQ "" OR Not IsValid("date", Arguments.dateacquired) OR Arguments.dateacquired GT NOW()>
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "Date acquired must be a valid date and cannot be in the future") />
</cfif>
<cfif TRIM(Arguments.num_discs) EQ "" OR Not IsValid("integer", Arguments.num_discs) OR Arguments.num_discs LTE 0 OR Arguments.num_discs GTE 5>
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "Number of discs must be a valid integer between 1 and 5") />
</cfif>
<cfif TRIM(Arguments.esrb_rating) EQ "" OR Not ListFind(validESRBs, Arguments.esrb_rating)>
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "ESRB does not have that rating") />
</cfif>
<cfif TRIM(Arguments.publisher) EQ "">
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "Publisher is required") />
</cfif>
<cfif TRIM(Arguments.genre) EQ "">
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "Genre is required") />
</cfif>
<cfif TRIM(Arguments.platform) EQ "" OR Not ListFind(validPlatforms, Arguments.platform)>
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "I don't own that platform") />
</cfif>
<cfif sResults['ResultCode'] EQ 200>
<cftry>
<cfquery datasource="#application.dsn#">
INSERT INTO videogames (title, dateacquired, num_discs, esrb_rating, publisher, genre, platform, seriesname)
VALUES (
<cfqueryparam value="#TRIM(Arguments.title)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.title))#" />,
<cfqueryparam value="#DateFormat(LEFT(Arguments.dateacquired, 10), "yyyy-mm-dd")#" cfsqltype="cf_sql_date" null="#NOT LEN(TRIM(Arguments.dateacquired))#" />,
<cfqueryparam value="#TRIM(Arguments.num_discs)#" cfsqltype="cf_sql_integer" null="#NOT LEN(TRIM(Arguments.num_discs))#" />,
<cfqueryparam value="#TRIM(Arguments.esrb_rating)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.esrb_rating))#" />,
<cfqueryparam value="#TRIM(Arguments.publisher)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.publisher))#" />,
<cfqueryparam value="#TRIM(Arguments.genre)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.genre))#" />,
<cfqueryparam value="#TRIM(Arguments.platform)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.platform))#" />,
<cfqueryparam value="#TRIM(Arguments.seriesname)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.seriesname))#" />
)
</cfquery>
<cfcatch>
<cfset sResults['ResultCode'] = 300 />
<cfset ArrayAppend(sResults['ResultMessages'], "An error occurred: " & cfcatch.Message) />
</cfcatch>
</cftry>
</cfif>
<cfreturn sResults />
</cffunction>
<cffunction name="deleteGame" access="remote" returnformat="JSON" returntype="struct" output="false">
<cfargument name="VideoGameID" type="numeric" required="false" default="" />
<cfset var sResults = StructNew() />
<cfset sResults['ResultCode'] = 200 />
<cfset sResults['ResultMessages'] = ArrayNew(1) />
<!--- just make sure we have a game ID --->
<cfif TRIM(Arguments.VideoGameID) EQ "" OR Not IsNumeric(Arguments.VideoGameID)>
<cfset sResults['ResultCode'] = 340 />
<cfset ArrayAppend(sResults['ResultMessages'], "Invalid video game id") />
</cfif>
<cfif sResults['ResultCode'] EQ 200>
<cftry>
<cfquery datasource="#application.dsn#">
DELETE FROM videogames
WHERE videogameid = <cfqueryparam value="#TRIM(Arguments.VideoGameID)#" cfsqltype="cf_sql_integer"/>
</cfquery>
<cfcatch>
<cfset sResults['ResultCode'] = 300 />
<cfset ArrayAppend(sResults['ResultMessages'], "An error occurred: " & cfcatch.Message) />
</cfcatch>
</cftry>
</cfif>
<cfreturn sResults />
</cffunction>
</cfcomponent>
And finally the JavaScript. Just over 200 lines of code which isn't too bad at all. I heavily commented so it's easier to follow along versus me explaining it all in paragraph form.
// to filter out ColdFusion's AJAX security leader, if necessary
$.ajaxSetup({
dataFilter:function(data,type) {
if (data.substring(0,2) == "//") {
//remove "//", if there is one
data = data.substring(2,data.length)
}
return data;
}
});
// place holders for variables we need to reference from multiple places
var selectedCell, cellColumn, cellRow, aRowData, originalCellValue, VideoGameID, selectedField;
$(document).ready(function(){
// style our buttons then hide all but insert
$(".tableButton").button();
$(".tableButton:not(#insertButton)").button().hide();
/*
notice we're setting our table to a variable name this time for easier manipulation/reference; also the dataTable
set up code has been updated to reflect DataTables 1.10's code
One exception to this is I am using sAjaxSource instead of the newer ajax. This is because ColdFusion 9.02 seems to be completely
unable to handle the new variables passed with 1.10 - it isn't properly seeing them as arrays. So by using sAjaxSource, we
have DataTables activate a legacy mode that indicates it should use the old style variables (while still letting us use the new
DataTable version overall
*/
$.fn.dataTable.ext.legacy.ajax = true;
var tVideoGames = $('#videoGameList').DataTable({
autoWidth: false,
pagingType: "full_numbers",
order: [[ 1, "desc" ]],
serverSide: true,
processing: false,
ajax: "datatables.cfc?method=getVideoGamesForTable",
columns: [
{ data: "title" },
{ data: "dateacquired" },
{ data: "genre" },
{ data: "num_discs" },
{ data: "esrb_rating" },
{ data: "publisher" },
{ data: "platform" },
{ data: "seriesname" },
{ data: "delete" }
],
columnDefs: [
{ width: "10em", className: "editable alignCenter", targets: [ 1 ] },
{ width: "5em", className: "editable alignCenter", targets: [ 3, 4 ] },
{ className: "editable", targets: [0, 2, 5, 6, 7]},
{ width: "1em", className: "deleteCol", orderable: false, targets: [ 8 ] }
]
});
/*
edit cells - if you would like to let people edit multiple fields, I'd remove the save on blur and instead use the
save button like we do with the insert button
*/
// flip text to input field on click of a table cell that is editable
$("#videoGameList").on("click", ".editable", function(){
// make an array of our base field names/IDs; in order of columns in table!
var aFields = new Array('title', 'dateacquired', 'genre', 'num_discs', 'esrb_rating', 'publisher', 'platform', 'seriesname');
// hide any previous message
$("#resultBox").hide();
// use if to avoid issues with double clicking!
if ($(this).html().indexOf("<input") == -1) {
// get the info on our cells, row, and our game id; I went more verbose here for easier following along
selectedCell = tVideoGames.cell(this);
cellColumn = selectedCell.index().column;
cellRow = selectedCell.index().row;
aRowData = tVideoGames.row(cellRow).data();
originalCellValue = selectedCell.data();
VideoGameID = aRowData['DT_RowId'];
selectedField = aFields[cellColumn];
// set up our field
var fieldToUse = '<input name="' + selectedField + '" id="' + selectedField + '" type="text" class="editField pure-input-1" data-originalvalue="' + originalCellValue + '" value="' + originalCellValue + '" />';
// load in the new field after clearing the original cell and focus it
selectedCell.data(fieldToUse);
$("input", this).focus();
}
});
// on blurring of a field, save the edit if one was made
$("#videoGameList").on("blur", ".editField", function(){
var newValue = $(this).val();
if (originalCellValue != newValue) {
var ColumnHeader = tVideoGames.column(cellColumn).header();
var ChangedColumn = $(ColumnHeader).text();
// if we're changing the title, do a little flip otherwise it puts the input field in the success message LOL
if (ChangedColumn == "Title") {
gameChanged = originalCellValue;
}
else {
gameChanged = aRowData['title'];
}
$.blockUI({ message: '<h1>Just a moment...</h1>' });
$.ajax({
url: "datatables.cfc?method=updateGameData",
dataType: "json",
type: "POST",
async: false,
data: {
VideoGameID: VideoGameID,
ChangedField: selectedField,
NewValue: newValue
},
success: function(data) {
// process the results and display either a success or the error
if (data['ResultCode'] == 200){
$("#resultBox").html(ChangedColumn + ' of ' + gameChanged + ' changed successfully');
$("#resultBox").removeClass("error").addClass("success").show();
// flip our cell back to text with our nice new value
selectedCell.data(newValue);
// show the refresh button in case they want to reapply sorting and the like
$("#refreshButton").show();
}
else {
$("#resultBox").html('');
$.each(data['ResultMessages'], function(index, value) {
$("#resultBox").append("<div>" + value + "</div>");
});
$("#resultBox").removeClass("results").addClass("error").show().focus();
}
}
});
$.unblockUI();
}
// if no change made, flip it back to text
else {
selectedCell.data(originalCellValue);
}
});
// if someone clicks a delete icon
$("#videoGameList tbody").on("click", ".deleteButton", function(){
// get cell/row data
selectedCell = tVideoGames.cell($(this).parent());
cellRow = selectedCell.index().row;
aRowData = tVideoGames.row(cellRow).data();
VideoGameID = aRowData['DT_RowId'];
VideoGameTitle = aRowData['title'];
// make oure delete message nice and specific
$("#gameName").html(VideoGameTitle);
// delete dialog config
$("#deleteConfirm").dialog({
modal: true,
width: 400,
height: 300,
buttons: {
"Delete Game": function() {
$.blockUI({ message: '<h1>Just a moment...</h1>' });
$.ajax({
url: "datatables.cfc?method=deleteGame",
dataType: "json",
type: "POST",
async: false,
data: { VideoGameID: VideoGameID },
success: function(data) {
// process the results, 200 is good, anything else give them the error
if (data['ResultCode'] == 200){
$("#resultBox").html(VideoGameTitle + ' deleted successfully');
$("#resultBox").removeClass("error").addClass("success").show();
// refresh the table
tVideoGames.draw();
}
else {
$("#resultBox").html('');
$.each(data['ResultMessages'], function(index, value) {
$("#resultBox").append("<div>" + value + "</div>");
});
$("#resultBox").removeClass("results").addClass("error").show().focus();
}
}
});
$.unblockUI();
$(this).dialog("close");
},
Cancel: function() {
$(this).dialog("close");
}
}
});
});
// refresh button is pretty easy
$("#refreshButton").click(function(){
$("#resultBox").hide();
$(".tableButton:not(#insertButton)").hide();
tVideoGames.draw();
});
// so is the insert button
$("#insertButton").click(function(){
$("#addRow, #saveButton, #cancelButton").show();
$("#insertButton").hide();
});
// the save button is only used for inserts in this example; it would be fairly easy to update it for allowing multiple field edits too
$("#saveButton").click(function(){
$.blockUI({ message: '<h1>Just a moment...</h1>' });
$.ajax({
url: "datatables.cfc?method=insertNewGame",
dataType: "json",
type: "POST",
async: false,
data: {
title: $("#title").val(),
dateacquired: $("#dateacquired").val(),
genre: $("#genre").val(),
num_discs: $("#num_discs").val(),
esrb_rating: $("#esrb_rating").val(),
publisher: $("#publisher").val(),
platform: $("#platform").val(),
seriesname: $("#seriesname").val()
},
success: function(data) {
if (data['ResultCode'] == 200){
$("#resultBox").html($("#title").val() + ' add successfully');
$("#resultBox").removeClass("error").addClass("success").show();
// refresh the table
tVideoGames.draw();
$("#addRow input").val('');
$("#addRow, .tableButton").hide();
$("#insertButton, #refreshButton").show();
}
else {
$("#resultBox").html('');
$.each(data['ResultMessages'], function(index, value) {
$("#resultBox").append("<div>" + value + "</div>");
});
$("#resultBox").removeClass("results").addClass("error").show().focus();
}
}
});
$.unblockUI();
});
// cancel button is also only used for inserts - blank the add row, rehide it, and reset our buttons
$("#cancelButton").click(function(){
$("#addRow input").val('');
$("#addRow, .tableButton:not(#insertButton)").hide();
$("#insertButton").show();
});
});
And with all that, here is it's video showing:
So again the main con with DataTables if you want grid editing like this is either paying for the Editor extension or having to write your own JS. Seriously, though, it didn't take me too long to write that code and most of it is pretty simple using jQuery and the DataTables API functions. Took maybe two days, and quite a bit of that was being interrupted by other stuff and having to relearn DataTables for the new version, which has some pretty awesome stuff.
That pretty much finishes my series on DataTables and ColdFusion. Doing the same in PHP would be similar, just the Ajax side would change (obviously) and I think PHP may be able to read in the newer variables and can avoid the legacy jump. If you'd like the code for this example, you can download it here. It also includes the SQL to make the table definition if you want to try it out on your own system.