We help IT Professionals succeed at work.

How do I access query results returned by ajax?

High Priority
143 Views
Last Modified: 2019-07-25
I'm doing an ajax call to a ColdFusion function to return a query. It should return a single record with three columns for an event's title, description, and date. I want to place each of those three pieces in their corresponding divs. Everything is working fine but I can't figure out how to access the results, and the options I've Googled up have failed me. How do I get the JSON equivalent of CF's queryName.columnName? Thanks!

The jQuery I'm using is as follows:
	$('.calendar-event').click(function(){
		 var id = this.id;
		 var splitid = id.split('_');
		 var eventID = splitid[1];
				console.log(eventID);
		 // AJAX request
		 $.ajax({
			type: "POST",
			url: "/cfc/calendar.cfc?method=getEventAjax",
		  dataType: "json",
			data: {
			 returnFormat: "json",
			 eventID: eventID
		 },
			success: function(response){ 
				console.log(response);
				// Add response in Modal body
				$('#eventTitle').html(what goes here for the title?);
				$('#eventDescription').html(what goes here for the description?);
				$('#eventDate').html(what goes here for the date, and what about formatting?);
				// Display Modal
				$('#eventModal').modal('show'); 
			},
			error: function (response) {
				console.log(response);
        $("#modal-body").html("This event could not be loaded.");
				$("#eventModal").modal("show"); 
      }
		});
	 });

Open in new window


The function is simple:
<cffunction name="getEventAjax" access="remote" returntype="query" hint="returns info for single event">
		<cfargument name="eventID" type="numeric" required="yes">
		<cfquery name="qGetEvent" datasource="#APPLICATION.ds#">
			SELECT eventTitle, eventDescription, eventDate FROM calendar
			WHERE ID = <cfqueryparam value="#ARGUMENTS.eventID#" cfsqltype="CF_SQL_INTEGER">
		</cfquery>
		<cfreturn qGetEvent>
	</cffunction>

Open in new window


In the console log I'm getting
{COLUMNS(3), DATA: Array(1)},
COLUMNS: (3) ["EVENTTITLE", "EVENTDESCRIPTION", "EVENTDATE"]
DATA: Array(1)
0: Array(3)
0: "Radio Interview with Lucy Ann Lance of 1290AM"
1: "<p>Interview will take place at 8:20am EST</p>"
2: "July, 29 2019 00:00:00"
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
What version of CF are you using?

Author

Commented:
Oops, sorry... CF 10. (Upgrading soon, but not soon enough.)
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
CF's bundling of queries is terrible, until just recently ... ;-) For CF10, I'd recommend building an array of structures instead.

	<cffunction name="getEventAjax" access="remote" returntype="array" hint="returns info for single event">
		<cfargument name="eventID" type="numeric" required="yes">

		<!--- localize variables --->
		<cfset local.response = []>

		<!--- simulates your database query --->
		<cfset local.qGetEvent = queryNew("eventTitle, eventDescription, eventDate"
				, "varchar,varchar,date"
				, [ {eventTitle: "Radio Interview with Lucy Ann Lance of 1290AM"
					, eventDescription:"<p>Interview will take place at 8:20am EST</p>"
					, eventDate = "2019-07-29"} 
				  ]
			  )>
		
		<!--- build array of structures --->
		<cfset local.results = []>
		<cfloop query="local.qGetEvent">
			<cfset arrayAppend( local.results
				, {"eventTitle": eventTitle
				   , "eventDescription": eventDescription
		                   , "eventDate": eventDate
				})>
		</cfloop>
		
		<cfreturn local.results>
	</cffunction>	

Open in new window


Then in jQuery success(), grab the first element in the array, i.e. response[0].columnName. Just remember column names are case sensitive in JS.


success: function(response) {
    $('#eventTitle').html( response[0].eventTitle );
    $('#eventDescription').html( response[0].eventDescription );
    // convert to date so it can be formatted
    var date = new Date(response[0].eventDate);
    $('#eventDate').html( date.toString() );
}

Open in new window


For formatting dates, you can either format it yourself using date methods like date.getMonth()+1, date.getDay(), etc... OR find a separate jQuery plugin for dates.  I haven't used them, but here are a few examples

https://github.com/datejs/Datejs
https://github.com/phstc/jquery-dateFormat

Author

Commented:
You are, as usual _agx_, my hero. :)
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Thanks :) Always glad to help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.