mck mck - 8 months ago 44
MySQL Question

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.weekend_type, w.community_id, w.start_date, w.end_date,
c.community_id, c.location, c.language, c.state,
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

It is dying on

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

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


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="date" value="#form.home_by_month#" />

I'm not 100% sure what datatype you're using, so "date" might not be right. Check your DB value for the proper one.