Coldfusion CFQUERY w/ Inner Join & Dateformat Not Working

Question

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...

Also, I highly suggest using cfquery param to sanitize your variable. I'm pretty sure you don't need octothorps inside the query tag but it's been over a year since I've work a cfquery. — Sterling Archer, Aug 31 '16 at 19:49
Ahhh I don't think you can apply a non SQL function to a query like that — Sterling Archer, Aug 31 '16 at 19:50
I need to get the month (i,e. 1,2,3,4,5,6,7,8,9,10,11, or 12) out of w.start_date, which is stored in MySQL as a DATE - and compare it to what I am passing in #form.home_by_month# — mck, Aug 31 '16 at 19:52
I know it's a PHP question, http://stackoverflow.com/questions/2039839/how-do-i-get-month-from-date-in-mysql but I think you're looking for the MYSQL function `MONTH()` — Sterling Archer, Aug 31 '16 at 19:53

score 8 · Accepted Answer · edited Sep 01 '16 at 02:56

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#" />

score 3 · Answer 2 · answered Sep 01 '16 at 03:33

(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.

This is a very good explanation of how language variables and database variables coexist in a query. (Also, hi Leigh!) — Sterling Archer, Sep 01 '16 at 13:02
Thanks Sterling. In *some* ways, it is analogous to mixing client (JS) and server side code (CF) within the same script. Just because you can mix them, does not mean the CF server can execute client side code, or vice versa ;-) — Leigh, Sep 01 '16 at 14:15

score 0 · Answer 3 · edited Sep 01 '16 at 01:36

0

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>

edited Sep 01 '16 at 01:36

Leigh

28,765
10
55
103

answered Aug 31 '16 at 19:57

mck

413
1
3
14

2

If this is your solution, you should take Sterling's advice of also using `cfqueryparam`. Always use `cfqueryparam` when sending parameters in a database query. – beloitdavisja Sep 01 '16 at 02:06
Also, filtering on a month and not the year can lead to unexpected results. – Dan Bracuk Sep 01 '16 at 12:58
In addition, if you are searching for records for a specific month *and* year, consider using [this paradigm](http://stackoverflow.com/questions/13666883/date-comparison-in-ms-sql-2005/13687006#13687006), which is more flexible - not to mention index friendly - than using `Month()`. – Leigh Sep 01 '16 at 14:17
Dan, of course. In the case of this query, I am actually looking for results from a specific month, regardless of year. – mck Sep 02 '16 at 10:08
(Edit) @mck - Yes, if you are looking for a specific month (for any year in the future) then that is a legitimate use of month(). BTW, not sure if it is relevant in this case, but keep in mind `now()` includes both a date and time. So the comparison would exclude records having the same date, but an earlier time. – Leigh Sep 02 '16 at 16:32

Coldfusion CFQUERY w/ Inner Join & Dateformat Not Working

3 Answers3