CODEWITHSUNDEEP
mysqlcoldfusioncfmllucee
mck
mck

Reputation: 513

Coldfusion CFQUERY w/ Inner Join & Dateformat Not Working

I am trying to wrap my head around dealing joins, mysql and coldfusion. The following query works without the last condition.

        <cfquery name="GetWeekends">
        SELECT w.id, w.weekend_type, w.community_id, w.start_date, w.end_date,
          w.language,
          c.community_id, c.location, c.language, c.state, c.country
        FROM _weekends w
        INNER JOIN _communities c
        ON w.community_id=c.community_id
        WHERE w.weekend_type = 1 AND w.start_date > Now() AND           
        #DateFormat(w.start_date, "m")# = '#form.home_by_month#'
        ORDER BY w.start_date ASC
        </cfquery>

It is dying on 

  #DateFormat(w.start_date, "m")#

telling me variable [W] doesn't exist. Sorry, I am learning as I go here...

Upvotes: 2

Views: 334

Answers (3)

Leigh
Leigh

Reputation: 28873

(Too long for comments...)

Since you mentioned being new to CF, a little background on how CF processes database queries may help in understanding why the error occurred and what types of things you can (and cannot) do within a cfquery.

Although CF can communicate with a database engine, the two are totally separate, and speak completely different languages. The fact that you can mix CFML and SQL within a cfquery tag gives the misleading impression that CF functions can operate on database objects (or vice versa). They can't. The CF server knows nothing about database objects (nor does the database engine understand CFML). Any CFML code within the query tag is processed first - on the CF server. The generated SQL is then sent to the database engine, and executed separately.

When the CF server encounters a database query, it parses the tag contents, looking for CFML variables or expressions which must be evaluated, ie:

SELECT Column FROM Table WHERE ColA = '#form.someField#' AND ColB = '#form.otherField#'

It then converts those variables and expressions into literal values, ie strings, numbers, etcetera. Finally, CF hands the generated SQL string off to the database engine for execution, ie:

SELECT Column FROM Table WHERE ColA = 'John Smith' AND ColB = 'ABC'

So the reason CF chokes on #DateFormat(w.start_date, "m")# is that it does not understand w.start_date refers to a database column. It thinks it is the name of a CF variable: specifically a structure named "w", containing the key "start_date". Obviously no such variables exist. Hence the undefined error.

Upvotes: 3

Sterling Archer
Sterling Archer

Reputation: 22395

The issue here is that DateFormat() is a ColdFusion function, and that cannot be applied with a MySQL "variable" (w). You need to use the MONTH() function for MySQL and pass the date into that.

Don't forget to sanitize your form inputs, you are highly susceptible to SQL injection. Use cfqueryparam like so:

MONTH(w.start_date) = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.home_by_month#" />

Upvotes: 8

mck
mck

Reputation: 513

The working solution was, thanks to Sterling Archer:

<cfquery name="GetWeekends">
    SELECT w.id
           , w.weekend_type
           , w.community_id
           , w.start_date
           , w.end_date
           , w.language
           , c.community_id
           , c.location
           , c.language
           , c.state
           , c.country
    FROM   _weekends w INNER JOIN _communities c ON w.community_id = c.community_id
    WHERE w.weekend_type = 1 
    AND   w.start_date > Now() 
    AND   MONTH(w.start_date) = '#form.home_by_month#'
    ORDER BY w.start_date ASC
</cfquery>

Upvotes: 0

Related Questions