Reputation: 640
I have written a database query to extract information in ColdFusion and I want to know how may I pass a value to the WHERE clause to get the relevant data. This is my code sample. can any one help?
<cfquery name="FILM_STRIP_QUERY" datasource="#dsn#">
select distinct tm.id as teachingmoduleid,
(select concat(prs.first_name, ' ',prs.last_name) AS Video_presenter from presentations pss
inner join topics tpcs on tpcs.id = pss.topic_id
inner join presenters prs on prs.id = pss.presenter_id
where pss.name = ps.name
and tpcs.title = tp.title
) AS video_presenter,
(select pss.43_png from presentations pss
inner join topics tpcs on tpcs.id = pss.topic_id
inner join presenters prs on prs.id = pss.presenter_id
where pss.name = ps.name
and tpcs.title = tp.title) AS png_name
from teaching_modules tm
inner join tm_segments sg on sg.module_id = tm.id
inner join topics tp on tp.id = sg.topic_id
inner join presenters prs on prs.id = tm.presenter_id
left outer join presentations ps on ps.id = sg.presentation_id
where tm.id =
</cfquery>
and this is the calling function
<cfloop = "FILM_STRIP_QUERY">
<!--- this is where I wanna pass the parameter--->
</cfloop>
Upvotes: 2
Views: 4061
Reputation: 1777
If you are using a CFC, then a function like this would work, including the query name ensuring CF releases the memory from the local variable declaration. Also uses the parameter and the cfqueryparam function.
<cffunction name="getFILM_STRIP" access="public" returntype="query" output="false">
<cfargument name="id" required="Yes" type="numeric">
<cfset FILM_STRIP_QUERY = "">
<cfquery name="FILM_STRIP_QUERY" datasource="#variables.dsn#">
<!--- select statement --->
WHERE colname = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value=#arguments.id# />
</cfquery>
<cfreturn FILM_STRIP_QUERY>
</cffunction>
Upvotes: 3
Reputation: 816
Do you mean something like this?
<cfset tmId = 5 />
<!--- or something like <cfset tmId = url.id /> --->
<cfquery name="FILM_STRIP_QUERY" datasource="#dsn#">
<!--- SELECT cols FROM wherever etc... --->
WHERE tm.id = <cfqueryparam cfsqltype="cf_sql_integer" value="#tmId#" />
</cfquery>
You could just do #tmid# without the CFQueryParam tag, but it's a good idea to use it for added security (validation) and the database will also cache the execution plan, hopefully improving performance the next time the query executes.
Upvotes: 5
Reputation: 31920
You should use the cfqueryparam tag to do this. This helps DB execution and also helps prevent SQL injection. e.g.
where tm.id = <cfqueryparam value="#form.ID#" CFSQLType="CF_SQL_INTEGER">
Upvotes: 2