CODEWITHSUNDEEP
javajavascriptsqlarrayscoldfusion
Mike
Mike

Reputation: 2339

converting Coldfusion query results to formatted javascript array

I am trying to write ColdFusion code runs a query then puts the results in an array in a certain format.

the query:

<cfquery name="get_cbox" datasource="ds" username="un" password="pw">
    SELECT CBOXADD, MFLPU, SATLPU, BOXTYPE
    FROM myTable
</cfquery>

and array with 1 element looks like:

<script type="text/javascript">
    var addresses = [{ name: "<table width=100% style='font-size:14px'><td rowspan='3'>OBS:1</td><tr><td>"#cboxadd#"</td><td align='right'>LPU M-F: "#mflpu#"</td></tr><tr><td>Barrigada Guam, 96910 ("#boxtype#")</td><td align='right'>LPU Sat: "#satlpu#"</td></tr></table>", to: #cboxadd#", Barrigada Guam, 96910" }];
</script>

How do I accomplish this?

Upvotes: 1

Views: 3138

Answers (3)

bittersweetryan
bittersweetryan

Reputation: 3443

Expanding on what Dan said I'd do something like this (assuming that you are not using CFCs):

<cfquery name="get_cbox" datasource="ds" username="un" password="pw">
    SELECT CBOXADD, MFLPU, SATLPU, BOXTYPE
    FROM myTable
</cfquery>

<cfset myStruct = parseQuery(get_cbox)>

<cfset pageJSON = SearilizeJSON(myStruct)>

<cffunction name="parseQuery" output="struct">
    <cfargument name="query" type="query">
    <cfset var html = "">
    <cfset var retStruct = StructNew()> 

    <cfloop query="#arguments.query#">
        <cfset html = "<table width=100% style='font-size:14px'><td rowspan='3'>OBS:1</td><tr><td>"#cboxadd#"</td><td align='right'>LPU M-F: "#mflpu#"</td></tr><tr><td>Barrigada Guam, 96910 ("#boxtype#")</td><td align='right'>LPU Sat: "#satlpu#"</td></tr></table>">
        <cfset retStruct.name = htnml>
        <cfset retStruct.to = CBOXADD>
    </cfloop>

    <cfreturn returnStruct>
</cffunction>

Since I haven't used tags in a long time the code may have a few syntax errors in it, but you should get the gist of what I'm trying to accomplish here. First we'll create a function to parse the output of your query into a structure, then we'll pass that structure to searalizeJSON, which will give you a nicely formatted JSON object (note that the case of the keys may be different after calling searilizeJSON). Then all you'd have to do on your page is:

<script type="text/javascript">
    var addresses = #pageJSON#;

    for(var i=0;i<addresses.length;i++){
        addresses[i].html....
        addresses[i].name....
    }
</script>

Upvotes: 1

Dan Short
Dan Short

Reputation: 9616

You could try the serializeJSON function in CF8 or CF9, which will take any ColdFusion object and turn it into a usable JSON string. That can then be used in your JavaScript functions.

Upvotes: 1

invertedSpear
invertedSpear

Reputation: 11054

From what I see your object looks wrong, but I'm not sure what you are doing with it, so I'm unsure of how to make it right for you, so I just have a smaller example.

<cfquery name="get_cbox" datasource="ds" username="un" password="pw">
    SELECT CBOXADD, MFLPU, SATLPU, BOXTYPE
    FROM myTable
</cfquery>

<cfset arrString = "">
<cfloop from="1" to="get_cbox.recordcount" index="i">
    <cfset arrString = listAppend(arrString,"{'name':'#get_cbox.CBOXADD[i]#','type':'#get_cbox.BOXTYPE[i]#'}")>
</cfloop>
<cfoutput>
<script type="text/javascript">
    var addresses = [#arrString#];
</script>
</cfoutput>

This will build an array of objects, each object has the properties 'name' and 'type' with values populated from the query.

Upvotes: 2

Related Questions