ColdFusion Query from SQL converted into JS ARRAY

Question

I have a sql database that I would like to query with coldfusion and set up into a js array. So that I can run my JS function to display a certain date. My JS array set up looks like this:

var natDays = [
    [2014, 1, 1, 'New Year'], 
    [2014, 1, 20, 'Martin Luther King'], 
    [2014, 2, 17, 'Washingtons Birthday'],       
    [2014, 5, 26, 'Memorial Day'], 
    [2014, 7, 4, 'Independence Day'], 
    [2014, 9, 1, 'Labour Day'], 
    [2014, 10, 13, 'Columbus Day'], 
    [2014, 11, 11, 'Veterans Day'], 
    [2014, 11, 27, 'Thanksgiving Day'], 
    [2014, 11, 28, 'Thanksgiving Day'],
    [2014, 12, 25, 'Christmas'],  
    [2014, 12, 26, 'Christmas'], 
    [2015, 1, 1, 'New Year'], 
    [2015, 1, 19, 'Martin Luther King'], 
    [2015, 2, 16, 'Washingtons Birthday'],       
    [2015, 5, 25, 'Memorial Day'], 
    [2015, 7, 3, 'Independence Day'], 
    [2015, 9, 7, 'Labour Day'], 
    [2015, 10, 12, 'Columbus Day'], 
    [2015, 11, 11, 'Veterans Day'], 
    [2015, 11, 26, 'Thanksgiving Day'], 
    [2015, 11, 27, 'Thanksgiving Day'],
    [2015, 12, 24, 'Christmas'],  
    [2015, 12, 25, 'Christmas']  
    ];

dbo.Holidays

so I am querying the database like this

<cfquery name="getHolidays">
        SELECT Holiday, date
        FROM dbo.Holidays
</cfquery>

But I am not sure how to convert the data into the JS format I need in order to use my functions:

var natDays = [
    [YEAR, MONTH, DAY, 'HOLIDAY']
];

what I have tried:

<cfquery name="getHolidays">
  select holiday, date
  from dbo.Holidays
</cfquery>

<cfset aryData  = [] />

<cfloop from="1" to="#getHolidays.recordcount#" index="j">
  <cfset ArrayAppend(aryData, DateFormat(getHolidays.date[j], "yyyy-mm-dd")) />
</cfloop>

<cfoutput>
  <cfdump var="#getHolidays#">
</cfoutput>

<script type="text/javascript" charset="utf-8">
var getHolidays = <cfoutput>#serializeJson(aryData)#</cfoutput>; 
console.log(getHolidays);
</script>

Create an array, loop over query, insert arrays into array, one per row, in the format you want, and then serialize it to json before returning. simple. — user400654
– user400654, Commented Oct 15, 2015 at 18:52
i mean, it's no different than building a table based on a query, it's actually simpler because you don't have to deal with html. — user400654
– user400654, Commented Oct 15, 2015 at 18:55
learning is always fun! if all you ever do is what you already know, it will get boring very fast. — user400654
– user400654, Commented Oct 15, 2015 at 18:57
Good, you're really close, now there's somewhere for us to start! answer inc. — user400654
– user400654, Commented Oct 15, 2015 at 20:48

user400654 · Accepted Answer · 2015-10-15 20:54:20Z

2

All you need to do now is split the date into an array of year/month/day, and place them into an array before you append it to the result array.

<cfquery name="getHolidays">
  select holiday, date
  from dbo.Holidays
</cfquery>

<cfset aryData  = [] />

<cfloop from="1" to="#getHolidays.recordcount#" index="j">
  <cfset dateArr = ListToArray(DateFormat(getHolidays.date[j], "yyyy-mm-dd"), '-')>
  <cfset ArrayAppend(aryData, [dateArr[1], dateArr[2], dateArr[3], getHolidays.holiday[j]]) />
</cfloop>

<cfoutput>
  <cfdump var="#getHolidays#">
  <cfdump var="#aryData#">
</cfoutput>

<script type="text/javascript" charset="utf-8">
var getHolidays = <cfoutput>#serializeJson(aryData)#</cfoutput>; 
console.log(getHolidays);
</script>

answered Oct 15, 2015 at 20:54

user400654

95.1k16 gold badges168 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user400654 Over a year ago

I would have just used serializeJson(getHolidays) and dealt with it in javascript. I'd only turn it into a function if you need to do this same kind of conversion in multiple places.

Collectives™ on Stack Overflow

ColdFusion Query from SQL converted into JS ARRAY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related