2 Replies Latest reply on Sep 12, 2011 12:17 PM by CFMXPrGrmR

    Returning JSON to CF/jQuery

    CFMXPrGrmR Level 2

      CF page:

      <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
      <html xmlns="http://www.w3.org/1999/xhtml">
      <head>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      <title>Untitled Document</title>
      <script src="jquery-1.6.2.min.js" type="text/javascript"></script>
      <script type="text/javascript">
      $(document).ready(function(e) {              
           $.getJSON('loaddata.cfm',function(data){
           $("#div1").html(data);
           });
      });
      </script>
      </head>
      <body>

       

      <div id="div1" style="background-color:tan; height:70px; width:450px;"></div>

       

      </body>
      </html>

       

       

      loaddata.cfm

      <cfquery datasource="pubs" name="jquerytest">
           Select * from jquery
      </cfquery>

      <cfoutput>#serializejson(jquerytest)#</cfoutput>

       

       

      loaddata.cfm is displaying {"COLUMNS":["ID","TITLE","CONTENT"],"DATA":[[1,"Dave","test content 1"],[2,"Bob","test content 2"],[3,"Jesse","test content 3"]]}

       

      I'm having an issue returning this CF query in JSON format. It's available but I'm not sure what format/structure jQuery expects, what I have isn't outputting any data. My

      $("#div1").html(data);

      isn't displaying any data.

        • 1. Re: Returning JSON to CF/jQuery
          JR "Bob" Dobbs Level 4

          Bear in mind that jQuery doesn't expect a particular format, you have the ability to retreive any JSON object and do something with it.  That being said most JSON services return a collection of key/value pairs, a serialized CFQUERY resultset is a little different.  A query object contains a COLUMNS and DATA property.  In the case of the DATA property each item is an array with one item per field. The COLUMNS property contains an array of column names.

           

          You should also set the HTTP header of the loaddata.cfm page to identify the contents as JSON, you can add the following just before the CFOUTPUT tag on that page:

          <cfcontent type="application/json" reset="yes">

           

          Here is a quick sample of JavaScript to get you started, you should be able to copy and paste this to replace the JS block in the sample you posted.

           

          <script type="text/javascript">
          $(document).ready(function(e) {             
               $.getJSON('loaddata.cfm',function(data){
                         
                  //build HTML text to be displayed
                  var txt = "<ul>";
                 
                  //the DATA property will contain one array for each row in your result set
                  for(var i = 0; i < data.DATA.length; i++)
                  {
                      txt = txt + "<li>" + data.DATA[i][1] + "</li>";  //add the second item in the array, to the string to be output
                  }      

           

                  txt = txt + "</ul>";
                 
                  $("#div1").append(txt);  //add the new HTML block to the DOM. 
               });
          });
          </script>

           

          I recommend that you take a look at:

           

          Ray Camden's blog, which contains many jQuery related articles:

          http://www.coldfusionjedi.com/index.cfm/2007/9/20/Quick-and-Dirty-JSONQuery-Example

           

          The tutorials section of the jQuery site:

          http://docs.jquery.com/Tutorials

          • 2. Re: Returning JSON to CF/jQuery
            CFMXPrGrmR Level 2

            That's a great help Bob, I appreciate you taking the time to write that. This sent the data correctly, so I'm able to move forward.

             

            I did take a look at those links before you had sent them, they pop up pretty high when searching on CF and jQuery. I just found another tutorial I thought was better. I'll take a deeper look. Thanks again.