I am trying to wrap my head around dealing joins, mysql and coldfusion. The following query works without the last condition.
SELECT w.id, w.weekend_type, w.community_id, w.start_date, w.end_date,
c.community_id, c.location, c.language, c.state, c.country
FROM _weekends w
INNER JOIN _communities c
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
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.