Accessing Query Data from CFC Function

Question

Here is what I am up against: I am trying to change the content of a CFDIV based on the selection from a CFSelect box.

To do this I have bound the CFDiv to a CFC and I am trying to return two columns from my query that is executed in that CFC; Alert_Status AND Alert_Priority. These values will be queried based on a selection from the CFSelect box in my CFM page. Company_Name is the value passed to the CFC from the selection in the CFSelect box. Once the query in the CFC is run, I would like to display the results in a DIV on that same CFM page as the select box.

Here is the CFC:

    <!---First Slect Box --->
<cffunction name="getData" access="remote" returntype="query">
    <cfoutput>
    <!--- Function to get data from datasource --->
    <cfquery name="data" datasource="#datasource#">
    select company_name, customer_id
    from customer_table
    where status <> '0'
    order by company_name
    </cfquery>
    </cfoutput>

    <!--- Return results --->
    <cfreturn data>
</cffunction>

<cffunction name="getDetail" access="remote" returnType="string">
    <cfargument name="company_name" type="any" required="true">

    <!--- localize function variables --->
    <cfset var dataDetail = "">
    <cfoutput>
    <cfquery name="dataDetail" datasource="#datasource#">
        SELECT tax_rate
        FROM   customer_table
        <!--- adjust cfsqltype if needed --->
        WHERE company_name = <cfqueryparam value="#ARGUMENTS.company_name#" cfsqltype="cf_sql_varchar">
    </cfquery>
    </cfoutput>
    <cfreturn dataDetail.tax_rate>
</cffunction>

<cffunction name="getAlerts" access="remote" returnType="query">
    <cfargument name="company_name" type="any" required="true">

    <!--- localize function variables --->
    <cfset var alertDetail = "">
    <cfoutput>
    <cfquery name="getID" datasource="#datasource#">
        select customer_id
        from customer_table
        where company_name = <cfqueryparam value="#ARGUMENTS.company_name#" cfsqltype="cf_sql_varchar">
    </cfquery> 
    <cfquery name="alertDetail" datasource="#datasource#">
        SELECT *
        FROM   customer_alerts
        <!--- adjust cfsqltype if needed --->
        WHERE customer_id = <cfqueryparam value="#getID.customer_id#" cfsqltype="cf_sql_varchar"> AND alert_status = 'on'
    </cfquery>
    </cfoutput>


    <cfreturn alertDetail>
</cffunction>

I am trying to display the query results for the query AlertDetail in a div on my main page.

Here is the portion of my CFM page that relates to this CFC:

          <cfdiv name="test" id="test" type="text" bind="cfc:cfcs.taxdata.getAlerts({company_name})" bindonload="true" bindattribute="value" rows="2" cols="2" readonly="yes"></cfdiv>

Any help would be greatly appreciated. Thanks. -Brian

I don't see anywhere in your CFM where you invoke the getAlerts() function, which is the only function I see that returns the AlertDetail query. Am I missing something? — WillardSolutions
Hard to separate the wheat from the chaff here. Please edit the question to remove the code that does not pertain to the function and div in question. — Dan Bracuk
Wot Dan said. Pls post a repro case that just demonstrates the issue without any unnecessary cruft (this should already be part of how you're own troubleshooting has panned out, I think) — Adam Cameron
Sorry about that. I've posted the textarea where I am trying to display the alertDetail query results. I removed all the unecessary code. Thanks. — Brian Fleishman
I only see a text box, not a div. A text box bind will be expecting a simple value, like a string. A query is a complex object, which is probably causing an error. Check the ajax debugger. Not related to your question, but .. you do not need to put cfoutput tags around queries. Any #variables# within the cfquery tag will be automatically evaluated. Also, it does not look like there is any need for two queries in getAlerts(). Just use a JOIN between the two tables. — Leigh

fyroc fyroc · Accepted Answer · 2014-05-30T13:17:07

Let's step back for a moment. I see your goal and sometimes using CFDiv only complicates things. I would just use AJAX. I wasn't sure what you meant by "two columns" so the current example I am sending you only shows one function being returned. Adding the second will be pretty easy to replicate.

<html>
<head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
    <script>
        // populate the dropdown
        $.ajax({
            type: "GET",
            url:"cfcs/taxdata.cfc?method=getData",
            dataType: "json",
            success: function (data) {
                $("#formSelect").html("");
                // may need tweeking depending on how you are outputting your query.
                $.each(data,function(i,obj)
                   {
                       var div_data="<option value="+obj+">"+obj+"</option>";
                       $(div_data).appendTo('#formSelect'); 
                   });  
            }
        });         
        // on change of dropdown value return the cfc data into the div
        $("#formSelect").change(function() {  
            var selectValue = $("#formSelect").val();
            $.ajax({
                type: "GET",
                url:"cfcs/taxdata.cfc?method=getAlerts",
                dataType: "json",
                data: { 
                    company_name: selectValue
                },
                success: function (data) {
                    $("#test").html(data);
                }
            });
        });
    </script>
</head>
<body>
    <form>
        <select id="formSelect">
        </select>
    </form>
    <div id="test"></div>
</body>

Also, below is how you can add multiple results to the <div>

<!-- Use multiple AJAX calls to each function. Have them return in separate divs within the main div. -->

<div id="test">
    <div id="query1"></div>
    <div id="query2"></div>
    <div id="query3"></div>
    <!-- You can also do paragraph tags. JQuery only looks for the ID or class -->
    <p id="query4"></p>
</div>

Option Two

<!-- 
    In your ajax instead of using $("#test").html(data); you can use appentTo() for each AJAX call
-->

success: function (data) {
    $(data).appendTo('#test');
}

Accessing Query Data from CFC Function

1 Answers