Ask a Jedi: Tracking views when using an Ajax-front end

This post is more than 2 years old.

Paul asks:

Hi Raymond, I have a Spry JSON master-detail arrangement on my website. I want to keep track of clicks on the listings in the top master part of the master-detail. Clicking a listing displays a summary of a particular sale in the lower detail panel. All I want at this stage is to increment a database field called "Clicks" in a table called "Sales" each time a listing in the top master panel is clicked. It sounds easy, but I've agonised over it because of the Spry/JSON, which I'm still learning my way around.

This is really interesting question because it helps remind us that the traditional forms of stats and reporting really need to change to support Ajax. My buddy Nico at Broadchoice is pretty intelligent about this kind of stuff, but I can hopefully answer the question adequately. Spry makes this rather easy. First let me share a simple demo. This uses XML, not JSON, but the solution I'll show will work the exact same with any type of dataset. First, the front end: <html>

<head> <title>Spry Test</title> <script type="text/javascript" src="/spryjs/xpath.js"></script> <script type="text/javascript" src="/spryjs/SpryData.js"></script>

<script type="text/javascript"> var mydata = new Spry.Data.XMLDataSet("sprydata.cfm","/people/person"); mydata.setColumnType("age","numeric"); </script> </head>

<body>

<div spry:region="mydata">

&lt;div spry:state="loading"&gt;Loading - Please stand by...&lt;/div&gt;
&lt;div spry:state="error"&gt;Oh crap, something went wrong!&lt;/div&gt;
&lt;div spry:state="ready"&gt;

&lt;p&gt;
&lt;table width="500" border="1"&gt;
	&lt;tr&gt;
		&lt;th spry:sort="name" style="cursor: pointer;"&gt;Name&lt;/th&gt;
		&lt;th spry:sort="age" style="cursor: pointer;"&gt;Age&lt;/th&gt;
		&lt;th spry:sort="gender" style="cursor: pointer;"&gt;Gender&lt;/th&gt;
	&lt;/tr&gt;
	&lt;tr spry:repeat="mydata" spry:setrow="mydata" &gt;
		&lt;td style="cursor: pointer;"&gt;{name}&lt;/td&gt;
		&lt;td style="cursor: pointer;"&gt;{age}&lt;/td&gt;
		&lt;td style="cursor: pointer;"&gt;{gender}&lt;/td&gt;
	&lt;/tr&gt;
&lt;/table&gt;	
&lt;/p&gt;
&lt;/div&gt;

</div>

<span spry:detailregion="mydata" id="dataregion"> <h2>{name}</h2> {name} is {age} years old and is a {gender} </span>

</body> </html>

If you know the basics of Spry, nothing here should be surprising. The CFM behind the scenes is just outputting a static XML file. Ok, so how can we handle detecting that the user has picked a row? Well remember that Spry has two types of event handlers. You can notice events on the data itself, and yes, Spry has an event for the current row changing, and Spry notices events for the region. I.e., it knows when it's writing the data out to the page.

Now here is the interesting thing. You can use an event handler on the data and easily detect the current row changing. This works perfect, except when the page loads. When Spry automatically selects the first row, it doesn't consider it a row change, so you can't handle that very first load.

It's different on the region though. If you use onPostUpdate event for the region, it will fire each and every time that region is updated, including when the page first loads.

Here is how I added the handler:

var myObserver = new Object; myObserver.onPostUpdate = function(notifier, data) { console.log('current row changed to '+mydata.getCurrentRow().name); }; Spry.Data.Region.addObserver("dataregion", myObserver);

All I did was use my Firebug console to log an event. My XML has a name attribute, that's where the .name comes from. Paul would probably want to get the ID field for his data or some other unique identifier. I ran this file and confirmed it worked fine, and it did. Now I just needed the actual server-side logging part. Once again, Spry makes this easy. I added one line:

Spry.Utils.loadURL("GET","/saveview.cfm?name=" + escape(mydata.getCurrentRow().name),true);

This fires off an HTTP request to a CFM. I passed along the name (again, this was just an arbitrary value from my XML data). I don't need to wait for the response (the last argument, true, specifies asynch), so this is all I need. The server side code would just handle the database update, or log, or whatever makes sense.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Paul posted on 9/16/2008 at 2:15 PM

Re: www.bargainz.co.nz

Just discovered a queer anomaly with the JSON result sets I'm getting on my computer vs my wife's computer.

In fact, on anybody else's computer I've tried so far, EXCEPT MINE, when I select a new city from the left-hand drop-down menu, it returns the correct recordcount shown in the brackets at the top, but the actual number of listings does not match that recordcount. Could someone give that a test and tell me if they're getting the same thing happening? On my laptop, everything works perfectly, whether in IE6, IE7, firefox.

There are two functions involved to get the data (Don't ask me why. It's complicated.). There is a mismatch in returned records between the two, but the second function is merely converting the first function to JSON, so they should both be returning the same recordcount.

If someone could vet my code and at least eliminate that as a problem, I would greatly appreciate it. It could be session-related, but that'll be phase two after eliminating the following code as the problem. And I've no idea why it works on my computer but no-one else's.

The following function gets the record count shown in the brackets next to the city name at the top:

<cffunction name="getAllSalesByCity" access="remote" returntype="query" output="false" hint="returns all sales by city">
<cfargument name="thecity" type="string" required="true" />
<cfset var AllSalesByCity = 0 />
<cfquery name="AllSalesByCity" datasource="ds">
SELECT
sales.record_id,
sales.retailer_id,
sales.startdate,
sales.enddate,
sales.title,
sales.hours,
sales.description,
sales.pdf,
sales.pdf_url,
sales.details,
sales.keywords,
sales.disabled,
sales.branches,
sales.photo1,
retailers.company_name,
retailers.city,
retailers.logo1,
retailers.logo2,
retailers.image1,
retailers.image2
FROM sales
INNER JOIN retailers
ON retailers.record_id = sales.retailer_id
WHERE sales.disabled <> 1
AND enddate > <cfqueryparam cfsqltype="cf_sql_timestamp" value="#DateAdd("d",-1,Now())#">
<cfif arguments.thecity neq ''>
AND (branches LIKE <cfqueryparam value="%#arguments.thecity#%" cfsqltype="char" /> <cfif arguments.thecity IS 'Auckland'> OR branches LIKE <cfqueryparam value="%North Shore%" cfsqltype="char" /></cfif>)
</cfif>
ORDER BY sales.startdate
</cfquery>
<cfreturn AllSalesByCity />
</cffunction>

The following function gets the records in JSON format for use with the Spry:

<cffunction name="getJSONSalesByCity" access="remote" returntype="query" output="false" hint="calls GetAllSalesByCity and changes to JSON format">
<cfargument name="city" type="string" required="true" default="#session.city#" />
<cfset var thecity = arguments.city>
<cfset var gasbc = getAllSalesByCity(thecity)>
<cfset var JSONResults = 0>
<cfset JSONResults = QueryNew("RECORD_ID, COMPANY_NAME, RETAILER_ID, TITLE, STARTDATE, ENDDATE, HOURS, KEYWORDS, PHOTO1, LOGO2, DESCRIPTION, PDF, PDF_URL", "Integer, VarChar, Integer, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar")>
<cfset newRow = QueryAddRow(JSONResults, #gasbc.recordcount#)>
<cfoutput query="gasbc">
<cfset temp = QuerySetCell(JSONResults, "RECORD_ID", "#gasbc.record_id#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "COMPANY_NAME", "#gasbc.company_name#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "RETAILER_ID", "#gasbc.retailer_id#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "TITLE", "#gasbc.title#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "STARTDATE", "#dateformat(gasbc.startdate,"dd mmm")#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "ENDDATE", "#dateformat(gasbc.enddate,"dd mmm")#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "HOURS", "#gasbc.hours#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "KEYWORDS", "#left(gasbc.keywords,20)#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "PHOTO1", "#gasbc.photo1#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "LOGO2", "#gasbc.logo2#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "DESCRIPTION", "#gasbc.description#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "PDF", "#gasbc.pdf#", #currentrow#)>
<cfset temp = QuerySetCell(JSONResults, "PDF_URL", "#gasbc.pdf_url#", #currentrow#)>
</cfoutput>
<cfreturn JSONResults />
</cffunction>

I don't know if I've explained that properly. Hope it's understood.

Comment 2 by Paul posted on 9/16/2008 at 2:16 PM

login for the site is username and password: guestshopper.

Comment 3 by Raymond Camden posted on 9/16/2008 at 3:31 PM

For me, I see 31 results. So it works fine for me.

Comment 4 by Paul posted on 9/16/2008 at 3:42 PM

If you select "Hamilton" from the left-hand nav drop-down menu, does the recordcount still match the number of records?

Comment 5 by Raymond Camden posted on 9/16/2008 at 3:43 PM

Yep, 14. Two pages of 7.