How do I get cfquery results in my .cfc file?

Question

My hope is to validate an input field (PromoCode) prior to form submission. @Adrian J. Moreno pointed me to "The Remember The Milk sign-up form" (https://jqueryvalidation.org/files/demo/milk/) and @SOS has walked me through the process to this point.

I’ve got everything working (sort of) except grabbing the PromoCode from the DB (it’s currently hardcoded in the .cfc file.)

The site is for my brother who teaches multiple training courses and asked if I can add a promo code option to the registration form. He has several courses so there may be several promo codes (different for each possible course.)

I have 2 questions:

How do I grab the PromoCode for each course from SQL to compare to what the user types?
As indicated above, I’ve got everything else working except, currently, if the user types a wrong promo code it tells them it’s invalid as expected… BUT the user cannot submit the form unless they clear the field or enter the proper code. I’d like the user to be able to submit the form regardless of whether the PromoCode is valid or not. If it IS valid, they get the discount… if not, they don’t.

Here’s the stripped down code. I can provide the full code if necessary.

CFC file:

component {
    remote boolean function validatepromocode(string promocode) returnFormat="json"{
        
        if (arguments.promocode == "john") { //Need to replace "John" with #cfoutput#
            return true;
        }

        return false;
    }

}

cfquery:


        SELECT ID, EVENT_NAME, NPT_STORY_TYPE, STORY_TYPE, PICTURE, SHOWDATE, SHOWENDDATE, 
            SHOWTIME, SHOWENDTIME, REGISTRATIONTIME, DOORSOPEN, AGE, SEATS, FEE_ADVANCED, 
                FEE_DAYOFSHOW, PROMOCODE, VENUE, ADDRESS, CITY, STATE, ZIP, SHOWDATE, 
                    PHONE, GOOGLEMAPADDRESS, COMMENTS, TEASER, REQUIREMENTS, 
                        STARTDATE, ENDDATE, SHORT_URL
                            FROM DBO.COURSES         
                            
                                WHERE SHORT_URL =   
                            
                                WHERE ID =

SOS · Accepted Answer

If each course can have its own promo code, then you'll need to pass both the "PromoCode" and "CourseId" values to the CFC. Use the data option to pass additional parameters to the remote url:

Javascript

$(document).ready(function() {
    var validator = $("#signupform").validate({
        rules: {
            promocode: {
                remote: {
                    url: "/components/promoCodeComponent.cfc?method=validatePromoCode",
                    data: { 
                        courseId : $("#courseId").val() 
                    }
                }
            }
        },
        messages: {
            promocode: {
                remote: jQuery.validator.format("Sorry, {0} is not a valid Promo Code")
            }
        },
        errorClass: "text-danger",
        validClass: "text-success"          
    });
});

Form


    
    Course Id: 

    Promo Code:

Modify your CFC function to accept an additional parameter: courseID. Inside the function, use QueryExecute() to lookup the promoCode and courseId in the database. If a match is found, return true (i.e. valid).

NB: Replace "Your_DataSource_Name" with your datasource name. See also the section titled "Default Datasource".

component {
    // Note: See Application.cfc docs on setting application level datasource, i.e. "this.datasource"
    remote boolean function validatePromoCode(string courseId, string promoCode) returnFormat="json"{
        
        local.qPromoCode = queryExecute(
            "   SELECT COUNT(*) AS TotalFound 
                FROM   Courses 
                WHERE  Id = :courseId 
                AND    PromoCode = :promoCode
                AND    LEN(PromoCode) > 0                                   
            "
            , { 
                promoCode = { value=arguments.promoCode, cfsqltype="varchar" }
                , courseId = { value=arguments.courseId, cfsqltype="integer", null=!isNumeric(arguments.courseId) }
              }
            , { datasource="Your_DataSource_Name" }
        );
                                        ;
        if (local.qPromoCode.TotalFound gt 0) {
            return true;
        }

        return false;
    }

}

Update 2/20/2022

Regarding question #2, I haven't tried anything like that with this plugin. What you describe sounds more like a "warning", and I don't think the plugin was designed for that. However, you might post a JS specific question to get a more definitive answer from those more familiar with the plugin.

If you're only using the plugin for this one field, using a bit of jQuery (instead of the plugin) might do in a pinch. Hook into the text field's blur() event. When the field loses focus, call the cfc with $.getJSON(). Then update a

with the response. The approach lacks the bells and whistles of the plugin (and could be improved, usability wise) but seems truer to your intent i.e. display warning vs hard fail.

Javascript

$(document).ready(function() {
    // attach handler to all text fields with class ".promocode"
    $(".promocode").on( "blur", function(evt) {
    
        // promo code field that triggered event
        var txtPromo = $(this);
        // find nearest "course" id 
        var txtCourse = txtPromo.prevAll('.courseid').first();
        // find nearest "status" element 
        var elemStatus = txtPromo.nextAll('.promocode-status').first();
        
        // clear any previous status
        elemStatus.removeClass("text-success")
                .removeClass("text-danger")
                .html("");
                
        // nothing entered, nothing to do
        if (txtPromo.val().trim() == "") {
            return;
        }
        
        // verify promo code 
        $.getJSON( "/path/PromoCode.cfc"
            , { method : "validatePromoCode"
            , courseId: txtCourse.val()
            , promoCode : txtPromo.val()
        })
        .done(function( response ) {
            if (response.isValidCode) {
                elemStatus
                    .addClass("text-success")
                    .html("Success");
            }
            else {
                elemStatus
                    .addClass("text-danger")
                    .html("Sorry, "+ response.promoCode + " is not a valid Promo Code");
            }
        });
    });
    
});

Form (multiple promo code fields)



   
     Class 1: 
     Promo Code: 
     
   
   
     Class 2: 
     Promo Code:

CFC

component {
    remote struct function validatePromoCode(string courseId, string promoCode) returnFormat="json"{
        
        local.qPromoCode = queryExecute(
            "   SELECT COUNT(*) AS TotalFound 
                FROM   Courses 
                WHERE  Id = :courseId 
                AND    PromoCode = :promoCode
                AND    LEN(PromoCode) > 0                                   
            "
            , { 
                promoCode = { value=arguments.promoCode, cfsqltype="varchar" }
                , courseId = { value=arguments.courseId, cfsqltype="integer", null=!isNumeric(arguments.courseId) }
              }
            , { datasource="SQLServer" }
        );
        
        return { 
            "promoCode": arguments.promoCode 
            , "isValidCode" : (qPromoCode.TotalFound gt 0) 
        };
    }

}

How do I get cfquery results in my .cfc file?

Answers (1)

Related Questions