mck mck - 3 months ago 17
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.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...

Answer

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.

Comments