Home > Software engineering >  How do I get cfquery results in my .cfc file?
How do I get cfquery results in my .cfc file?

Time:02-19

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:

  1. How do I grab the PromoCode for each course from SQL to compare to what the user types?
  2. 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.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"> 
<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="/scripts/jquery.validate.js"></script>

<script>
    $(document).ready(function() {
        var validator = $("#signupform").validate({
            rules: {
                promocode: {
                    remote: "/components/promocodecomponent.cfc?method=validatepromocode"
                }
            },
            messages: {
                promocode: {
                    remote: jQuery.validator.format("<span class='text-danger font-weight-bold font-italic ml-2'>Sorry, {0} is not a valid Promo Code</span>")
                }
            }
        });
    });
</script>

<div >
    <div >
        <form id="signupform" autocomplete="off" method="get" action="">
            <input id="promocode" name="promocode" type="text" value="">
            <br>
            <input id="signupsubmit" name="signup" type="submit" value="Signup">
        </form>
    </div>
</div>

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:

<cfquery name="GetAllCourses" datasource="#request.dsn#">
        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         
                            <cfif isDefined('url.sname')>
                                WHERE SHORT_URL = <cfqueryparam value="#TRIM(url.sname)#" cfsqltype="cf_sql_varchar">  
                            <cfelseif isDefined('url.id')>
                                WHERE ID = <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer"> 
                            </cfif>             
</cfquery>

CodePudding user response:

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

<form id="signupform" autocomplete="off" method="get" action="">
    <!--- demo only --->
    Course Id: <input id="courseId" name="courseId" type="text" value=""><br>
    Promo Code: <input id="promocode" name="promocode" type="text" value=""><br>
    <input id="signupsubmit" name="signup" type="submit" value="Signup">
</form>

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;
    }

}
  • Related